Duplicate attendee records across shows and the six stage fix for them

The audience team says the portfolio drew 128,400 people last year. The warehouse says 96,300. Both numbers came out of the same registration platform, and the gap is duplicate attendee records across shows: rows that belong to one human being and are counted as several because nothing in them is equal.

The version of this argument that gets made in most organisations is that the data is messy and somebody should clean it. That framing has cost a lot of teams a year. The rows are not dirty. They are accurate records of four separate registration events, each one correct at the moment it was captured, and the work is to decide which of them refer to the same person and what the surviving record should say.

Halbert Dunn, then chief of the National Office of Vital Statistics at the US Public Health Service, gave the problem its name in the American Journal of Public Health in December 1946. Every person, he wrote, creates a Book of Life that starts with birth and ends with death, and record linkage is the process of assembling the pages of that book into a volume. His complaint eighty years ago was that no personal cross index exists to lead anyone to all of a person's records. A registration archive is the same problem with worse handwriting.

What one buyer looks like in four registration files

Take a category buyer at a grocery group who attends two of your shows on alternating years. Across five years your files hold these rows.

• 2021, packaging show. Anna Kowalczyk, [email protected], Northgate Foods Ltd, no mobile captured, postcode M15 4BX.
• 2023, ingredients show. Ania Kowalczyk, [email protected], Northgate Foods, mobile 07700 900412, postcode M15 4BX.
• 2024, packaging show. Anna Kowalzcyk, [email protected], Northgate, mobile 07700 900412, no postcode.
• 2026, ingredients show. A Kowalczyk, [email protected], Northgate Foods Group, no mobile, postcode M2 3AA.

Four rows, one buyer. The email addresses agree on nothing. The employer is written four ways, one of which is a shared inbox belonging to an assistant. The surname carries a transposition in 2024 and a nickname in 2023. Postcode agrees twice, disagrees once and is missing once.

Now scale it. If 128,400 rows resolve to 96,300 people, the duplication factor is 128,400 divided by 96,300, which is 1.33. That average hides the shape of the problem, because duplication is concentrated. A person who came once contributes one row and one person. Everything above 1.0 comes from people who came more than once, which is the exact population your commercial team is asking about when it asks how many buyers the portfolio shares.

The six stages, and why four is not enough

Binette and Steorts, reviewing the field in Science Advances in 2022, present the standard pipeline as four stages: attribute alignment, blocking, entity resolution and canonicalisation. That is the right skeleton for a research paper. An event business running this against live registration data needs six, because two of the four have to be split before anyone will trust the output.

The six are ingest, blocking, pairwise scoring, decision, survivorship and golden record. Scoring and decision separate because the score is a property of the pair and the decision is a policy with thresholds somebody signs. Survivorship and golden record separate because choosing which value wins is a different argument from publishing the result to downstream systems, and the two get changed by different people on different timescales.

Ingest, where more matching problems get solved than anywhere else

Ingest is normalisation, and it is dull, and it removes more duplicates per hour of effort than any scoring model you will build afterwards.

Lowercase and trim the email. Strip the plus tag. Parse the mobile to an international format so that 07700 900412 and +447700900412 stop being different strings. Strip legal suffixes and punctuation from the employer name so that Northgate Foods Ltd, Northgate Foods and NORTHGATE FOODS LTD. collapse to one token set. Split the postcode into its outward and inward parts, because the outward part survives an office move within a city and the full postcode does not.

On the four rows above, normalisation alone links the 2023 and 2024 records through the mobile number, which was stored with a space in one file and a leading plus in the other. One join, no model, two rows resolved. Do this before you measure anything, because a matcher evaluated on unnormalised data is measuring your parser.

What does blocking actually do to the comparison count?

Comparing every pair in a file means n times n minus one, over two. For 128,400 rows that is 128,400 times 128,399 divided by 2, about 8.24 billion pairs. At a hundred thousand comparisons a second that run takes roughly 23 hours, and you will want to run it more than once.

Blocking cuts the candidate set by only comparing rows that share a cheap key. Block on the first three characters of the normalised surname plus the email domain, and the Kowalczyk rows sharing northgatefoods.co.uk land together while the gmail row does not, which is the whole tension in blocking for entity resolution and the reason you run several passes rather than one. The stage matters here because any true pair whose rows never share a block cannot be recovered later, no matter how good the scoring is.

Pairwise scoring, then a decision that is a policy

Scoring compares the fields of a candidate pair and returns a number. Between the 2021 and 2023 rows you have a surname that agrees exactly after normalisation, a forename pair of Anna and Ania that a string comparator scores around 0.87, employer tokens that overlap completely once the suffix is stripped, a postcode that agrees in full, and an email that disagrees. Four agreements and one disagreement, and the disagreement is on the field most teams treat as decisive.

The decision stage turns that number into an action, and it needs two thresholds rather than one. Above the upper threshold, merge. Below the lower one, leave alone. In between, a person looks. A pair of cut offs at 92 and 75 on a 0 to 100 scale is a reasonable starting policy, and it is a policy, chosen by somebody, sized against how many pairs a steward can actually work through in a week.

The asymmetry is what drives the choice. A missed match leaves a duplicate, which is visible and annoying and fixable next quarter. A false merge deletes a buyer from your universe, stops her receiving anything, and leaves no error message behind. Set the upper threshold where a wrong merge is rare, and let the review band absorb the argument.

Why is survivorship a separate stage from the merge decision?

Because the two questions have different owners. Deciding that four rows are one person is a data question with a probability attached. Deciding that the surviving record says Northgate Foods Group rather than Northgate Foods Ltd is a business rule, and it will be wrong for some fields under any policy you pick.

Most recent wins is the usual default and it is right for employer, job title and mobile. It is wrong for a corporate email address when the most recent row came from an assistant's shared inbox, which is exactly what the 2026 row is. Prefer corporate email over free webmail, prefer the longest non nickname form of a first name, and keep the rule set short enough that somebody can read it aloud in a meeting.

The golden record then holds the survivors plus the provenance: which source row each winning value came from, which rule chose it, and when. Article 5(1)(d) of the General Data Protection Regulation requires personal data to be accurate and, where necessary, kept up to date, with every reasonable step taken to erase or rectify inaccurate data. A merged record with no lineage cannot demonstrate that, and it cannot answer the only question a steward ever asks, which is why this record says what it says.

Where this stops

Six stages will not save you from a genuinely ambiguous pair. Two buyers at the same small distributor, related, same surname, same address, same employer, both registering: the evidence points at one person and the truth is two. No threshold fixes that, because the model is reading the data correctly.

The second limit is measurement. You cannot report a duplicate rate without a labelled sample, and building one is manual work. Two hundred pairs judged by a person who knows the sector gives you a defensible estimate of how many true pairs your pipeline is finding, which matters more than the pipeline's internal score, and it is the difference between a number you can defend and a number you inherited. The wider portfolio case for doing any of this, including why exact email matching undercounts the buyers you care about most, sits in cross event attendee matching, and the mechanics of one person holding many email addresses sit in their own post.

Start this week with a count that takes an afternoon. Take two editions of one show and normalise the emails and mobile numbers. Count the rows matching on email. Then count the rows matching on mobile where the email differs, and the rows matching on surname plus employer where both differ. Those last two counts are the duplicates your current unified data layer is missing, measured on your own file.