CRM Data Cleanup
This skill turns a messy CRM export into a merge plan a human can check, plus files that
are ready to import. It works the same way for any vendor, because every CRM exports CSV.
Duplicate cleanup usually fails in one of three ways. Exact-email matching misses most
duplicates. Merging on shared inboxes like info@ fuses different people into one. And a
merge overwrites good values with blanks, losing history that can't be recovered. Most
CRM merges can't be undone, so this skill never merges anything itself. It plans, a
human approves, and the CRM's own merge tool does the merging.
Files
scripts/dedupe.py: the dry-run engine: normalize, block, score, cluster, pick a
survivor, merge fields. Needs pandas and rapidfuzz. Uses phonenumbers if it's
installed.
scripts/evaluate.py: scores a run against a labeled truth file (pairwise precision
and recall).
assets/fixture/: 41 synthetic contacts and 14 companies with planted duplicates,
plus truth.csv.
references/field-standards.md: normalization rules, state and country codes,
lifecycle stage mapping, and flag meanings.
references/vendor-notes.md: what merge keeps and destroys in each CRM, import
quirks, and links to the official docs.
references/safe-rollout.md: backup, sample merge, batch execution, and rollback.
Workflow
- Get the exports. Ask for a full-property CSV of contacts (or leads/people) and,
when companies are in scope, companies (accounts/organizations) with record IDs.
Record IDs matter because the cleaned files update records by ID.
- Back up first. Before any merge, confirm the user has a dated full export that
includes associations (
safe-rollout.md, Phase 0). A backup is the only real rollback.
- Run the dry run:
pip install pandas rapidfuzz phonenumbers # phonenumbers is optional but preferred
python scripts/dedupe.py --contacts contacts.csv --companies companies.csv \
--out plan/ --as-of 2026-09-22 [--default-region GB] [--gmail-canonical]
The script detects columns from common vendor headers. Fix a wrong guess with
--col email="E-mail 1" (or --company-col for the companies file).
- Check
summary.json before trusting any number. Confirm column_map (a deal
Stage column mapped as lifecycle skews everything), phone_engine, and warnings,
such as a block that was skipped for being too large.
- Present the results by tier. Auto clusters: the count and a few examples. Review
queue: every pair, with its reasons. Flags: counts by type. Associations: orphaned
and missing links. Lead with the review queue, because it's where the user's judgment
is needed.
- Walk the review queue with the data owner. Record
merge or keep in the
decision column. Then look at the kept_survivor_conflict rows in
merge_plan.csv: those are the values that will disappear.
- Hand off the execution plan. Merge a sample of 5 to 10 clusters in the native tool,
verify, then do the rest in batches. After merging, import the cleaned files keyed on
record ID. Follow
safe-rollout.md, and check vendor-notes.md for the specific CRM.
Merge-safety rules
- Never auto-merge on a role or shared identifier.
info@, sales@, a household
inbox, or an office main line shows that records belong to the same organization, not
that they're the same person. A shared identifier goes to review at most.
- Auto-merge needs a strong ID plus a compatible name. A strong ID is an exact
non-shared email, or an exact non-shared phone. A name match alone, even 100% with the
same company, only goes to review, because two people named David Kim at one company is
normal in large organizations.
- Re-check every pair inside a cluster. A=B by email and B=C by phone can chain two
different people together. The script demotes any cluster with an incompatible pair,
or with more than
--max-cluster (5) members, to review.
- Never overwrite a non-empty value with an empty one. The survivor's value wins.
Blanks are filled from the best-ranked loser. Create date takes the earliest, last
activity the latest, and lifecycle stage the furthest along. Losing emails and phones
go to
additional_emails and additional_phones, so they aren't dropped.
- Pick the survivor on purpose. The default order is
most_recent_activity, then
most_complete, then oldest_created. Add business_email first when the work
address should be the primary email. In HubSpot and Pipedrive the primary's values win
every conflict, so the survivor choice decides what's kept.
- Gmail dot and plus folding is opt-in only (
--gmail-canonical). Google says dots
don't matter for personal Gmail, but they do matter for Workspace domains. Applied by
default, it would merge real, distinct mailboxes.
- Stay a dry run. Never call a CRM API, and never write to the input files. A merge
runs in the CRM's native tool, which moves activity history. Delete-and-reimport
doesn't.
- Flag, don't delete. Stale, test, and invalid records go in
record_flags.csv. The
data owner decides whether to archive or delete them.
Output format
| File |
Contents |
merge_plan.csv |
One row per cluster per field: cluster_id, survivor_id, loser_ids, survivor_rule, field, winning_value, source_id, action, other_values. The action is kept_survivor, kept_survivor_conflict, filled_from_loser, earliest, latest, furthest_stage, or preserved_secondary |
review_queue.csv |
Pairs needing a human: both records side by side, confidence, reasons, suggested_survivor, and blank decision and decided_by columns |
cleaned_contacts.csv |
The original columns with normalized values. Survivors carry merged values, and losers are removed. Also additional_emails, additional_phones, _cleanup_action, and _flags |
record_flags.csv |
invalid_email, email_domain_typo, role_email, shared_phone, no_contact_method, phone_unparsed, junk_or_test, missing_name, stale |
companies_*.csv, cleaned_companies.csv |
The same outputs for companies (domain-first matching) |
associations.csv |
company_merged (the ID was re-pointed to the survivor), orphan_company_id, missing_association (the email domain matches a company) |
summary.json |
Counts, flag totals, column map, settings, warnings |
When reporting to the user, give: records in and out, auto clusters, review pairs, flag
counts, the three riskiest review pairs, and the next gate from safe-rollout.md.
Worked example (bundled fixture)
python scripts/dedupe.py --contacts assets/fixture/contacts.csv \
--companies assets/fixture/companies.csv --out plan/ --as-of 2026-09-22
python scripts/evaluate.py --plan-dir plan/ --truth assets/fixture/truth.csv
The fixture is 41 contacts holding 14 true duplicate pairs. It includes case and
whitespace email variants, a name typo, a nickname with a reformatted phone, the same
person with a work and a Gmail address, Inc versus no Inc, a hyphenated married name,
a 3-record cluster, and a Gmail dot/plus variant. It also plants traps that must not
merge: three people sharing info@stark.com, a couple sharing a household inbox, three
colleagues on one office line, and two different people named David Kim.
| Run |
Auto pairs |
Wrong auto |
Auto precision |
Auto recall |
Recall incl. review |
| Naive: exact lowercase email only |
11 |
4 (the info@ trio and the household) |
63.6% |
50.0% |
n/a |
| Default |
11 |
0 |
100% |
78.6% |
100% |
--gmail-canonical |
12 |
0 |
100% |
85.7% |
100% |
In the default run, 7 contact clusters absorb 9 records, and 6 pairs go to review: 3 true
duplicates plus the household and info@ traps. For companies, Acme Inc/ACME and
Tyrell Corp/The Tyrell Corporation auto-merge by domain. Globex LLC/Globex Corporation (no domain) and Umbrella Ltd/Umbrella Corporation (different domains) go
to review. Three association issues surface, including an orphaned CO99 reference.
Cluster MC0001 (Jane Doe x3) shows the field rules at work. Create date 2022-03-14
comes from the oldest record. Lifecycle stage customer is the furthest along. The blank
state on the survivor is filled from C001, and the work email is kept in
additional_emails.
Tuning
--auto-name-sim 85, --review-name-sim 88, --conflict-name-sim 60: raise the auto
threshold for large, name-dense databases.
--stale-days 365, --as-of: set the staleness window. Always pass --as-of so reruns
are reproducible.
--max-block 500: blocks bigger than this are skipped with a warning. Keeping blocks
small keeps pair scoring fast on 100k-row exports.
- Extend
NICKNAMES, ROLE_LOCALS, LIFECYCLE_MAP, or LEGAL_SUFFIXES in the script
for a specific region or portal. Custom HubSpot lifecycle stages have numeric internal
IDs.
1---2name: crm-data-cleanup3description: Cleans up CRM data from HubSpot, Salesforce, Pipedrive, Zoho, Close, Attio, or any CRM CSV export. Finds duplicate contacts and companies with fuzzy matching (not just exact email), normalizes emails, phones to E.164, names, domains, states, and countries, flags stale, invalid, role, and test records, finds broken contact-company associations, and produces a reviewable merge plan plus import-ready files without touching the live CRM. Use this skill whenever the user wants to dedupe, find or merge duplicate contacts, merge companies or accounts, clean up a CRM, prep a CRM migration or re-import, or says the CRM export is a mess. Also use it when they mention only one symptom, like "the same person is in here three times", "half our leads are junk", or "our company records don't match".4metadata:5 source: https://skillmd.com/skills/onewave-ai/crm-data-cleanup6---78# CRM Data Cleanup910This skill turns a messy CRM export into a merge plan a human can check, plus files that11are ready to import. It works the same way for any vendor, because every CRM exports CSV.1213Duplicate cleanup usually fails in one of three ways. Exact-email matching misses most14duplicates. Merging on shared inboxes like `info@` fuses different people into one. And a15merge overwrites good values with blanks, losing history that can't be recovered. Most16CRM merges **can't be undone**, so this skill never merges anything itself. It plans, a17human approves, and the CRM's own merge tool does the merging.1819## Files2021- `scripts/dedupe.py`: the dry-run engine: normalize, block, score, cluster, pick a22 survivor, merge fields. Needs `pandas` and `rapidfuzz`. Uses `phonenumbers` if it's23 installed.24- `scripts/evaluate.py`: scores a run against a labeled truth file (pairwise precision25 and recall).26- `assets/fixture/`: 41 synthetic contacts and 14 companies with planted duplicates,27 plus `truth.csv`.28- `references/field-standards.md`: normalization rules, state and country codes,29 lifecycle stage mapping, and flag meanings.30- `references/vendor-notes.md`: what merge keeps and destroys in each CRM, import31 quirks, and links to the official docs.32- `references/safe-rollout.md`: backup, sample merge, batch execution, and rollback.3334## Workflow35361. **Get the exports.** Ask for a full-property CSV of contacts (or leads/people) and,37 when companies are in scope, companies (accounts/organizations) with record IDs.38 Record IDs matter because the cleaned files update records by ID.392. **Back up first.** Before any merge, confirm the user has a dated full export that40 includes associations (`safe-rollout.md`, Phase 0). A backup is the only real rollback.413. **Run the dry run:**42 ```bash43 pip install pandas rapidfuzz phonenumbers # phonenumbers is optional but preferred44 python scripts/dedupe.py --contacts contacts.csv --companies companies.csv \45 --out plan/ --as-of 2026-09-22 [--default-region GB] [--gmail-canonical]46 ```47 The script detects columns from common vendor headers. Fix a wrong guess with48 `--col email="E-mail 1"` (or `--company-col` for the companies file).494. **Check `summary.json` before trusting any number.** Confirm `column_map` (a deal50 `Stage` column mapped as lifecycle skews everything), `phone_engine`, and `warnings`,51 such as a block that was skipped for being too large.525. **Present the results by tier.** Auto clusters: the count and a few examples. Review53 queue: every pair, with its reasons. Flags: counts by type. Associations: orphaned54 and missing links. Lead with the review queue, because it's where the user's judgment55 is needed.566. **Walk the review queue with the data owner.** Record `merge` or `keep` in the57 `decision` column. Then look at the `kept_survivor_conflict` rows in58 `merge_plan.csv`: those are the values that will disappear.597. **Hand off the execution plan.** Merge a sample of 5 to 10 clusters in the native tool,60 verify, then do the rest in batches. After merging, import the cleaned files keyed on61 record ID. Follow `safe-rollout.md`, and check `vendor-notes.md` for the specific CRM.6263## Merge-safety rules6465- **Never auto-merge on a role or shared identifier.** `info@`, `sales@`, a household66 inbox, or an office main line shows that records belong to the same organization, not67 that they're the same person. A shared identifier goes to review at most.68- **Auto-merge needs a strong ID plus a compatible name.** A strong ID is an exact69 non-shared email, or an exact non-shared phone. A name match alone, even 100% with the70 same company, only goes to review, because two people named David Kim at one company is71 normal in large organizations.72- **Re-check every pair inside a cluster.** A=B by email and B=C by phone can chain two73 different people together. The script demotes any cluster with an incompatible pair,74 or with more than `--max-cluster` (5) members, to review.75- **Never overwrite a non-empty value with an empty one.** The survivor's value wins.76 Blanks are filled from the best-ranked loser. Create date takes the earliest, last77 activity the latest, and lifecycle stage the furthest along. Losing emails and phones78 go to `additional_emails` and `additional_phones`, so they aren't dropped.79- **Pick the survivor on purpose.** The default order is `most_recent_activity`, then80 `most_complete`, then `oldest_created`. Add `business_email` first when the work81 address should be the primary email. In HubSpot and Pipedrive the primary's values win82 every conflict, so the survivor choice decides what's kept.83- **Gmail dot and plus folding is opt-in only** (`--gmail-canonical`). Google says dots84 don't matter for personal Gmail, but they do matter for Workspace domains. Applied by85 default, it would merge real, distinct mailboxes.86- **Stay a dry run.** Never call a CRM API, and never write to the input files. A merge87 runs in the CRM's native tool, which moves activity history. Delete-and-reimport88 doesn't.89- **Flag, don't delete.** Stale, test, and invalid records go in `record_flags.csv`. The90 data owner decides whether to archive or delete them.9192## Output format9394| File | Contents |95|---|---|96| `merge_plan.csv` | One row per cluster per field: `cluster_id, survivor_id, loser_ids, survivor_rule, field, winning_value, source_id, action, other_values`. The `action` is `kept_survivor`, `kept_survivor_conflict`, `filled_from_loser`, `earliest`, `latest`, `furthest_stage`, or `preserved_secondary` |97| `review_queue.csv` | Pairs needing a human: both records side by side, `confidence`, `reasons`, `suggested_survivor`, and blank `decision` and `decided_by` columns |98| `cleaned_contacts.csv` | The original columns with normalized values. Survivors carry merged values, and losers are removed. Also `additional_emails`, `additional_phones`, `_cleanup_action`, and `_flags` |99| `record_flags.csv` | `invalid_email`, `email_domain_typo`, `role_email`, `shared_phone`, `no_contact_method`, `phone_unparsed`, `junk_or_test`, `missing_name`, `stale` |100| `companies_*.csv`, `cleaned_companies.csv` | The same outputs for companies (domain-first matching) |101| `associations.csv` | `company_merged` (the ID was re-pointed to the survivor), `orphan_company_id`, `missing_association` (the email domain matches a company) |102| `summary.json` | Counts, flag totals, column map, settings, warnings |103104When reporting to the user, give: records in and out, auto clusters, review pairs, flag105counts, the three riskiest review pairs, and the next gate from `safe-rollout.md`.106107## Worked example (bundled fixture)108109```bash110python scripts/dedupe.py --contacts assets/fixture/contacts.csv \111 --companies assets/fixture/companies.csv --out plan/ --as-of 2026-09-22112python scripts/evaluate.py --plan-dir plan/ --truth assets/fixture/truth.csv113```114115The fixture is 41 contacts holding 14 true duplicate pairs. It includes case and116whitespace email variants, a name typo, a nickname with a reformatted phone, the same117person with a work and a Gmail address, `Inc` versus no `Inc`, a hyphenated married name,118a 3-record cluster, and a Gmail dot/plus variant. It also plants traps that must not119merge: three people sharing `info@stark.com`, a couple sharing a household inbox, three120colleagues on one office line, and two different people named David Kim.121122| Run | Auto pairs | Wrong auto | Auto precision | Auto recall | Recall incl. review |123|---|---|---|---|---|---|124| Naive: exact lowercase email only | 11 | 4 (the `info@` trio and the household) | 63.6% | 50.0% | n/a |125| Default | 11 | 0 | 100% | 78.6% | 100% |126| `--gmail-canonical` | 12 | 0 | 100% | 85.7% | 100% |127128In the default run, 7 contact clusters absorb 9 records, and 6 pairs go to review: 3 true129duplicates plus the household and `info@` traps. For companies, `Acme Inc`/`ACME` and130`Tyrell Corp`/`The Tyrell Corporation` auto-merge by domain. `Globex LLC`/`Globex131Corporation` (no domain) and `Umbrella Ltd`/`Umbrella Corporation` (different domains) go132to review. Three association issues surface, including an orphaned `CO99` reference.133134Cluster `MC0001` (Jane Doe x3) shows the field rules at work. Create date `2022-03-14`135comes from the oldest record. Lifecycle stage `customer` is the furthest along. The blank136state on the survivor is filled from C001, and the work email is kept in137`additional_emails`.138139## Tuning140141- `--auto-name-sim 85`, `--review-name-sim 88`, `--conflict-name-sim 60`: raise the auto142 threshold for large, name-dense databases.143- `--stale-days 365`, `--as-of`: set the staleness window. Always pass `--as-of` so reruns144 are reproducible.145- `--max-block 500`: blocks bigger than this are skipped with a warning. Keeping blocks146 small keeps pair scoring fast on 100k-row exports.147- Extend `NICKNAMES`, `ROLE_LOCALS`, `LIFECYCLE_MAP`, or `LEGAL_SUFFIXES` in the script148 for a specific region or portal. Custom HubSpot lifecycle stages have numeric internal149 IDs.