What logic to use to rollup/merge multiple person entities as the same? (tight, but fuzzy enough to broaden matches)

Question

I have multiple instances of people entities which are often times the same person. Where the address First-Last is the same at the same address, it's a no-brainer to merge/rollup them.

However, due to data entry inconsistencies, there must be a way to deviate a bit from the exactness. I think the credit card industry does this a little bit: zip plus street number, or street name? ...something of that nature.

In order to solidify my matching, I cleaned up the address strings, trying to make them as standard as possible ("Hwy" --> "Highway", etc.).

I need something that still will make matches on records that would look obvious to a person just by glancing at them, but fails to have exactly matching data.

Here is my initial thought, concatenate a string made up of the following:

First Initial
LEFT8 of the LastName (allows inconsistent endings, such as "Esq." or "CPA")
LEFT3 of Zip
Street Number
LEFT8 of the StreetName (not Addr1 -- "Oak" for "8 N Oak Street")

Did I miss something here? I think I made it loose enough to overcome normal data entry inconsistencies, but tight enough to avoid incorrect matches.

Answer 1

I was involved in a project to clean up name and address data for a large financial institution. We achieved a success rate automatically of about 98.4% but unfortunately this still left about 150,000 mismatches.

The way we attacked the problem was to (over time) build up a rule base of the types of errors that could occur, and extending the fuzziness of the logic to cover identified classes of error.

A significant amount of data cleansing can indeed be done by reference to (UK) post codes and house number and/or name. In the UK fuzziness can be introduced by consideration of the first part of the post code - which determines a wide area. I'm not clear whether the same applies to zip codes.

However this approach does not deal well with addresses that are out of the normal run - my own address is an example; I live on a boat, and as a consequence have some additional pieces of address in order to ensure correct addressing.

Anomalies of this sort are always likely to need manual intervention.

Incidentally, your assertion that it's a no-brainer to merge/rollup people whose First-Last is the same at the same address needs to be challenged. The most difficult cases we had in data cleansing were precisely where there were two people (eg father and son) of the same name living at the same address. Equally, if somebody of the same name bought a property (which happens) then again there are problems of "re-duplication".