Posts Tagged people matching

Following on from the previous post, here’s my own recipe for a people-matching system. I don’t go into all the gory technical details – the post would be five times the length – but I hope there’s enough here to get the theme across.

Assumptions

Let’s assume a few things:

We have a large number of Person records (millions?), all from the UK.

We believe that a significant proportion of these records are duplicates or redundant, as they represent the same people; but the details may differ.

We only want to identify the records that match; we’re not looking to build a single all-encompassing representation of a person, wherein data from one record ‘enriches’ another. (More to come on that subject in a future post.)

The quality of data in each Person record varies, maybe markedly; some records may be customers we have a long-standing relationship with, and are very confident of their details. But other records might come from less trusted or unverified sources, e.g. a survey on the website that promises a voucher in return for answering some questions and entering a few personal details; these details would clearly be less reliable.

Our records have the following details (‘data points’):

First name

Last name

Date of birth

Address

Mobile phone number

Email address

, plus a unique PersonID. We might have other data points available, e.g. home telephone number, employment details, bank account number / sort code, and we could absolutely use them for matching. For now, we’ll concentrate on the six above.

Every Person record only has one set of details, that is, just the six pieces of information that we’ll use for matching. This restriction isn’t that realistic – people can have more than one mobile number or email address, and they move house, and occasionally change their names. Taking this into account in a system doesn’t significantly alter the structure of what’s being proposed here.

Canonical data

For each one of these data points, we want to generate and store what is known as the canonical version: that is, the version that is as standard as we can possibly make it. For names, the simplest thing to do is remove spaces, hyphens and apostrophes, which will help us match “Peter O’Toole” against “Peter Otoole”, or “Andrew Lloyd Webber” against “Andrew Lloyd-Webber”. Dates of birth don’t need changing, as long as they’re being stored in a sensible format (so in SQL we should be using DATE, and not VARCHAR). Phone numbers might be accepted in our system as “+442077319333” or “020 7731 9333”, but we need to translate them into one format – I’d go for the former.

Email addresses: As mentioned in the the previous post, email domains can be synonyms of each other, e.g. gmail.com and googlemail.com. Depending on your data, making domains canonical might be too much effort. Also, with some providers (including gmail) the local part of the address (the left-hand side of the @ symbol) can contain labels, so you’ll need to strip those out to make the canonical version.

Addresses: Making canonical versions of bricks-and-mortar addresses can be the biggest headache, which is why you’ll need an address validator: a piece of software that takes an unformatted address, and turns it into a formatted address. Examples of address validators include PCA Predict (formerly Postcode Anywhere) and Experian Data Quality (formerly QAS). The way they work is that if we present, e.g.

10
SW1A 2AA

or

10 Downing Street
City Of Westminster
LONDON

to the validator, then we’ll get the same result back:

10 Downing Street
LONDON
SW1A 2AA

If you pay for the extra data, then you can get the UDPRN (Unique Delivery Point Reference Number), which maps every postal address into an 8-digit code. If not, you can make your own loose version of it by hashing the text of the validated address, which you can then use for comparing.

From this point on, I’ll assume (a) that all our data points are canonical, and (b) there are no known ‘test’ people within our data – if there are, we should remove them from any matching process.

Excluding common data points

Next, we’ll need to consider generating lists of common and/or dummy data. For example, what are the most popular mobile numbers in the data?

In SQL, our query would look something like this:

SELECT
TOP 20
Mobile, COUNT(1) AS Total
FROM dbo.Person
WHERE Mobile IS NOT NULL
GROUP BY Mobile
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC

If your data comes to you via third parties (e.g. affiliates, introducers, brokers), I should imagine you’ll see something like:

+447000000000
+447777777777
+447123456789
+447111111111
...

with some surprisingly large counts against each. You clearly can’t use these mobile numbers for matching; in fact, you should look at all the associated Person records, to see if it’s worth excluding everyone with obvious dummy numbers like these. But you’ll probably just want to exclude this data point from the match, not exclude the entire Person record.

Similarly, you’ll need to do the same for email addresses, e.g. test@test.com, noone@nothing.com, etc.; and home telephone numbers, if you capture them. (As mentioned previously, when faced with a home telephone number field, some people like to enter phone numbers for their local Chinese restaurants, taxi firms and pubs.)

It’s not as necessary to identify common names and addresses, although you might see a few Joe Bloggs, John Smiths and David Camerons. [Brief aside: it’s always useful to have a table containing the headline details of famous people, so you can stop them getting into your system at the point of entry. It’s unlikely you’ll end up with the Prime Minister’s mobile number or email address in your database – but his name and address are no secret!]

Hard matches

We’re at the point where our data is as standard (canonical) as we can make it, and we have lists (tables) of data points we know we’re NOT going to be matching.

The next step is to ask: Given two sets of details, by what criteria would we confidently say “yes, these two people are the same”? A complete match would be:

First name, Last name, Date of birth, Address*, Mobile, Email, Home phone

, i.e. every single piece of information – but that’s the most obvious case, and there are certainly subsets of these details that we’d be happy to accept.

(* Address meaning either a UDPRN or a hashed version of the text data returned from the validator.)

If the details of Person A matched those of Person B, for any set of the following details, I’d consider the match to be a hard match:

Set 1: First name, Last name, Postcode, Mobile, Email

Set 2: First name, Last name, Address, Mobile

Set 3: First name, Last name, Address, Email

Set 4: Last name, Date of birth, Postcode, Mobile, Email

Set 5: First name, Last name, Date of birth, Address

Set 6: Date of birth, Address, Mobile

Set 7: Date of birth, Address, Email

where Set 1 is the best, and Set 7 the ‘least best’. (You, and your data, of course may disagree.) So, every single piece of information in a set has to be identical between person A and person B for us to consider it a match.

Note that no set of details is a subset of any other; the larger set of details would be redundant.

Remember that, if at all possible, our criteria shouldn’t match the following people:

Twins

Parents and children with the exact same name

Older relatives borrowing mobile numbers and email addresses from other family members

For each Person record, we’ll make (up to) 7 separate hashes, one for each set of details, and store them in a new table. An MD5 hash takes up 16 bytes, so if we had a million records, it’s a maximum of 150MB in space (if we include numeric IDs for the Person and for the set of details).

Finding hard matches between Person records then becomes as simple as finding hashes with more than one PersonID entry in our table – a simple GROUP BY, which if indexed properly, will be lightning quick.

(NB: If we’ve decided that a piece of information in a Person record isn’t suitable for matching, due to being too common or an obvious dummy, then we can’t generate any hashes that include that piece of information; we can’t just put a blank in, the match would be too loose. That’s why it’s “(up to) 7” – not “7” – hashes.)

What next?

What about the rest of our data? We’ve three options:

Do nothing. Depending on our requirements, this might be ok; in the grand scheme of things, what’s a handful of duplicate emails / letters / phone calls?

Manual matching. Again, depending on our situation, this might be perfectly feasible. If there’s any human intervention (for example, a phone call) in our end-to-end process, then it could be straightforward to give your customer-facing employees a screen that says “We’ve found these sets of details that might be the same person, do you agree?”, then let them approve or reject the match.

Scoring. Give various amounts of points to each part of the match criteria, depending on the strength of match between data points. If you like, you can consider this a soft match (cf. the hard matching above).

The second and third options require that you’ve some reason to believe a match might exist in the first place, e.g. an email address or mobile number in common; or a combination of date of birth and postcode. With the right indexes on the data, it’s trivial to generate these candidate lists quickly. (Of course, you don’t need to attempt to match Person records you’ve already matched against each other.)

Scoring

The goal is to come up with a scoring mechanism such that likely matches get more ‘points’ than unlikely ones. Outside of some Very Hard Computer Science (probably involving neural networks), I don’t know of any standard ways of generating scorecards where the outcome is unknown – if the outcome is known, then standard methods apply (Google: Building a scorecard.) However, for this particular application, common sense should get us where we want to go.

For string data like names (first or last), we can make an educated guess at a scoring mechanism, e.g.

Full match: 10 points

One letter out: 9 points

Name A is contained within B (or vice-versa), e.g. “Carter” would match “Knowles-Carter”: 7 points

First n letters of A = First n letters of B, where 1 ≤ n ≤ 4: n points

Last n letters of A = Last n letters of B, where 2 ≤ n ≤ 5: n-1 points

Both names are longer than 5 letters, and have 2 letters incorrect: 2 points

I don’t know that the above is optimal, but it feels like a good starting point.

How do we assess how far apart strings are? We need a function that, given two strings, calculates some notion of ‘distance’ between them. Such functions already exist, and the Levenshtein distance (the number of insert/replace edits needed to turn one string into another) is a popular choice.

For non-string data like dates of birth, we’d start with something like:

Full match: 8 points

One day out: 7 points

Month / day swapped: 6 points

One month out: 5 points

One year out : 4 points

Day and month correct: 3 points

etc. I’m sure you get the idea: the point is we have lots of options!

Hence, we build up a scorecard that takes two sets of details and returns a single number, a score. We can apply this scoring function to every pair of details in our candidate list, and generate a score for all matches. Inspecting these matches by eye, it becomes fairly obvious what a good threshold for acceptance would be, and what changes to our scorecard are needed. Within a few iterations, we should have a scorecard and threshold that give us the balance we need between matching correct sets of details (where correctness is assessed by eye, by manually checking a sample), and not matching incorrect sets of details.

The scorecards can get as complicated as you like: you can award extra points for an uncommon domain in the email addresses, or for having the same post town, or the same home phone number area code; you can subtract points for too many repeated digits in the mobile numbers, or having 1st January as the date of birth, or having a common name (‘John Smith’). The law of diminishing returns applies in spades – you have to determine whether the extra effort is warranted.

One very important thought: you MUST record exactly how a match between two sets of details was made. Was it a hard match? If so, which one? If the match was via a scorecard, which scorecard was it? (You’ll end up with more than one.) What was the score? What was the threshold that it passed? If you don’t record this information, you can’t hope to reliably improve on the matching process in future.

Summary

I’ve not finished on this subject yet, but hopefully this article gives you some ideas about building your own person-matching system, should you require one. I’ve yet to cover:

I’m going to spend a few posts on a subject that is, frankly, the bane of my life: people matching. That is, given two sets of person-related details, do I believe they are the same person? It’s eminently useful for many things including keeping marketing costs down, improving customer service, and very importantly, preventing fraud.

If your dataset(s) contain a unique person key, e.g. Social Security Number in the USA, or National Insurance Number here in the UK, then the task is obviously pretty simple (barring errors in the data). If there’s no unique person key, you’ve got a great deal more work to do. I’d say it follows a 95 / 5 rule: to match the first 95% of your dataset takes 5% of the time, the 5% that’s left takes the remaining 95% of the time. (Hence why it causes me grief: you can end up writing reams of code to match a handful of details, in a never-ending quest for greater accuracy!)

Before I start discussing how I’d do people matching in a “perfect world” scenario, I’m going to list some of the problems I’ve encountered when trying to match data from UK sources.

Names

Shortened or alternative forms of the first name: e.g. Bill / William, Peggy / Margaret, Jack / John. And these days, Alfie probably isn’t short for Alfred, just as Harry probably isn’t short for Harold (or even a boy’s name).

As per the above, I wouldn’t ever assume a particular first name implies a gender; you’ll be wrong at some point, and an awkward conversation might ensue.

Married names, people taking their spouse’s last name, without any restrictions on gender.

Double-barrelling last names with spouse or partner.

Very common names – names like ‘George Smith’ and ‘Claire Wilson’ mean placing more reliance on other pieces of information when matching.

Titles

In my experience, Mr/Ms/Miss/Mrs etc. are rarely correct enough to rely on to indicate gender or married status*, even when the primary source is data the customer has entered themselves. Also, the gender-neutral Mx is becoming increasingly common.

Let’s not even get into the realms of Professor, Doctor, Lord/Lady, Reverend and assorted military titles…

*Using gender and married status purely as aids to matching people, nothing else.

Dates of birth

It’s very easy to get the date of birth wrong with mis-typing, or getting the month and day the wrong way round. Also, people (a) don’t like to give their birthdate out, so may give a dummy one (1st Jan 1970 is common), or (b) will lie about their age if they think it improves their chances of obtaining a product or service.

People with “non-traditionally British” names

People from other countries adopting a Western-style first name alongside their traditional birth-name (e.g. Chinese people).

First names / family names may not be in the expected order (again, e.g. Chinese).

Names that have more than one translation into English, e.g. Mohammed / Muhammad / Mohamed.

Different character sets! Greek, Cyrillic, Arabic, etc.

(“Non-traditionally British” is an ugly turn of phrase, there must be a better way of putting it…)

Family

Fathers and sons with exactly the same first, middle and last names. (Far more common than you’d think!)

Twins; especially twins with very similar first names (Mia/Mya, Ethan/Evan).

You can’t reliably infer relationships using only differences in age; two customers from the same family, 32 years apart, could potentially be siblings, parent/child, or even grandparent/grandchild.

Addresses

Living at more than one address; in particular, students living away from home.

Different naming conventions; flats in Scotland can be named by floor number / flat number, e.g. 2/1 (2nd floor, 1st flat).

Some address-related problems can be solved by using the Unique Property Reference Number (UPRN) or the Unique Delivery Point Reference Number (UDPRN) to represent the address, but neither of these has widespread adoption yet.

Email addresses

Having more than one email address.

Labels, e.g. fred.smith+SPAM@mailbox.com and fred.smith+NOTSPAM@mailbox.com. The canonical version of the email address would be fred.smith@mailbox.com, which may be more useful for matching purposes.

Format: Validating the syntax of 99% of email addresses is straightforward, getting the full 100% is almost impossible. See here [wikipedia] for a brief explanation about which characters are allowed in an email address.

Other

People who work overseas and have non-UK mobile number and address; they could be a valid customer, as per your policies, but with only non-UK contact details. Do your systems accept a phone number that doesn’t start +44?

Driving License / Passport : most existing systems only validate the format of the identifying numbers, which makes them a target for fraudsters. Newer systems can validate images of the documents.

Device IDs are great for fraud detection, but can present a problem when matching people; families often share devices, and what about public computers in libraries and internet cafes?

Electoral Roll: Being on the full electoral roll at an address is no guarantee that the person is living there, and the converse is also true.

Third-party services exist to validate/verify almost all the information above, singularly and together. However, none of the services are perfect, so matching person-level data comes down to cost (third party data and development time), and your tolerance for mistakes – how embarrassing might it be if you get it wrong?

If you have any examples of when matching personal details has proved trickier than you thought it was going to be, please let me know in the comments below!