Data Cleansing: The Work Before the AI Project

Marketing platforms have started shipping AI agents whose job is cleaning lead data, which tells you something about where the work actually sits. The glamorous part of an AI project is the model. The part that decides whether it works is whether your records say the same thing about the same customer twice.

What actually goes wrong in a small business database

Problem	How it got there	What it breaks
Duplicate customers	Two people created the same account	Every per-customer total
Inconsistent naming	Free-text fields, no convention	Grouping, matching, joins
Dead contacts	Nobody removes people who left	Outreach, and your sender reputation
Mixed date and number formats	Imports from three systems	Anything time-based, silently
Empty required fields	Nothing forced them at entry	Segmentation and reporting

Mixed date formats deserve singling out, because they fail quietly. A spreadsheet reading 03/04/2026 as March 4th in some rows and April 3rd in others produces a chart that looks perfectly reasonable and is wrong, and nobody checks a chart that looks reasonable.

Do it in this order

Deduplicate first. Every fix you apply before deduplicating has to be applied twice, and the second copy will be missed. Matching on email catches most of it; matching on name plus postcode catches most of the rest.

Standardise formats second. Dates, phone numbers, province and state codes, currency. This is boring and it is what makes the remaining problems visible, because inconsistency hides errors underneath it.

Handle missing values third, and decide per field whether blank means unknown or means zero. Those are different and treating them the same produces confidently wrong averages.

Validate against the outside world last, where you can. Addresses, business numbers, domains that no longer resolve. This step tells you which records are stale rather than merely untidy.

What AI does well, and where to stop it

Fuzzy matching is the genuine strength. Recognising that Acme Ltd and ACME Limited at the same address are one company is exactly the kind of judgment that defeats a rule-based script and comes easily to a model. The same applies to normalising formats and inferring an obvious missing value from surrounding context.

Deciding which of two conflicting records is correct is where it should stop. When one says the contact is Sarah and the other says Michael, the answer depends on knowledge that is not in either record, and a model asked to resolve it will choose one and sound certain.

Run it as a proposal step. The model produces a merge list with its reasoning, a person approves anything that destroys information, and you keep a log of what was merged so a bad decision can be reversed. That split is the same review discipline described in quality assurance when AI writes the first draft.

Clean for a decision, not for tidiness

Cleaning an entire database is the version of this project that runs for a quarter and gets abandoned. Clean the fields that feed the decision you are trying to improve, and leave the rest.

A churn analysis needs one record per customer and accurate order dates. It does not need current job titles. A reorder system needs accurate stock counts and supplier lead times, and nothing else in the record matters to it. Scoping that way turns a quarter into a fortnight.

Deciding what to clean also tends to reveal that the fields you need live in different systems, which is a separate project rather than a cleansing one, as in breaking down data silos.

Fix the tap, not just the puddle

A cleanse without a change at the entry point is a one-time improvement that decays at exactly the rate it always did. Within a year the duplicates are back, because whatever created them is still running.

Three controls cover most of it. A duplicate check when a record is created, which most systems support and few have switched on. Dropdowns instead of free text on the fields you group by. And one named owner per dataset, because shared responsibility for data quality reliably produces none.

Where records arrive from paper or scans, the entry point is the scanning step and its accuracy decides everything downstream, which is covered in turning paper into something useful.

What it unlocks

Clean records are the precondition for the projects people actually want. Churn signals only work when an account appears once, which is the dependency in what your retention data already shows. Any assistant answering questions about your customers inherits whatever your CRM believes.

Running the readiness check before committing to a build is cheaper than discovering it mid-project, and the broader version of that assessment is in is your data ready for AI, with the strategic case in quality data as the real bottleneck.

Frequently Asked Questions

What is data cleansing and why does AI need it?

Data cleansing is finding and fixing records that are duplicated, out of date, inconsistently formatted or simply wrong. AI needs it because a model has no way to know that three spellings of a company name are the same customer, so it treats them as three. Every downstream result, from a churn score to a sales total, inherits that error without any indication that it happened.

In what order should we clean data?

Deduplicate first, because every later fix applied to a duplicate has to be applied again. Then standardise formats, since consistent dates, phone numbers and province codes make the remaining problems visible. Then deal with missing values, then validate against the outside world where you can. Doing it in the other order means repeating work, which is the usual reason these projects stall halfway.

Can AI clean our data for us?

It is genuinely good at the fuzzy matching parts: spotting that two records with slightly different spellings are the same entity, normalising inconsistent formats, and filling obvious gaps from context. It is not good at deciding which of two conflicting records is correct, because that depends on knowledge outside the data. Treat it as proposing merges rather than performing them, with a person approving anything that destroys information.

How much of our data actually needs cleaning?

Less than the full database, in most cases. Clean the records that feed the decision you are trying to improve, rather than every field of every record. A churn analysis needs accurate order dates and one record per customer; it does not need the contact job titles to be current. Scoping by the decision is what keeps the work to weeks rather than quarters.

How do we stop the data getting dirty again?

Fix the entry point, which is usually a free-text field or a process that lets the same customer be created twice. A cleanse without that is a one-time improvement that decays at the rate it always did. The cheapest controls are a duplicate check on creation, dropdowns in place of free text on the fields that matter, and one named owner for each dataset.