Skip to content
Français

Three lists, one customer

  • Lesson 1
  • 25 min
  • Module 1: The matching problem

Where we are

In the SQL course, we joined tables on a key that matched exactly. Real customer lists rarely match exactly. In this lesson, we count Larkfield’s customers across three lists, and we see what each duplicate costs the shop. First, think of a clinic that finds one patient filed three times.

Plays from YouTube. Nothing loads from YouTube until you press play. Privacy

What you will be able to do

See why one shop can count the same customer two or three times, and what that costs. Then learn the words for the problem, so that the rest of the course can measure it.

Before you startWhat to know and to have readyAbout 25 minutes

What you need to know

What you need on your computer

Nothing. You look at the same records yourself in Meet the records, the last lesson of this module.

Think of a small clinic. A woman visits it three times in ten years. The first time, the clerk writes “Valérie Tremblay”. Years later, a new clerk writes “TREMBLAY, VAL” and does not find the old file. The third time, she gives a new phone number, and a third file starts.

Now the clinic has three files for one patient. Each file has part of her story. One file has her allergy, one has her new address, and one has her last blood test. The clinic sends her three reminder letters. Worse, a doctor who opens one file does not see the allergy in another.

The clinic’s problem is not a lack of data. It has too many copies of the same person, and no one can see that they are the same. This course is about that problem: how to decide, record by record, “is this the same thing?”

Larkfield is the online shop for home and garden products that you may know from other courses. In 2026, it bought a chain of garden centres. Now three lists describe its customers:

  1. The shop’s accounts: people who buy online. 31,128 records.
  2. The loyalty programme: people who collect points. 17,037 records.
  3. The garden centres’ list: people who bought at a garden centre. 11,943 records.

Each list was built by a different team, at a different time, with different rules. Nobody planned that they would one day be put together. Many people are in two lists, and some are in all three.

Every record in this course is synthetic. Our code made the records from 37,500 invented people. Then it added realistic mistakes: typing errors, nicknames, old addresses, shared family emails and missing accents. We know who each record really belongs to, so we can measure every method exactly. The names, streets and towns are invented, the phone numbers use the 555 exchange, and the emails use reserved example domains.

TermOmarLarkfield’s product manager. He asks the business questions: what a change costs, and what it gives the shop., the product manager, needs one number for the board: how many customers do we have? He starts the easy way and counts the rows of the three lists. That gives 60,108.

But we know the truth. The 60,108 records belong to 37,500 true customers. So the row count is too high by 22,608. That is 60.3% more customers than Larkfield really has.

TermTomásLarkfield’s AI engineer. He often suggests a quick shortcut, and the lesson shows why it fails., an AI engineer, has a quick fix: “Join the lists on the email address. Same email, same customer.” He tries it on the emails exactly as people typed them. Now Larkfield counts 50,367 customers. That is better, but still 12,867 too many.

Three ways to count Larkfield's customers in the synthetic records. Counting rows gives 60,108; joining on the email as typed gives 50,367; the true number is 37,500.
Text description of the chart

A column chart with three columns, on an axis from 0 to 65,000 customers. Counting every row of the three lists gives 60,108 customers. Joining records that have the same email, exactly as typed, gives 50,367 customers. The true number of customers is 37,500. The true column is the lowest: both counting methods count far too many customers.

The email join fails in two ways at the same time:

  • It misses people. Hélène Thibault typed her email with capitals in one list and without them in another. Other customers use another email address in another list. Their records stay apart.
  • It joins strangers. A whole family can share one email, such as thegreens@example.com. The join puts the whole family into one “customer”.

So an exact join is not enough. It also does not tell Omar which of its joins are wrong. You need a way to decide, for two records, “same or not”, and a way to check that decision. Let’s look at one customer first.

Here is Valérie Tremblay, one of TermLarkfieldA made-up Canadian online shop for home and garden products. It is not a real company. All Nextia courses use its help desk and data as the example.Learn more Meet Larkfield’s synthetic customers, as the three lists store her. These are real rows of the course data.

Three record cards side by side: shop account S-009232, loyalty member L-010072 and garden centre G-000001. Each card has six fields: name, email, phone, address, postcode and date of birth. Dashed boxes mark the differences: the shop writes the phone as +1 514 555 8296 and has a typing error in the postcode, H6P 3H1 instead of H6P 3H7; the loyalty list has no address column and writes the date of birth as 31/01/1975; the garden centre writes TREMBLAY, VAL in capitals without the accent, puts the postcode inside the address, and has no date of birth. Arrows from the three cards lead to one golden record, LK-G-000001, with one value for each field and the record that each value comes from.
One synthetic customer in three lists. The dashed boxes are the differences that a matcher must see through. The golden record below takes one value for each field, and remembers where each value came from.
Text description of the diagram

The picture shows three record cards for Valérie Tremblay. The shop account S-009232 has her full name, her email and the phone written as +1 514 555 8296. It also has her street address, the postcode H6P 3H1 and her date of birth, 1975-01-31. The postcode has a typing error: the last digit should be 7. The loyalty record L-010072 has the same name and email, and the phone as ten digits. It has no address column, the right postcode H6P 3H7, and the date of birth as day/month/year: 31/01/1975. The garden-centre record G-000001 writes the name as TREMBLAY, VAL: surname first, in capitals, with a short first name and no accent. Its address line includes the postcode, and its date of birth is empty. Arrows from the three cards lead to one golden record, LK-G-000001. It has one value for each field. The name comes from the shop. The email, the phone and the date of birth come from the loyalty record. The address and postcode come from the garden centre.

A person sees at once that these three records are one woman. A computer sees six fields that are almost never equal. Look at what is different:

  • The same value, written another way. +1 514 555 8296 and 5145558296 are one phone number. 31/01/1975 and 1975-01-31 are one date.
  • A mistake. The shop’s postcode H6P 3H1 has one wrong character.
  • A shorter or older value. “VAL” is short for “Valérie”. The garden centres’ old till system drops accents and writes in capitals.
  • A missing value. The loyalty list has no address column. The garden centre has no date of birth.

None of these differences means “another person”. But other small differences do. Marc Fortin and Marie Fortin have almost the same name, and they are two people. That is why this problem is hard.

The box at the bottom of the picture is a golden record: one clean record for each true customer, built from all of its records. It keeps one value for each field and remembers which record gave it. The project builds it in Module 7. The three source records never change, so a wrong merge can be undone.

The course uses a few words in one exact meaning. They come from the picture above.

  • An entity is the real thing that the records describe: one person, one company or one product. Valérie is one entity.
  • A record is one row in one list that describes an entity. Valérie has three records.
  • Entity resolution is the work of finding which records describe the same entity. It is the name of this course’s problem.
  • Record linkage is entity resolution between two or more lists: which shop account goes with which loyalty member?
  • Deduplication is entity resolution inside one list: which two shop accounts are the same person?

Larkfield needs both. Some people have two shop accounts, and most people with several records are in several lists. People also say “matching” for all of it, and so does this course. The SQL course’s join is the simplest kind of matching: two records match only when a key is exactly equal.

Most customers are easy: they have one record. The trouble is in the rest. The chart counts the true customers by their number of records.

True customers by their number of records, in the synthetic data. 19,564 customers have one record; 17,936 have two to five.
Text description of the chart

A column chart with five columns. 19,564 customers have one record. 13,644 customers have two records. 3,929 have three records. 346 have four records. 17 have five records. The first column is a little higher than the second, and the columns fall quickly after that.

So 17,936 customers, about half, have more than one record. A matcher works on pairs of records, because “same or not” is a question about two records. One customer with two records makes one true pair. Valérie has three records, so she makes three true pairs: shop–loyalty, shop–garden and loyalty–garden. A customer with five records makes ten pairs.

Records of one customer True pairs each Customers True pairs in all
2 1 13,644 13,644
3 3 3,929 11,787
4 6 346 2,076
5 10 17 170
All 17,936 27,677

The 27,677 true pairs are the answer key of this course. Every method is measured by how many of them it finds, and by how many wrong pairs it adds.

Omar asks the question that matters to the business: “What does a duplicate cost us?” There are three answers.

Valérie gets every marketing email three times, once for each record. She also gets three printed catalogues. She may think that Larkfield is careless, and she may unsubscribe. The course uses an assumption for this cost: 3 dollars a year for each pair of records that should be one customer but is not. It is a choice of ours for the exercises, not a measured price.

Valérie collects points in the loyalty programme. Her garden-centre purchases go to another record, so her points are split over two accounts. She never reaches the next reward, and she calls customer service to complain. The 3 dollars a year include this too.

Some people use duplicates on purpose. A small group opens many accounts with small differences in their names and addresses. Each account uses a first-order discount, or returns goods many times. Each account looks normal alone. Only when the accounts are linked does the pattern appear.

The course data has no fraud ring. We describe this cost, but we do not measure it. It is still a reason why fraud teams use the same matching methods that you learn here.

Hana, the merchandising lead, owns two supplier feeds. Northfield Supply sends 4,460 products, and Copperleaf Wholesale sends 3,501. 2,750 products are in both feeds, with different titles:

Feed Product Title
Northfield NF-00001 Hearthside Cedar Raised Garden Bed, 4 ft, Sand
Copperleaf CL-01686 raised garden bed 1.2 m cedar sand - Hearthside

One product, two titles: other word order, other capitals, and feet against metres. And the opposite trap is there too. “Larkfield Basics Metal Trellis, 6 ft, Natural” and the same title with “(Pack of 2)” are two different products. If Hana cannot match the feeds, she cannot compare prices between suppliers. The supplier feeds are synthetic too.

Larkfield wants one list of customers and one list of products, decided by measurement, not by feeling. Merges must be undoable, and a person must check the cases where the method is unsure. Six people work on this in the course:

  • TermPriyaLarkfield’s data scientist. She measures how well the assistant works, on test sets and not on guesses., the data scientist, builds the measurements and compares the methods. She is your main guide.
  • Omar, the product manager, owns the customer count and asks what each error costs.
  • TermAmiraLarkfield’s developer. She writes the code of the help-desk tools in the courses., the developer, owns the matching job: how it runs every day, how fast it is, and how to undo a merge.
  • Tomás, the AI engineer, has quick ideas. Some of them are common mistakes, and the course tests them.
  • Hana, the merchandising lead, owns the supplier feeds: pack sizes, units and two names for one product.
  • Ivana, the customer-data steward, owns the golden records and the review queue. The course assumes that she can check 300 pairs a day.

The course compares many methods on the same records. They go from exact keys and rules to trained classifiers, embeddings and a language model for the unsure pairs. Each method gets the same test and the same measures, in one comparison table.

Common mistake

Join on the email and call it done

Symptom: Tomás joins the three lists on the email address. The count falls from 60,108 to 50,367. “Problem solved,” he says, and the marketing team uses the new list.

Cause: the join only finds people who typed exactly the same email in every list. It still counts 12,867 customers too many. It also joins strangers who share a family email, so some people now get mail that was meant for a brother or a mother. Nobody checked which joins were right, because nobody compared the result with the truth.

Fix: measure a matcher before you trust it. Count the pairs that it joins correctly, the pairs that it joins wrongly, and the true pairs that it misses. The next lesson names these two errors and puts a price on each one.

Consequence in AI work: “the number went down” is not a measurement. A matcher can make the count look better while it merges strangers.

Larkfield’s records are synthetic, so we know every answer. Real work has other data and other traps. So the course ends with three case studies on real, public data. Each one takes a dataset from the raw files to a recommendation.

Case studies in this course

At the end of the course, you use what you learn on a realistic project, from the data to the recommendation. Complete at least one for the certificate; the others are optional.

  • Match products between two shopsOn an open product-matching benchmark with an allowed licence: blocking, string, supervised and embedding methods, and a language model for the unsure pairs; report precision at a fixed recall and the cost per thousand pairs.
  • Link bibliographic recordsOn open bibliographic records with an allowed licence: Splink against a pair classifier; error analysis of titles, author lists and venues.
  • Find duplicate people and companiesOn simulated person records with an allowed licence and an open company register: rules, Splink and clustering into entities with a golden record.

Read the list as a map of where the course goes. The case studies use the whole course, so most learners start them after Module 8.

Check your understanding

Larkfield's lists have 60,108 records and 37,500 true customers. By how much does a row count over-count, and why does joining on the email as typed not fix it?

Show the answer

The row count is 22,608 too high: 60,108 / 37,500 = 1.603, so it counts 60.3% more customers than there are. The email join gives 50,367, still 12,867 too many. It misses people who typed their email in another way, with capitals or another address, in another list. And it joins different people who share a family email. So it makes both kinds of error at once.

Check your understanding

Use the words entity, record, record linkage and deduplication to describe Valérie Tremblay's case, and a customer with two shop accounts.

Show the answer

Valérie is one entity: one real person. She has three records, one in each list. Finding that the shop account, the loyalty record and the garden-centre record belong together is record linkage, because it works across lists. A customer with two shop accounts has two records in one list; finding them is deduplication. Both are entity resolution.

Check your understanding

A customer has four records. How many true pairs does she make, and why does a matcher count pairs?

Show the answer

Four records make 6 pairs: each record pairs with the 3 others, and each pair is counted once, so 4 × 3 / 2 = 6. A matcher counts pairs because its basic question, “same or not”, is about two records. The 27,677 true pairs of the course data are the answer key. We measure a method by how many of them it finds, and by how many wrong pairs it adds.

Recap

  • Larkfield’s three lists hold 60,108 synthetic records of 37,500 true customers. Counting rows over-counts by 60.3%. Joining on the email as typed still counts 50,367.
  • One customer can look very different in three lists: other formats, mistakes, short names, missing values. Small differences can also mean two different people.
  • An entity is the real thing. A record is one row about it. Entity resolution finds the records of one entity: record linkage across lists, deduplication inside one list.
  • 17,936 customers have more than one record, which makes 27,677 true pairs: the answer key.
  • Duplicates cost double emails and catalogues, split loyalty points, and hidden fraud. The supplier feeds have the same problem with product titles.

You can now say what the matching problem is and why an exact join does not solve it. But a matcher can be wrong in two different ways, and they do not cost the same. Next, you name the two errors and put a price on each one.

Next: False merges and missed matches