How to Deduplicate Customer Data

Customer data deduplication is the process of identifying and merging duplicate records that represent the same customer across one or more databases. It's the most common entry point to identity resolution — every organization with a CRM, billing system, or marketing platform has duplicates.

This guide walks through the complete deduplication process: profiling your data, choosing a matching strategy, defining survivorship rules, and automating the pipeline.

Why Duplicates Exist

Duplicates aren't a sign of bad data management — they're an inevitable consequence of how organizations collect data:

Cause	Example
Multiple entry points	Customer signs up on website, is also added by sales rep
Name variations	"Robert Smith" in CRM, "Bob Smith" in billing
Format differences	"123 Main St" vs "123 Main Street" vs "123 Main St."
Typos	"[email protected]" vs "[email protected]"
System migrations	Records imported from legacy system without dedup
Mergers & acquisitions	Two companies with overlapping customer bases
Lack of shared identifiers	CRM uses email, support uses ticket ID, billing uses account number

The cost of duplicates compounds over time:

• Marketing waste: Sending the same campaign to the same person 3 times costs 3x and annoys the customer
• Inflated metrics: 50,000 "customers" might be 35,000 real people — planning based on inflated counts leads to bad decisions
• Poor customer experience: Support agent sees 3 separate records and asks the customer to repeat themselves
• Compliance risk: GDPR "right to be forgotten" requires finding all records for a person — duplicates make this impossible without dedup
• Revenue leakage: Duplicate invoices, missed cross-sell opportunities, incorrect billing

Step 1: Profile Your Data

Before writing a single matching rule, understand what you're working with.

Identify Your Sources

List every system that contains customer records:

Source	Records	Key Fields	Primary Key
CRM (Salesforce)	120,000	name, email, phone, address	contact_id
Billing (Stripe)	85,000	name, email, card_last4	customer_id
Support (Zendesk)	200,000	name, email	ticket_requester_id
Marketing (Mailchimp)	150,000	email, name	subscriber_id

Assess Data Quality

For each field, measure:

• Completeness: What percentage of records have a non-null value?
• Uniqueness: How many distinct values exist? (High uniqueness = good discriminator)
• Consistency: Are formats consistent? ("555-123-4567" vs "+15551234567" vs "5551234567")

import pandas as pd

df = pd.read_csv("customers.csv")

# Completeness
print(df.notna().mean())
# email     0.92
# phone     0.68
# address   0.75
# name      0.99

# Uniqueness (approximate)
print(df.nunique() / len(df))
# email     0.87  ← good discriminator
# phone     0.72  ← good discriminator
# address   0.65  ← moderate
# name      0.45  ← poor (many "John Smith"s)

Fields with high completeness and high uniqueness are your best matching candidates. Email and phone are typically the strongest.

Estimate Duplicate Rate

A rough estimate helps set expectations:

# Quick duplicate check on email
email_dupes = df.groupby('email').size()
estimated_dupe_rate = (email_dupes > 1).sum() / len(email_dupes)
print(f"Estimated duplicate rate (email): {estimated_dupe_rate:.1%}")
# Estimated duplicate rate (email): 12.3%

Most B2B databases have 10-30% duplicates. Consumer databases can be higher.

Step 2: Normalize Your Data

Raw data must be cleaned before comparison. Normalization doesn't change the original records — it creates standardized versions used only for matching.

Common Normalizations

Field	Raw	Normalized
Name	"Robert J. Smith Jr."	"robert smith"
Email	"[email protected]"	"[email protected]"
Phone	"(555) 123-4567"	"5551234567"
Address	"123 Main St., Ste. 4"	"123 main st ste 4"
Company	"Acme Corp."	"acme"

Normalization Checklist

• Case: Lowercase everything
• Whitespace: Collapse multiple spaces, trim leading/trailing
• Punctuation: Remove periods, commas, hyphens (for phones)
• Titles and suffixes: Remove "Mr.", "Mrs.", "Jr.", "III"
• Email: Remove dots before @, remove +tags, lowercase domain
• Phone: Strip country code, parentheses, dashes, spaces → digits only
• Company: Remove "Inc.", "LLC", "Corp.", "Ltd."
• Address abbreviations: "St" → "street", "Ave" → "avenue" (or the reverse — pick one and be consistent)

Step 3: Choose Your Matching Strategy

Single-Source Deduplication

If you're deduplicating within a single database:

Strategy: Find duplicate clusters within one dataset
Blocking: Group by email domain + first 3 chars of last name
Matching: Compare pairs within each block

Cross-Source Deduplication

If you're matching across multiple databases:

Strategy: Link records across sources, then find clusters
Blocking: Multiple passes (email, phone, name+zip)
Matching: Compare pairs from different sources within each block

Matching Rules

Start with the strongest identifiers and add fuzzy rules as needed:

Tier 1 — Exact match (high confidence)

Rule	Example Match
Email exact	[email protected] = [email protected]
Phone exact	5551234567 = 5551234567
SSN/Tax ID exact	123-45-6789 = 123-45-6789

Tier 2 — Fuzzy match (medium confidence)

Rule	Algorithm	Threshold	Example Match
Name similar	Jaro-Winkler	> 0.9	"Robert Smith" ↔ "Rob Smith" (0.91)
Address similar	Jaro-Winkler	> 0.85	"123 Main St" ↔ "123 Main Street" (0.92)
Name + City	Jaro-Winkler + Exact	> 0.88 + exact	"Bob Smith, Chicago" ↔ "Robert Smith, Chicago"

Tier 3 — Compound rules (catch remaining matches)

Rule	Logic
Name + Address	Name JW > 0.85 AND Address JW > 0.80
Name + Phone prefix	Name JW > 0.90 AND first 7 phone digits match
Email domain + Name	Same email domain AND Name JW > 0.92

Setting Thresholds

• Too high (e.g., 0.98): You'll miss real duplicates with minor variations
• Too low (e.g., 0.70): You'll merge different people who happen to have similar names
• Start at 0.85: This is a good default for most name/address comparisons
• Use a review zone: Records scoring between 0.65 and 0.85 should be flagged for human review, not auto-merged

Step 4: Define Survivorship Rules

When duplicates are found, you need to decide which values survive into the canonical (golden) record.

Common Strategies

Strategy	Rule	Best For
Source priority	CRM values override billing values	When one source is authoritative
Most recent	Use the most recently updated value	Addresses, phone numbers
Most complete	Use the non-null or longest value	Names (full name > abbreviation)
Aggregation	Combine values from all sources	Email addresses, tags

Field-Level Survivorship

Different fields may need different strategies:

Field	Strategy	Rationale
Name	Source priority (CRM)	Sales reps verify names
Email	Aggregation (keep all)	Customer may use different emails for different purposes
Phone	Most recent	People change phone numbers
Address	Most recent	People move
Account tier	Highest value	Don't downgrade a customer
Created date	Earliest	True first-contact date

Step 5: Implement the Pipeline

Option A: Kanoniv (Declarative)

Define your entire dedup pipeline in a YAML spec:

entity:
  name: customer
sources:
  - name: crm
    adapter: csv
    location: crm_export.csv
    primary_key: contact_id
  - name: billing
    adapter: csv
    location: stripe_customers.csv
    primary_key: customer_id
  - name: support
    adapter: csv
    location: zendesk_users.csv
    primary_key: requester_id
rules:
  - name: email_exact
    type: exact
    field: email
    weight: 1.0
  - name: phone_exact
    type: exact
    field: phone
    weight: 0.95
  - name: name_address
    type: composite
    children:
      - type: jaro_winkler
        field: name
        threshold: 0.9
      - type: jaro_winkler
        field: address
        threshold: 0.85
survivorship:
  strategy: source_priority
  priority: [crm, billing, support]
decision:
  thresholds:
    match: 0.85
    review: 0.65

from kanoniv import Spec, Source, reconcile, validate

spec = Spec.from_file("dedup-spec.yaml")
validate(spec).raise_on_error()

sources = [
    Source.from_csv("crm", "crm_export.csv"),
    Source.from_csv("billing", "stripe_customers.csv"),
    Source.from_csv("support", "zendesk_users.csv"),
]

result = reconcile(sources, spec)

print(f"Input records: {result.total_input_records}")
print(f"Golden records: {len(result.golden_records)}")
print(f"Merge rate: {result.merge_rate:.1%}")
print(f"Review pairs: {len(result.review_pairs)}")

Option B: pandas (Manual)

For simple, single-field dedup on small datasets:

import pandas as pd
from thefuzz import fuzz

df = pd.read_csv("customers.csv")

# Exact email dedup
exact_dupes = df[df.duplicated(subset=['email'], keep=False)]
print(f"Exact email duplicates: {len(exact_dupes)}")

# Fuzzy name dedup (slow — O(n²), only for small datasets)
from itertools import combinations

fuzzy_matches = []
for i, j in combinations(df.index, 2):
    name_score = fuzz.ratio(df.loc[i, 'name'], df.loc[j, 'name'])
    if name_score > 90:
        fuzzy_matches.append((i, j, name_score))

This approach works for a few thousand records but doesn't scale. For anything beyond toy datasets, use a tool with blocking.

Step 6: Validate Results

Spot-Check Matches

Review a random sample of matches to verify quality:

# Sample 20 matched pairs and inspect
sample = result.matched_pairs.sample(20)
for pair in sample:
    print(f"Record A: {pair.record_a}")
    print(f"Record B: {pair.record_b}")
    print(f"Score: {pair.score:.2f}")
    print(f"Rule: {pair.matched_rule}")
    print("---")

Look for:

• False positives: Different people merged together (too aggressive)
• False negatives: Same person in different clusters (too conservative)
• Field conflicts: Cases where survivorship produced unexpected results

Measure Quality

If you have labeled data (known true matches and non-matches):

Metric	Formula	Target
Precision	True matches / All matches	> 95%
Recall	Found matches / All true matches	> 90%
F1	2 × (P × R) / (P + R)	> 92%

Common Issues

Symptom	Likely Cause	Fix
Too many false positives	Threshold too low	Raise match_threshold
Too many false negatives	Rules too strict	Add fuzzy rules, lower thresholds
Giant clusters (100+ records)	Chain linking	Add cluster size limits, stricter clustering
Name mismatches merging	Name JW threshold too low	Raise name threshold to 0.92+
Address mismatches merging	Address comparison too lenient	Use token-based comparison, require city match

Step 7: Operationalize

Deduplication isn't a one-time project. New duplicates enter your systems every day.

Batch Dedup

Run deduplication on a schedule:

Daily: Process new records added in the last 24 hours
Weekly: Re-evaluate existing clusters with updated data
Monthly: Full reconciliation across all sources

Real-Time Dedup

Resolve duplicates as records arrive:

New record arrives → Query identity graph → Match found?
  Yes → Merge into existing golden record
  No  → Create new golden record

Monitoring

Track these metrics over time:

• Match rate: Percentage of records that match an existing entity (should be stable)
• New entity rate: Percentage of records that create new entities (should be stable)
• Review queue size: Number of borderline matches awaiting review (should not grow unbounded)
• Cluster size distribution: Watch for abnormally large clusters (chain linking)

Deduplication Checklist

1. Profile your data: sources, fields, completeness, uniqueness
2. Normalize fields: case, whitespace, formatting, abbreviations
3. Start with exact matching on strong identifiers (email, phone)
4. Add fuzzy matching for names and addresses with conservative thresholds
5. Define survivorship rules per field
6. Validate with spot checks and precision/recall measurement
7. Automate with scheduled batch runs and/or real-time resolution
8. Monitor match rates, review queues, and cluster sizes over time

Frequently Asked Questions

How long does deduplication take?

For batch processing: seconds to minutes for datasets under 1 million records with proper blocking. Kanoniv's local SDK processes ~100K records in under 10 seconds on a laptop. Without blocking, the same dataset would take hours.

Should I deduplicate before or after loading data into my warehouse?

Both. Before loading catches obvious duplicates early and prevents them from polluting downstream analytics. After loading (batch reconciliation) catches duplicates across sources that can only be identified when all data is available.

What if I can't afford to merge records automatically?

Use a review workflow. Set a conservative match threshold so only high-confidence matches are auto-merged. Flag everything else for human review. Even reviewing 100 borderline pairs per week dramatically improves data quality over time.

How do I handle "household" deduplication?

Household deduplication groups records by physical address or relationship rather than individual identity. Two people at the same address are different entities but the same household. This requires a different entity model — define a "household" entity in addition to a "person" entity, with different matching rules (address-based rather than name-based).

What about real-time deduplication in a streaming pipeline?

Real-time dedup processes one record at a time against a persistent identity graph. When a new record arrives, the system checks if it matches any existing entity and either merges or creates a new one. Kanoniv Cloud provides a resolution API for this use case with sub-millisecond latency.