Most CRM data problems are not caused by bad data. They are caused by a bad match key and an import that wrote straight to production.
This post describes one pattern that prevents most of it: plan first, write second. The import script produces a plan of what it would do, a human checks the counts, and only then does it write. It works with HubSpot, Pipedrive, Salesforce, or a Postgres table. The concepts are the same.
The problem
You have a CSV of 500 leads from an enrichment tool. You push it into the CRM. A week later someone notices three records for the same person and two companies called "Acme" with different domains.
What went wrong was upstream of the CRM. The import never decided what "the same person" means.
Step 1: Define the match key and normalize it
Pick one unique key per object:
- Contacts: email address. In HubSpot this is the default dedupe property for contacts.
- Companies: domain name. Also HubSpot's default company dedupe property.
Then normalize before comparing:
- Lowercase and trim emails.
Sarah.Chen@Example.comandsarah.chen@example.comare the same person. - For domains, strip the protocol,
www., paths and trailing slashes.https://www.nexusflow.com/aboutbecomesnexusflow.com. - Never use free-mail domains (gmail.com, outlook.com, yahoo.com) as a company key. Otherwise every Gmail user becomes an employee of one giant company called "gmail.com".
A decision worth making deliberately: plus-addressing. ali+test@example.com may or may not be the same person as ali@example.com. Stripping the +tag merges more but can merge distinct people on shared inboxes. Most teams leave it alone and flag it.
Step 2: Dedupe inside the file first
If your file has the same email twice, a naive loop will check the CRM twice, find nothing both times, and create two records. Deduplicate the file before touching the CRM. When two rows collide, choose a rule (most complete row wins, or last row wins) and log what was skipped.
Step 3: Look up each key
For every remaining row, ask the CRM whether the key exists:
- exists: update (only fill empty fields, or define which fields the import is allowed to overwrite)
- does not exist: create
Use the batch endpoints. HubSpot's batch APIs accept up to 100 records per request, which keeps you well away from rate limits compared with one call per row.
Step 4: Print the plan
The plan is the product. It should look like this:
Contacts create: 37 update: 6 skip (dup in file): 1
Companies create: 36 update: 6
Associations: 42
Print the first ten rows of each bucket too. A person scanning the output catches things no validation rule would.
Step 5: Sanity-check the counts
This is the step that earns its keep. Compare the plan to what you expected:
- You expected mostly updates (re-enriching existing leads) and the plan says mostly creates? Your match key is not matching. Check normalization.
- You expected mostly new records and the plan says mostly updates? You may be overwriting real data with a stale file.
- The skip count is high? The source has heavy duplication; fix it upstream.
If any of these look wrong, you stop. Nothing has been written.
Step 6: Write, then log
Only after approval, run the same code path with dry_run=False. This matters: the dry run and the real run should share one function, so the plan is a true preview and not a separate guess. Write in batches, log every record ID created or updated, and keep that log. If something is off later, you can undo precisely instead of guessing.
Make it idempotent
A good import can be run twice and the second run creates nothing. Once you are matching on a normalized key, that mostly comes for free: the second run sees every record as an update. If your second run creates records, that is a bug, and the dry run would have told you.
Gotchas people hit
- Normalizing only on one side. If the CRM stores mixed-case emails and you only lowercase your file, matches fail. Compare lowercase to lowercase.
- Company domain from the contact's email. Fine for work addresses, wrong for free-mail. Skip those.
- Overwriting richer data. Decide per field whether the import may overwrite. A common safe default is fill-empty-only.
- No record of what changed. Always keep a log of IDs and before/after values for updates.
Summary
- Choose one match key per object and normalize it.
- Dedupe the file before dedupe against the CRM.
- Print create / update / skip counts and have a human approve them.
- Share one code path between dry run and real run.
- Log everything you write.
The cost of this pattern is about an hour of work. The cost of skipping it is a duplicate-merge project later, and those are never fun.
What do you match contacts on in your CRM, and has it ever failed you?
Top comments (1)
"The dry run and the real run should share one function" is the line that makes this post worth keeping, and it is the one most implementations get wrong. When the plan is generated by a separate code path that only looks like the writer, the preview drifts silently the first time someone fixes a bug in one branch and not the other, and the drift is invisible until a plan said 37 creates and the run made 41.
Two additions from having built these.
Free-mail as a company key is worse than it looks. You correctly say never key a company on gmail.com. The subtler version is that the normalization creates the trap: if you derive the company domain from the contact's email and then strip
www.and the protocol,mail.google.comandgoogle.comcollapse to the same key, which is usually right and occasionally merges a customer with a vendor. A short denylist of mail providers plus a "domain must have an MX record and not be a known webmail" check catches most of it, but I would log every derived-key decision and let a human scan the first twenty — same reasoning as your plan printout.Fill-empty-only has an ordering dependency that bites on re-runs. If a field was empty in the CRM, filled by import run #1, and the source file later corrects it, fill-empty-only will refuse to apply the correction on run #2 — because the field is no longer empty. That is correct by the rule and wrong by intent. The fix that has held up for me is to record, per field, whether the current value was written by a previous import (a source-of-truth marker), so an import may overwrite its own past writes but not a human's. Without that marker, fill-empty-only slowly freezes bad data in place.
On the counts sanity check: the one signal missing from your list is the create/update ratio change between runs. A steady 37 creates against the same 500-row file means the key genuinely is not matching; a 37 that drops to 0 on run #2 with no source changes means idempotency is working. Logging both numbers per run makes that visible for free.
The plus-addressing note is well judged — flag, do not merge, is the right default.
I write about import and reconciliation failures at some length; my profile links to it.