Cleaning your data before you automate
"Garbage in, garbage out" is true and almost useless as advice, because it implies you should clean everything first. Nobody has ever finished doing that. Businesses that try spend six months on a data project, lose the appetite, and never automate anything.
The useful question is narrower: which specific data problems will actually break the automation you are about to build, and which can safely be left alone? Usually the first list is short.
Clean what the automation has to match on. Leave everything else. A field the automation never reads can be as wrong as it likes and nothing breaks. Four problems genuinely break builds: duplicate entities, inconsistent identifiers, free text where a value belongs, and relationships that are wrong rather than missing.
Do it in order: stop it getting worse, clean only the matching keys, deduplicate with a human confirming each merge, normalize in the automation rather than the database, and leave the rest. Poor data is rarely a reason to stop, because the review queue absorbs a great deal of it and continuous cleanup is a legitimate automation in its own right.
INV-001234, INV1234 and 1234. Matching is exact by nature, so every variation is a miss.The four problems that actually break builds
1. Duplicate entities
The big one, by a distance. "Acme Ltd", "ACME Limited", "Acme Limited " with a trailing space, and "Acme" are four records and one company. Any automation that matches an incoming document or inquiry to an existing record will either fail to match or match the wrong one.
It is worse than it looks because it compounds: the automation cannot match, so it creates a fifth record, and now the problem grows at machine speed rather than human speed. Automating on top of duplicates industrializes the mess.
2. Inconsistent identifiers
The same reference written as INV-001234, INV1234 and 1234. Purchase order numbers with and without a prefix. Account codes someone pads with zeros. Matching is exact by nature, so every variation is a miss unless the automation is told about it.
This is often fixable in the automation with a normalization rule rather than in the data, which is cheaper and safer. Worth raising during scoping so it gets designed in.
3. Free text where a value belongs
A date field containing "ASAP". A status field with eleven values, four of which mean the same thing and two of which are typos. A quantity column with "2 boxes" in it. Any rule that branches on these will branch wrongly.
The fix is usually not retrospective cleanup. It is deciding the canonical set of values, mapping the existing variants onto it, and constraining the field going forward so the problem stops growing.
4. Relationships that are wrong rather than missing
A contact attached to the wrong company. An invoice against the wrong project. Missing links are easy. The automation notices nothing is there and asks. Wrong links are dangerous, because everything proceeds confidently and the output is plausible and incorrect.
What you can safely leave
Equally important, and it is where most of the six months goes.
- Fields the automation never reads. Obvious when stated and routinely ignored. An empty industry field does not affect invoice matching.
- Old records outside the working set. If the automation only touches the last eighteen months, 2016 can stay as it is.
- Cosmetic inconsistency. Mixed capitalization, inconsistent phone formats, addresses on one line or two. Normalize in the automation, not in the database.
- Anything a human will review anyway. If it lands in a review queue by design, imperfect data costs a glance rather than a failure.
Finding out what you actually have
Half a day of profiling beats a month of assuming. Five checks, on the fields the automation will use:
- Count distinct values in each field you will match on. A status field with 40 distinct values in a system that officially has 6 tells you everything immediately.
- Sort entity names alphabetically and read them. Duplicates cluster visually. This unsophisticated method finds more than most tooling and takes twenty minutes.
- Check fill rates. What percentage of records have the field populated at all? A field that is 60% empty cannot be a matching key.
- Test the format assumption. Take 100 values from the identifier field and see how many match the pattern you believe it follows. It is rarely 100.
- Look at the newest and oldest records. Data quality usually differs sharply between them, and the difference tells you when the rules changed and whether anyone enforced them.
We do this during scoping. It is a substantial part of why an estimate made after looking at the data differs from one made after a conversation about the data.
Do it in this order
- Stop it getting worse. Before cleaning anything, constrain the inputs: required fields, picklists instead of free text, validation on the forms that create records. Cleaning while the source keeps producing new mess is a treadmill.
- Clean only the matching keys. The entity names and identifiers the automation depends on. Nothing else, yet.
- Deduplicate with a human in the loop. Automated merging on a fuzzy match will merge two genuinely different companies with similar names, and unmerging is worse than the original problem. Propose, let a person confirm, then merge.
- Normalize in the automation, not the database, wherever you can. Rules live in one visible place and can change without a migration.
- Leave the rest. Revisit when something actually needs it.
Keep a record of what was merged into what, and keep it permanently. Someone will eventually ask why a record disappeared, or a merge will turn out to be wrong. Without a merge log there is no answer, and recovery is manual.
Who should do it
Almost always your team rather than ours, and not for the reason you would expect. Deciding whether "Acme Ltd" and "ACME Limited" are the same company sounds mechanical and is not. Sometimes they are genuinely two entities: a parent and a subsidiary, or an old and a new trading name. That is business knowledge, and it lives with you.
What we bring is the profiling that tells you where to look, the shortlist of what actually needs deciding, and the tooling to apply decisions in bulk once made. What we will not do is guess at the answers, because a confidently wrong merge is more expensive than the duplicate was.
Where cleanup is genuinely large, it is quoted separately and visibly rather than hidden inside a build price. If you see "data cleansing" as an unpriced line in a proposal, ask what it covers and who is deciding.
When to automate anyway
Poor data is not usually a reason to stop, and two situations are worth naming.
When the automation itself is the cleanup. Continuous deduplication and enrichment is a legitimate automation in its own right, and one we can price after scoping. If your CRM has drifted over five years, building the thing that keeps it clean is often a better first project than a manual clean that starts drifting again immediately.
When the review queue absorbs it. A design that routes anything ambiguous to a person tolerates far worse data than one that must decide alone. That is a deliberate trade: accept a lower straight-through rate at launch, let the queue catch the mess, and use the corrections to improve both the data and the rules. See designing a review queue.
The only case where we would genuinely say stop is when the matching keys are so unreliable that the automation would be wrong more often than right, and nobody can say which records are correct. That is rare, and when it happens the honest first project is fixing the data, priced as its own piece of work.
Not sure whether your data is good enough?
We profile it during scoping and tell you which specific fields need attention before anything is built, and which you can safely ignore. Free, and you keep the findings.
You will talk to Fayez or Kareem. Nobody else.