How Lookalikes compares values
On this page
Read this page when a score surprises you, or when you want to choose a Minimum or a Weight with confidence. You can use Lookalikes without it: the Lookalikes page says what each setting does, and the templates give good starting values. If you only want to know what to change, go to When a score surprises you at the end of this page.
Two words come back on almost every line of this page. A similarity says how alike two values of one column are, from 0% to 100%, always with a % sign. A score is the weighted average of the similarities of one pair of records, from 0 to 100, without a % sign. The run, Test these rules and Try two texts all use the same code, so every number on this page is also the number on your screen.
| Compare | In one line |
|---|---|
| Exact | 100% when the values are the same letter for letter, else 0%. |
| Normalized | 100% when the values are the same after cleaning, else 0%. |
| Typos | One typo in a 15-character name scores about 97%: Hendriks Bouwen and Hendriks Bauwen score 97.3%. On this page a space counts as a character. |
| Edits | One changed character in 7 scores 85.7%: 3511 AB and 3512 AB. |
Step 1: which records meet
Primentra does not compare every record with every other record: on a large entity that takes too long. Two records are compared only when they share a key in a column set under Compare records that share. A pair that never meets is never scored. So when a pair you expect is not in the queue, look here first.
Primentra makes the keys from the cleaned value of step 2: the value that Compare reads. So the keys differ per Compare choice. The examples below use Typos or Edits, which give the same keys on a Name column. On this page and on the screen, a key of two parts shows with a bar: bak|de.
- the whole value: the cleaned value is the key, up to 50 characters. Bakkerij De Vries gives bakkerij de vries.
- the first letters: the first letters of the cleaned value are the key, 4 by default. Bakkerij De Vries gives bakk. You set the number in the column row, from 1 to 50. A space counts as a letter.
- a pair of words: the first 3 letters of two of the first three words. Bakkerij De Vries gives three keys: bak|de, de|vri and bak|vri. A word shorter than 3 letters, such as de, counts as it is.
- nothing: the column makes no key. It still counts in the score.
How many keys a pair of words gives depends on the number of words:
- When the value is one word only, the key is its first 2 and last 3 letters: primentra gives pr|tra. A word of 1 to 3 letters is its own key: de gives de.
- When the value is two words, there is one key: Jansen Transport gives jan|tra.
- When the value is three words or more, there are three keys, from the first three words. The words after the third make no key.
Bakkerij De Vries and Bakkerij De Vris share all three keys and meet. Jansen Transport B.V. and Janssen Transport BV share jan|tra and meet too. A typo in one word still leaves a shared key.
Keys and Compare
- With Exact, capitals stay in the key. Bakkerij De Vries and bakkerij de vries do not meet on the whole value, and not on the first letters either: Bakk and bakk are two keys.
- With Normalized, all spaces are gone, so a pair of words sees one long word. Bakkerij De Vries gives ba|ies and Bakkerij De Vris gives ba|ris, so those two do not meet. On a Normalized column, use the whole value or the first letters.
Too many records on one key
When more records share a key than Most records sharing a value allows (1,000 by default), Primentra skips that key. An example: 1,200 records have the city Amsterdam, and City is set to the whole value. The key amsterdam has 1,200 records, so Primentra skips it. Those records can still meet through another column, for example a pair of words in Name. A record with only skipped keys, or with no key at all, shows under Not checked. The estimate under the columns and Test these rules name the skipped keys.
Step 2: Primentra cleans the value
Before it makes keys or compares, Primentra cleans each value. The stored value never changes. What the cleaning does depends on Compare:
| Cleaning action | Exact | Normalized | Typos | Edits |
|---|---|---|---|---|
| Capitals become lower case: Müller becomes müller | No | Yes | Yes | Yes |
| Letters of their own become plain letters: ß becomes ss, æ becomes ae, ø becomes o | No | Yes | Yes | Yes |
| Accents go: müller becomes muller | No | Yes | Yes | Yes |
| Dots and apostrophes go, with no space: B.V. becomes bv, O'Brien becomes obrien | No | Yes | Yes | Yes |
| Other punctuation becomes a space: Müller & Söhne becomes muller sohne, Jansen-Visser becomes jansen visser | No | Yes, then the spaces go | Yes | Yes |
| Spaces | Only those at the start and the end go | All spaces go: NL 0012.34.567.B01 becomes nl001234567b01 | One space between words | One space between words |
| Legal forms on the Name column go: Jansen Transport B.V. becomes jansen transport | No | Yes | Yes | Yes |
| Legal forms on other columns go | No | No | Yes | No |
ß, æ and ø are not letters with an accent. They are letters of their own, so Primentra writes them as the plain letters a keyboard has.
Cleaning does not expand abbreviations. v.d. Berg becomes vd berg, and van den Berg becomes van den berg. With Typos, v.d. Berg Logistiek and Van den Berg Logistiek score 79.9%. When your data has many such abbreviations, test a lower Minimum with Test these rules.
A pitfall with postcodes: AB is a legal form (Swedish), so under Typos the postcode 3511 AB becomes 3511. BV is a legal form too, so 3511 BV also becomes 3511, and the two postcodes look the same. Use Normalized for postcodes: on a column other than Name it keeps AB, and 3511 AB becomes 3511ab. The list of legal forms is under Legal forms on the Lookalikes tab.
Step 3: Typos uses Jaro-Winkler
In plain words: Jaro-Winkler counts the letters that stand in about the same place in both values. It takes a little off for letters that are swapped. And it rewards values that start the same, because a typo is more likely near the end of a name than at the start. The result is a number from 0 to 1, which Primentra shows as a percentage. The method has two parts.
Part 1: Jaro
- Find the matching letters. A letter of A matches the same letter in B when the two are close in place. To find how close: take half the length of the longer value, round it down, and take 1 off. For 17 characters that is 7, so two letters match when they are at most 7 positions apart. Each letter matches once. Call the count m.
- Count the swaps. Read the matching letters of A in order, and those of B in order. Count the places where the two lists differ, and halve that count. Call it t.
- Calculate Jaro: (m / length of A + m / length of B + (m - t) / m) / 3.
Part 2: the Winkler bonus for the same start
- Count the equal letters at the start, up to 4. Call it p.
- Calculate Jaro-Winkler: Jaro + p x 0.1 x (1 - Jaro).
The bonus closes part of the gap to 1. Four equal first letters close 40% of it. Every pair gets the bonus, also a pair with a low Jaro. Some versions of Jaro-Winkler give it only when Jaro is above 0.7. Primentra does not.
Example 1: two letters swapped
Martha and Marhta. Each letter of Martha has a partner in Marhta. Read in order, the letters differ in 2 places:
| Place | 1 | 2 | 3 | 4 | 5 | 6 |
|---|---|---|---|---|---|---|
| Martha | m | a | r | t | h | a |
| Marhta | m | a | r | h | t | a |
| Match | same | same | same | swapped | swapped | same |
So m = 6, and the 2 differing places give t = 1.
| Step | Result |
|---|---|
| Jaro | (6 / 6 + 6 / 6 + (6 - 1) / 6) / 3 = 0.944 |
| Equal start p | 3 (mar) |
| Jaro-Winkler | 0.944 + 3 x 0.1 x (1 - 0.944) = 0.961, so 96.1% |
Example 2: a missing letter
Bakkerij De Vries and Bakkerij de Vris. Cleaned, they are bakkerij de vries (17 characters, spaces included) and bakkerij de vris (16 characters).
| Step | Result |
|---|---|
| Close in place | At most 7 positions apart, as in Part 1 |
| Matching letters m | 16. Only the e of vries has no partner. |
| Swaps t | 0. The matching letters are in the same order. |
| Jaro | (16 / 17 + 16 / 16 + 16 / 16) / 3 = 0.980 |
| Equal start p | 4 (bakk) |
| Jaro-Winkler | 0.980 + 4 x 0.1 x (1 - 0.980) = 0.988, so 98.8% |
Why Typos is wrong for numbers
000012345678 and 000012345679 are two different numbers. Jaro-Winkler sees 11 of 12 matching digits and an equal start, and gives 96.7%. For a name, one other letter is a typo. For a number, one other digit is another number. Use Normalized for numbers and codes.
How many typos a Minimum lets through
Each typo below is one letter replaced by another.
| Pair | Characters | Typos | Similarity | 85% | 88% | 90% | 95% |
|---|---|---|---|---|---|---|---|
| Brink Bouw and Brink Bouv | 10 | 1 | 96.0% | Passes | Passes | Passes | Passes |
| Brink Bouw and Brimk Bouv | 10 | 2 | 90.7% | Passes | Passes | Passes | Refused |
| Brink Bouw and Brimk Bovv | 10 | 3 | 86.0% | Passes | Refused | Refused | Refused |
| Hendriks Bouwen and Hendriks Bauwen | 15 | 1 | 97.3% | Passes | Passes | Passes | Passes |
| Hendriks Bouwen and Hendrlks Bauwen | 15 | 2 | 94.7% | Passes | Passes | Passes | Refused |
| Kuipers Installaties and Kuipers Installatiis | 20 | 1 | 98.0% | Passes | Passes | Passes | Passes |
| Kuipers Installaties and Kuipars Installatiis | 20 | 2 | 90.4% | Passes | Passes | Passes | Refused |
So with names like these, 95% lets one typo through and refuses two, and 85% to 90% lets two through. Three typos in Brink Bouw pass 85% only, and only just. A typo in the first 4 letters costs more, because it takes away part of the bonus for an equal start: Hendriks Bouwen and Hindriks Bouwen score 91.7%.
Three pitfalls
- Word order. De Vries Bakkerij and Bakkerij De Vries score 57.5% with Typos and 17.6% with Edits. They still meet, because both give the key de|vri. But a Name that must be close at 85% refuses them.
- Müller and Mueller. Cleaning gives muller and mueller: not the same. Typos gives 91.7%, and Müller Bouw and Mueller Bouw score 95.4%.
- Two real companies. Bakkerij De Vries and Bakkerij De Vos score 95.1%: two bakeries that Typos reads as one typo. A second column, such as the VAT number or the postcode, must tell them apart.
Long values
Two equal values score 100%, whatever their length. Primentra checks that first.
For two different values, Typos and Edits compare the first 200 characters. The characters after that count as different. So the similarity is that of the first 200 characters, times 200 / the length of the longer value. Two values of 300 characters that differ only at character 251 score 66.7%. Two different long values never score 100%.
Try two texts accepts up to 500 characters. Typos and Edits compare the first 200 of them, as a run does.
Step 4: Edits uses the edit distance
The edit distance is the smallest number of one-character changes that turn A into B. A change is: add a character, remove a character, or replace a character. The method is also called the Levenshtein distance.
Similarity = 1 - edit distance / length of the longer value.
| A | B | Changes | Similarity |
|---|---|---|---|
| 3511 ab | 3511ab | Remove the space: 1 | 1 - 1 / 7 = 85.7% |
| 3511 ab | 3512 ab | Replace 1 with 2: 1 | 1 - 1 / 7 = 85.7% |
The two rows show the weak point. A typo and a real difference cost the same. So use Edits as Extra proof on short codes, not as Must be close.
Step 5: Exact and Normalized
Exact and Normalized give 100% when the two values are equal after step 2, and 0% when they are not. There is nothing in between. That is why Minimum does not apply to them. So Must be close on an Exact or Normalized column refuses the pair at any difference: the similarity is then 0%, and there is no Minimum to pass.
Step 6: from similarities to one score
- A column that is empty on one of the two records does not count.
- A Must be close column under its Minimum refuses the pair. The pair is not shown, whatever its score.
- Score = the sum of (similarity x weight) of the counting columns / the sum of their weights, x 100. Primentra rounds it to one decimal.
A Must be close column also counts in the score, with its weight. The role only adds the refusal of rule 2.
An example: Name 0.988 with weight 3, VAT number 1 with weight 3, City 1 with weight 1, Postcode 1 with weight 1.
Score = (0.988 x 3 + 1 x 3 + 1 x 1 + 1 x 1) / 8 x 100 = 99.6.
The score thresholds then decide what happens. Auto-merge asks two more things: at least 2 counting columns, and those columns carry at least 70% of the total weight. The Lookalikes page has the full rules under Score thresholds.
The reasons in the queue
The queue shows a reason per column. Each reason comes from a step on this page:
- Name 99% similar: the similarity of step 3 (Typos) or step 4 (Edits).
- City exact: the values are equal after cleaning, so the similarity is 100%, as in step 5.
- VAT number empty on one side, not counted: rule 1 above.
- Name different (79%): a similarity under the Minimum of that column. You see this only on an Extra proof column, because a Must be close column under its Minimum refuses the pair (rule 2 above).
Questions
Why is a pair I expect not in the queue?
Look for these four causes:
- The two records share no key, or only a key that Primentra skips. See step 1.
- A Must be close column is under its Minimum. See rule 2 of step 6.
- The score is under the Review threshold. See Score thresholds on the Lookalikes page.
- A person marked the pair Not a match, and it waits under Dismissed. See Work the queue on the Lookalikes page.
Which Compare do I pick?
| Column | Compare | Why |
|---|---|---|
| Company or person name | Typos | It forgives a typo and removes legal forms. |
| Postcode | Normalized | Spaces do not count, and AB and BV stay. See step 2. |
| VAT or KvK number | Normalized | NL 0012.34.567.B01 and NL001234567B01 are equal. One other digit is another number. |
| Email address | Normalized | Capitals and dots do not count: Jan.de.Vries@Bakkerij.nl and jandevries@bakkerij.nl are equal. |
| Phone number | Normalized | 020 123 4567 and 020-123 45 67 are equal. +31 20 123 4567 is still another value. |
| Street | Typos | It forgives a typo in a street name. But it reads a house number as letters: Oudegracht 231 and Oudegracht 233 score 97.1%, so keep the house number in its own column. |
| City | Normalized | Den Haag and den haag are equal. Many records share a city, so it supports a match but cannot make one. |
| Code | Normalized, or leave Code out | Each record has its own Code. Normalized finds only the same code written another way: SUP-1001 and sup 1001. |
| Date | Exact | A date column offers only Exact. |
The templates set these for you. Address compares Street with Must be close at 90%, and the house number with Normalized. Company and Address give City weight 1; when City is a Domain column, they compare the code of the city exactly. No template compares Code. Product compares a GTIN, EAN or SKU column with Normalized, weight 4.
How many letters may differ at a Minimum of 85%?
It depends on the length and on the place. In the table of step 3, two wrong letters in 10 to 20 characters pass 85%. Three wrong letters in Brink Bouw can pass or not: Brimk Bovv scores 86.0%, and Bfimk Bovw scores 82.0%, because its second letter is wrong. So count on two, and test with Test these rules.
Does word order matter?
Yes. De Vries Bakkerij and Bakkerij De Vries score 57.5% with Typos. See the pitfalls in step 3.
Is Müller the same as Mueller?
No. Cleaning gives muller and mueller. Typos gives 91.7%, so the two pass a Minimum of 90% and are refused at 95%. In a longer name the difference weighs less: Müller Bouw and Mueller Bouw score 95.4%.
How does Jaro-Winkler work, exactly?
See step 3. It counts the letters that match in about the same place, takes off a little for swapped letters, and gives a bonus for an equal start of up to 4 letters.
Why do 3511 AB and 3512 AB score 85.7% with Edits?
One character differs in 7. Edits cannot tell a typo from a real difference. See step 4.
Is the similarity in Try two texts the same as in a run?
Yes. Try two texts, Test these rules and the run use the same code on the server.
When a score surprises you
| What you see | Read | Change on the Lookalikes tab |
|---|---|---|
| A true pair scores too low | Steps 3 and 4: the similarity per column | Compare (Typos for names), a lower Weight for the column that differs, or a lower Review threshold |
| A true pair never appears | Step 1, then rule 2 of step 6 | Compare records that share (one more column, or a pair of words), Most records sharing a value, or the Minimum of a Must be close column |
| Wrong pairs appear | The pitfalls of step 3, and step 6 | A higher Minimum, Must be close on a column that tells them apart, or a higher Review threshold |
| A number column gives high scores | Step 3, Why Typos is wrong for numbers | Compare: Normalized |
| A postcode behaves oddly | Step 2, the postcode pitfall | Compare: Normalized |
Related
- Lookalikes: what each setting does, and how to work the queue
- Performance: how long a run takes on 500,000 rows
Did not find what you need?
Try the troubleshooting pages, or ask us.