Documentation
Use Primentra
Getting Started
Data Grid
Approvals
Set up
Modeling
Business Rules
Users & Roles
Sign-in & Security
Install and run
Installation
Administration
Integration & API
Architecture
Migrate from MDS
Migrating from MDS
Documentation/Business Rules/How Lookalikes compares values

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.

CompareIn one line
Exact100% when the values are the same letter for letter, else 0%.
Normalized100% when the values are the same after cleaning, else 0%.
TyposOne 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.
EditsOne 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 actionExactNormalizedTyposEdits
Capitals become lower case: Müller becomes müllerNoYesYesYes
Letters of their own become plain letters: ß becomes ss, æ becomes ae, ø becomes oNoYesYesYes
Accents go: müller becomes mullerNoYesYesYes
Dots and apostrophes go, with no space: B.V. becomes bv, O'Brien becomes obrienNoYesYesYes
Other punctuation becomes a space: Müller & Söhne becomes muller sohne, Jansen-Visser becomes jansen visserNoYes, then the spaces goYesYes
SpacesOnly those at the start and the end goAll spaces go: NL 0012.34.567.B01 becomes nl001234567b01One space between wordsOne space between words
Legal forms on the Name column go: Jansen Transport B.V. becomes jansen transportNoYesYesYes
Legal forms on other columns goNoNoYesNo

ß, æ 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

  1. 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.
  2. 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.
  3. Calculate Jaro: (m / length of A + m / length of B + (m - t) / m) / 3.

Part 2: the Winkler bonus for the same start

  1. Count the equal letters at the start, up to 4. Call it p.
  2. 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:

Place123456
Marthamartha
Marhtamarhta
Matchsamesamesameswappedswappedsame

So m = 6, and the 2 differing places give t = 1.

StepResult
Jaro(6 / 6 + 6 / 6 + (6 - 1) / 6) / 3 = 0.944
Equal start p3 (mar)
Jaro-Winkler0.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).

StepResult
Close in placeAt most 7 positions apart, as in Part 1
Matching letters m16. Only the e of vries has no partner.
Swaps t0. The matching letters are in the same order.
Jaro(16 / 17 + 16 / 16 + 16 / 16) / 3 = 0.980
Equal start p4 (bakk)
Jaro-Winkler0.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.

PairCharactersTyposSimilarity85%88%90%95%
Brink Bouw and Brink Bouv10196.0%PassesPassesPassesPasses
Brink Bouw and Brimk Bouv10290.7%PassesPassesPassesRefused
Brink Bouw and Brimk Bovv10386.0%PassesRefusedRefusedRefused
Hendriks Bouwen and Hendriks Bauwen15197.3%PassesPassesPassesPasses
Hendriks Bouwen and Hendrlks Bauwen15294.7%PassesPassesPassesRefused
Kuipers Installaties and Kuipers Installatiis20198.0%PassesPassesPassesPasses
Kuipers Installaties and Kuipars Installatiis20290.4%PassesPassesPassesRefused

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.

ABChangesSimilarity
3511 ab3511abRemove the space: 11 - 1 / 7 = 85.7%
3511 ab3512 abReplace 1 with 2: 11 - 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

  1. A column that is empty on one of the two records does not count.
  2. A Must be close column under its Minimum refuses the pair. The pair is not shown, whatever its score.
  3. 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:

  1. The two records share no key, or only a key that Primentra skips. See step 1.
  2. A Must be close column is under its Minimum. See rule 2 of step 6.
  3. The score is under the Review threshold. See Score thresholds on the Lookalikes page.
  4. 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?

ColumnCompareWhy
Company or person nameTyposIt forgives a typo and removes legal forms.
PostcodeNormalizedSpaces do not count, and AB and BV stay. See step 2.
VAT or KvK numberNormalizedNL 0012.34.567.B01 and NL001234567B01 are equal. One other digit is another number.
Email addressNormalizedCapitals and dots do not count: Jan.de.Vries@Bakkerij.nl and jandevries@bakkerij.nl are equal.
Phone numberNormalized020 123 4567 and 020-123 45 67 are equal. +31 20 123 4567 is still another value.
StreetTyposIt 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.
CityNormalizedDen Haag and den haag are equal. Many records share a city, so it supports a match but cannot make one.
CodeNormalized, or leave Code outEach record has its own Code. Normalized finds only the same code written another way: SUP-1001 and sup 1001.
DateExactA 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 seeReadChange on the Lookalikes tab
A true pair scores too lowSteps 3 and 4: the similarity per columnCompare (Typos for names), a lower Weight for the column that differs, or a lower Review threshold
A true pair never appearsStep 1, then rule 2 of step 6Compare 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 appearThe pitfalls of step 3, and step 6A higher Minimum, Must be close on a column that tells them apart, or a higher Review threshold
A number column gives high scoresStep 3, Why Typos is wrong for numbersCompare: Normalized
A postcode behaves oddlyStep 2, the postcode pitfallCompare: Normalized
  • Lookalikes: what each setting does, and how to work the queue
  • Performance: how long a run takes on 500,000 rows