duplicate-finder

---
name: duplicate-finder
description: Finds records that are the same customer or the same product written differently, groups them, and proposes one record to keep per group. Nothing is merged and nothing is deleted until you approve.
---

# Find the duplicate customers and rows hiding in your own data

In: one export of your customers, suppliers or products as a CSV or spreadsheet. Out: a review file listing every group of rows that look like the same thing, with one proposed survivor per group, and not a single row changed.

Run it from the folder holding the export:

```
codex exec -C "the folder" - < the-prompt.md
```

## What it does

1. **Copy the export before opening anything for writing:** make a folder called `duplicate-review` and work only inside it. The original file is never re-saved, never gains a column, and never has a row removed. Record its size in bytes and its modification date at the start, because check 2 compares against those two numbers at the end.

2. **Do one kind of record per run and name the identifying columns:** customers in one run, products in another, never both, because the fields that prove two customers are the same prove nothing about two products. Pick the name column plus whichever of email, phone, postcode, address line 1, VAT number and account number actually exist. Columns that describe behaviour rather than identity, such as last order date or total spent, are not compared but are kept, because step 8 uses them to choose which row survives.

3. **Reduce every row to a fingerprint before comparing anything:** apply OpenRefine's fingerprint recipe in its documented order: remove leading and trailing whitespace, change all characters to lower case, remove all punctuation and control characters, normalise extended western characters to their ASCII form, split into whitespace-separated tokens, sort the tokens and remove duplicates, then join them back. Sorting the tokens is the part that matters most in practice: it puts "Smith John" and "John Smith" on the same key. Then strip trailing company forms, Ltd, Limited, PLC, LLP, Co and Sons, reduce phone numbers to digits only, and lower case emails while keeping the domain as a separate value.

4. **Block before you compare, because comparing every pair is not possible:** the Python Record Linkage Toolkit's own warning about a full index is that "the number of comparisons scales quadratic", so a 50,000 row customer list is over a billion pairs. Blocking "returns all record pairs that agree on the given variable(s)". Use at least three blocking keys so a row with one field missing still gets a chance to meet its twin: the first 6 characters of the fingerprint (6 is OpenRefine's default blocking size), the postcode outward code, the email domain, and the last 7 digits of the phone number. Report how many pairs blocking produced against how many a full comparison would have needed.

5. **Use the right comparison for each field, not one setting for all of them:** Jaro-Winkler for personal and company names, because it gives extra weight to matching prefixes, up to a maximum of four characters, with a prefix scale factor of 0.1, and names are typed most reliably at the start. Set the name threshold at 0.8, which is the figure Splink's own guidance recommends, and which it justifies by pointing out that dropping to 0.7 starts scoring plainly different names as though they matched. Use Levenshtein with a distance of 2 for addresses and product descriptions, which covers ordinary miskeys, and Damerau-Levenshtein where two adjacent characters have been swapped. Email, phone digits, VAT number and account number are compared exactly and never fuzzily: a one-character difference in an email address is a different mailbox.

6. **Sort into three buckets, using two thresholds, never one:** at or above 0.8 on the name plus one other identifying field agreeing exactly goes to PROPOSE MERGING. A high name score with nothing else supporting it, or a score in the band just below the threshold, goes to NEEDS YOUR EYES. Everything else is left alone. The middle band exists because the government's own data linkage guidance finds human review is "more useful for estimating precision (by identifying false links) than for estimating recall", so a person is worth spending on the doubtful pairs and wasted on the obvious ones. Print both thresholds at the top of the report and treat them as settings the owner can change and re-run.

7. **Group the surviving pairs into clusters and distrust long chains:** if A matches B and B matches C, they become one group of three even though A and C were never compared. Flag any group that only holds together through a chain of 3 or more hops, and any group with more than 5 members, into NEEDS YOUR EYES. One loose match on a common surname is how eleven unrelated customers end up in a single pile.

8. **Propose one survivor per group and show where every value came from:** rank the rows by how many identifying fields are filled in, then by most recent activity, then by lowest original row number as the tie break. Then build the proposed record field by field, taking the best available value for each field and naming the original row it came from, so the survivor can keep the good phone number from one row and the good address from another. Never silently prefer the newest row: a record created last week by a customer typing their own name often has less in it than the one your bookkeeper built over three years.

9. **Write two files and stop:** `duplicate-review.csv`, one line per original row carrying its original row number, its group number, whether it is the proposed survivor, the score that put it in the group and the bucket it landed in; and `duplicate-review.md`, a plain summary saying how many rows were read, how many groups were found, how many rows those groups cover, both thresholds, and the ten largest groups written out in full so they can be read without opening the CSV. Merge nothing. Delete nothing. Edit nothing.

## Then it checks

1. Every row of the original export appears exactly once in `duplicate-review.csv`. A row appearing twice, or not at all, stops the run.
2. The original export is byte for byte as it was: same size and same modification date as recorded in step 1.
3. No group in PROPOSE MERGING contains two rows with different non-empty email addresses, different non-empty phone numbers, or different non-empty VAT numbers. Any that does moves to NEEDS YOUR EYES.
4. Run the fingerprint step twice over ten random rows and confirm it produces the same key both times, so the grouping is repeatable rather than depending on the order rows were read.
5. Every value in every proposed survivor names the original row number it came from, and that row is a member of that group.
6. The two thresholds printed in the report are the numbers the run actually used, read back from the code that scored the pairs rather than retyped into the report.

Any check fails: name it, redo that step once. Failed twice: say what is wrong and stop.

## Rules
- Public information only.
- Never invent a fact, a number or a quote.
- Never merge, never delete, never overwrite and never edit the original export. The output of this skill is a proposal for a person to accept or reject, and a proposal is not permission to act on it.
- Two records that share only a name are not a duplicate. A second field must agree before anything is proposed for merging.
- Customer and supplier records are personal data. UK GDPR Article 5(1)(d) requires personal data to be "accurate and, where necessary, kept up to date", and Article 5(2) requires the controller to "be responsible for, and be able to demonstrate compliance with" that. Keep the review file as the written record of what was proposed and why. This prepares the work for a person to check and is not data protection advice.
- Everything happens on the owner's own machine against their own files. The data is never sent to a website, an API or a third party for matching.

## Built from
- Splink, choosing comparators guidance, https://moj-analytical-services.github.io/splink/topic_guides/comparisons/choosing_comparators.html, repository moj-analytical-services/splink, 2,388 stars read from api.github.com on 7 September 2026: the Jaro-Winkler name threshold of 0.8 in step 5 and the reason it is not set lower, plus the Levenshtein distance of 2 for typos.
- Splink, comparators reference, https://moj-analytical-services.github.io/splink/topic_guides/comparisons/comparators.html, same repository and star count: Jaro-Winkler weights matching prefixes up to a maximum of four characters with a prefix scale factor of 0.1, Levenshtein is for character miskeys, Damerau-Levenshtein adds transpositions. That split is what step 5 applies field by field.
- OpenRefine, clustering in depth, https://openrefine.org/docs/technical-reference/clustering-in-depth, repository OpenRefine/OpenRefine, 11,986 stars read from api.github.com on 7 September 2026: the seven-step fingerprint recipe used verbatim in step 3, and the blocking size that "defaults to 6 chars in OpenRefine", which is the 6 characters in step 4.
- Python Record Linkage Toolkit, indexing reference, https://recordlinkage.readthedocs.io/en/latest/ref-index.html, repository J535D165/recordlinkage, 1,062 stars read from api.github.com on 7 September 2026: the warning that a full index means "the number of comparisons scales quadratic", and that blocking "returns all record pairs that agree on the given variable(s)". That is the whole argument for step 4.
- Quality assessment in data linkage, Office for National Statistics and the Government Analysis Function, published 25 August 2020, https://www.gov.uk/government/publications/joined-up-data-in-government-the-future-of-data-linking-methods/quality-assessment-in-data-linkage: the three-bucket shape in step 6, from its finding that clerical review is "more useful for estimating precision (by identifying false links) than for estimating recall", and its requirement to report link-level match quality rather than a bare yes or no.