Clean Your Data Before You Automate It
Automation applied to bad data produces wrong outcomes faster and more consistently. Cleaning first is not a delay — it is the project.
Every automation assumes the data is right. Send the invoice to the address on file. Text the number in the record. Segment by the industry field. Each assumption is fine until the field is empty, stale or was filled in by three people using three conventions.
The five checks worth running first
- Completeness: what percentage of records have the fields your automation depends on? Below about 90%, build the exception path before the happy path.
- Duplicates: the same customer under two spellings, with history split between them. Merge before automating, or you will contact both.
- Format consistency: phone numbers with and without country codes, dates in two formats, states as abbreviations and full names.
- Staleness: when was each record last verified? Email addresses decay at a meaningful rate every year.
- Validity: does the email actually exist, does the address resolve, is the tax number correctly formed?
Cleaning without stopping
Do not attempt a big-bang cleanup of everything. Fix the records that matter now — active customers, open opportunities — and set validation at the point of entry so the problem stops growing. Historical records can be cleaned progressively or, honestly, archived. Most databases contain years of data nobody will ever query. This is the shape most of our process automation engagements take.
Keeping it clean
- Validate at entry. Free-text fields where a dropdown belongs are the origin of most inconsistency.
- Make required fields genuinely required, or accept that they are optional and design for empty.
- Assign ownership. Data quality without an owner degrades at a predictable rate.
- Report on quality monthly alongside your other metrics, so decay is visible before it is expensive.
- 90% completeness before automating a field
- 5 checks worth running first
- 1 owner for data quality
Frequently asked questions
Can AI clean our data?
It is genuinely good at standardising formats, detecting probable duplicates and inferring missing categories from context. It cannot know whether a phone number is still in use. Use it for the mechanical work and verification for the factual.
How long does a cleanup take?
For a typical small-business CRM, one to three weeks for the active records, most of it spent on decisions about merge rules rather than the work itself. The historical archive can be deferred indefinitely.
More on this topic: Automation.
Keep reading
Want this built for your business? See what we do.