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.com and sarah.chen@example.com are the same person.
  • For domains, strip the protocol, www., paths and trailing slashes. https://www.nexusflow.com/about becomes nexusflow.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

  1. Normalizing only on one side. If the CRM stores mixed-case emails and you only lowercase your file, matches fail. Compare lowercase to lowercase.
  2. Company domain from the contact's email. Fine for work addresses, wrong for free-mail. Skip those.
  3. Overwriting richer data. Decide per field whether the import may overwrite. A common safe default is fill-empty-only.
  4. 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?