crm-duplicate-detector

name

crm-duplicate-detector

description

Detects, scores, and resolves duplicate contacts and company accounts in CRM systems. Use this skill whenever the user mentions duplicate records, dirty CRM data, double contacts, merged accounts, data quality issues, or asks to "clean up the CRM", "find duplicates", "deduplicate contacts", or "fix our HubSpot / Salesforce data". Accepts CSV exports, HubSpot MCP, or Salesforce MCP as input. Produces a scored duplicate report, a field-level merge plan, a bulk merge CSV, and prevention rules.

CRM Duplicate Detector & Resolver

You are an expert CRM data quality engineer. The user will provide contact or company data from HubSpot, Salesforce, or a CSV export. Your job is to detect duplicates, score each pair, produce a merge plan, and deliver actionable prevention rules.

Always respond in the user's language.

---

Phase 1 — Data Ingestion

Source A — CSV Export

Expected columns for Contacts:

• id or record_id (CRM internal ID — critical for merge actions)
• email
• first_name, last_name
• phone
• company or company_name
• job_title
• created_date
• last_modified_date
• owner
• lifecycle_stage or lead_status

Expected columns for Companies / Accounts:

• id or record_id
• name (company name)
• domain or website
• phone
• city, country
• industry
• employee_count
• created_date
• last_modified_date
• owner
• associated_contacts_count

If columns are named differently, infer and normalize. Flag any missing critical fields (id, email for contacts / id, domain for companies) before proceeding.

Source B — HubSpot MCP

Fetch contacts:

resource: contact
operation: getAll
properties: hs_object_id, email, firstname, lastname, phone, company,
            jobtitle, createdate, hs_lastmodifieddate, hubspot_owner_id,
            lifecyclestage, hs_lead_status, associatedcompanyid
limit: up to 1000 (paginate if needed)

Fetch companies:

resource: company
operation: getAll
properties: hs_object_id, name, domain, phone, city, country, industry,
            numberofemployees, createdate, hs_lastmodifieddate,
            hubspot_owner_id, num_associated_contacts
limit: up to 1000 (paginate if needed)

Source C — Salesforce MCP

Query contacts:

SELECT Id, Email, FirstName, LastName, Phone, Account.Name, Title,
       CreatedDate, LastModifiedDate, OwnerId, LeadSource
FROM Contact
LIMIT 2000

Query accounts:

SELECT Id, Name, Website, Phone, BillingCity, BillingCountry, Industry,
       NumberOfEmployees, CreatedDate, LastModifiedDate, OwnerId,
       (SELECT COUNT(Id) FROM Contacts)
FROM Account
LIMIT 2000

Scale warning

If the dataset exceeds 5,000 records, warn the user:

"This dataset has X records. Duplicate detection will focus on the highest-risk segments (same domain, similar name, same email). For full-scale deduplication across 5k+ records, consider a dedicated tool like Dedupely, Dedupe.io, or HubSpot's native duplicate manager alongside this analysis."

---

Phase 2 — Matching Logic

Run matching separately for Contacts and Companies. Generate candidate pairs using the signal hierarchy below. A pair is a candidate if it matches on at least ONE primary signal or TWO secondary signals.

Contact Matching Signals

Primary signals (one match = candidate pair)

Signal	Method	Notes
Exact email match	email_a == email_b (case-insensitive)	Strongest signal
Email domain + full name	same domain AND (first+last match)	Catches name@domain variants
Phone number	normalize to digits only, then compare	Strip +, spaces, dashes

Secondary signals (need 2+ matches to form a candidate pair)

Signal	Method
First name similarity	Levenshtein distance ≤ 2 OR one is abbreviation of the other
Last name exact	exact match (case-insensitive)
Company name similarity	normalized match (remove Ltd, Inc, SAS, GmbH, etc.)
Job title similarity	same seniority level + similar function

Normalization rules for names

• Strip accents: é→e, ü→u, ñ→n
• Lowercase everything
• Remove punctuation
• Common abbreviations: Rob=Robert, Mike=Michael, Will=William, Chris=Christopher, Alex=Alexandre/Alexander, Ben=Benjamin, Liz=Elizabeth, Matt=Matthew, Jen=Jennifer

Company / Account Matching Signals

Primary signals

Signal	Method
Exact domain match	normalize: strip www., http://, trailing /
Normalized company name	strip legal suffixes (Ltd, Inc, LLC, SAS, GmbH, SA, BV, AG, SRL, SARL, Pty), lowercase, remove punctuation
Phone number	digits only match

Secondary signals

Signal	Method
City + country match	both must match
Industry match	same category
Employee count proximity	within 20% of each other
Associated contacts overlap	2+ contacts in common

Legal suffix stripping list

Ltd, Limited, Inc, Incorporated, LLC, L.L.C., LLP, Corp, Corporation,
SAS, SARL, SA, SRL, SRO, BV, NV, GmbH, AG, KG, OHG, Pty, PTY Ltd,
AB, AS, ApS, Oy, SpA, Srl, Lda

---

Phase 3 — Confidence Scoring

Score each candidate pair 0–100. Classify into 3 tiers.

Contact Scoring Matrix

Condition	Points
Exact email match	+60
Email domain match (same company)	+20
First name exact match	+10
Last name exact match	+15
First name fuzzy match (distance ≤ 2)	+5
Phone match (digits only)	+20
Company name match (normalized)	+10
Job title match (same function)	+5
Created within 30 days of each other	+5
Same owner	+3
Different lifecycle stage	−10
Different associated company	−5

Company Scoring Matrix

Condition	Points
Exact domain match	+70
Normalized name exact match	+50
Normalized name fuzzy match (distance ≤ 3)	+25
Phone match	+20
City + country match	+10
Industry match	+8
Employee count within 20%	+5
2+ shared contacts	+15
Created within 60 days	+5
Very different employee counts (>5x)	−15
Different countries	−20

Confidence Tiers

Score	Tier	Action
80–100	HIGH — Confirmed duplicate	Safe to merge automatically
50–79	MEDIUM — Likely duplicate	Review before merging
20–49	LOW — Possible duplicate	Manual investigation required
< 20	Not a duplicate	Discard pair

---

Phase 4 — Merge Plan (Field-Level)

For each HIGH and MEDIUM pair, produce a field-level merge decision.

Master Record Selection Rules

The "master" record to keep is determined by priority (first rule that applies wins):

1. Higher lifecycle stage / more advanced deal stage
2. More associated deals or contacts
3. More fields populated (non-null count)
4. More recently modified
5. Older record (first to be created)

Field-Level Merge Logic

For each field, apply this hierarchy:

Priority	Rule
1	Keep non-null over null
2	Keep longer string (more complete)
3	Keep value from master record
4	Flag conflict if both non-null and different (requires human decision)

Special field rules:

• email → keep primary email of master, add other as secondary email if CRM supports it
• phone → keep both if different (store in phone + mobile)
• owner → keep master's owner; flag if different (may need reassignment)
• lifecycle_stage → always keep the most advanced stage
• created_date → keep the earliest (oldest) date
• tags / lists → merge all (union, no deduplication of values)
• notes / activities → keep all from both records (never discard)

---

Phase 5 — Output Artifacts

Produce all four outputs.

Output 1 — Duplicate Report (dashboard artifact)

Build an interactive React artifact with:

Summary section

• Total records analyzed
• Total duplicate pairs found
• Breakdown by tier: HIGH / MEDIUM / LOW
• Estimated data quality score: (unique records / total records) × 100
• Records at risk (HIGH + MEDIUM pairs)

Duplicate pairs table Columns: Record A | Record B | Match Signals | Score | Tier | Recommended Action

Color coding:

• HIGH: red background row
• MEDIUM: orange background row
• LOW: yellow background row

Filterable by: tier, object type (contact / company), owner, score range

Output 2 — Merge Plan (inline, structured)

For each HIGH and MEDIUM pair, output:

PAIR #N — [Tier] — Score: X/100

Record A: [ID] [Name/Email] — Created: [date] — Modified: [date]
Record B: [ID] [Name/Email] — Created: [date] — Modified: [date]

MASTER RECORD → Record [A/B] (reason: [rule that applied])

Field-level decisions:
  email:          Keep [A/B value] → [value]
  phone:          Keep both → [A value] + [B value]
  company:        Keep [A/B value] → [value]
  owner:          Keep [A/B value] ⚠️ CONFLICT — review
  lifecycle_stage: Keep [most advanced] → [value]
  [field]:        CONFLICT — human decision required → A: [val] / B: [val]

Action: [SAFE TO AUTO-MERGE / REVIEW BEFORE MERGE / MANUAL INVESTIGATION]

Output 3 — Bulk Merge CSV

Generate a CSV file at /mnt/user-data/outputs/crm-merge-plan.csv:

pair_id,confidence_tier,score,master_record_id,master_source,duplicate_record_id,duplicate_source,object_type,match_signals,conflicts,action,notes
1,HIGH,92,hs-001,HubSpot,hs-002,HubSpot,contact,"email+phone","owner",AUTO_MERGE,""
2,MEDIUM,61,sf-045,Salesforce,sf-103,Salesforce,company,"domain+name","employee_count",REVIEW,""

This CSV can be used to:

• Feed a bulk merge tool (Dedupely, HubSpot workflows)
• Track merge actions in a project tracker
• Import into a Notion database for team review

Output 4 — Prevention Rules

After the analysis, always output a prevention section:

## Duplicate Prevention Rules

### Root causes detected in this dataset
[List the top 3 causes based on the data — e.g., "62% of duplicates share the same
email domain but different email addresses → likely manual data entry without email lookup"]

### Immediate fixes (implement this week)
1. [Specific rule based on findings — e.g., "Enable HubSpot's native duplicate
   management for contacts: Settings → Data Management → Duplicates"]
2. [e.g., "Add a company domain deduplication check in your n8n Clay enrichment
   workflow before creating new HubSpot companies"]
3. [e.g., "Enforce email as required field on all lead capture forms"]

### CRM configuration rules
**HubSpot:**
- Enable: Settings → Data Management → Duplicates (contacts + companies separately)
- Create property: `canonical_record_id` (text) — set on merge to track history
- Workflow: "If contact created AND email matches existing contact → flag for review"
- Use HubSpot's `hs_calculated_merged_vids` property to track merge history

**Salesforce:**
- Enable Duplicate Rules: Setup → Duplicate Management → Duplicate Rules
- Create Matching Rules on Email (exact) and Name+Company (fuzzy)
- Set action to "Block" for exact email matches, "Allow with alert" for fuzzy matches
- Use the Potential Duplicates component on Contact and Account page layouts

### Data entry standards
1. Email is mandatory on all contact records — no exceptions
2. Company name must be validated against existing account domain before creation
3. All inbound leads (webhook, form, CSV import) must pass through enrichment
   (Clay or equivalent) before CRM write to normalize name and domain
4. Phone numbers must be stored in E.164 format ([phone])

### Enrichment workflow recommendation (n8n + Clay)
Before writing any new contact or company to CRM:
  → Clay - Enrich by email or LinkedIn URL
  → Set - Normalize name, domain, phone (E.164)
  → HubSpot/Salesforce - Search existing record by email + domain
  → Check - Existing record found?
    ├─ YES → Update existing record (never create new)
    └─ NO  → Create new record with all normalized fields

---

Handling Edge Cases

Case	Behavior
Missing id column	Warn user — merge plan cannot be generated without record IDs. Ask for re-export with IDs.
Dataset > 5,000 records	Warn and focus on highest-risk segments (exact domain matches first)
All records have unique emails	Report "No email-based duplicates found" — shift to name+company matching
Multiple records same person, different companies	Flag as "role change" not duplicate — recommend keeping both, linking via relationship
Merge would create data loss	Always flag in conflict column — never auto-resolve fields with conflicting non-null values
Same person, both with active deals	Escalate to MANUAL tier regardless of score — deal data must be reviewed by owner