How to Clean Up a Customer List With AI

"Clean up my customer list" is three different jobs wearing one name, and only two of them should ever be handed to a model without a human checking every row. Sorting them out first is what separates a spreadsheet that is finally usable from one that quietly invented six customers who do not exist.

The three jobs:

1. Normalisation. Making the same thing look the same. "St." and "Street", "Ltd" and "Limited", phone numbers in four formats, names in ALL CAPS. Mechanical, verifiable, safe to automate.

2. Deduplication. Deciding that two rows are the same customer. Mostly safe, with a review queue for the uncertain middle.

3. Enrichment. Filling in gaps: guessing an industry from a company name, inferring a job title, completing a partial address. This is the one that fabricates, and it should never be applied without a source.

Most disasters come from running all three in one prompt and treating the output as finished.

Before you touch anything: take a copy and a count

Two minutes that save an afternoon.

Export the raw list to a file you do not edit again. Then record four numbers: total rows, rows with an email, rows with a phone, and count of distinct email addresses. Those four are your before-picture. Every step below should change them in a direction you can predict, and a step that changes them in a direction you did not predict is a step that went wrong.

Job one: normalisation

This is the part where AI earns its keep with no real risk, because every change is checkable by looking at it.

Work in batches of 50 to 100 rows rather than pasting 4,000 at once. Long inputs are where models start dropping rows silently, and a dropped row is much harder to notice than a wrong one.

A prompt that behaves:

Normalise the rows below. Return the same number of rows, in the same order, as CSV.

Rules:
- Company names: expand Ltd to Limited, remove trailing punctuation, keep original capitalisation
  of acronyms.
- Person names: Title Case, but leave McDonald, O'Brien and van der Berg style names intact.
- Phone numbers: E.164 format, assume +44 if no country code and the number starts 0.
- Emails: lowercase, trim whitespace.
- Do not change any other field.
- If a value is ambiguous, leave it exactly as it is and add UNSURE in the notes column.

Rows:
[paste 50 rows]

Three things in that prompt do the work. Demanding the same row count in the same order makes drops detectable with a formula. Naming the specific edge cases stops well-meaning damage. The UNSURE escape hatch gives the model somewhere to put uncertainty other than a confident guess. That last technique generalises, and we wrote about it in how to make AI say I don't know.

Check after every batch: does the output have exactly the rows you sent, in order? If not, halve the batch size.

Job two: deduplication

Duplicates are rarely identical. They are "Acme Ltd" and "ACME Limited", or the same person at an old and new email, or one record with a typo in the postcode.

The workflow that works is a confidence split, not a yes-or-no.

Ask the model to group candidate duplicates and score each group, then handle the three bands differently:

Group these rows into sets that refer to the same customer.

For each group return: the row IDs, a confidence value of high, medium or low, and one
sentence naming the specific evidence.

Rules:
- Two different people at the same company are NOT duplicates.
- A shared generic email (info@, hello@) is weak evidence, not strong evidence.
- Never merge rows with different payment or account identifiers.
- When the evidence is a name similarity only, the answer is low.

Then merge only the high band automatically, and only after you have read ten of them. The medium band is a work queue, not a result.

The rule about two people at the same company is worth stating explicitly every time. Left to itself, a model will helpfully collapse a company's whole contact list into one row.

Job three: enrichment, and why to be strict about it

Ask a model to fill in a missing industry, company size, or job title and it will produce something plausible for every row, including the rows where it has no idea. There is no signal in the output distinguishing recall from invention. That is not a flaw you can prompt your way out of, it is what a hallucination is.

Two safe patterns:

• Derive, do not guess. Deriving a country from a dialling code, or a domain from an email, is a transformation of data you already hold. That is fine.

• Source, then extract. If you have a real source, a website, a filed record, a form the customer completed, give it to the model and ask it to extract. That is reading, not guessing.

Anything else stays blank. A blank field is honest and a wrong one is expensive, and under the UK GDPR accuracy principle inaccurate personal data is data you are obliged to correct, which makes an invented job title a liability rather than a nice-to-have.

Verify with a real sample, not a vibe

Before you replace the live list, pull 30 rows at random from the cleaned version and check them against the original by hand. Thirty is enough to catch a systematic error, and systematic errors are what actually happen: a rule applied too broadly, a column shifted by one, a batch that came back short.

What to look for:

• Row count matches the original minus intentional merges. Nothing else should have vanished.

• Distinct email count moved only by the number of merges you approved.

• No field contains text that reads like an explanation rather than a value. "Likely a technology company" in an industry column means enrichment leaked in.

• Spot-check five merges specifically. Merges are the only irreversible step.

If the 30-row sample is clean, ship it. If two or more rows are wrong, the error is systematic, and re-running with a tighter prompt is cheaper than fixing by hand.

What to keep out of the model entirely

Not everything in a customer list should be pasted into a chat window. Payment details, identity document numbers, and anything a customer gave you under a confidentiality expectation belong in a redacted column before the file goes anywhere. Strip them, run the clean, join them back on the row ID. The wider question of what is safe to send is covered in is it safe to give AI access to my data.

Keeping it clean afterwards

A cleaned list decays. The cheap habit that stops it decaying is validation at the point of entry: normalise phone numbers and emails on the form, not in a quarterly cleanup. Doing this once a year with a model is fine. Doing it monthly means the intake is broken.

Once the list is trustworthy, it becomes usable for the things people actually want it for, like categorising expenses and transactions or winning back lapsed customers. The broader picture of where AI fits in a small operation is in our AI for small business overview.

FAQ

How big a batch can I paste into a model?

Start at 50 rows. The failure mode is silent row drops, not errors, so the safe size is whatever keeps output row count equal to input row count every time. Wide rows with many columns need smaller batches.

Will AI delete customers by mistake?

It will if you let it merge on its own judgement. Automate only the highest-confidence merges, keep everything else in a review queue, and never merge rows that carry different account or payment identifiers.

Can AI fill in missing industries or company sizes?

Not reliably, and not safely. It produces a plausible answer for every row including the ones it knows nothing about. Fill gaps only by deriving from data you hold or by extracting from a real source you supply.

Should I do this in a spreadsheet or a database?

Whichever holds the truth. If your CRM is the system of record, clean an export and import the reviewed result rather than editing both. Two half-cleaned copies is worse than one dirty one.

A clean customer list is also the backbone of accurate books. If you're the one reconciling those records yourself, see how a solo bookkeeper can use AI without hiring staff for the rest of that workflow.