Every revenue question about a company comes back to one list: its customers. In manufacturing and distribution that list rarely exists in one place. The ERP holds orders and invoices, the CRM holds some of the conversations, the inboxes hold the rest, and ten years of acquisitions, system migrations and manual entry have given the same customer many names.
A typical mid-sized company has records like these for a single account:
ACME MFG INC ship-to Dayton OH 45402 Acme Manufacturing, Inc. bill-to Dayton, Ohio 45402 Acme Mfg - Plant 2 ship-to Springfield OH 45501 acme manufacturing crm acmemfg.com ACME MANUFACTURING CO. erp-2014 Dayton OH
Until those are one account, nothing downstream is reliable: lifetime value, share of wallet, lapsed-customer lists and every forecast built on them. This is how we reconcile them.
1. Normalize, without overwriting
Each source record is copied into a staging table with a normalized name alongside the original. Normalizing means lowercasing, stripping punctuation, expanding common abbreviations and removing legal suffixes, so that ACME MFG INC and Acme Manufacturing, Inc. both become acme manufacturing.
import re
SUFFIXES = r"\b(inc|incorporated|llc|ltd|limited|co|corp|corporation|gmbh|sa|plc)\b"
ABBREV = {"mfg": "manufacturing", "intl": "international", "eng": "engineering", "&": "and"}
def normalize(name: str) -> str:
s = name.lower().replace("&", " and ")
s = re.sub(r"[^\w\s]", " ", s)
s = " ".join(ABBREV.get(t, t) for t in s.split())
s = re.sub(SUFFIXES, " ", s)
return re.sub(r"\s+", " ", s).strip()
Addresses get the same treatment: standard state and street abbreviations, postal codes trimmed to five digits in the US. The original values are never changed.
2. Block, then compare
Comparing every record with every other is slow and produces false matches. Records are first grouped into blocks that share a cheap key, such as the email domain, the postal code, or the first word of the normalized name, and only records in the same block are compared. Generic domains like gmail.com are excluded from domain blocking, because they say nothing about the company.
3. Score candidate pairs
Each pair in a block gets a score from several signals: string similarity on the normalized name (we use token-set similarity, which ignores word order), an exact match on domain or phone number, and the distance between addresses. A shared domain is strong evidence. A similar name alone is weak, because "Precision Machine" describes hundreds of companies.
4. Three bands, one of them human
Pairs above a high threshold are merged automatically. Pairs below a low threshold are rejected. The band in between goes to a person who knows the customers, usually someone in customer service or sales, with both records side by side. In a dataset of a few thousand accounts, the review band should be small enough for one person to clear in a day or two. If it isn't, the thresholds need work.
5. Keep a crosswalk
The output is a crosswalk table that maps every source record to one master account ID, with the reason and the score for each link. Merges can be undone by deleting a row. Plants and divisions are kept as children of a parent account, because a distributor needs to see both the parent's total and the plant that stopped ordering.
source_system | source_id | master_id | rule | score erp | C-10442 | A-0081 | domain+name | 0.97 erp | C-20917 | A-0081 | reviewed | 0.78 crm | 88123 | A-0081 | domain | 1.00
What goes wrong
Three cases cause most of the errors. Distributors and buying groups appear as the customer on invoices for many end users. Acquired companies keep their old name in one system and the new one in another. And sales reps sometimes create a new CRM account for every contact. Each needs its own rule, written down where the team can see it.