Data Hygiene: How to Clean a Messy CRM Before You Automate It

Short answer: Clean a CRM in four passes: standardise formats, deduplicate against a defined matching key, fill or explicitly mark gaps, and validate against an external source where possible. Do this before building automation, because automation on dirty data multiplies errors rather than revealing them. Budget more time than seems reasonable — cleaning usually takes longer than the build that follows.

Why cleaning has to come first

Automation and AI are amplifiers. Feed a workflow a database with three records for one company, and it will send three emails, create three projects, or report three clients. It won't flag the problem, because from its perspective there is no problem — the data says three.

The cost of cleaning first is a few weeks. The cost of cleaning after go-live is the same work plus unpicking whatever the automation did in the meantime.

Pass 1: Standardise formats

Mechanical and high-value. Working systematically through each field type:

• Names — consistent capitalisation, no trailing whitespace, legal suffixes handled consistently (Ltd, Limited, LTD are one thing).
• Emails — lowercase, validated syntax, obvious typos in common domains corrected.
• Phone numbers — one format, ideally international.
• Addresses — split into components rather than one text blob; postcodes uppercased and space-normalised.
• Dates — real date fields, one format. Mixed formats are a common source of silently wrong records.
• Status and category fields — mapped to a controlled option list, with the variants recorded so you know what mapped to what.

Pass 2: Deduplicate

Define your matching key before you start. In order of reliability:

1. A registered identifier — company number, VAT number. Definitive where available.
2. Email domain plus normalised company name. Strong for B2B.
3. Normalised name plus postcode. Good, with caveats for multi-site organisations.
4. Fuzzy name matching alone. Use as a candidate generator with human review, never as an automatic merge rule.

When merging, keep the most complete version of each field rather than the most recent record wholesale, and log every merge. Someone will ask where a record went.

Pass 3: Handle gaps explicitly

Empty fields carry meaning that must be made explicit. For each important field, decide whether blank means unknown, not applicable, or genuinely empty — and encode that distinction rather than leaving it ambiguous. Then decide which gaps are worth filling: for most businesses, missing email on active clients matters and missing sector on dormant leads doesn't.

Pass 4: Validate against an external source

Where a public register exists, use it. For UK B2B data, Companies House gives you authoritative company names, numbers, status and registered addresses. Matching your records against it resolves naming variants and surfaces dissolved companies still sitting in your pipeline.

One caution from experience: name-based matching against a register produces false positives at a rate that will surprise you. Match on identifiers where possible, and treat name-only matches as candidates requiring review rather than as confirmed.

How to keep it clean

Cleaning is a project; hygiene is a practice.

• Enforce field types and required fields at entry.
• Restrict record creation to defined routes — forms and automations, not free typing.
• Run a monthly duplicate check rather than an annual clean.
• Add a data quality view: records missing critical fields, surfaced where someone will see them.
• Give the dataset an owner.

Frequently asked questions

How long does a clean take?

For a few thousand records with typical mess, one to three weeks. For tens of thousands with multiple sources, considerably more — and it should be scripted rather than manual.

Should I clean everything or just active records?

Clean active records thoroughly, dormant records lightly, and archive anything untouched for several years rather than cleaning it.

Can AI do the cleaning?

It's genuinely useful for standardisation and for proposing duplicate candidates. Merge decisions and validation should stay human — an incorrect automatic merge is very hard to reverse.

We've cleaned and migrated datasets from a few thousand to nearly a million records. Tell us what you're working with.