Search the CRM for one mid-sized customer and four accounts come back. A rep typed one in by hand. An event import made another. The website made a third when someone at the customer asked for a demo, and a prospecting agent made the fourth while researching the company.
All four passed a duplicate check. Just not the same one.
The rep screen has a rule someone tuned years ago. The import tool has a matching option the uploader may not tick. The integration checks its own external ID. The agent goes through the API, which often checks nothing. Merging the four takes an afternoon. Stopping a fifth means changing how records get made. That takes one create service in front of the CRM, and no other way to create an account or contact.
Take create rights away from everything else
The service takes a proposed record and the channel it came from, normalizes it, scores it against existing records, and applies that channel’s policy: write it, hand back the existing record, or write it and flag it for a person.
None of that helps if anything can go around it. Every channel calls the service, the rep screen included, and the CRM accepts account and contact creates only from the service’s integration user. Lead conversion counts as a rep-screen create, and it’s the path people forget. Leave one path writing directly and that’s where your duplicates will come from.
Normalize before you compare anything
Most failed matches are formatting. “Acme Inc.” and “ACME, Incorporated” are one company to a person and two strings to a database. Every create goes through these steps first:
Normalization, applied in this order to every create
1. Domain Website field, or the email domain if there is no website.
Lowercase. Strip scheme, "www.", path and port.
Reduce to the registrable domain with the Public Suffix
List: shop.acme.co.uk -> acme.co.uk.
Discard free webmail and ISP domains.
2. Country Map to ISO 3166-1 alpha-2: "USA", "U.S.", "United States" -> US.
3. Brand, city If the raw name ends in " - " or " | " followed by the
record's own city ("Acme Dental - Austin"), split there.
The brand goes on to step 4. The city is kept for matching.
4. Name Unicode NFKC, lowercase, fold accents, "&" -> "and",
remove punctuation, collapse spaces.
5. Suffix Strip trailing legal suffixes from the list below, whole
words only, longest first and repeatedly, never down to an
empty name. Keep "group" and "holdings": they often mark a
separate legal entity.
6. Phone Parse to E.164 with the country from step 2 as the default
region. Drop extensions. If it does not parse, leave it empty.
Legal suffixes, longest first:
private limited, incorporated, corporation, company, limited,
pty ltd, pvt ltd, gmbh, sarl, corp, inc, llc, llp, ltd, plc,
sas, srl, ab, ag, bv, co, kg, kk, lp, nv, oy, sa
Strip "spa" only when the country is IT, and "as" only for NO or DK,
so "Acme Day Spa" in Ohio keeps its spa.
Scoring a match, then acting by channel
The match index holds those normalized fields for every existing account, kept current from the CRM’s change events. Events land seconds late, and two creates for one company can arrive inside that gap, so the service also writes each new record into the index before returning. An import file gets matched against itself first.
An exact domain is strong evidence and a fuzzy name in the same country is medium. A shared phone is weak alone, since one switchboard or booking line can serve several businesses, but it adds up.
The total lands in one of three bands, and each channel decides what a band means. On a strong match, a rep or agent gets the existing record back and nothing is written, an import fills that record’s empty fields, and a web form puts its contact under it. The uncertain middle gets created and flagged to a steward queue, so nobody’s work stops on a maybe. Contacts get a similar file keyed on email.
# entry-policy.yaml, read by the create service on every account create
version: 12
object: account
match:
index: account_match_index
combine: sum_capped_at_1 # add the weights of every rule that fires
rules:
- id: exact_domain
when: { domain: equal }
weight: 0.90
- id: fuzzy_name_same_country
when: { name_similarity_min: 0.88, country: equal } # Jaro-Winkler on normalized names
weight: 0.60
- id: same_phone
when: { phone: equal }
weight: 0.30
never_match: # checked before scoring
- id: franchise_locations # Acme Dental - Austin is not Acme Dental - Dallas
when: { brand: equal, domain: different, city: different } # different: both present, unequal
thresholds:
strong: 0.85 # at or above: same company
possible: 0.50 # from here up to strong: uncertain
actions:
create: "Write the record."
return_existing: "Write nothing. Return the existing record, its owner, its territory and a request-access action."
fill_blanks_on_existing: "Copy values into empty fields on the existing record. Never overwrite."
use_existing_account: "Write no account. Create the incoming contact under the existing one."
create_and_flag: "Write the record and send the pair to the steward queue."
# flag_only: true means work out the action, log it, create anyway,
# and send every strong match it would have acted on to the steward queue.
channels:
rep_screen: # lead conversion included
flag_only: false
on_strong: return_existing
on_possible: create_and_flag
on_none: create
agent:
flag_only: false
on_strong: return_existing
on_possible: create_and_flag
on_none: create
import:
flag_only: false
on_strong: fill_blanks_on_existing
on_possible: create_and_flag
on_none: create
web_form:
flag_only: false
on_strong: use_existing_account
on_possible: create_and_flag
on_none: create
integration:
flag_only: true # still in its two weeks of flag-only
on_strong: return_existing
on_possible: create_and_flag
on_none: create
stamps:
created_via: [rep_screen, agent, import, web_form, integration]
actor_type: [person, agent, system]
also_record: [actor_id, on_behalf_of] # on_behalf_of: the person an agent works for
apply_to: [account, contact]
What a refused create sends back
A bare “duplicate detected” error mostly teaches people to misspell company names more creatively. What comes back with the refusal decides whether the block sticks.
A rep gets the existing record, its owner, its territory and a request-access action, in case the first account is someone else’s. The request runs through the same rules as the territory assignment engine, so the account either moves with a reason attached or stays, and the rep sees which rule decided.
An agent gets the existing account’s ID and attaches its findings there. An import gets a results file, one row per input row: created, matched to which record, or flagged.
Stamp the channel and the actor
Every record the service writes gets created_via (the channel) and actor_type (person, agent or system), plus actor_id and, when an agent works for someone, on_behalf_of.
The duplicate report below groups by created_via. Without it you can’t tell which channel leaks. The actor fields are for cleanup. If a prompt change sends an agent off creating junk accounts all afternoon, its actor_id and a time range give stewards the exact set to delete, and nothing a person typed gets swept up.
Counting the duplicates that got through
Track the duplicate-creation rate per channel weekly, with one proxy: a new account merged with an older one within 30 days of its creation, whichever survives. It misses duplicates nobody notices and a week’s figure settles only a month later, but it’s cheap and it points at the leaking channel. The query needs a merge log with both record IDs and an account table that keeps merged-away rows. If your CRM doesn’t log merges, route them through the service too.
-- Weekly duplicate-creation rate by channel.
-- A new account counts as a duplicate if, within 30 days of its creation,
-- it was merged with an older account, whichever of the two survived.
-- Assumes crm.account keeps merged-away rows (soft-deleted, not dropped).
-- Only weeks whose newest account has had its full 30 days are shown.
WITH new_accounts AS (
SELECT
a.id,
COALESCE(a.created_via, 'unknown') AS created_via,
a.created_at,
DATE_TRUNC('week', a.created_at) AS created_week
FROM crm.account AS a
WHERE a.created_at >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '20 weeks'
AND a.created_at < DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '5 weeks'
),
merge_pairs AS (
-- each merge, seen from both records
SELECT merged_id AS account_id, surviving_id AS other_id, merged_at
FROM crm.merge_log
UNION ALL
SELECT surviving_id, merged_id, merged_at
FROM crm.merge_log
),
duplicates AS (
SELECT DISTINCT n.id
FROM new_accounts AS n
JOIN merge_pairs AS p
ON p.account_id = n.id
AND p.merged_at < n.created_at + INTERVAL '30 days'
JOIN crm.account AS o
ON o.id = p.other_id
AND o.created_at < n.created_at
)
SELECT
n.created_week,
n.created_via,
COUNT(*) AS accounts_created,
COUNT(d.id) AS duplicates,
ROUND(100.0 * COUNT(d.id) / COUNT(*), 1) AS duplicate_rate_pct
FROM new_accounts AS n
LEFT JOIN duplicates AS d
ON d.id = n.id
GROUP BY n.created_week, n.created_via
ORDER BY n.created_week, n.created_via;
Two weeks of logging what it would have refused
Picture a rep mid-task whose save is refused, with the refusal pointing at a record that turns out to be a different company. A few of those and people find workarounds.
Every channel starts with flag_only: true for two weeks. The service works out what it would have done, creates the record anyway, and sends each strong match to a steward, who confirms or rejects it. That gives a false-positive rate per channel before anyone is blocked. Switch a channel to blocking when you’d defend each wrong block to the person who hit it.
No model sits in this path. A create happens inside someone’s save, so the answer has to come back fast and the same every time, and a refused rep needs a reason they can check, like “same domain as an account owned by another rep.” A model can help stewards with the uncertain band later. It shouldn’t be the one refusing a save.
Franchises in the strong band
To test the design on paper I invented a company where 19% of new accounts in a quarter turn out to be duplicates. Imports cause 42% of them, the prospecting agent 27% (it creates an account for every company it researches), reps 21%, mostly for accounts someone else owns, web forms 7% and integrations 3%.
Two weeks of flag-only show a 6% false-positive rate, mostly franchises. “Acme Dental - Austin” and “Acme Dental - Dallas” are separate customers with their own websites, but they share the brand’s booking line, so name plus phone puts them in the strong band. One rule fixes it, franchise_locations in the policy above: same brand, different domain and different city is never a match.
After the switch to blocking, weekly duplicate creation drops from 140 to 22. The agent’s change is my favorite. A refused create hands it the existing account, so its research lands on the record a rep already works from instead of a fresh one nobody opens.