Duplicate Customer Records Between Systems: How They Form and How to Stop Them

A duplicate customer is not a technical fault but a missing decision: no single identity key was defined, no system was named master, and so each system creates its own version of the same person. The result is three addresses, two quotations, and a report nobody trusts.

How duplicates form

Row four is the worst of them, because it produces duplicates at a steady rate with nobody doing anything.

How to pick an identity key

In Israel there are three candidates, each with a drawback:

• A normalised phone number - nearly always present, compares well after cleaning, but sometimes shared by several people at the same business.
• A company or VAT number - precise, but collected by only some businesses and absent for private customers.
• Email - convenient, but missing on a substantial share of customers and changed more often than the rest.

The practical answer is one primary key and one secondary: phone as primary, company number as secondary for businesses. A name is never a key - it is only a hint.

Which side is master?

The question sounds technical and is entirely managerial. Three questions settle it:

1. Where the data was first created - usually where it is most accurate.
2. Where it is verified against a document - usually the system that issues invoices.
3. Who actually corrects it when it is wrong.

In a typical business, billing details are mastered in the invoicing system, contact details and preferences in the CRM, and every other system displays only. Simply writing that split on one page solves more problems than any cleaning tool.

A merge process that loses nothing

1. Find candidates by normalised key - not by name.
2. Show both records side by side before any decision.
3. Pick a winning record and keep the differing values from the other in a note rather than deleting them.
4. Move the activity - quotations, documents, conversations - to the winner.
5. Mark the loser as merged rather than deleting it, so old links do not break.
6. Record who merged and when.

Step 5 is what separates a reversible merge from an irreversible one. Deleting looks tidier and produces documents that point nowhere.

How do you stop it happening at all?

• A check on creation: search by normalised phone before allowing a new record.
• Forgiving search: matching half a number and a misspelled name.
• Normalisation on entry: every number stored in one format, without spaces or hyphens.
• Forms that recognise: a customer submitting a form with an existing number attaches to the record instead of creating one.
• One sync direction per integration wherever possible.

Those five are a day or two of configuration and they eliminate most duplication sources permanently. By comparison, manually cleaning thousands of records is a week of work that repeats every year. The recommended order for a one-off clean is in migrating data from Excel into a CRM.

What duplication actually costs

The visible cost is time: someone hunting for which record holds the real information. The second is larger - decisions made on partial data. A report showing 1,200 customers when there are really 950 changes the conversion rate, the cost per lead, and nearly every metric that rests on a count.

The third cost lands on the customer and does the most reputational damage: a reminder sent twice, a quotation contradicting an earlier one, or a rep calling to offer something the customer already bought. The customer does not see a database duplicate - they see a business that does not remember who they are.

Three special cases

• A private customer who became a company. Same person, new company number. That is not a duplicate to delete but two entities to link, because documents belong to each separately.
• A business that changed owners or names. Here you do merge, but keep the old name as a searchable field - otherwise nobody will ever find the history.
• Several contacts at the same business. Not a duplicate at all, and trying to "clean" it is a common error that destroys real information. The correct structure is one company with several contacts beneath it.

The third is where automatic cleaning does the most damage, because all three records share a company name and a main phone number - and any automated tool will classify them as duplicates.

Who owns this in the business

Customer data quality is the one area everybody touches and nobody owns. The fix is not a new role but two short routines: once a month, someone runs a report of records created with a phone number that already existed; once a quarter, someone reviews the last twenty records and checks they look right.

Those two routines take under an hour a month, and they are enough because prevention already does most of the work. What does not work is relying on someone to report it: a rep who finds a duplicate fixes it for themselves and moves on, and never mentions it.

Sources

• Fireberry by Powerlink
• Kala CRM