Build the company list

Run several narrow queries, each tied to one segment and one source, and load everything into a single raw table that remembers where each row came from. One huge export is hard to diagnose, while ten logged queries show exactly which one brought the junk.

At this stage you collect, and you resist cleaning. Deduplication and normalization come in the next chapter, and they need the provenance you are about to record. The deliverable here is a raw company table and a query log. The inputs are your criteria sheet and your source register. Our payroll vendor for French multi-site employers is still invented, and so are its numbers.

Write the query plan before extracting anything

One line per query. The expected volume is your guess, written down in advance so a surprising result gets noticed.

ID	Segment	Source	Query	Expected rows	Source limit	How to split
Q1	Childcare groups	SIRENE	88.91A, active, 20 to 499, 3+ sites	400	None	None
Q2	Care home groups	SIRENE	87.10A, active, 20 to 499, 3+ sites	350	None	None
Q3	Childcare groups	Sales Navigator	Keyword “crèche”, France, 11 to 500	600	Results capped per search	By region
Q4	Childcare sites	Maps	“crèche” + city, 30 largest cities	1,200	200 per search in the tool below	By city
Q5	Lookalikes	Similar companies	30 best customers as seeds	300	Depends on seed	By seed

Search interfaces cap what they return, and the cap changes, so check the current one for each source. When a query reaches its cap, you’ve seen a truncated sample with no warning. Cut it by region, headcount band or industry until every slice sits comfortably below the limit.

The registry gives you a sanity check on the other sources. If a keyword search returns several times more companies than the registry holds for that activity, the keyword is pulling in people who only mention the topic.

Set up the raw table

Keep every source in one table with the same columns. Leave a column empty when a source doesn’t have it, and keep the original values untouched.

Column	Example	Purpose
raw_id	Generated	Row identity
query_id	Q3	Link to the query log
run_order	3	Measuring what each query added
source	sales_navigator	Provenance
extracted_at	2026-11-09	Freshness, later maintenance
name_raw	Les P’tits Loups SAS	As delivered
siren	9 digits, or empty	Strong key for France
domain_raw	www.example.fr	Future key, cleaned next chapter
linkedin_url	Company page URL	Key for LinkedIn sources
city, postcode, country	Lyon, 69003, FR	Geography
naf_code	88.91A	Filter
headcount_raw	Band code or range	Filter, formats vary by source
open_sites	6	Filter
source_payload	JSON of the original row	Recover anything you did not map

Extract from the registry

Q1 and Q2 run on the two SIRENE files, loaded into a database or a local analytical engine. The legal unit file carries the activity and headcount. The establishment file lets you count the open sites.

WITH sites AS (
  SELECT siren, COUNT(*) AS open_sites
  FROM etablissement
  WHERE etatAdministratifEtablissement = 'A'
  GROUP BY siren
)
SELECT
  'Q1'                               AS query_id,
  'sirene'                           AS source,
  CURRENT_DATE                       AS extracted_at,
  u.siren,
  u.denominationUniteLegale          AS name_raw,
  u.activitePrincipaleUniteLegale    AS naf_code,
  u.trancheEffectifsUniteLegale      AS headcount_raw,
  u.statutDiffusionUniteLegale       AS diffusion_status,
  s.open_sites
FROM unite_legale u
JOIN sites s ON s.siren = u.siren
WHERE u.etatAdministratifUniteLegale = 'A'
  AND u.activitePrincipaleUniteLegale = '88.91A'
  AND s.open_sites >= 3
  AND (u.trancheEffectifsUniteLegale IN ('12','21','22','31','32')
       OR u.trancheEffectifsUniteLegale IS NULL
       OR u.trancheEffectifsUniteLegale = 'NN');

Accounts with an unknown band stay in, flagged for review, since dropping them would remove companies on the basis of a gap in the data. Check the column names against the current file documentation, because they can change between releases.

The same queries in Derrick

Derrick, which I co-founded, so factor that in, runs these from a sheet or its web app and writes each result to rows, which suits teams without a database.

Query	Feature	What you put in	What you get
Q1, Q2	Import Companies by NAF Code	88.91A in a column, optional département	One company per row in a new tab, with SIREN/SIRET, open establishments, headcount band
Q3	Import LinkedIn Companies	The Sales Navigator search URL	Company data, one row per company
Q4	Google Maps Scraper	“crèche Lyon”	Up to 200 places: name, phone, website, address
Q5	Find Similar Companies	A customer’s LinkedIn company URL, optional country	Similar companies with industry, country, website and a similarity score

Credits follow the rows: 1 per company on the NAF and Sales Navigator imports, 1 per place on Maps, 1 per matched company on similar companies. The NAF import returns every company under the code, and you apply the 20 to 499 and 3+ site filters afterward in the sheet, so you pay for the whole population of that code. For a large code, run one département first and look at the output. Import Companies from a Prompt is handy for exploring a segment in one sentence, but treat it as discovery and never as proof that you’ve seen the entire market.

Without Derrick, Q1 and Q2 run on the free file as above, Q3 can go through your search tool’s own export if your plan and its terms allow it, and small lists can be copied by hand. Keep the same raw table whichever route you take.

Measure what each query added

Before deduplication, a crude key is enough to see whether a query brought anything new. Run the queries in their planned order.

WITH keyed AS (
  SELECT *, COALESCE(siren, lower(domain_raw), linkedin_url) AS account_key
  FROM raw_companies
),
first_seen AS (
  SELECT account_key, MIN(run_order) AS first_run
  FROM keyed
  GROUP BY account_key
)
SELECT
  k.query_id,
  COUNT(DISTINCT k.account_key) AS accounts,
  COUNT(DISTINCT CASE WHEN f.first_run = k.run_order THEN k.account_key END) AS new_accounts
FROM keyed k
JOIN first_seen f USING (account_key)
GROUP BY k.query_id, k.run_order
ORDER BY k.run_order;

The key will undercount overlaps between a registry row with no domain and a LinkedIn row with no SIREN, which is fine for this purpose. Then check precision: pull 50 random rows per query and mark each one as a real target or not.

=SORTN(A2:F1500, 50, 0, RANDARRAY(1499), TRUE)

Query	Rows	New accounts	New share	Precision on 50 rows	Decision
Q1	412	412	100%	96%	Keep
Q2	368	368	100%	92%	Keep
Q3	580	190	33%	62%	Keep, tighten the keyword
Q4	1,100	140	13%	74%	Keep the new ones only
Q5	240	12	5%	58%	Stop

A reasonable stopping rule: when a new query adds under 10% of new accounts and its precision is falling, you’ve reached what your sources can see. Q5 hits that point. The 190 accounts from Q3 are worth a read, because they may be groups the registry filed under a code you excluded, and that would send you back to the criteria sheet.

Keep the query log with the data

Query	Run on	By	Exact parameters or URL	Rows	Cap reached	Credits or cost	Notes
Q3	2026-11-09	Growth	Sales Navigator URL	580	No	580	Split into 13 regions

You will rerun these queries when you refresh the TAM, so anything not written here will be rebuilt from memory.

Your next step: write your query plan, run the anchor queries first, and measure what each following query adds until you hit your stopping rule. Then take the raw table to deduplication, before you spend anything on enrichment.