Reconciling Two Account Lists Into One Company Universe

You have two company lists from different sources - a CRM export and a sourced list, say - and you need to fold them into one deduplicated master without losing records or over-merging distinct firms. This guide is for the RevOps, data-ops, and data-steward roles answerable for how that master was built. It carries one reconciliation all the way through, with the intermediate counts at each fork, including the false-merge that had to be undone.

Most cleanup guides stop at an abstract merge-or-keep rubric. This one shows the arithmetic: how many pairs the exact pass removes, how many the normalization step recovers, how many the review queue catches, and where the count moves when a wrong merge gets rolled back. Follow along on your own two lists.

What reconciling two account lists actually requires

Reconciliation means resolving two company lists into one set of golden records where each real-world firm appears exactly once, with a count you can defend and an audit trail you can show. It is not a single button. It is a pipeline: normalize, exact-match, fuzzy-score, band by confidence, review the ambiguous middle, apply survivorship, and log everything.

The hard part is not the matching. It is the asymmetry between the two ways you can be wrong. A missed duplicate leaves two records where there should be one - annoying, recoverable, visible. A wrong merge deletes a real record and reparents its children onto the wrong firm, and in most CRMs that cannot be undone. Every threshold decision downstream is a response to that asymmetry.

A wrong merge is much harder to undo than a missed duplicate, and every threshold you set is an answer to that fact.

I will use one worked case throughout, drawn from a documented reconciliation. Treat its numbers as illustrative of shape, not as a benchmark. In that case the pre-merge account duplicate rate was 21.4 percent, high-confidence pairs at or above 90 percent similarity made up 66 percent of candidate pairs, the merge itself succeeded 99.4 percent of the time, 22 merges needed rollback, and the final account count fell 19.6 percent. Those figures anchor what "normal" looks like at each stage below.

Why exact matching alone leaves duplicates on the table

Exact matching misses true duplicates by design, not by mistuning, and that structural blind spot is exactly what the normalization step exists to recover. Understand this before you set a threshold, because it explains why you cannot skip preprocessing.

The reference implementation of native dedupe compares configurable fields through matching rules that build match keys, then only compares records that share a key. If two records do not share a match key, they are never considered duplicates - they are not scored at all. So a single domain typo, or "Acme Inc" against "Acme, Inc.", produces different keys and the pair becomes invisible. Exact-key matching misses a lot of true duplicates, and fuzzy scoring is what catches those missed pairs.

Two more limits worth knowing before you lean on native tooling. Native merge handles accounts, contacts, and leads but only three records at a time, with no automation and no cross-object support. And you are capped at five duplicate rules per object. For a two-list reconciliation of any size, that pushes the real work into staging tables you control.

The lesson: no clever similarity algorithm rescues a pipeline that skipped normalization. The 15 to 25 percent preprocessing lift is recovered before any algorithm change, which means it is the cheapest accuracy you will ever buy.

Normalizing company names without over-merging

Normalization resolves cosmetic variants of the same name to one canonical value so the exact pass can catch them, but it has a ceiling and a trap you must respect. Do the high-impact steps, stop where meaning begins.

The steps that pay, in rough order of impact:

• Lowercasing and stripping common legal suffixes (Inc, LLC, Corp, Ltd) give the biggest accuracy gains. A named Python package, cleanco, does the suffix work.
• Removing punctuation is the next highest-impact step.
• Normalizing whitespace collapses double spaces and stray tabs.
• Root-domain extraction reduces every website to a stable key, which is often more reliable than the name.

Deterministic rules like these resolve about 85 to 90 percent of company-name cases instantly. But name normalization only fixes legal forms. It does nothing for common business words like Group, Holdings, and Solutions, and standard fuzzy matchers treat every token equally, so they fail on company names built from those words. The residual 10 to 15 percent is not a compute problem you can grind through - it is judgment.

The trap sits at the other extreme. Strip too much and you destroy real distinctions. Removing geographic qualifiers can merge "Toyota Motor Corporation Japan" and "Toyota Motor Manufacturing Kentucky" - the same brand, but different legal entities. This is the over-normalization false-merge, and it is why step one keeps the raw name in a separate field. Always run normalization on a staging copy and keep company_name_raw before you overwrite anything.

There is a genuine disagreement in the field about ordering. Some sources normalize, then exact-match, then fuzzy-score. Others argue that token weighting should replace heavier normalization rather than follow it, because down-weighting generic tokens does the entity-distinction work that suffix stripping cannot. For a two-list reconciliation I favor normalize-then-exact-then-fuzzy, then add rarity weighting inside the fuzzy step. That keeps the cheap deterministic wins while handling the business-word residual where it actually lives.

Setting the auto-merge, review, and reject thresholds

Set three bands - auto-merge, review, reject - and put the auto-merge cutoff high enough that a machine decision is effectively certain. Practitioners disagree on the exact numbers but agree on the shape and on which direction to bias.

Here is where published cutoffs land. Note they are expressed on different scales; read each row against its own source.

The spread is real, but the logic collapses to one principle: auto-merge only pairs at a very high confidence on high-certainty fields, queue the middle range for a human, and auto-dismiss pairs below the floor. Given that merges are permanent, I default to the precision-heavy end - 0.98 or higher for auto-merge, roughly 0.85 to 0.98 for review - and I require at least one hard identifier in the auto band regardless of name score.

That last requirement matters more than the number. Field completeness, not name similarity, often decides the case. The same Acme pair scores about 0.94 with a matching VAT number but only about 0.78 without it. So a real duplicate lands in the review queue not because its names are far apart but because a hard identifier is missing. Do not chase that gap by lowering the name threshold - you will import false merges.

The worked reconciliation, stage by stage

Run the pipeline in the fixed order below, recording a count at every stage so the final number is traceable back to raw rows. This is the part you follow with your hands.

Now the arithmetic on the documented case, so you can see how the count moves. The two staged lists carried a pre-merge duplicate rate of 21.4 percent. Normalization and the exact pass cleared the cosmetic variants. Fuzzy scoring split the remaining candidate pairs: 66 percent scored at or above 90 percent (the high-confidence band), 22 percent landed in the 60 to 89 percent medium band and went to review, and the tail below that was dismissed. Survivorship and merge ran at a 99.4 percent success rate. Twenty-two merges later needed rollback. The final account count fell 19.6 percent, a hair under the 21.4 percent raw duplicate rate - the gap between those two numbers is exactly the distinct firms the review queue protected from over-merging.

The 22 rollbacks are the point of this teardown. In that case, a batch of pairs auto-merged on high name similarity where the clusters actually spanned two legal entities - the over-normalization trap. The fix was not a better algorithm. It was re-inspecting every cluster that spanned two countries against the retained raw name, unmerging the false pairs, and adding a rule that a country or tax-ID conflict blocks auto-merge no matter the name score. Keep the raw name and you can undo this in an afternoon. Discard it and you are guessing.

When your source lists are themselves sparse or inconsistent, describing what you want in plain language beats hand-stitching filters. I built Refolk so you can ask for exactly the segment you need and get back people who match, without reconciling three exports first.

Choosing which record survives the merge

Survivorship is the rule that decides which record wins when a cluster collapses to one, and you declare it before you look at the data, not after. Guessing per-cluster is how bias and stale values sneak onto the golden record.

Three techniques are documented, and best practice stacks them rather than picking one:

• Most Recent orders date-stamped records newest first and keeps the newest.
• Most Frequent treats a value that repeats across records as reliable.
• Most Complete favors the record with the fewest incomplete or empty attributes.

On top of those, add source precedence: prioritize authoritative systems so the CRM of record beats a sourced list on ties. Stacking system priority with completeness leads to fewer null values and more accurate survivorship. Document the stack in writing before you merge.

1. Source precedence: system_of_record > enrichment_vendor > sourced_list
2. Most Complete: field-level, non-null and non-placeholder value wins
3. Most Recent: on a real business event date, NOT system-modified date
4. Most Frequent: value appearing in the majority of cluster records
5. Tie-break: manual steward decision, reason logged

Two traps here. First, recency poisoning: survivorship on most-recent updates gets thrown off by metadata updates from a nightly batch run, which stamps every record "modified today" and hands the golden record whichever happened to run last. Use a real business timestamp, not the system-modified date. Second, treating missing as evidence. Absence of a value is missingness, not proof of anything - a caution the source data itself illustrates. In Refolk's index, only 42 of 1,075 US RevOps managers explicitly list "Salesforce" as a skill, 3.9 percent, despite the tool being near-universal in the role. If you scored "most complete" naively, you would penalize records for an absence that means nothing.

Where reconciliation goes wrong

The failure modes below are the most valuable part of this standard, because each one produces a plausible-looking master that is quietly wrong. Learn the tell and the check for each.

Two of these deserve extra weight because they cost the most to recover from.

The first is the irreversible merge with no log. Native merges delete the losing records, reparent their contacts, opportunities, and activities onto the survivor, and cannot be undone. There is no native merge-history tracking, so once it runs you cannot even trace which accounts were merged or when. The recycle bin is a false comfort: deleted duplicates sit there up to 30 days, but restoring one does not reinstate its relations to child objects that were reparented onto the master. The check is procedural, not technical - sandbox test, export first, and write your own merged-ID audit table.

The second is the review queue as false comfort. The queue is a sensor, not a chore. Its size and the steward's reject rate read out whether your thresholds are set right. A queue of 30 to 50 pairs a week is healthy; a queue of 5,000 a week means the thresholds are wrong. If the steward is rubber-stamping everything, the threshold is too cautious. If they are rejecting half, it is too aggressive. When the queue explodes, recalibrate the bands - do not hire.

Proving the result and keeping it clean

Close the job with two load-bearing metrics and an audit file, then set a guardrail so the duplicates do not grow back. A single record count means nothing without the coverage figure and the log behind it.

The two metrics to report:

• Match coverage (recall): the percentage of records that can be accurately linked to their true counterpart. This tells you how many duplicates you caught.
• Duplicate creation rate: the volume of new duplicates appearing after cutover. Track it against a documented target of under 1 percent of new records. In the worked case the ongoing rate settled at 0.8 percent, inside target.

The audit log is what makes the count defensible. A post-merge report should show which records were merged, which field values were kept or discarded, which related records were moved, plus the user and timestamp - a full trail essential for compliance and troubleshooting. Build it as an external table, because the CRM will not build it for you.

To keep the universe clean after cutover, re-run the creation-rate check on a schedule and treat any drift above 1 percent as a signal that an upstream feed is writing un-normalized names. The mechanism to watch is the same one that created the duplicates in the first place: a source that skips normalization will regenerate variants faster than you can merge them. Fix it at the intake, not the master. When you need to rebuild or extend the list of firms behind this universe, describe the segment you want in plain English rather than reconciling yet another export by hand - that is the friction the whole pipeline exists to remove.