Entity resolution in pure Cypher

The resolver builds a small graph and reasons over it. A Record is one row of either export, labeled Crm or Billing. An Entity is one normalized name, shared by every record that normalizes to it (RESOLVES_TO). A Token is one word of those names (HAS_TOKEN), and SIMILAR links words that look like typos of each other. Entities that might be the same company get a scored CANDIDATE edge: the strong ones become SAME_AS, and the doubtful ones POSSIBLE_MATCH, for a human to check. At the end, every entity's cluster property names the company it resolved to.

The steps below build it in this order:

Load the two exports

Uniqueness constraints give MERGE an index to look keys up in, and the cluster index serves the report queries at the end.

CREATE CONSTRAINT record_id IF NOT EXISTS FOR (r:Record) REQUIRE r.id IS UNIQUE;
CREATE CONSTRAINT entity_key IF NOT EXISTS FOR (e:Entity) REQUIRE e.key IS UNIQUE;
CREATE CONSTRAINT token_text IF NOT EXISTS FOR (t:Token) REQUIRE t.text IS UNIQUE;
CREATE INDEX entity_cluster IF NOT EXISTS FOR (e:Entity) ON (e.cluster)

(no rows) · indexes added: 1, constraints added: 3

Each export names its columns differently; the load maps both fixture lists, one JSON object per CSV row, onto one Record shape. The last statement is a guard: an email without an @ means a row was split in the wrong place.

UNWIND $crm AS row
CREATE (:Record:Crm {id: row.account_id, name: row.account_name,
                     email: row.contact_email, city: trim(row.city)});

UNWIND $billing AS row
CREATE (:Record:Billing {id: row.customer_no, name: row.legal_name,
                         email: row.billing_email, city: trim(row.billing_city)});

MATCH (r:Record)
RETURN count(CASE WHEN r:Crm THEN 1 END) AS crm,
       count(CASE WHEN r:Billing THEN 1 END) AS billing,
       count(CASE WHEN NOT r.email CONTAINS '@' THEN 1 END) AS malformed

| crm | billing | malformed |
|-----|---------|-----------|
| 194 | 202     | 0         |
1 row · nodes created: 396, properties set: 1584, labels added: 792

Note The exports started out as CSV, and 33 of the names contain a quoted comma, like "Golden Gate Logistics, Inc.". LOAD CSV honors RFC 4180 quoting (quoted delimiters, escaped "" quotes, and newlines inside quoted fields), so those rows load as-is today; this fixture predates that and was converted with Python's csv module to JSON for UNWIND. Either path works — and keep the malformed guard.

Normalize names into keys

Normalizing is string and list processing. Lowercase the name, turn & into and, replace every run of punctuation with a space using string.regexReplace, split into words, and drop legal suffixes, the, and single letters (so L.L.C. disappears letter by letter). The email domain is kept as evidence, unless it is a free-mail domain, which says nothing about the company. The step ends by listing three Golden Gate companies, spelled in capitals and lower case, with and without a leading The, with stray spaces, and with several legal suffixes. Edit names to look at others.

MATCH (r:Record)
WITH r,
     [t IN split(trim(string.regexReplace(replace(lower(r.name), '&', ' and '),
                                          '[^a-z0-9]+', ' ')), ' ')
      WHERE size(t) > 1
        AND NOT t IN ['the', 'inc', 'incorporated', 'corp', 'corporation', 'co',
                      'company', 'llc', 'ltd', 'limited', 'plc', 'gmbh']] AS words,
     split(lower(trim(r.email)), '@')[1] AS domain
SET r.key = string.join(words, ' '),
    r.domain = CASE WHEN domain IN ['gmail.com', 'yahoo.com', 'outlook.com',
                                    'hotmail.com', 'icloud.com', 'aol.com']
                    THEN null ELSE domain END;

MATCH (r:Record)
WHERE any(n IN $names WHERE lower(r.name) CONTAINS n)
RETURN r.name AS name, r.email AS email, r.key AS key, r.domain AS domain
ORDER BY key, name

| name                               | email                         | key                     | domain                    |
|------------------------------------|-------------------------------|-------------------------|---------------------------|
| "  The Golden Gate Labs Corp. "    | "[email protected]"      | "golden gate labs"      | "goldengatelabs.com"      |
| "  golden gate labs corp "         | "[email protected]"      | "golden gate labs"      | "goldengatelabs.com"      |
| "GOLDEN GATE LOGISTICS, INC."      | "[email protected]" | "golden gate logistics" | "goldengatelogistics.com" |
| "Golden Gate Logistics, Inc."      | "[email protected]"               | "golden gate logistics" | null                      |
| "  The Golden Gate Security, LLC " | "[email protected]"  | "golden gate security"  | "goldengatesecurity.com"  |
| "Golden Gate Security L.L.C."      | "[email protected]"  | "golden gate security"  | "goldengatesecurity.com"  |
6 rows · properties set: 737

Six spellings, three keys: one per company. The free-mail contact leaves its record without a domain.

One entity per key

MERGE on the key groups the records that normalize the same way. This exact pass already does most of the work: the 396 records become 244 entities, and the fuzzy steps below only have to take those to 211 companies.

MATCH (r:Record)
MERGE (e:Entity {key: r.key})
MERGE (r)-[:RESOLVES_TO]->(e)

(no rows) · nodes created: 244, relationships created: 396, properties set: 244, labels added: 244

Weigh words by rarity

Each key splits into Token nodes. How much a shared word counts depends on how rare it is: two names that share pinnacle are more likely to be the same company than two that share labs. The weight is an inverse document frequency, log(N / df) + 0.1, where N is the number of entities and df is the number that use the token, counted with COUNT { }. Each token also stores its character bigrams for the next step.

MATCH (e:Entity)
UNWIND split(e.key, ' ') AS text
MERGE (t:Token {text: text})
MERGE (e)-[:HAS_TOKEN]->(t);

MATCH (e:Entity)
WITH count(e) AS n
MATCH (t:Token)
WITH t, n, ' ' + t.text + ' ' AS padded
SET t.idf = log(toFloat(n) / COUNT { (t)<-[:HAS_TOKEN]-() }) + 0.1,
    t.bigrams = coll.distinct([i IN range(0, size(padded) - 2) |
                               substring(padded, i, 2)]);

MATCH (t:Token)
WITH t, COUNT { (t)<-[:HAS_TOKEN]-() } AS entities
ORDER BY t.text
RETURN entities, round(t.idf, 2) AS idf, count(*) AS tokens,
       collect(t.text)[..4] AS examples
ORDER BY entities DESC

| entities | idf  | tokens | examples                                             |
|----------|------|--------|------------------------------------------------------|
| 16       | 2.82 | 1      | ["labs"]                                             |
| 15       | 2.89 | 1      | ["security"]                                         |
| 14       | 2.96 | 1      | ["energy"]                                           |
| 13       | 3.03 | 1      | ["foods"]                                            |
| 12       | 3.11 | 5      | ["acme", "analytics", "outfitters", "software"]      |
| 11       | 3.2  | 9      | ["brightpath", "capital", "consulting", "logistics"] |
| 10       | 3.29 | 3      | ["cobalt", "kestrel", "textiles"]                    |
| 9        | 3.4  | 4      | ["gate", "golden", "health", "quantum"]              |
| 8        | 3.52 | 4      | ["brewing", "dental", "harborview", "zenith"]        |
| 7        | 3.65 | 8      | ["evergreen", "freight", "granite", "horizon"]       |
| 6        | 3.81 | 5      | ["beacon", "driftwood", "juniper", "meridian"]       |
| 5        | 3.99 | 5      | ["blue", "crescent", "harbor", "pinnacle"]           |
| 4        | 4.21 | 5      | ["riverstone", "silverline", "summit", "tidewater"]  |
| 3        | 4.5  | 4      | ["atlas", "bacon", "lakeside", "sterling"]           |
| 2        | 4.9  | 6      | ["dnetal", "drfitwood", "falcon", "oakridge"]        |
| 1        | 5.6  | 32     | ["aalytics", "alas", "analtyics", "analytcs"]        |
16 rows · nodes created: 94, relationships created: 502, properties set: 282, labels added: 94

Common words like labs weigh about half as much as the 32 words that appear in only one name, and every one of those is a typo.

Catch typos with bigram similarity

Relate has no Levenshtein or Jaro function, but Dice similarity over character bigrams is a few list comprehensions. Pad the word with spaces so its first and last letters count, take every two-character window, and compare the two sets: 2 × shared / (size(a) + size(b)).

WITH ' ' + lower($a) + ' ' AS pa, ' ' + lower($b) + ' ' AS pb
WITH coll.distinct([i IN range(0, size(pa) - 2) | substring(pa, i, 2)]) AS ga,
     coll.distinct([i IN range(0, size(pb) - 2) | substring(pb, i, 2)]) AS gb
WITH ga, gb, [g IN ga WHERE g IN gb] AS shared
RETURN ga, gb, shared, round(2.0 * size(shared) / (size(ga) + size(gb)), 3) AS dice

| ga                                         | gb                                   | shared                         | dice  |
|--------------------------------------------|--------------------------------------|--------------------------------|-------|
| [" b", "be", "ea", "ac", "co", "on", "n "] | [" b", "ba", "ac", "co", "on", "n "] | [" b", "ac", "co", "on", "n "] | 0.769 |
1 row

beacon and bacon share five bigrams, for a score of 0.769. Edit the words and run it again: swapping two letters breaks more bigrams than dropping one, so sloar and solar score only 0.5.

Now compare every pair of tokens whose lengths differ by at most two, link the pairs that score at least 0.55, and list the ten weakest links, where a false match would show up first:

MATCH (a:Token), (b:Token)
WHERE a.text < b.text AND abs(size(a.text) - size(b.text)) <= 2
WITH a, b, size([g IN a.bigrams WHERE g IN b.bigrams]) AS shared
WITH a, b, 2.0 * shared / (size(a.bigrams) + size(b.bigrams)) AS dice
WHERE dice >= 0.55
CREATE (a)-[:SIMILAR {dice: dice}]->(b);

MATCH (a:Token)-[s:SIMILAR]->(b:Token)
RETURN a.text AS a, b.text AS b, round(s.dice, 3) AS dice
ORDER BY dice, a, b
LIMIT 10

| a           | b          | dice  |
|-------------|------------|-------|
| "coablt"    | "cobalt"   | 0.571 |
| "dental"    | "dnetal"   | 0.571 |
| "pahrma"    | "pharma"   | 0.571 |
| "vertex"    | "vetrex"   | 0.571 |
| "zenith"    | "zentih"   | 0.571 |
| "sftware"   | "sotfware" | 0.588 |
| "horizon"   | "hroizon"  | 0.625 |
| "quantum"   | "qunatum"  | 0.625 |
| "analtyics" | "analytcs" | 0.632 |
| "aalytics"  | "analytcs" | 0.667 |
10 rows · relationships created: 43, properties set: 43

Every one of the 43 links (the counters) joins a typo to its word, or two typos of the same word (sftware and sotfware). None joins two different real words.

Block, then score candidate pairs

Comparing every pair of entities grows with the square of the data. Blocking compares only entities that share a token, or a token and its SIMILAR neighbor. In a graph that is a traversal, (a)-[:HAS_TOKEN]->()-[:SIMILAR]-{0,1}()<-[:HAS_TOKEN]-(b), where {0,1} makes the typo hop optional.

A candidate's score is the IDF-weighted share of both names' tokens found on the other side, exactly or through SIMILAR. Each entity first stores near, its own tokens plus their SIMILAR neighbors, so that scoring does not repeat the traversal for every token of every pair. The tokens enter the list ordered — ORDER BY t.text, s.text before the collects — so near is plan-choice-stable: collect keeps its encounter order, and a planner free to pick either traversal direction must not change what the graph renders. Then two CALL (a, b) { } subqueries each sum one side's token weights, and the weights of the tokens found in the other side's near list.

MATCH (e:Entity)-[:HAS_TOKEN]->(t:Token)
OPTIONAL MATCH (t)-[:SIMILAR]-(s:Token)
WITH e, t, s ORDER BY t.text, s.text
WITH e, collect(DISTINCT t.text) + collect(DISTINCT s.text) AS near
SET e.near = near;

MATCH (a:Entity)-[:HAS_TOKEN]->(:Token)-[:SIMILAR]-{0,1}(:Token)
      <-[:HAS_TOKEN]-(b:Entity)
WHERE a.key < b.key
WITH DISTINCT a, b
CALL (a, b) {
  MATCH (a)-[:HAS_TOKEN]->(t:Token)
  RETURN sum(t.idf) AS wa, sum(CASE WHEN t.text IN b.near THEN t.idf ELSE 0.0 END) AS ma
}
CALL (a, b) {
  MATCH (b)-[:HAS_TOKEN]->(t:Token)
  RETURN sum(t.idf) AS wb, sum(CASE WHEN t.text IN a.near THEN t.idf ELSE 0.0 END) AS mb
}
CREATE (a)-[:CANDIDATE {score: (ma + mb) / (wa + wb)}]->(b);

MATCH (e:Entity)
WITH count(e) AS n
MATCH ()-[c:CANDIDATE]->()
RETURN n * (n - 1) / 2 AS all_pairs, count(c) AS candidates

| all_pairs | candidates |
|-----------|------------|
| 29646     | 2230       |
1 row · relationships created: 2230, properties set: 2474

Blocking keeps under 8% of the possible pairs. At a few hundred entities you could afford them all; at a few hundred thousand, blocking makes it possible.

Add corroborating evidence

Names are not the only evidence. Each entity collects the email domains and cities of its records, and each candidate records whether its two sides share one. Entities that share a corporate domain but no name token become candidates too, with a score of 0, so that a human gets to see them: seven pairs here. Grouping entities by domain finds them without comparing every pair.

MATCH (e:Entity)<-[:RESOLVES_TO]-(r:Record)
WITH e, collect(DISTINCT r.domain) AS domains, collect(DISTINCT r.city) AS cities
SET e.domains = domains, e.cities = cities;

MATCH (a:Entity)-[c:CANDIDATE]->(b:Entity)
SET c.sameDomain = any(d IN a.domains WHERE d IN b.domains),
    c.sameCity = any(x IN a.cities WHERE x IN b.cities);

MATCH (e:Entity)
UNWIND e.domains AS domain
WITH domain, collect(e) AS sharing
UNWIND sharing AS a
UNWIND sharing AS b
WITH DISTINCT a, b
WHERE a.key < b.key AND NOT (a)-[:CANDIDATE]-(b)
CREATE (a)-[:CANDIDATE {score: 0.0, sameDomain: true,
                        sameCity: any(x IN a.cities WHERE x IN b.cities)}]->(b)

(no rows) · relationships created: 7, properties set: 4969

Choose the thresholds

Two thresholds in the decide parameters drive the decision: merge is the score that merges on its own, and corroborated is the lower score that merges with a shared domain or city. Before deciding, look at the strongest candidates that fall short of merge:

MATCH (a:Entity)-[c:CANDIDATE]->(b:Entity)
WHERE c.score < $merge
RETURN a.key AS a, b.key AS b, round(c.score, 3) AS score,
       c.sameDomain AS shared_domain, c.sameCity AS shared_city
ORDER BY score DESC, a, b
LIMIT 5

| a                      | b                      | score | shared_domain | shared_city |
|------------------------|------------------------|-------|---------------|-------------|
| "blue harbor pharma"   | "blue harbor security" | 0.724 | false         | true        |
| "blue harbor security" | "blue harbor widgets"  | 0.724 | false         | true        |
| "blue harbor health"   | "blue harbor security" | 0.717 | false         | false       |
| "blue harbor pharma"   | "blue harbor widgets"  | 0.714 | false         | true        |
| "blue harbor health"   | "blue harbor pharma"   | 0.707 | false         | false       |
5 rows

These are different companies that share a two-word prefix, and they score about 0.72. Typo pairs score 1.0, because every token finds its match. The gap is wide, so 0.9 merges safely, and 0.75 leaves room for a near-typo backed by a shared domain or city.

Merge and collapse

Merge a candidate when its score reaches merge, or reaches corroborated with a shared domain or city. Each connected group of SAME_AS edges is one company. To find the groups, every entity starts with its own key as a label and repeatedly takes the smallest label among its SAME_AS neighbors, one pass over the edges per round. (A quantified path such as (e)-[:SAME_AS]-{0,6}(o) would enumerate every path instead, and explode once loose thresholds form dense groups.) UNWIND range(1, 10) runs ten rounds, and each run of the CALL subquery sees the labels the previous one wrote. Then a second subquery names each company after its best spelling: the key with the most records, then the one whose words are most common elsewhere, which is the lowest total IDF, summed with reduce over a pattern comprehension.

The cell deletes earlier SAME_AS edges first, so you can edit the thresholds and run it again. unsettled counts SAME_AS edges whose two ends landed in different companies; it is 0 when ten rounds were enough.

MATCH ()-[s:SAME_AS]->() DELETE s;

MATCH (a:Entity)-[c:CANDIDATE]->(b:Entity)
WHERE c.score >= $merge OR (c.score >= $corroborated AND (c.sameDomain OR c.sameCity))
CREATE (a)-[:SAME_AS {score: c.score}]->(b);

MATCH (e:Entity)
SET e.cluster = e.key;

UNWIND range(1, 10) AS round
CALL (round) {
  MATCH (e:Entity)-[:SAME_AS]-(o:Entity)
  WITH e, min(o.cluster) AS lowest
  WITH e, lowest WHERE lowest < e.cluster
  SET e.cluster = lowest
};

MATCH (e:Entity)
WITH e.cluster AS component, collect(e) AS members
CALL (members) {
  UNWIND members AS o
  WITH o, COUNT { (o)<-[:RESOLVES_TO]-() } AS records,
       reduce(w = 0.0, idf IN [(o)-[:HAS_TOKEN]->(t) | t.idf] | w + idf) AS weight
  ORDER BY records DESC, weight, o.key
  LIMIT 1
  RETURN o.key AS name
}
UNWIND members AS e
SET e.cluster = name;

MATCH (e:Entity)
WITH count(e) AS entities, count(DISTINCT e.cluster) AS companies,
     count(CASE WHEN e.key <> e.cluster THEN 1 END) AS merged
RETURN entities, companies, merged,
       COUNT { MATCH (a:Entity)-[:SAME_AS]->(b:Entity)
               WHERE a.cluster <> b.cluster } AS unsettled

| entities | companies | merged | unsettled |
|----------|-----------|--------|-----------|
| 244      | 211       | 33     | 0         |
1 row · relationships created: 34, properties set: 555

Note Inside CALL () { } and CALL (x) { }, Relate rejects a WHERE on an aggregate from the same WITH: WITH e, min(o.cluster) AS lowest WHERE lowest < e.cluster fails with "variable 'lowest' not defined". Repeat the WITH before the WHERE, as above. Outside a subquery the one-line form works.

The first ten of the 33 spellings that were merged into another:

MATCH (e:Entity)
WHERE e.key <> e.cluster
RETURN e.cluster AS company, e.key AS merged_spelling
ORDER BY company, merged_spelling
LIMIT 10

| company                | merged_spelling        |
|------------------------|------------------------|
| "acme dental"          | "acme dnetal"          |
| "atlas security"       | "alas security"        |
| "beacon consulting"    | "bacon consulting"     |
| "beacon robotics"      | "bacon robotics"       |
| "blue harbor pharma"   | "blue harbor pahrma"   |
| "brightpath freight"   | "brightpath freght"    |
| "brightpath pharma"    | "brightpath pahrma"    |
| "cobalt logistics"     | "coblt logistics"      |
| "driftwood security"   | "drfitwood security"   |
| "harborview analytics" | "harborview analtyics" |
10 rows

The best-spelling rule picks beacon consulting over bacon consulting because beacon is the more common word. When both spellings have a typo it can only pick one of them: further down the list, maple analytcs merges into maple aalytics.

Note A score of 1.0 merges without corroboration, so bacon robotics joins beacon robotics as a typo. In real data those could be two companies. If a false merge costs more than a review, require corroboration for every merge.

See one company

Open the graph view. Three records, three spellings of Harborview Analytics: harboview and analtyics are SIMILAR to harborview and analytics, so the three entities score 1.0 against each other, and their SAME_AS edges form a triangle.

MATCH p = (:Record)-[:RESOLVES_TO]->(:Entity {cluster: $company})-[:HAS_TOKEN]->(:Token)
RETURN p
UNION
MATCH p = (:Entity {cluster: $company})-[:SAME_AS]->(:Entity)
RETURN p
UNION
MATCH p = (:Entity {cluster: $company})-[:HAS_TOKEN]->(:Token)-[:SIMILAR]->(:Token)
          <-[:HAS_TOKEN]-(:Entity {cluster: $company})
RETURN p

// 13 rows
(n92:Record:Crm {city: "Atlanta", email: "[email protected]", id: "C0093", key: "harborview analytics", name: "Harborview Analytics, LLC"})-[r92:RESOLVES_TO]->(n487:Entity {cities: ["Atlanta"], cluster: "harborview analytics", domains: cypher`[]`, key: "harborview analytics", near: ["analytics", "harborview", "aalytics", "analtyics", "analytcs", "harboview"]})-[r587:HAS_TOKEN]->(n664:Token {bigrams: [" a", "an", "na", "al", "ly", "yt", "ti", "ic", "cs", "s "], idf: 3.1122615755052014, text: "analytics"})
(n92)-[r92]->(n487)-[r586:HAS_TOKEN]->(n685:Token {bigrams: [" h", "ha", "ar", "rb", "bo", "or", "rv", "vi", "ie", "ew", "w "], idf: 3.517726683613366, text: "harborview"})
(n294:Record:Billing {city: "Atlanta", domain: "harborviewanalytics.com", email: "[email protected]", id: "B0101", key: "harboview analytics", name: "Harboview Analytics LLC"})-[r294:RESOLVES_TO]->(n608:Entity {cities: ["Atlanta"], cluster: "harborview analytics", domains: ["harborviewanalytics.com"], key: "harboview analytics", near: ["analytics", "harboview", "aalytics", "analtyics", "analytcs", "harborview"]})-[r835:HAS_TOKEN]->(n664)
(n294)-[r294]->(n608)-[r834:HAS_TOKEN]->(n723:Token {bigrams: [" h", "ha", "ar", "rb", "bo", "ov", "vi", "ie", "ew", "w "], idf: 5.597168225293202, text: "harboview"})
(n328:Record:Billing {city: "Atlanta", domain: "harborviewanalytics.com", email: "[email protected]", id: "B0135", key: "harborview analtyics", name: "The Harborview Analtyics L.L.C."})-[r328:RESOLVES_TO]->(n615:Entity {cities: ["Atlanta"], cluster: "harborview analytics", domains: ["harborviewanalytics.com"], key: "harborview analtyics", near: ["analtyics", "harborview", "analytcs", "analytics", "harboview"]})-[r849:HAS_TOKEN]->(n726:Token {bigrams: [" a", "an", "na", "al", "lt", "ty", "yi", "ic", "cs", "s "], idf: 5.597168225293202, text: "analtyics"})
(n328)-[r328]->(n615)-[r848:HAS_TOKEN]->(n685)
(n487)-[r3188:SAME_AS {score: 1.0}]->(n608)
(n615)-[r3207:SAME_AS {score: 1.0}]->(n608)
(n615)-[r3206:SAME_AS {score: 1.0}]->(n487)
(n487)-[r586]->(n685)-[r912:SIMILAR {dice: 0.8571428571428571}]->(n723)<-[r834]-(n608)
(n615)-[r849]->(n726)-[r936:SIMILAR {dice: 0.7}]->(n664)<-[r835]-(n608)
(n615)-[r849]->(n726)-[r936]->(n664)<-[r587]-(n487)
(n615)-[r848]->(n685)-[r912]->(n723)<-[r834]-(n608)

Queue the doubtful pairs for review

Some evidence is too weak to act on. Two companies with the same corporate email domain might be one company, or a parent and its subsidiary. A name score between corroborated and merge with nothing to back it up is just as doubtful. These pairs go to a human as a POSSIBLE_MATCH edge between the two companies. Like the merge step, the cell replaces the queue each time it runs.

MATCH ()-[p:POSSIBLE_MATCH]->() DELETE p;

MATCH (a:Entity)-[c:CANDIDATE]->(b:Entity)
WHERE a.cluster <> b.cluster AND (c.sameDomain OR c.score >= $corroborated)
WITH CASE WHEN a.cluster < b.cluster THEN [a.cluster, b.cluster]
          ELSE [b.cluster, a.cluster] END AS pair,
     max(c.score) AS score,
     count(CASE WHEN c.sameDomain THEN 1 END) > 0 AS sharedDomain
MATCH (x:Entity {key: pair[0]}), (y:Entity {key: pair[1]})
CREATE (x)-[:POSSIBLE_MATCH {score: score,
                            reason: CASE WHEN sharedDomain THEN 'shared domain'
                                         ELSE 'similar name' END}]->(y);

MATCH (a:Entity)-[p:POSSIBLE_MATCH]->(b:Entity)
RETURN a.key AS a, b.key AS b, round(p.score, 3) AS score, p.reason AS reason
ORDER BY a, b
LIMIT 100

| a                    | b                      | score | reason          |
|----------------------|------------------------|-------|-----------------|
| "acme outfitters"    | "tidewater widgets"    | 0.0   | "shared domain" |
| "acme robotics"      | "northwind outfitters" | 0.0   | "shared domain" |
| "golden gate labs"   | "oakridge consulting"  | 0.0   | "shared domain" |
| "horizon pharma"     | "quantum security"     | 0.0   | "shared domain" |
| "ironclad security"  | "riverstone pharma"    | 0.0   | "shared domain" |
| "juniper sloar"      | "juniper solar"        | 0.466 | "shared domain" |
| "pinnacle logistics" | "quantum media"        | 0.0   | "shared domain" |
7 rows · relationships created: 7, properties set: 14

Six pairs share a domain but no name word: they look like subsidiaries that use a parent's mail server. The seventh, juniper sloar and juniper solar, is the typo that bigrams missed; the shared domain is what brings it to a human.

Report

A map projection builds one document per company, with COLLECT { } subqueries for its record ids. The summary counts the companies found in both exports, the orphans found in only one, and the companies with duplicate rows inside one export: the rows to clean up in the source systems.

MATCH (c:Entity)
WHERE c.key = c.cluster
WITH c {.key,
        crm: COLLECT { MATCH (:Entity {cluster: c.key})<-[:RESOLVES_TO]-(r:Crm)
                       RETURN r.id ORDER BY r.id },
        billing: COLLECT { MATCH (:Entity {cluster: c.key})<-[:RESOLVES_TO]-(r:Billing)
                           RETURN r.id ORDER BY r.id }
       } AS company
WITH count(company) AS companies,
     count(CASE WHEN size(company.crm) > 0 AND size(company.billing) > 0
                THEN 1 END) AS in_both,
     count(CASE WHEN size(company.crm) > 1 OR size(company.billing) > 1
                THEN 1 END) AS duplicated,
     count(CASE WHEN size(company.crm) = 0 OR size(company.billing) = 0
                THEN 1 END) AS orphans
RETURN companies, in_both, duplicated, orphans,
       COUNT { ()-[:POSSIBLE_MATCH]->() } AS to_review

| companies | in_both | duplicated | orphans | to_review |
|-----------|---------|------------|---------|-----------|
| 211       | 151     | 33         | 60      | 7         |
1 row

Note Two details keep this query fast in Relate. Inside COLLECT { }, matching starts at the pattern's first node, so start from the node the outer query pins down: (:Entity {cluster: c.key})<-…-(r) uses the cluster index, and the reverse walks every CRM record for each company, about 40 times slower. And count with count(CASE …) over rows: collecting every company into one list and filtering it with list comprehensions is quadratic, more than ten times slower here.

Score against the ground truth

The generator recorded which company each row came from. Precision and recall are counted over pairs of records: a pair is found when both records resolve to the same company, real when the ground truth says they are the same company, and correct when both are true.

The pairs never need to be listed. A group of records with c CRM rows and b billing rows holds c × b cross-file pairs and c(c-1)/2 + b(b-1)/2 in-file pairs. So each record is counted in three groups: its resolved company (found pairs), its true company (real pairs), and the two together (correct pairs). UNWIND ['all', k.kind] adds every count to the total as well as to its kind.

UNWIND $truth AS t
UNWIND t.records AS id
MATCH (r:Record {id: id})
SET r.truth = t.entity;

MATCH (e:Entity)<-[:RESOLVES_TO]-(r:Record)
UNWIND [{side: 'found', company: e.cluster},
        {side: 'real', company: r.truth},
        {side: 'correct', company: e.cluster + ' / ' + r.truth}] AS g
WITH g.side AS side, g.company AS company,
     count(CASE WHEN r:Crm THEN 1 END) AS c,
     count(CASE WHEN r:Billing THEN 1 END) AS b
UNWIND [{kind: 'cross-file', n: c * b},
        {kind: 'in-file', n: (c * (c - 1) + b * (b - 1)) / 2}] AS k
UNWIND ['all', k.kind] AS pairs
WITH pairs,
     sum(CASE WHEN side = 'found' THEN k.n ELSE 0 END) AS found,
     sum(CASE WHEN side = 'real' THEN k.n ELSE 0 END) AS real,
     sum(CASE WHEN side = 'correct' THEN k.n ELSE 0 END) AS correct
RETURN pairs, found, real, correct,
       round(toFloat(correct) / found, 3) AS precision,
       round(toFloat(correct) / real, 3) AS recall
ORDER BY pairs

| pairs        | found | real | correct | precision | recall |
|--------------|-------|------|---------|-----------|--------|
| "all"        | 216   | 218  | 216     | 1.0       | 0.991  |
| "cross-file" | 182   | 183  | 182     | 1.0       | 0.995  |
| "in-file"    | 34    | 35   | 34      | 1.0       | 0.971  |
3 rows · properties set: 396

Every merge is right, and only two real pairs are missed: juniper sloar against the two juniper solar records, one in each export. To check the thresholds, label every candidate pair with the truth and bucket the scores:

UNWIND [{score: '0.90 to 1.00', low: 0.9, high: 1.1},
        {score: '0.75 to 0.90', low: 0.75, high: 0.9},
        {score: '0.50 to 0.75', low: 0.5, high: 0.75},
        {score: '0.00 to 0.50', low: 0.0, high: 0.5}] AS band
CALL (band) {
  MATCH (a:Entity)-[c:CANDIDATE]->(b:Entity)
  WHERE band.low <= c.score < band.high
  WITH c, EXISTS {
    MATCH (a)<-[:RESOLVES_TO]-(x:Record), (b)<-[:RESOLVES_TO]-(y:Record)
    WHERE x.truth = y.truth
  } AS same
  RETURN count(CASE WHEN same THEN 1 END) AS same_company,
         count(CASE WHEN NOT same THEN 1 END) AS different,
         round(max(CASE WHEN NOT same THEN c.score END), 3) AS best_different
}
RETURN band.score AS score, same_company, different, best_different

| score          | same_company | different | best_different |
|----------------|--------------|-----------|----------------|
| "0.90 to 1.00" | 34           | 0         | null           |
| "0.75 to 0.90" | 0            | 0         | null           |
| "0.50 to 0.75" | 0            | 810       | 0.724          |
| "0.00 to 0.50" | 1            | 1392      | 0.5            |
4 rows

No candidate scores between 0.75 and 0.9 on this data, and no pair of different companies scores above 0.724, so the thresholds have room on both sides. The one same-company pair below them is juniper sloar, at 0.466. Rerun this check on your own data before you trust the thresholds. Finally, grade the review queue:

MATCH (a:Entity)-[p:POSSIBLE_MATCH]->(b:Entity)
WITH a, b, EXISTS {
       MATCH (:Entity {cluster: a.key})<-[:RESOLVES_TO]-(x:Record),
             (:Entity {cluster: b.key})<-[:RESOLVES_TO]-(y:Record)
       WHERE x.truth = y.truth
     } AS same
RETURN count(*) AS pairs, count(CASE WHEN same THEN 1 END) AS same_company,
       collect(CASE WHEN same THEN a.key + ' / ' + b.key END) AS same_pairs

| pairs | same_company | same_pairs                        |
|-------|--------------|-----------------------------------|
| 7     | 1            | ["juniper sloar / juniper solar"] |
1 row

A reviewer who accepts everything gains the two missed pairs but merges six pairs of different companies. The queue is short enough to check by hand, which is the point of it.

Try it on your own data

• Change merge and corroborated in the merge and review cells, run them again, then rerun the report and the score to see precision and recall move. Even extreme values finish quickly.
• Extend the suffix and free-mail lists in the normalize step to match your data; they are plain Cypher lists.