Duplicate customer records: how to find and merge them safely

Almost every customer register that is a few years old contains duplicates. The same customer registered by the physical shop and by the website, with the name written two different ways; a company entered once with its full legal name and once with its acronym; a person who changed email and signed up again. Duplicates inflate the number of customers, split the purchase history and make the same communication arrive twice. Removing them requires method, because wrongly merging two different people is worse than leaving them separate.

Why duplicates are not identical

Two duplicate records are rarely identical. More often they differ in the details:

An exact comparison finds nothing. The work therefore splits into three phases: making values comparable, identifying candidates, deciding and merging.

Phase 1: normalise

Normalising means transforming each value into a standard form, without altering the original data: you create helper columns or a separate table.

• Text: lower case, multiple spaces reduced to one, leading and trailing spaces removed, accents possibly stripped for comparison.
• Email: lower case and no spaces.
• Phone numbers: digits only, with the international prefix added consistently.
• Addresses: abbreviations brought to a single form («V.» and «Via» become «via»), punctuation removed.
• Names: words sorted alphabetically, so that «Esposito Giuseppe» and «Giuseppe Esposito» match.

SELECT id,
       LOWER(TRIM(email)) AS email_norm,
       REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_norm
FROM customers;

Regular expression syntax varies between databases; alternatively, normalisation can be done with a script.

Phase 2: find the candidates

Exact matches on normalised values

The first pass, and the most reliable, looks for records with the same normalised value on a strongly identifying field: email, tax code, VAT number, mobile phone.

SELECT email_norm, ARRAY_AGG(id) AS ids
FROM customers_norm
GROUP BY email_norm
HAVING COUNT(*) > 1;

Approximate matches

Names and addresses need similarity measures. The most widely used are:

• Levenshtein distance: the minimum number of characters to insert, delete or replace to turn one string into the other. «Rossi» and «Rosi» have a distance of 1;
• trigram similarity: strings are broken into groups of three characters and you measure how many they share. PostgreSQL provides it through the pg_trgm extension;
• phonetic encodings, which give the same code to words that sound alike. The best-known algorithms were designed for English and work poorly with Italian surnames.

Comparing every record with every other quickly becomes impractical: with 100,000 customers there are almost five billion pairs. So you use a blocking key: you compare only records that share something simple, such as the same postcode or the same first three letters of the normalised surname. The number of comparisons drops dramatically.

Combining several clues

A single similar field is not enough. Two different people can both be called Giuseppe Esposito and live in the same city. It is better to assign a score:

• same email: strong clue;
• same phone: strong clue;
• very similar name and same address: strong clue;
• similar name and same city: weak clue.

Pairs with a high score are merged, intermediate ones are reviewed by a person, low ones are discarded.

Phase 3: merge

Merging is the most delicate part, and it must be thought through before touching the data.

1. Choose the primary record, usually the oldest or the most complete.
2. Decide field by field which value to keep: the most recent, the most complete, the verified one.
3. Move the references: orders, invoices, tickets and consents belonging to the secondary record must be updated to point to the primary.
4. Do not delete the secondary straight away: mark it as merged, keeping a reference to the primary. If the merge was wrong, you can go back.
5. Record the operation in a history table: which records, when, by what criterion.

BEGIN;
UPDATE orders SET customer_id = 101 WHERE customer_id = 245;
UPDATE tickets SET customer_id = 101 WHERE customer_id = 245;
UPDATE customers SET merged_into = 101, active = false WHERE id = 245;
INSERT INTO merge_history (primary_id, secondary_id, merged_at, criterion)
VALUES (101, 245, now(), 'same email');
COMMIT;

Everything inside a transaction, so that an error halfway through does not leave orders scattered across two records.

Watch out for privacy consents: if one record has withdrawn consent to communications and the other has not, after the merge the more restrictive or more recent choice must prevail, not the convenient one.

Preventing new duplicates

Cleaning up once is not enough if the causes remain. The most effective countermeasures:

• UNIQUE constraints on normalised values, such as the lower-case email;
• searching for similar customers before creating a new one, in the interfaces staff use;
• a single point of customer creation, instead of several systems each creating customers on their own.

In short

• Real duplicates differ in the details: normalise the values first.
• Exact matches on strong fields are the first and safest pass.
• Names and addresses need similarity measures and blocking keys.
• Merging moves the references, keeps the secondary record and logs the operation.
• Constraints and preventive searches stop duplicates from forming again.