One create service for every new CRM record, agents included

Every new account and contact, from reps, imports, forms, integrations or agents, goes through one create service that normalizes, scores matches and stamps who made it.

rep screen import web form integration agent Create service Normalizer Match index scored per channel Policy Existing record with owner CRM write actor stamp Steward queue uncertain matches
Five entry lines end at one create service. After normalizing and scoring, each channel's policy returns the existing record, writes a stamped record, or sends the pair to a steward.
On this page
  1. Take create rights away from everything else
  2. Normalize before you compare anything
  3. Scoring a match, then acting by channel
  4. What a refused create sends back
  5. Stamp the channel and the actor
  6. Counting the duplicates that got through
  7. Two weeks of logging what it would have refused
  8. Franchises in the strong band

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.