Background
A common task is matching a list of organizations, such as a file of a few hundred company or fund names, to the accounts in a CRM or database. The names rarely match exactly, and most differences aren’t spelling mistakes:- acronyms:
ADIAvsAbu Dhabi Investment Authority (ADIA) - abbreviations and state codes:
NJ Division of InvestmentvsNew Jersey Division of Investment - extra words:
TemasekvsTemasek Holdings Pte Ltd - a second organization in parentheses:
Northwind College (Contoso Advisors)
- Find candidates. Generate matching keys for each name with NAME_MATCH_KEYS and join the two sources on them. Only names that share a word or acronym are compared.
- Score. Score each candidate pair with FUZZY_MATCH and the
companymethod. - Decide. Keep the best account for each row, and label it
MATCH,REVIEWorNO MATCH.
id and name fields and the second has account_id and account_name. Rename to suit your data.
Steps
1. Generate keys on both sides
Add a Select after each source. Keep the fields you need and add a key field:
Each row now appears once per key.
NAME_MATCH_KEYS returns null for a blank or placeholder name such as N/A; the COALESCE gives such rows on the first source a key that matches nothing, so they still reach the output as NO MATCH. Accounts with blank names on the second source are dropped, which is what you want.
2. Join on the keys
Add a Join with the first source on the left, type Left join, matchingmatch_key to account_key. Rows with no candidate keep a null account_id, so they still reach the output as NO MATCH.
3. Remove duplicate pairs
Two names can share more than one key. Add a Select that keeps onlyid, name, account_id and account_name, then a Distinct.
4. Score each pair
Add a Select that keeps those four fields and adds:5. Rank the candidates
Add a Window partitioned byid and ordered by match_score descending, with:
Then a second Window partitioned by
account_id and ordered by match_score descending, with row_number as account_rank and lead of match_score (offset 1, default 0) as account_runner_up. This tells you whether another row is an even better fit for the same account.
6. Keep the best candidate and label it
Add a Filter that keepsrow_rank == 1, then a Select that adds:
match_status. Send the result to a destination, or use a Filter on match_status to write matches and rows for review to separate outputs.
Tuning
- Thresholds.
80(match),60(review) and the8-point lead over the runner-up are starting points. Check a sample ofREVIEWrows and adjust. - Ties. If two accounts score the same for one row, the lead over the runner-up is
0, so the row goes to review rather than being matched to either. - Known aliases. Some names can’t be worked out from their words, such as nicknames or acronyms used for different organizations. Keep a small file of
alias,full_namepairs and, before step 1, use a Join or Lookup to replace a known alias with its full name. - Large second sources. The join only pairs names that share a distinctive word or acronym, which is usually far fewer comparisons than a Cross Join. Short acronym keys and words that are common in your data (for example
healthorenergyin a list of hospitals) can still bring in many candidates; check the row count after the Distinct in step 3 on a real run.
Related
- FUZZY_MATCH
- NAME_MATCH_KEYS
- Window transformation
- Lookup transformation, which also offers Company names as a fuzzy algorithm for database lookups