UK data cleansing checklist

How to use this checklist

Run the stages in order. Stage 1 and 2 happen before a single value is changed; most cleaning projects that go wrong skipped them. Each item is a yes or no question about your file. Where an item does not apply (no phone numbers, no addresses), cross it out rather than leaving it blank, so the finished sheet shows what was checked and what was out of scope.

The rules behind the checks are the ones on our data cleaning service page and in the guides linked at the end. Nothing here needs a particular tool. A spreadsheet is enough for a file of a few thousand rows; beyond that, script the checks so they can be re-run on the next export.

Stage 1

Before you touch the file

• Keep an untouched copy of the original export, with its date and the system it came from in the filename.
• Record the row count and the column list. Every later stage reconciles back to these two numbers.
• Confirm the lawful basis for holding each category of personal data in the file, and whether any rows are past their retention period and should be deleted rather than cleaned.
• Agree in writing who signs off the cleaning rules and who signs off the finished file. They are often different people.
• Decide what "done" means: which fields must be complete, which formats are mandatory, and what duplicate rate is acceptable.
• Check whether the file will be loaded into another system afterwards, and get that system's field lengths and formats before choosing your own.

Stage 2

Profile it

• Fill rate per column: how many rows have a value, and how many of those values are placeholders such as "N/A", "unknown", "test" or a single full stop.
• Distinct value count per column. A "county" column with 400 distinct values has a spelling problem; a "title" column with 2 has a data-entry rule you need to know about.
• Format mix per column: how many dates are DD/MM/YYYY, how many MM/DD/YYYY, how many are text like "last year".
• Trailing and leading whitespace, double spaces, and non-printing characters. Count them; they hide duplicates.
• Character encoding: search for mojibake such as "é" or "’". If you find any, the file has been through at least one bad conversion and names with accents are already damaged.
• Exact duplicates on the full row, then on the key you actually care about (email, company number, name plus postcode).
• Near duplicates: same email with different capitalisation, same name with and without a middle initial, same address with "Rd" and "Road".
• Write the profile up as a one-page summary. This is the baseline you will compare the cleaned file against.

Stage 3

Agree the rules before changing anything

• One written rule per column: the target format, what happens to values that cannot be converted, and whether the original value is kept in a shadow column.
• Duplicate rule: which fields define a match, how close a fuzzy match has to be, and which of two matching rows survives (most recent, most complete, or the one from the system of record).
• Case rule for names and addresses. Title case breaks "McDonald" and "van der Berg"; decide how you will handle those before you run it.
• Rejects rule: rows that fail a mandatory check go to a separate rejects file with the reason, they are not silently dropped.
• Enrichment rule, if any: which external source, what it may overwrite, and how a conflict between the source and the file is resolved.
• Sign-off on the rules from the person who owns the data, before stage 4 starts.

Stage 4

Standardise

• Trim whitespace and collapse double spaces on every text column.
• Names: strip titles into their own column, fix casing per the agreed rule, flag names that are all one word or contain digits.
• Email: lower-case the domain, trim, validate the syntax, and flag role addresses (info@, sales@) if the file is for one-to-one contact.
• UK postcodes: upper-case, single space before the inward code, validate against the official format, and flag postcodes that are valid in form but do not exist.
• Addresses: split into lines and town and county consistently, expand or abbreviate street types one way only, and verify against Royal Mail PAF or an equivalent where the budget allows.
• Phone numbers: store in one international format, strip formatting characters, and flag numbers with the wrong length for their prefix.
• Dates: convert to ISO 8601 (YYYY-MM-DD), reject impossible dates, and flag dates that fall outside the range the column can sensibly hold.
• Company names: strip legal suffixes into their own column ("Ltd", "PLC", "LLP") so that matching works, and keep the original for display.
• Categorical columns (status, source, segment): map every variant to the agreed list and reject anything unmapped.

Stage 5

De-duplicate

• Run exact-match de-duplication first, on the standardised key, and record how many rows it removed.
• Run fuzzy matching second, and review every match above the threshold that involves a different email or postcode by hand.
• Merge survivors according to the agreed rule; never merge by keeping the first row you happen to see.
• Keep a merge log: which rows were combined into which, so a merge can be undone.

Stage 6

Validate and reconcile

• Row reconciliation: original rows = cleaned rows + rejected rows + rows removed as duplicates. If the sum does not match, stop and find out why.
• Re-run the stage 2 profile on the cleaned file and put the before and after figures side by side.
• Spot-check a random sample of at least 50 rows by hand against the original export.
• Check every mandatory field is now complete, or that its gaps are in the rejects file.
• Load a sample into the destination system, if there is one, and confirm nothing is truncated or mis-typed.

Stage 7

Deliver and keep it clean

• Deliver three files: the cleaned data, the rejects with reasons, and the before-and-after quality report.
• Deliver the rules as a script or a documented procedure that can be run on the next export without starting again.
• Fix the source where you can: a validation rule on the web form or CRM field stops the same problem coming back.
• Set a re-run date. Contact data decays every month, and a file that was clean in January is not clean in June.

The guides behind the checks

• How we hold 99.8% data accuracy for UK clients
• UK postcode data cleaning
• Email data cleansing
• Duplicate customer records
• Data cleaning for a CRM migration
• Data profiling guide