Key takeaways
- Normalise names, emails, phone numbers, and addresses before comparing them.
- Use deterministic rules first and reserve fuzzy matching for the residue.
- Block candidate pairs so the comparison set stays manageable.
- Merge through a crosswalk and review uncertain matches, not the obvious ones.
On this page
Why duplicates survive
Customer records arrive through several routes: a webshop, a sales spreadsheet, an accounts package, and a support inbox. Each route may create its own record, with its own spelling and its own identifier. Without a shared key, the same customer appears more than once.
Duplicates distort customer counts, revenue per customer, segmentation, and communication. They also create operational risk when one record is updated and another is not. Deduplication is therefore a matching problem and a process problem.
Define the entity and the surviving record
Before writing rules, define what a customer is for this purpose. A billing entity, a delivery address, and a contact person are different things. Decide which one the master record represents, and record the decision.
Define survivorship too: which value wins when two records disagree. The most recent record may be right for an address, while the earliest may be right for the customer since date. Write the rules per field rather than choosing one record wholesale.
Normalise before comparing
Most matching failures start with formatting. Trim spaces, lower-case email addresses, remove punctuation from phone numbers, and standardise country codes and address abbreviations. Store the original values alongside the cleaned ones so nothing is lost.
The query below creates comparison keys without changing the source data. It keeps the raw email for display and adds a normalised version for matching.
SELECT
customer_id,
email,
LOWER(TRIM(email)) AS email_key,
REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_key,
UPPER(TRIM(postcode)) AS postcode_key
FROM customers_raw;Use deterministic rules first
Deterministic rules match records on a strong identifier: exact email, exact phone, tax number, or a combination of postcode and house number. They are precise and easy to explain. Use them to catch the clear cases before any scoring. The table below compares common rules; only the strongest belong in the deterministic pass.
Combine fields carefully. An email alone can be shared by two people in the same household or reused after a closure. A phone number can be a switchboard. A rule that is too broad creates false merges that are harder to undo than duplicates.
| Rule | Signal strength | Main risk |
|---|---|---|
| Exact tax or company number | Very strong | Missing or reused identifiers |
| Exact email | Strong | Shared or recycled addresses |
| Exact phone | Moderate | Household or switchboard numbers |
| Postcode plus house number | Moderate | Shared buildings and sub-units |
| Name plus address similarity | Weak to moderate | Common names and spelling variants |
Add fuzzy matching with blocking
Fuzzy matching compares strings that are similar but not identical. Edit distance counts the minimum single-character edits needed to turn one string into another; token-based methods compare words; phonetic methods group names that sound alike. A threshold turns a score into a candidate match.
Comparing every record with every other record grows quickly. Blocking reduces the set by comparing only records that share a key, such as the first three characters of a surname plus the postcode. Choose blocking keys that are stable and not too broad.
- Normalise the field before applying a similarity score.
- Use different thresholds for different fields; a name match is weaker than an email match.
- Keep the score and the matched fields for audit.
- Do not let a chain of weak matches merge unrelated records.
Review, merge, and prevent recurrence
Split candidates into three groups: automatic matches above a high threshold, automatic non-matches below a low threshold, and a review queue in between. A person reviews the middle group with the evidence visible, not a bare score.
Merge through a crosswalk table that maps each source record to the surviving customer ID. Keep the source records and the match decision. That makes the merge reversible and lets future reports trace a customer back to the original systems.
Finally, fix the intake. Validate and match new records at creation, and warn when a likely duplicate is entered. Deduplication that runs only as a periodic clean-up will keep finding the same problem.
- Record the rule, score, reviewer, and decision for every merge.
- Suppress merged records from counts rather than deleting them.
- Monitor the duplicate rate by source to find the worst intake channel.
- Re-run matching after major system changes.
Frequently asked questions
Should duplicate customers be deleted?+
Usually no. Mark one as the surviving record, keep the others linked through a crosswalk, and exclude them from reporting counts. Deletion loses history and is hard to reverse.
Is fuzzy matching reliable enough to run automatically?+
Only above a high threshold, and preferably for low-risk fields. Keep a review queue for uncertain matches and record the evidence.
Which fields matter most?+
Stable identifiers such as a tax number or account number matter most. Email and phone are useful but can be shared. Names and addresses are weaker signals on their own.
How are duplicates prevented from returning?+
Match at the point of entry, require a strong identifier where possible, and review the sources that produce the most duplicates.
Need help applying this?
Get help with Data Cleanup & Pipelines.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
How to build a useful data dictionary
How to document datasets so people can find, trust, and use them: scope, definitions, grain, keys, ownership, and maintenance.
Data quality dimensions with practical checks
A practical guide to the six data quality dimensions, the checks that test each one, and how to decide which failures should stop a report.