There's a moment in most automation projects where everything stops. The workflow is built, the model is doing its part, the interface works — and then someone tries it against the real customer list and discovers that the same company appears four times under three spellings, half the records have no country, and the field called status contains eleven values, of which six are typos of the other five.
Nothing after that point works properly until this is fixed. And it's almost never in the plan.
So here is the cleanup, in the order we actually do it. You can run the whole thing yourself. It's unglamorous work that pays back regardless of whether you ever build anything on top of it.
Step 1: Decide what the record actually is
Before touching data, answer one question per dataset: what is one row?
It sounds trivial. It isn't. In most CRMs, "customer" is quietly three different things — the legal entity that gets the invoice, the site that receives the delivery, and the person who replies to email. When those three are squashed into one table, every downstream question becomes ambiguous. "How many customers do we have?" has three defensible answers, and any automation you build will pick one at random.
Write down, in one sentence each, what one row means in each of your main tables. Where the sentence needs an "or", you've found a table that needs splitting.
This is the step people skip, and it's the one that causes the expensive rework.
Step 2: Fix identity before anything else
Duplicates are the single most damaging data problem, because they multiply. One duplicated customer becomes duplicated orders, duplicated invoices, and a report that quietly overstates everything.
The work:
- Pick a key. Something that genuinely identifies the entity. For companies, a registration number beats a name every time. For people, an email address beats a name. Names are not identifiers — companies rename, people marry, and everyone abbreviates differently.
- Normalise before comparing. Strip legal suffixes, lowercase, remove punctuation and extra whitespace, then compare. Most duplicate pairs are found by this alone.
- Merge with a rule, not by feeling. Which record survives? Usually the one with the most recent activity. Write the rule down, apply it consistently, and keep a record of what was merged into what — you will need it when someone asks why an old order disappeared.
- Then prevent the next one. A cleanup without a uniqueness constraint or a duplicate check on the entry form is a cleanup you will repeat in a year.
Step 3: Make the important fields mandatory, and reduce them to what's real
Look at the fields you actually use for decisions — status, category, owner, country, value. For each one, count the distinct values.
You will find some version of this everywhere: a status field with active, Active, ACTIVE, aktiv, in progress, In Progress, wip, pending, and one entry that just says ??.
Fix it in three moves:
- Agree a closed list with the people who use the field. Usually the real list is four to six values, not eleven.
- Map the old values onto the new list. Anything unmappable becomes
unknown— explicitly, not silently. - Constrain the input. A dropdown instead of a text box. This is the whole fix. Everything else is temporary.
Free-text fields are fine for notes. They're catastrophic for anything a system has to branch on.
Step 4: Get dates, numbers and currencies into one format
Boring and non-negotiable. The recurring offenders:
- Dates as text.
01/02/2026is 1 February in most of Europe and 2 January in the US, and if both spellings are in the same column, no rule can separate them retroactively. Store dates as dates, in ISO form, in one timezone. - Numbers as text. Amounts with currency symbols, thousands separators, or a comma decimal mark inside a text field. Anything that has to be summed must be stored as a number.
- Mixed currencies in one column with no currency column next to it. Amount and currency belong together, always.
- Empty vs zero vs null. These mean three different things. Decide which is which and be consistent, because a report that treats blank as zero will confidently produce a wrong average.
Step 5: Write down where each dataset comes from and who owns it
The final step isn't cleaning — it's making the cleaning stick.
For each dataset, one line: where it originates, what it feeds, who is allowed to change its structure, and how often it updates.
This little table is what turns a one-off cleanup into a durable asset. Without it, in six months someone will add a column, an automation will break silently, and nobody will be able to say who to ask. With it, the answer takes ten seconds.
If you have the same data in two systems, decide now which one is the source of truth. Not "they should match" — a rule about which one wins when they don't. They will not match.
How long this takes
For a small company with a CRM, an accounting system and a couple of spreadsheets: two to five days of focused work, most of it in steps 2 and 3. It's not a quarter-long data programme, and it doesn't need a platform purchase.
The temptation is to do it in parallel with the automation build. We've tried it both ways and it's slower in parallel, because every decision about the data becomes an interruption to a half-built system.
What this buys you, even if you build nothing
This is the part worth saying plainly: none of the above is AI work. It's the work that makes reporting trustworthy, onboarding faster, and every future system cheaper to build. If your automation project got cancelled tomorrow, the cleanup would still have been worth doing.
And when you do build something, it's the difference between a system that works on the demo data and a system that works on Monday morning.
A five-minute test
Take your main customer table and check:
- Search for one big customer's name. How many records come back?
- Count distinct values in your most-used status field. More than eight?
- Pick ten random rows. How many have a blank in a field you consider essential?
- Ask two colleagues how many active customers you have. Do the numbers match?
Any "yes" to the wrong answer means step 2 or 3 is where your next week goes — and it will be the cheapest week in the whole project.
If you want to pair this with the process side, our process audit guide covers the same idea from the workflow direction: find the real work first, then decide what deserves automating.
