Deduplicate and normalize

A raw company list is wrong in two predictable ways: one company appears under three names, and rows that look alike turn out to be different companies. Fix both before you spend anything on enrichment, because every later number in the TAM counts these rows.

Throughout this chapter, say you sell payroll software to French SMEs, and the extraction step left you with 18,000 rows from the SIRENE registry, a LinkedIn export and a trade directory. All volumes below are invented for the example.

Give every account one key before you compare anything

I key accounts on the root domain, because it is the identifier most sources share. For French companies I add the SIREN as a second key, since registry rows often have no website.

Key	Good for	Breaks when
Root domain	Matching across any source and country	The company has no site, or its “website” is a social page
SIREN (9 digits)	One French legal unit	The row comes from a source that doesn’t carry it
SIRET (14 digits)	One establishment	You use it as the account key and count every site as a customer
LinkedIn company URL	Cross-checking and finding people later	A company has several pages, or none
Name and city	Last resort	Two companies share a name, or one uses an abbreviation

Trust follows that order: domain, then SIREN, then LinkedIn. A company name alone never merges two rows automatically, since two unrelated businesses in different départements can carry the same one.

Normalize the fields you will match on

Add normalized columns next to the raw ones and never overwrite the original. You will want it the day a merge turns out to be wrong.

Field	Raw	Rule	Normalized
domain_root	https://www.Acme-Paie.fr/contact?x=1	Lowercase, drop protocol, www, path and parameters	acme-paie.fr
name_norm	ACME PAIE S.A.S.	Lowercase, drop punctuation, accents and legal form	acme paie
siren	123 456 782	Digits only, stored as text, 9 characters	123456782
naf_code	62.01Z	Uppercase, no dot	6201Z
country	France, Fr.	ISO two-letter code	FR
headcount_band	35 salariés, 11-50	Map each source to your own bands	10-49 or 11-50, kept per source

In Google Sheets, with the raw website in column A and the raw name in column B:

=REGEXREPLACE(REGEXREPLACE(LOWER(TRIM(A2)), "^(https?://)?(www\.)?", ""), "[/?#].*$", "")

=TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(B2), "[.,]", ""), "\b(sas|sasu|sarl|eurl|sa|sci|snc)\b", ""))

The first formula keeps subdomains, so paie.acme.com and acme.com stay different. Filter the rows with more than one dot and fix them by hand, or use a public suffix library if you work in code. For accents, PostgreSQL has the unaccent extension; in a sheet, a chain of SUBSTITUTE covers the French cases.

Decide what counts as an account when subsidiaries appear

The right unit is the one that signs your contract. Write the rule down, because your list will contain every one of these cases.

Situation	Treatment for payroll software
One SIREN, several SIRET (shops, plants)	One account, establishments in a child table
One domain, several SIREN	One account, flagged group_check, until you know who buys
Several brands and domains, one owner	Separate accounts linked by parent_account_id
Franchise network	One account per franchisee legal unit, with a network column
Holding with no employees	Keep as a parent only, never as an SME to sell to
French subsidiary of a foreign group	Keep the French entity, record the parent

Public SIRENE data describes legal units and their establishments. It won’t tell you who owns whom, so group links come from the company’s own site, LinkedIn or a corporate-structure source. Don’t chase ownership across 15,000 rows. Flag the obvious cases now and resolve the rest once scoring has told you which accounts deserve the work.

Remove the rows that should not be in the TAM

Pattern	How to spot it	Action
Ceased company	etatAdministratifUniteLegale = C in the SIRENE file	Remove
Domain that isn’t the company’s	Blocklist: social networks, directories, site builders	Empty the domain, keep the row
Unknown employee range	Registry code NN, which covers non-employers and missing information	Treat as unknown, never as zero
Placeholder values	N/A, -, en cours de création	Empty the field
Outside the ICP on a hard criterion	Sector or country you never sell to	Move to an excluded tab with the reason

Keep the excluded rows somewhere. They stop you from importing the same junk at the next refresh.

Merge with written rules and keep the trail

Confidence	Rule	Action
High	Same domain_root, or same siren	Merge automatically
Medium	Same name_norm and postal code, different or missing domain	Human review queue
Low	Same name_norm only	Leave separate

When rows merge, choose the winner field by field. The registry wins for legal name, SIREN, NAF code and address. The company’s site or LinkedIn page wins for the website. Headcount stays in two columns, one per source, because the next chapter needs both. In SQL, with source_rank as an integer you assign to each source:

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY domain_root
      ORDER BY (siren IS NOT NULL) DESC, source_rank ASC, collected_at DESC
    ) AS rn
  FROM accounts_raw
  WHERE domain_root IS NOT NULL
)
SELECT * FROM ranked WHERE rn = 1;

Log every merge with merge_id, kept_account_id, removed_account_id, match_rule and merged_at, so you can undo one without restarting. In the example, the funnel looks like this:

Step	Rows left
Raw, three sources	18,000
After removing ceased and out-of-scope rows	15,400
After automatic merges on domain and SIREN	11,900
After the review queue	11,700

If the count barely moves, your keys are probably too weak. Check the share of rows that have neither a domain nor a SIREN.

Doing it with Derrick, or by hand

I co-founded Derrick, so take this with that in mind. Find Duplicates runs from the web app, the Google Sheets sidebar, the API or an AI assistant over MCP. You select the columns to compare, a duplicate column flags the matching rows, and it costs no credits. Point it at domain_root rather than the raw name. It won’t normalize your values, pick the surviving row or understand a group structure, so the rules above stay your job.

For French rows with a name and no SIREN, SIRET/SIREN Enrichment takes a SIRET, a SIREN or a company name, with an optional département to disambiguate. It costs 1 credit per company and returns the legal data, plus matched_name and a confidence score when you searched by name. Send weak matches to the review queue, because a wrong SIREN will merge two companies without telling you.

The method works without any tool. A helper column with =COUNTIF($C$2:$C,C2)>1 does the flagging, the SQL above picks the survivors, and the SIRENE file on data.gouv.fr gives you identifiers for free if you are willing to write the name matching yourself.

Your next step: add the normalized columns to a 200-row sample, read the medium-confidence matches by hand and adjust the rules before running the full list. The output is one accounts table where each company has a unique account_id, ready for enrichment.