SiSu RevOps

M5  /  L5.3  /  about 11 minutes

Match rules with bands, and the merge that costs you a customer

Somebody in your company is going to run a dedupe. It might be you. When it goes wrong, it is not quiet: two real customers become one record, support cannot find the second one, the renewal sits on an account nobody owns, and the fix is a restore from a backup you have to ask for.

Duplicate detection is the one place in a hygiene pass where an agent hands you confident, well-formatted, wrong answers. Not because it is careless. Because "same company" is a judgment, and you did not write down what you meant.

This lesson runs on the accounts.csv you copied from your pack's data folder into 10-data/ in L5.1.

What you will be able to do

  • Write a match rule with bands: some pairs safe to auto-merge, the rest to a human with evidence attached.
  • Tell a duplicate apart from two records that merely resemble each other, and name the field that decides it.
  • Reject any duplicate output whose rows do not say why they matched.

Ban the unexplained score

You will be offered output like this:

account_a account_b match_score
ACC-02947 ACC-03047 0.96
ACC-02839 ACC-03086 0.94

Unusable, and not because 0.96 is wrong. A reviewer cannot tell which pair is safe, what the score is made of, or how to stop a rejected pair coming back next month. A number with no components is an opinion wearing a lab coat.

The rule for this module, and the exercise sheet in labs/LAB-05 of your practice pack checks for it (start with its README): if the output cannot state why two records matched, it is not reviewable, and therefore not usable.

Same output, made reviewable:

account_a account_b rule evidence band
ACC-02947 ACC-03047 R1 exact-domain-plus-name domain halloway-logistics.example.com identical; names identical after casing normalisation; country Germany both auto
ACC-02839 ACC-03086 R3 name-only-cross-country names differ only in legal suffix (AG, AB); domains follifoot-hospitality.example.com and .example.org differ; countries Germany and Sweden differ review, never auto

Same amount of agent work. The difference is entirely in your brief.

Bands, and the one that is not a band at all

Write the rule in 40-reference/field-policy.md, the file you started in L5.2, under a heading the brief can reference. The thresholds are yours. A starting shape that works on this dataset:

R1  auto band
    Normalised domain equal
    AND normalised name similarity >= 0.90
    AND country equal
    -> propose merge, still draft-only, still reviewed in bulk

R2  review band
    Domain equal, names not similar
    OR names similar >= 0.90, domains not equal
    -> one human decision per pair, with evidence

R3  never auto, regardless of any score
    Country differs
    OR one record is a Customer and the other is Churned
    OR either record has open pipeline
    -> human decision, and the survivor question goes with it

Normalisation, stated so it is reproducible: trim whitespace, collapse internal double spaces, lowercase, strip the legal suffix set you name (Inc, Ltd, Limited, PLC, GmbH, AG, AB, SA, SAS, SL, NV, BV, LLC, Corp), strip punctuation and ampersands.

Three things worth saying out loud:

R3 is not a third band, it is a veto. It runs first and ignores the similarity score. Every veto in it exists because something happened to somebody: merging across countries destroys a legal entity distinction, merging a customer into a churned record destroys revenue history, merging a record with open pipeline moves a live deal to an account the rep does not own.

The auto band is still draft-only. "Auto" means "accept in bulk after a scan", not "apply without a human". Nothing in this module writes to a system of record. Promotion is L5.4.

You chose 0.90, so be able to say why. Write the number and one line of reasoning in the file. Next month, when a pair lands just under it, you will want to know what you were thinking.

Try this now

Paste the brief section that produces the duplicate list:

OUTCOME
Produce a banded duplicate candidate list for accounts.

INPUTS
10-data/accounts.csv (308 rows). Rules: 40-reference/field-policy.md sections
R1, R2, R3 including the normalisation list.

OUTPUT SHAPE
30-output/hygiene/duplicates.csv, columns exactly:
account_a, account_b, rule, evidence, band, name_similarity,
domain_match, country_match, lifecycle_a, lifecycle_b, arr_usd_a, arr_usd_b

CONSTRAINTS
Apply R3 first. A pair that trips any R3 condition is band=review regardless of
similarity, and the evidence column states which condition fired.
Every row's evidence column names the fields compared and their values.
Do not merge anything. Do not modify accounts.csv.

DEFINITION OF DONE
Every candidate pair appears exactly once (A before B by account_id).
No row has an empty rule or evidence cell.

EXCEPTIONS RULE
A record with a blank domain and a blank owner cannot be scored. Write it to
30-output/hygiene/exceptions.csv with reason "insufficient fields to match".

What you should see: a candidate list you can check against the key afterwards. answer-keys/PLANTED-ISSUES.md in your pack, which you open only after attempting the lab, documents 8 account duplicates under a name variant, each with its partner named, for example ACC-02947 against ACC-03047 (Halloway Logistics AG, upper-case variant) and ACC-02890 against ACC-02988 (Highmoor Retail Inc, punctuation variant).

Note what the second pair does to a lazy rule: ACC-02890 is highmoor-retail.example.net and ACC-02988 is highmoor-retail.example.com. Same company, different domain string. A rule that requires domain equality misses it entirely. That pair belongs in R2, gets a human, and the human takes eight seconds: the evidence column already says employee count 7956 on both and owner Bettina Costa on both.

Tell these apart: a duplicate, and two records that resemble each other

This is the distinction that costs money when it is got wrong.

A duplicate

ACC-02947 ACC-03047
account_name HALLOWAY LOGISTICS AG Halloway Logistics AG
domain halloway-logistics.example.com halloway-logistics.example.com
country Germany Germany
employee_count 712 712
segment Mid-Market Mid-Market
owner Bettina Farrow Bettina Farrow
lifecycle_status Prospect Prospect
created_date 2025-03-15 2024-09-19

Everything that identifies the company is identical. The only differences are casing and a creation date six months apart: the fingerprint of one company entered twice, most likely by a form fill that did not check. PLANTED-ISSUES.md lists ACC-02947 in the duplicate-accounts class, partner ACC-03047. Band: auto. Action: propose merge, survivor is the older record unless the reviewer says otherwise.

Two records that resemble each other

ACC-02839 ACC-03086
account_name Follifoot Hospitality Group AG Follifoot Hospitality Group AB
domain follifoot-hospitality.example.com follifoot-hospitality.example.org
country Germany Sweden
employee_count 1452 132
segment Mid-Market SMB
owner Aisling Calderon Franka Mertens
csm Tomasz Jonker Tomasz Jonker
lifecycle_status Churned Customer
arr_usd 0.0 27000.0

Name similarity here is higher than in the first example on most string measures: the names differ by one character. Same domain stem. Same CSM. A name-similarity rule with a 0.90 threshold merges these two without hesitating. And merging them deletes a live customer worth 27,000 into a churned record. The renewal date disappears, the CSM's book is short one account, and the first person to notice is the customer.

What separates them is not similarity, it is identity evidence: different country, different top-level domain, an eleven-fold difference in employee count, one record live and one churned. Any one of those is enough to veto. That is what R3 is for.

Worth knowing: neither row appears in the duplicate-accounts class in PLANTED-ISSUES.md; that class documents 8 pairs, all genuine duplicates, and this is not one of them. The Follifoot pair is real data that behaves exactly like this trap, so practice on it, but it is not a documented planted pair and the key will not confirm it.

What the feedback looks like

What a learner hears after submitting a duplicate list with ACC-02839, ACC-03086 in the auto band:

Your list put ACC-02839 and ACC-03086 in the auto band on a name similarity of 0.94, above your stated 0.90 threshold. The rule fired correctly. The rule is the problem.

These are two different companies. ACC-02839 is in Germany with 1,452 employees and is churned. ACC-03086 is in Sweden with 132 employees, is a live customer, and carries 27,000 USD of ARR. The names differ only in the legal suffix, AG against AB, precisely the part your normalisation strips before comparing. It removed the only piece of the name that distinguished the two entities.

Accepted in a bulk review, this merge removes a paying customer from your account list. That is the failure the band structure exists to prevent: a safety failure, not just a wrong count.

What would have caught it. Not a higher threshold: 0.94 still clears 0.95 on some measures, and raising the bar loses ACC-02890 against ACC-02988, a real duplicate whose domains differ. What catches it is a veto that runs before the score: country_match = false sends the pair to review no matter the similarity. The same veto catches the lifecycle mismatch, the second independent reason this pair must not merge.

What to change. Add R3 to field-policy.md and re-run. Then check for the opposite error: a real duplicate R3 pushed into review unnecessarily. If R3 sends half your candidates to review, it is too broad and you have moved the cost, not removed it. Report the trade in your run log.

What you keep

  • 40-reference/field-policy.md, now with R1, R2 and R3, your thresholds and your normalisation list.
  • 30-output/hygiene/duplicates.csv, banded, with rule and evidence on every row.

Check yourself

  1. Two accounts share a domain exactly, have completely different names, and one has open pipeline. Which band, and which condition decided it?
  2. A colleague proposes raising the similarity threshold from 0.90 to 0.97 "to be safe". Name the real duplicate class in this dataset that change would start missing, and say what you would do instead.
  3. Your duplicate list has 40 rows and 38 are in the review band. Is the rule too tight or too loose, and what does that cost the reviewer?

Where this goes next

L5.4 is where the pass gets checked, where the naive roll-up gets caught, and where you write down the conditions under which any of this would ever write to the CRM.