Matching Two Customer Lists When Names and Emails Are Messy

Matching two customer lists is a ladder, not a single join. You start with the safest rule, then climb to looser ones, and you trust each rung a little less than the one below. Let me show you the ladder on a CRM list and a newsletter list typed by different humans on different days.

Two lists, same customers in different clothes

Say marketing hands you a newsletter export and asks which subscribers are already customers. Nobody agreed on capital letters, spaces or apostrophes. Below are five CRM customers and five subscribers. Asha shows up with shouting capitals and stray spaces. Ben lost an apostrophe. Chen has a typo. Dana looks a lot like Dara. Frank is a stranger. Run the blocks in order in one query window.

DROP TABLE IF EXISTS #Subscribers;
DROP TABLE IF EXISTS #Crm;
CREATE TABLE #Crm (CrmId int PRIMARY KEY, FullName nvarchar(40) NOT NULL, Email nvarchar(60) NOT NULL, Zip char(5) NOT NULL);
CREATE TABLE #Subscribers (SubId int PRIMARY KEY, FullName nvarchar(40) NOT NULL, Email nvarchar(60) NOT NULL, Zip char(5) NOT NULL);
INSERT #Crm VALUES
    (1, N'Asha Patel',   N'asha.patel@example.com', '10001'),
    (2, N'Ben O''Neil',  N'ben.oneil@example.com',  '20002'),
    (3, N'Chen Wei',     N'chen.wei@example.com',   '30003'),
    (4, N'Dara Johnson', N'dara.j@example.com',     '40004'),
    (5, N'Elan Murphy',  N'elan.m@example.com',     '50005');
INSERT #Subscribers VALUES
    (901, N'  ASHA PATEL ', N'Asha.Patel@Example.com ', '10001'),
    (902, N'Ben ONeil',     N'bo@example.net',          '20002'),
    (903, N'Chen Wey',      N'cwey@example.net',        '30003'),
    (904, N'Dana Johnson',  N'dana.j@example.net',      '40004'),
    (905, N'Frank Lee',     N'fl@example.net',          '60006');

Clean first, compare second

Never join on raw text. Trim both ends, lower the case, and drop apostrophes and periods from names. I also keep the first letter and the SOUNDEX code of the last name, because rung three needs them. The originals stay in the table, so a human can always see what went in.

SELECT c.CrmId, c.FullName, c.Zip,
       LOWER(TRIM(c.Email)) AS CleanEmail, n.CleanName,
       LEFT(n.CleanName, 1) AS FirstLetter,
       SOUNDEX(RIGHT(n.CleanName, CHARINDEX(' ', REVERSE(n.CleanName) + ' ') - 1)) AS LastSound
INTO #CrmClean
FROM #Crm AS c
CROSS APPLY (SELECT LOWER(REPLACE(REPLACE(TRIM(c.FullName), '''', ''), '.', ''))) AS n(CleanName);

SELECT s.SubId, s.FullName, s.Zip,
       LOWER(TRIM(s.Email)) AS CleanEmail, n.CleanName,
       LEFT(n.CleanName, 1) AS FirstLetter,
       SOUNDEX(RIGHT(n.CleanName, CHARINDEX(' ', REVERSE(n.CleanName) + ' ') - 1)) AS LastSound
INTO #SubClean
FROM #Subscribers AS s
CROSS APPLY (SELECT LOWER(REPLACE(REPLACE(TRIM(s.FullName), '''', ''), '.', ''))) AS n(CleanName);

SELECT SubId, CleanName, CleanEmail, LastSound FROM #SubClean ORDER BY SubId;

Asha is now plain lowercase text with no spaces around it. Ben’s name reads ben oneil, which equals the cleaned CRM name.

Cleaning has limits. It will not turn Bob into Robert, and it will not fix a name typed last name first. Those rows fall through every rung, which is fine. An unmatched row is cheap to review. A wrong merge is expensive to undo. Also watch for shared email addresses. A family sharing one inbox can match several CRM rows on rung one, and my tie-breaker would quietly pick the lowest CRM id. Count candidates per subscriber before you trust rung one blindly.

Climb the ladder, one rung at a time

Rung one is the same cleaned email. Rung two is the same cleaned name in the same ZIP code. Rung three is the same first letter and a last name that sounds alike, again in the same ZIP code. Every rung writes its candidates into one table, tagged with its rung number.

DROP TABLE IF EXISTS #Candidates;
CREATE TABLE #Candidates (SubId int, CrmId int, Rung int, Reason varchar(30));

INSERT #Candidates
SELECT s.SubId, c.CrmId, 1, 'same email'
FROM #SubClean AS s JOIN #CrmClean AS c ON c.CleanEmail = s.CleanEmail;

INSERT #Candidates
SELECT s.SubId, c.CrmId, 2, 'same name and ZIP'
FROM #SubClean AS s JOIN #CrmClean AS c ON c.CleanName = s.CleanName AND c.Zip = s.Zip;

INSERT #Candidates
SELECT s.SubId, c.CrmId, 3, 'sounds alike, same ZIP'
FROM #SubClean AS s
JOIN #CrmClean AS c ON c.Zip = s.Zip AND c.FirstLetter = s.FirstLetter AND c.LastSound = s.LastSound;

SELECT SubId, CrmId, Rung, Reason FROM #Candidates ORDER BY SubId, Rung, CrmId;

Look at subscriber 901. Asha passes all three rungs, so the same pair appears three times. Ben passes two. Chen and Dana pass only the last one, and Frank passes none.

Keep one answer per subscriber

A good match also passes the looser rungs, so you must keep only the best rung per subscriber. ROW_NUMBER does that, with the CRM id as a tie-breaker. The LEFT JOIN keeps unmatched subscribers in the list.

WITH Best AS (
    SELECT SubId, CrmId, Rung, Reason,
           ROW_NUMBER() OVER (PARTITION BY SubId ORDER BY Rung, CrmId) AS rn
    FROM #Candidates
)
SELECT s.SubId, s.FullName AS SubscriberName, b.CrmId, c.FullName AS CrmName, b.Rung, b.Reason
FROM #Subscribers AS s
LEFT JOIN Best AS b ON b.SubId = s.SubId AND b.rn = 1
LEFT JOIN #Crm AS c ON c.CrmId = b.CrmId
ORDER BY s.SubId;

Trust the rungs differently

Asha matches on rung one and Ben on rung two. I would act on both. Rung three gives Chen Wey to Chen Wei, which is probably right, and Dana Johnson to Dara Johnson, which may be two different people. Same ZIP and similar sounds are a hint, not proof. I send every rung three pair to a human, and I never merge them automatically.

One more habit that saves you. Keep the rung number in whatever table you save the result to. Six months later, someone will ask why two records were merged, and the rung is your answer.

DROP TABLE IF EXISTS #Candidates;
DROP TABLE IF EXISTS #SubClean;
DROP TABLE IF EXISTS #CrmClean;
DROP TABLE IF EXISTS #Subscribers;
DROP TABLE IF EXISTS #Crm;

Next time two lists refuse to agree, build the ladder and let the safe rungs do the heavy lifting.

A fuzzy match is not a fact, it is a suggestion for a human to review.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.