Duplicate rate as a metric you can actually report to a board

Somebody on the board asks how clean the registration data is, and the honest answer takes twenty minutes to give. Duplicate rate as a metric is the shortest version that survives the room: duplicate records over total records, one number, tracked edition on edition.

The trouble is that the number is not one number. On the same file of 40,000 registrations I can produce 5.6 per cent, 4.0 per cent or 2.3 per cent, all of them arrived at honestly, and the difference is entirely in what I decided a duplicate was before I started counting.

What has to be decided before a duplicate rate means anything

Three decisions, and none of them is technical.

The first is the rule that puts two records in the same group. DAMA UK's 2013 paper on the six primary dimensions for data quality assessment defines uniqueness as no thing being recorded more than once based upon how that thing is identified, and the second half of that sentence is the load bearing part. Identification is a choice you make. Change it and the population of duplicates changes with it.

The second is what the thing is. Black and van Nederpelt's 2020 research paper for the DAMA NL Foundation separates two definitions that get used interchangeably and are not the same: objects uniqueness, the degree to which objects of the real world occur only once as a record, and records uniqueness, the degree to which records occur only once in a data file. A person who registered twice with two different email addresses breaks the first and satisfies the second. Two identical rows written by a form that let somebody press submit twice break both.

The third is the population. One event edition, one show across five years, or the whole portfolio. A buyer attending your kitchen show and your hospitality show is a duplicate at portfolio level and a perfectly correct pair of registrations at edition level.

Working the rate on one edition of 40,000 registrations

Take a single edition with 40,000 registration records and count it three ways.

Under a verified email rule, where two records are the same when they carry the same email address that has been confirmed by a click or survived a deliverability check, 1,600 records sit in a group with at least one other. That gives 1,600 divided by 40,000, which is 4.0 per cent.

Loosen it to exact name plus company, normalised for case and punctuation, and 2,240 records fall into groups, which is 5.6 per cent. The extra 640 records are mostly two people at the same firm who share a surname, plus the handful of genuinely identical names that any file of this size contains, so the looser rule has bought you a bigger number and a worse one.

Now count the surplus instead. Those 1,600 email matched records resolve into 690 distinct people, so merging every group would remove 1,600 minus 690, which is 910 rows. That is 910 divided by 40,000, or 2.3 per cent.

Three numbers from one file: 5.6, 4.0 and 2.3 per cent. Each of them is arithmetically correct. Only one of them should ever appear on a slide with your name on it, and which one matters less than never quietly swapping it for another.

Which of the three numbers goes on the board slide?

I would report the verified email rate, 4.0 per cent, with the definition printed under it in six words.

The reason is that it is the version a finance director can act on. It says four in every hundred registration records are a second copy of somebody already in the file, which converts directly into badge stock, into email sends against a list, and into the denominator of every audience number the show publishes. The surplus version, 2.3 per cent, is the better engineering number, because it tells you how many rows a merge would actually remove. The name plus company version is the worst of the three, because its errors run in the direction of counting strangers as the same person, and a metric that overstates a problem gets discounted the first time somebody checks a sample.

Whichever you pick, print the rule next to it. A duplicate rate without its definition is a number nobody can reproduce, and the first person who tries will get a different answer and stop believing the series.

The query that produces it

The whole measurement is a group by, and it should be short enough that a sceptical colleague can read it in one screen.

select count(*) as dup_records
from (
  select lower(trim(email)) as k, count(*) as n
  from registrations
  where edition_id = :edition
    and email_verified = true
  group by 1
  having count(*) > 1
) g
join registrations r on lower(trim(r.email)) = g.k

The surplus version is the same query with sum(n - 1) in place of the record count. Run both, store both, and let the board number be one of them.

Two things go wrong in practice. Placeholder emails such as noreply@ or the shared inbox a group registration was made from will collapse dozens of genuinely different people into one enormous group, so exclude a small blocklist of addresses and report how many records that removed. And unverified emails carry typos, which split real duplicates apart and flatter your rate, which is the reason for the verified condition in the first place.

How do you keep the number comparable across editions?

Freeze the rule, and version it when you change it.

Store the definition as a short identifier next to the figure, something like dup_rule_v2_verified_email_edition, and write the rate into a table with the edition, the population size and the rule id. When you improve the matching and the rate moves from 4.0 to 2.8 per cent, the table shows a rule change on the same date and nobody mistakes a measurement improvement for a data improvement.

Recompute history under the new rule where you can. Two editions restated under one rule tell you something. Two editions under two rules tell you nothing, and somebody will put them on the same chart anyway.

The rate you report is a floor, and the slide should say so

The verified email rule counts only the duplication it can see. A person who registered once from a work address and once from a personal one sits in the file twice, matches on nothing, and appears in no group, so the 4.0 per cent is a lower bound on the real duplication in the file. Print the words "at least" in front of the figure.

This is the objects and records split from earlier doing practical work. The email rule audits records uniqueness against one identifier, while the person with two addresses is a failure of objects uniqueness, invisible to any rule built on exact keys. Closing that gap is a matching exercise, which is cluster J's ground.

The floor framing also survives an audit. When a sceptical director pulls ten records and finds a duplicate pair the rate missed, a floor absorbs the finding and the series keeps its credibility, while a figure presented as complete loses the room over a single counterexample.

What target should sit next to the number?

A board that accepts a metric asks for a target at the next meeting, and there is no external number worth giving them. Any published duplicate rate was counted under somebody else's rule on somebody else's file, and the worked example above moves from 2.3 to 5.6 per cent on definition alone, so a borrowed benchmark would measure nothing about you.

Set the target on the trend instead. Flat or falling under a frozen rule is the healthy state. The alarm worth writing down in advance is a jump of more than a point between editions, because a jump that size means the intake changed: a new registration form shipped, or an imported partner list arrived with its own copies of people already in the file. At that moment the rate is doing its best work, because it has located a process problem while the fix is still cheap. Fix the form or the import first, and let the merge queue mop up what already got in.

Where the duplicate rate stops

The rate is a count. It says nothing about which records the duplicates are, and that is where the money is.

Two duplicate registrations for a visitor who never showed up cost you a badge and a line in a spreadsheet. Two duplicate records for a buyer your sales team has been chasing for three years cost you a meeting, because the outreach history sits on one record and the person is working from the other. A file with 4.0 per cent duplication concentrated in your top accounts is worse than one with 8 per cent spread evenly across day visitors, and the single rate cannot tell those apart. Weighting by whatever value you already hold on a record is the fix, and it is worth doing before you spend a quarter chasing the headline number down.

The rate is also silent on the errors made in the other direction. Every merge you perform to reduce it can collapse two real people into one, and that failure is invisible, permanent and much more expensive than the duplicate it removed. Measuring it needs a separate sample of merges labelled by a human, which is K25's subject and the number I would watch more closely than this one.

Start by running the query above on your most recent edition, twice, once counting records and once counting surplus. Write both numbers and the rule down in one place. Then find out where they came from, by tagging each record with the system that created it and grouping the duplicate clusters by the sources they span, which is K26 and usually points at one form with a resubmission problem. Both numbers belong in the same unified data reporting as the audience figures they quietly distort.