Cleaning Up Customer Data: The One-Off Process and the Control That Prevents a Repeat

Cleaning a customer list is a few days of work; preventing it happening again is one day of configuration. Businesses that do only the first find themselves back in the same state within six months - which is why the right order is defining the control before starting to clean.

What is actually dirty

Row two is worth fixing first, because it is cheap, automatic, and it is what makes row one detectable.

The working order

1. Back up the current state to a separate file before touching anything.
2. Normalise phone numbers and emails - no spaces, one format, lowercase.
3. Identify duplicate candidates by the normalised key, not by name.
4. Review manually and decide what merges.
5. Merge while preserving the differing values in a note.
6. Flag inactives by last activity date, without deleting.
7. Configure the control that prevents new duplicates being created.

Step 7 alone decides whether the work holds. Without it the list gets dirty again at the pace new records are created, which is usually a few months.

What counts as a duplicate and what does not

A duplicate is the same entity recorded twice. Three situations that look like duplicates and are not:

• Several contacts at one company - the correct structure is one company with people beneath it.
• A private customer who became a company - two entities to link, because documents belong to each separately.
• Two branches of the same customer - sometimes genuinely two records, depending on how you bill.

Automatic merging on name similarity destroys exactly those three, and that damage is nearly impossible to reverse. The full reasoning for telling them apart is in duplicate customer records between systems.

What to do with inactive customers

Do not delete. Flag. A record of a customer who bought once four years ago is information - it says they knew you, what they bought, and what they paid. Deleting erases that too.

What is right is removing them from current metrics and from mailing lists, so they neither inflate counts nor generate complaints. A simple segmentation - active, dormant, inactive - with a last-activity date solves it without losing anything. And when you plan a win-back campaign, that group is exactly who you contact.

The control that prevents a repeat

Four settings, all one-off:

• A check on creation - search by normalised phone before a new record opens.
• Automatic normalisation on entry - every number stored in one format.
• Minimal mandatory fields - so people do not invent values to move on.
• Blocking imports that overwrite or duplicate existing records.

The fourth is the forgotten one and is responsible for most relapses: an old file loaded again restores everything you cleaned. The same loading logic appears in migrating data from Excel into a CRM.

How long it takes

On a file of a few thousand records, normalisation and detection are hours; the manual merge decisions are most of the work, usually a few concentrated days. What stretches it into weeks is trying to do it "in between other things" - so allocate three consecutive days and finish.

What to avoid is a partial cleanup that stops halfway. A half-normalised list is worse than two lists, because there is no way to know which part to trust - and that is precisely the state that makes people stop using search.

How to measure it worked

Three numbers before and after: total records, duplicates by the key, and records with no valid phone number. After the control is in place, measure a fourth monthly - how many new records were created with a phone number that already existed. That number should be zero, and when it is not, the control is not working.

What if the data lives in several places?

That is the situation in most businesses: a list in the CRM, a list in the invoicing software, and a file on somebody's computer. Cleaning only one of them improves little, because the other two keep producing new records in their own format.

The order that works is choosing one source of truth for customer details - usually the system that issues documents, because the data there is verified against an invoice - cleaning it, and then aligning the others to it. What matters is not cleaning them all in parallel: that produces three different versions of "clean", and then there is no way to know which is right.

As for somebody's personal file, the answer is almost never to delete it but to understand why it exists. In most cases it exists because something in the system is inconvenient - and deleting it alone means it reappears under another name within a fortnight.

The most expensive mistake in a cleanup

Deleting so the list looks clean. A list of 800 real customers beats 3,000 records, but only when the 2,200 removed genuinely were not customers. When deletion follows "looks irrelevant", it usually also removes people who bought once and could buy again.

The simple rule is that permanent deletion is reserved for clear cases only - empty records, tests, and duplicates that were merged. Everything else gets flagged. That is also what lets you go back if it turns out you were wrong, an option that does not exist after deletion.

Sources

• How to improve business efficiency
• Israel Tax Authority - information for businesses