> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# ETL: Matching Company Names Across Two Sources

> Match organization names between a file and a CRM in Integrate.io ETL with NAME_MATCH_KEYS and FUZZY_MATCH, picking one best match per row.

## Background

A common task is matching a list of organizations, such as a file of a few hundred company or fund names, to the accounts in a CRM or database. The names rarely match exactly, and most differences aren't spelling mistakes:

* acronyms: `ADIA` vs `Abu Dhabi Investment Authority (ADIA)`
* abbreviations and state codes: `NJ Division of Investment` vs `New Jersey Division of Investment`
* extra words: `Temasek` vs `Temasek Holdings Pte Ltd`
* a second organization in parentheses: `Northwind College (Contoso Advisors)`

Comparing every row with every row (a Cross Join) gets slow as the sources grow, and picking the best match by hand is error-prone. This recipe does it in three stages:

1. **Find candidates.** Generate matching keys for each name with [NAME\_MATCH\_KEYS](https://www.integrate.io/docs/etl/name-match-keys.md) and join the two sources on them. Only names that share a word or acronym are compared.
2. **Score.** Score each candidate pair with [FUZZY\_MATCH](https://www.integrate.io/docs/etl/fuzzy-match.md) and the `company` method.
3. **Decide.** Keep the best account for each row, and label it `MATCH`, `REVIEW` or `NO MATCH`.

In the steps below, the first source has `id` and `name` fields and the second has `account_id` and `account_name`. Rename to suit your data.

## Steps

### 1. Generate keys on both sides

Add a **Select** after each source. Keep the fields you need and add a key field:

| Source | Expression | Alias |
| - | - | - |
| First source | `FLATTEN(TOKENIZE(COALESCE(NAME_MATCH_KEYS(name), '-'), ' '))` | `match_key` |
| Second source | `FLATTEN(TOKENIZE(NAME_MATCH_KEYS(account_name), ' '))` | `account_key` |

Each row now appears once per key. `NAME_MATCH_KEYS` returns `null` for a blank or placeholder name such as `N/A`; the `COALESCE` gives such rows on the first source a key that matches nothing, so they still reach the output as `NO MATCH`. Accounts with blank names on the second source are dropped, which is what you want.

### 2. Join on the keys

Add a **Join** with the first source on the left, type **Left join**, matching `match_key` to `account_key`. Rows with no candidate keep a null `account_id`, so they still reach the output as `NO MATCH`.

### 3. Remove duplicate pairs

Two names can share more than one key. Add a **Select** that keeps only `id`, `name`, `account_id` and `account_name`, then a **Distinct**.

### 4. Score each pair

Add a **Select** that keeps those four fields and adds:

| Expression | Alias |
| - | - |
| `COALESCE(FUZZY_MATCH(name, account_name, 'company'), 0)` | `match_score` |

### 5. Rank the candidates

Add a **Window** partitioned by `id` and ordered by `match_score` descending, with:

| Function | Alias |
| - | - |
| `row_number` | `row_rank` |
| `lead` of `match_score`, offset 1, default `0` | `runner_up_score` |

Then a second **Window** partitioned by `account_id` and ordered by `match_score` descending, with `row_number` as `account_rank` and `lead` of `match_score` (offset 1, default `0`) as `account_runner_up`. This tells you whether another row is an even better fit for the same account.

### 6. Keep the best candidate and label it

Add a **Filter** that keeps `row_rank == 1`, then a **Select** that adds:

```sql theme={null}
(match_score >= 80 AND match_score - runner_up_score >= 8 AND account_rank == 1 AND match_score > account_runner_up)
  ? 'MATCH'
  : (match_score >= 60 ? 'REVIEW' : 'NO MATCH')
```

as `match_status`. Send the result to a destination, or use a **Filter** on `match_status` to write matches and rows for review to separate outputs.

## Tuning

* **Thresholds.** `80` (match), `60` (review) and the `8`-point lead over the runner-up are starting points. Check a sample of `REVIEW` rows and adjust.
* **Ties.** If two accounts score the same for one row, the lead over the runner-up is `0`, so the row goes to review rather than being matched to either.
* **Known aliases.** Some names can't be worked out from their words, such as nicknames or acronyms used for different organizations. Keep a small file of `alias,full_name` pairs and, before step 1, use a **Join** or **Lookup** to replace a known alias with its full name.
* **Large second sources.** The join only pairs names that share a distinctive word or acronym, which is usually far fewer comparisons than a Cross Join. Short acronym keys and words that are common in your data (for example `health` or `energy` in a list of hospitals) can still bring in many candidates; check the row count after the Distinct in step 3 on a real run.

## Related

* [FUZZY\_MATCH](https://www.integrate.io/docs/etl/fuzzy-match.md)
* [NAME\_MATCH\_KEYS](https://www.integrate.io/docs/etl/name-match-keys.md)
* [Window transformation](https://www.integrate.io/docs/etl/using-components-window-transformation.md)
* [Lookup transformation](https://www.integrate.io/docs/etl/using-components-db-lookup-transformation.md), which also offers **Company names** as a fuzzy algorithm for database lookups


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.