Finding the Same Thing Written Differently

Duplicates are near-universal

Every business with more than a few years of records has duplicate customers, suppliers or products. They accumulate through data entry, imports, mergers and systems that do not check.

Duplicate master records are the single most common blocker in integration and reporting projects. Validation against a master with four versions of one customer produces four different answers.

Two stages

1. Exact matching on identifiers — registration numbers, email addresses, codes
2. Fuzzy matching on names, addresses and other text, scored and reviewed

The first is safe to act on automatically. The second is not, and should always produce a review queue rather than a merge.

What makes fuzzy matching work

• Normalise first — case, punctuation, common abbreviations, legal suffixes
• Compare several fields, not one
• Weight the fields by how distinctive they are
• Block on something cheap first, so you are not comparing everything to everything
• Score, then threshold, then review

Merging is the risky part

Keep a record of every merge and what it combined. Undoing a wrong merge without that record is close to impossible.

Prevent recurrence

Deduplication is a one-off exercise unless the creation process is changed. A check at the point of creation — does something similar already exist? — prevents most of it.

Otherwise you will be running the same exercise again in two years.

Frequently asked questions