Normalizing and Deduping
Outcome: normalized keys and values across every source feeding your table, a tested dedupe rule, and merge behaviour written down per field type.
- Surface
- App
- Level
- Intermediate
- Uses
- Formula columns · AI normalization for the tail
- Credits
- ~5 for the AI tail
- Prerequisite
- Two or more sources feeding one table
Why this lesson sits in an AI course
Because the temptation is to solve it with a model, and mostly you should not.
Normalization is a rules problem with a small ambiguous tail. Rules handle 90% of it for free and deterministically; the model earns its place only on the remainder. Getting this split right is the difference between a cheap, reliable pipeline and an expensive, non-reproducible one.
Rules first
Domains
Lowercase, strip protocol, strip www., strip path and query, keep the registrable domain. This is your match key and it must be exact.
Countries
Map everything to ISO codes. “US”, “USA”, “United States”, “U.S.” collapse to one value.
Company names
Trim, fix casing, strip legal suffixes into a separate column — Inc, Ltd, GmbH, Pty, LLC. Keep the original for use in copy.
Titles
Keep verbatim. Derive seniority and function into new columns. Never overwrite.
Numbers
Strip currency symbols and separators, store as numbers, band separately.
All formula columns. All free. All deterministic.
Where AI helps
Only the tail:
| Case | Why rules struggle |
|---|---|
| Titles in several languages | Keyword lists do not generalize across languages |
| Highly unusual titles | ”Chief Happiness Officer”, “Growth Ninja” |
| Company names that differ in structure, not spelling | ”Acme Group Holdings” versus “Acme” |
| Free-text location fields | ”Greater London Area, UK” versus “London, England” |
Run the AI layer only where the rule returned unclassified, constrained to the same value set — the same pattern as lesson 11.
Entity resolution across sources
When the same company arrives from three sources with three spellings, the answer is not fuzzy name matching. It is a deterministic key.
| Grain | Key | Fallback |
|---|---|---|
| Company | Root domain | LinkedIn company URL |
| Person | LinkedIn profile URL | Verified work email |
| Event | Source ID + company key | — |
Fuzzy name matching creates false merges, and a false merge is far worse than a duplicate. Two genuinely different companies collapsed into one row corrupts every downstream number and cannot be detected by looking at the table. Duplicates are visible and fixable; bad merges are neither.
Where you have no domain, resolve one first — enrich to find it — rather than matching on name. That is one cheap lookup against a permanent data-quality problem.
Safe fuzzy matching
There is a narrow band where fuzzy matching is defensible:
- Within the same company, matching a person’s name against a known employee list.
- As a flag for review, never as an automatic merge.
- With a high threshold and a human confirming anything below certainty.
Outside those, use the deterministic key.
Merge rules
Write these once, per field type, and every re-run behaves predictably:
| Field type | Rule |
|---|---|
| Volatile facts — headcount, title, stack | Newest wins |
| Contact data | Verified beats unverified, regardless of age |
| Human-entered — owner, notes, disqualification | Never overwritten by automation |
| Events and signals | Append, never replace |
| Inferred values | Lose to observed values from a provider |
That last row connects to lesson 10: the source flag is what makes the rule enforceable.
Do this now
Build the normalization columns
Domain, country, company name, seniority, function. All formulas.
Check for key collisions
Group by normalized domain and look for rows that should not be together.
Add the AI tail
Only on rows the rules could not handle.
Test dedupe deliberately
Import the same file twice. The row count must not change.
Write the merge rules
Five lines, one per field type.
Walk one row through a re-run
By hand. Confirm each field behaves as the rule says.
Check your work
- Every source writes into normalized columns
- The match key is a domain or profile URL, never a name
- Duplicate import leaves the row count unchanged
- Merge rules exist per field type
- Inferred values cannot overwrite observed ones
Where this breaks
Normalizing in place destroys the original. Once a verbatim job title has become “engineering”, the copy step has nothing real to quote and you cannot re-derive what the person actually calls themselves. Always normalize into a new column and keep the raw value — storage is free, re-enrichment is not.
Further automation
Once keys and merge rules are stable, the table can safely re-run on a schedule — which is the prerequisite for everything in modules 7 to 9. A pipeline that cannot be re-run without corrupting itself is a one-off, however good its output.
Next lesson
14 — Copy that references something real, where the Outreach module begins.
Reference for this lesson: Import, Tables, CRM integrations, GTM Engineering — dedupe and hygiene.