Choosing a string similarity measure for each field on a registration record

Open the config file of almost any first implementation and you find one line that sets a similarity function, one line that sets a threshold, and both applied to every field on the record. Jaro-Winkler at 0.85, everywhere, because that is what the tutorial used.

It half works, which is the problem. The surname comparisons look sensible, the company comparisons quietly produce nonsense, and nobody separates the two because the output is a single score. Choosing a string similarity measure is a decision to make per field, and the reason is that each field on a registration record fails in its own way.

Elmagarmid, Ipeirotis and Verykios grouped the measures by exactly that logic in their survey of duplicate record detection in IEEE Transactions on Knowledge and Data Engineering in 2007, splitting them into character based, token based, phonetic and numeric families. The families are not competing implementations of one idea. They answer different questions.

Why one measure everywhere fails

Think about how each field gets corrupted between two registrations by the same person.

A forename is corrupted by typing and by nicknames. Anthony becomes Tony, Kathryn becomes Katherine, and a hurried keyboard turns Daniel into Daneil. The differences are one or two characters, in a short string, usually late in the string.

A company name is corrupted by wording. Brightwell Packaging Systems Limited becomes Brightwell Packaging, or the division is written instead of the parent, or somebody types the trading name from the badge instead of the contracting entity from the contract. The differences are whole words.

An email domain is barely corrupted at all. Once lowercased, two records either share a domain or they do not, and a near miss is almost never the same domain mistyped.

A postcode is corrupted by truncation and formatting, and its similarity is geographic instead of textual, so two strings that look alike can be far apart and two that look nothing alike can be neighbours.

Put one character based function across all four and you get a measure that is right on the forename, systematically harsh on the company name, wasteful on the domain, and meaningless on the postcode. The score that comes out is dominated by whichever field happens to have the most characters.

What belongs on each field

The assignments below are defaults, not laws, and each of them has a reason attached that you can argue with.

Forename: a prefix weighted character comparator, plus a nickname table. The errors are keystrokes and the start of the name is the most reliable part, which is what the prefix bonus in J12 was built for. The nickname table is separate work and no string function replaces it, because Bob and Robert share almost no characters.

Surname: normalised edit distance with a length aware threshold. Convert the integer distance to a similarity by dividing by the length of the longer string, then apply a stricter cut on short surnames, for the reasons worked through in J14. Keep a phonetic code alongside it for blocking, never for scoring.

Company name: token overlap weighted by token rarity. The unit of difference is a word, so count words, and weight each word by how rare it is in your own exhibitor directory. That is J13's argument, and the weighting is what stops two firms sharing the word Systems from scoring 0.5.

Email domain: exact match after lowercasing. Nothing else. A similarity of 0.9 between two domains is not evidence of anything, since gmail.com and gmai1.com are different registrations belonging to different parties, and near misses on domains are more often phishing than typing.

Postcode: prefix agreement at a defined level. Discussed below, because this is the one I would change from the usual default.

Job title: nothing, unless you have a controlled vocabulary. Head of Marketing, Marketing Director and VP Marketing have no useful string similarity and comparing them adds noise to the score. Map to a controlled list or leave the field out of the comparison entirely.

How do you actually choose between two measures?

Build a labelled grid and read the two rates separately. This takes an afternoon and settles arguments that otherwise recur every quarter.

There is a published version of the same exercise worth knowing about first. Cohen, Ravikumar and Fienberg tested edit distances, token based measures and hybrids across a set of name matching tasks at the IJCAI workshop on Information Integration on the Web in 2003, and their strongest overall performer was a hybrid that put a character comparator inside a token scheme weighted by term frequency. Read that as an instruction to test on your own fields rather than as a recommendation to copy their winner, since their tasks were bibliographic and organisational name matching and yours is a registration record with a company field, a postcode and an email address on it.

Assemble 200 pairs: 100 you know to be the same person and 100 you know to be different, drawn from the same file so the difficulty is realistic. Drawing the 100 false pairs at random will make the test far too easy, since random pairs disagree on everything, so draw them from pairs that already agree on at least one field.

For each candidate measure and threshold, count two things. How many of the 100 true pairs does it fire on, and how many of the 100 false pairs does it fire on.

Suppose on the forename field a prefix weighted comparator at 0.90 fires on 88 true pairs and 9 false ones, while normalised edit distance at 0.85 fires on 79 true pairs and 6 false ones. Precision for the first is 88 divided by 97, which is 0.907. For the second it is 79 divided by 85, which is 0.929.

The second measure wins on precision and loses on recall, which is the least interesting way to read the grid. The useful number is the ratio between the two fire rates. The first measure fires 0.88 of the time on true pairs and 0.09 on false ones, a ratio of about 9.8. The second fires 0.79 against 0.06, a ratio of about 13.2. Per agreement, the second measure carries more evidence, and that ratio is what a scoring model consumes when it converts field agreement into weight, which is the mechanism in cross event attendee matching.

Then check whether the difference is real. The standard error on a proportion near 0.90 across 97 observations is the square root of 0.9 times 0.1 divided by 97, which is about 0.030. Three points. A gap of two points between two measures on 200 pairs tells you nothing, and a gap of fifteen points tells you plenty. Size the labelled set to the decision you are trying to make, and accept that 200 pairs eliminates bad options rather than ranking good ones.

The postcode is where I would change the default

The common advice is to use numeric distance on a postcode, and I think it is wrong often enough to be worth replacing.

Subtracting two US ZIP codes measures nothing consistent. 60601 and 60607 are six apart and both in Chicago. 96162 and 97001 are 839 apart and both in the Pacific north west, while 96161 and 96162 are adjacent numbers in the same district. The numeric line runs roughly geographically in some regions and jumps state boundaries in others, so a threshold on the difference means different things in different parts of the file.

Compare prefixes instead, at a level that corresponds to a real unit. The first three digits of a US ZIP code identify a sectional centre, which is a genuine geographic area. In the United Kingdom the outward code is the equivalent, so SW1A and SW1B agree at district level and W1 does not. Treat prefix agreement as a banded comparison: full agreement, agreement at the coarse level, and disagreement, each with its own weight.

The same argument applies to any field whose text encodes a hierarchy. Parse it, compare at the level that means something, and do not ask a string function to discover the structure by accident.

What does a missing field do to the grid?

It quietly changes what you measured, and this is the failure most likely to survive into production.

If 38 per cent of your registration rows have no phone number, then a comparison on phone is undefined for most pairs, and how your implementation treats undefined decides the result. Scoring a missing field as disagreement pushes every sparse pair below the threshold, so records from self-service channels match worse than records from managed ones, and it looks like a data quality finding about those channels when it is an artefact of your own default.

Score missing as neither agreement nor disagreement, which means the field contributes zero weight, and report field coverage next to every match rate you publish. When you build the grid of 200 pairs, count how many of them actually have both values present for each field. If only 46 pairs out of 200 have a postcode on both sides, your postcode comparison was chosen on a sample of 46, and you should say so.

Where this stops

The grid tells you which measure separates true from false pairs on the pairs you labelled. It cannot tell you about the pairs you never see, and your labelled set came from somewhere.

If the true pairs were harvested from a review queue, they are pairs the current system already surfaced, so they share whatever bias the current blocking has. Measures that would shine on the pairs you are missing look mediocre on the pairs you have. The correction is to include some pairs found by a different route entirely, such as two badge scans collected at the same stand on the same day, or a delegate who told you in a survey that they attended both shows.

The second limit is drift. A measure chosen on a 2024 file is running on a 2026 file with a different country mix, a different registration form and a different share of mobile completions. Nothing alerts you. Re-run the grid annually against a fresh labelled sample, and keep the old results, because the change over time is the most useful thing the exercise produces.

Take twenty pairs out of your review queue this week and score each field by hand under two candidate measures. You will find at least one field where both measures give the same answer on all twenty, which means the field is not doing any work and can be dropped from the comparison entirely, and that saving is usually larger than anything you gain by tuning the unified data thresholds.