How these numbers were made

Lists the exact technology-category spellings in the crawl, so the queries below filter on real values rather than guesses.

-- 00_category_discovery.sql — enumerate Wappalyzer category names before trusting them
--
-- PURPOSE
--   The adoption queries (01, 02) filter on Wappalyzer category *names* as they appear
--   in HTTP Archive (e.g. 'Analytics', 'Tag managers'). Those names are controlled by
--   the Wappalyzer ruleset, not by us, and can change between crawls. Run this first,
--   against the free 10k sample table, and confirm the exact spelling of every category
--   used in 01/02 before spending money on the full crawl tables.
--
-- VERIFIED SCHEMA ASSUMPTIONS (checked against har.fyi on 2026-07-18):
--   - `httparchive.crawl.pages` is partitioned by `date` (DATE, required in WHERE) and
--     clustered; `client` STRING ('desktop'|'mobile'), `is_root_page` BOOL.
--     Source: https://har.fyi/reference/tables/pages/
--   - `technologies` is ARRAY<STRUCT> with `technology` STRING and repeated
--     `categories` (STRING) — both need UNNEST.
--     Source: https://har.fyi/reference/structs/technology/
--   - `httparchive.sample_data.pages_10k` is the sanctioned small prototype table.
--     Source: https://har.fyi/guides/minimizing-costs/
--
-- COST GUARD
--   This runs against the 10k sample table only — cost is negligible, but still
--   dry-run out of habit:
--     bq query --nouse_legacy_sql --dry_run < data/queries/00_category_discovery.sql
--
-- RUN
--   bq query --nouse_legacy_sql --format=csv --max_rows=1000 \
--     < data/queries/00_category_discovery.sql

SELECT
  category,
  COUNT(DISTINCT t.technology) AS technologies,
  COUNT(DISTINCT page) AS pages
FROM `httparchive.sample_data.pages_10k`,
  UNNEST(technologies) AS t,
  UNNEST(t.categories) AS category
WHERE client = 'mobile'
  AND is_root_page
GROUP BY category
ORDER BY pages DESC;

Feeds §1. Pages with each technology category detected, per quarterly crawl, over the total pages in that crawl.

-- 01_adoption_by_category.sql — adoption by technology category over time
--
-- PURPOSE
--   For a small set of crawl months, count root pages running at least one detected
--   technology in each tracked category (analytics, tag managers, consent/CMP, CDP,
--   A/B testing), plus the per-month page total, so the report can chart adoption
--   share over time.
--
-- VERIFIED SCHEMA ASSUMPTIONS (checked against har.fyi on 2026-07-18):
--   - `httparchive.crawl.pages`: partitioned by `date` (DATE, required in every WHERE),
--     clustered; one row per page per client per crawl. Columns used: `date`, `client`
--     STRING ('desktop'|'mobile'), `page` STRING, `is_root_page` BOOL, `technologies`.
--     Source: https://har.fyi/reference/tables/pages/
--   - `technologies` is ARRAY<STRUCT> with `technology` STRING and repeated
--     `categories` STRING — both require UNNEST; detection is Wappalyzer-based.
--     Source: https://har.fyi/reference/structs/technology/
--   - Category names below follow Wappalyzer naming as surfaced by HTTP Archive.
--     VALIDATE the exact spellings with 00_category_discovery.sql (cheap, sample
--     table) before running this query, and edit the IN list to match.
--
-- COST GUARD (do not skip)
--   - Literal dates in the WHERE clause guarantee partition pruning; each extra month
--     is another partition scanned — keep the list short.
--   - Only `date`, `page`, `technologies` plus the filter columns (`client`,
--     `is_root_page`) are read; with BigQuery's columnar billing you pay only for
--     those columns (https://har.fyi/guides/minimizing-costs/).
--   - ALWAYS dry-run and abort if the estimate exceeds your budget:
--       bq query --nouse_legacy_sql --dry_run < data/queries/01_adoption_by_category.sql
--   - Prototype the query shape on `httparchive.sample_data.pages_10k` first.
--   - LIMIT does not reduce bytes scanned.
--
-- RUN (see data/README.md)
--   bq query --nouse_legacy_sql --format=csv --max_rows=100000 \
--     < data/queries/01_adoption_by_category.sql \
--     > data/export/input/adoption_by_category.csv

WITH
  pages AS (
    SELECT date, page, technologies
    FROM `httparchive.crawl.pages`
    -- Prototype first: FROM `httparchive.sample_data.pages_10k`
    WHERE date IN ('2025-09-01', '2025-12-01', '2026-03-01', '2026-06-01')
      -- EDIT: quarterly snapshots ending at the latest published crawl.
      -- Literal dates (not variables) so the partition pruner provably applies.
      AND client = 'mobile'
      AND is_root_page
  ),

  totals AS (
    SELECT date, COUNT(*) AS pages_total  -- one row per page in `pages`
    FROM pages
    GROUP BY date
  ),

  category_hits AS (
    -- DISTINCT page: several detected technologies on one page can share a category.
    SELECT p.date, category, COUNT(DISTINCT p.page) AS pages_with_category
    FROM pages AS p,
      UNNEST(p.technologies) AS t,
      UNNEST(t.categories) AS category
    WHERE category IN (
      -- EDIT to match 00_category_discovery.sql output before running:
      'Analytics',
      'Tag managers',
      'Cookie compliance',        -- consent / CMP
      'Customer data platform',
      'A/B Testing'
    )
    GROUP BY p.date, category
  )

SELECT
  c.date,
  c.category,
  c.pages_with_category,
  tt.pages_total,
  ROUND(SAFE_DIVIDE(c.pages_with_category, tt.pages_total), 6) AS share_of_pages
FROM category_hits AS c
JOIN totals AS tt USING (date)
ORDER BY c.date, c.category;

Feeds §2. Share of pages running each analytics technology within each rank bucket.

-- 02_top_analytics_by_rank.sql — top analytics technologies by site-rank bucket
--
-- PURPOSE
--   For one crawl month, rank the most-detected 'Analytics' technologies within each
--   CrUX popularity bucket (top 1k / 10k / 100k / 1M), with a per-bucket page total,
--   so the report can show market share by tier (e.g. GA4 vs Adobe vs Matomo/Plausible).
--
-- VERIFIED SCHEMA ASSUMPTIONS (checked against har.fyi on 2026-07-18):
--   - `httparchive.crawl.pages`: partitioned by `date` (required in WHERE), clustered
--     (clustering includes `rank`, so the `rank <=` filter also cuts scan). Columns
--     used: `date`, `client` STRING, `page` STRING, `is_root_page` BOOL,
--     `rank` INTEGER — site popularity from CrUX, stored as coarse magnitude buckets
--     (1000, 10000, 100000, ...), so GROUP BY rank yields the tier directly.
--     Source: https://har.fyi/reference/tables/pages/ and
--     https://har.fyi/guides/minimizing-costs/
--   - `technologies` ARRAY<STRUCT>: `technology` STRING + repeated `categories`
--     STRING, both via UNNEST. Source: https://har.fyi/reference/structs/technology/
--   - 'Analytics' is the Wappalyzer category name — confirm the exact spelling with
--     00_category_discovery.sql before running.
--
-- COST GUARD (do not skip)
--   - Single date partition + `rank <= 1000000` (a clustering column) + only the
--     columns above. Dry-run first, abort over budget:
--       bq query --nouse_legacy_sql --dry_run < data/queries/02_top_analytics_by_rank.sql
--   - Prototype on `httparchive.sample_data.pages_10k` first (note: cost estimates
--     with rank filters can be inaccurate on the full table — the dry run is the
--     authority; see https://har.fyi/guides/minimizing-costs/).
--   - QUALIFY/LIMIT trim output rows only, never bytes scanned.
--
-- RUN (see data/README.md)
--   bq query --nouse_legacy_sql --format=csv --max_rows=100000 \
--     < data/queries/02_top_analytics_by_rank.sql \
--     > data/export/input/top_analytics_by_rank.csv

WITH
  pages AS (
    SELECT page, rank, technologies
    FROM `httparchive.crawl.pages`
    -- Prototype first: FROM `httparchive.sample_data.pages_10k`
    WHERE date = '2026-06-01'  -- EDIT: latest published crawl (partition filter, required)
      AND client = 'mobile'
      AND is_root_page
      AND rank <= 1000000      -- top-1M tier and up; rank is a clustering column
  ),

  bucket_totals AS (
    SELECT rank AS rank_bucket, COUNT(*) AS pages_total_in_bucket
    FROM pages
    GROUP BY rank
  ),

  tech_hits AS (
    SELECT
      p.rank AS rank_bucket,
      t.technology,
      COUNT(DISTINCT p.page) AS pages_with_technology
    FROM pages AS p,
      UNNEST(p.technologies) AS t,
      UNNEST(t.categories) AS category
    WHERE category = 'Analytics'  -- validate spelling via 00_category_discovery.sql
    GROUP BY rank_bucket, t.technology
  )

SELECT
  DATE '2026-06-01' AS date,  -- keep in sync with the partition filter above
  h.rank_bucket,
  h.technology,
  h.pages_with_technology,
  b.pages_total_in_bucket,
  ROUND(SAFE_DIVIDE(h.pages_with_technology, b.pages_total_in_bucket), 6) AS share_of_bucket
FROM tech_hits AS h
JOIN bucket_totals AS b USING (rank_bucket)
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY h.rank_bucket
  ORDER BY h.pages_with_technology DESC, h.technology
) <= 25
ORDER BY h.rank_bucket, h.pages_with_technology DESC;

Feeds §3 — the headline “68.3% of pages fire a known tracker”. Tests every request URL against the vendor fingerprint library, then counts distinct pages with ≥1 match.

-- 03_tracking_request_share.sql — share of requests per page that hit a known tracking vendor
--
-- GENERATED FILE — do not edit the vendor_patterns CTE by hand.
-- Source: data/fingerprints/vendors.json (version 1, 66 vendors).
-- Regenerate with:  cd data && uv run python export/gen_tracking_query.py
--
-- PURPOSE
--   For one crawl month, classify every request URL against the webtracking.org vendor
--   fingerprint library and report, per vendor category (plus an '__any__' rollup):
--   how many pages issue at least one matching request, and the distribution of the
--   per-page share of requests that are tracking requests.
--
-- VERIFIED SCHEMA ASSUMPTIONS (checked against har.fyi on 2026-07-18):
--   - `httparchive.crawl.requests` is partitioned by `date` (DATE, required in WHERE)
--     and clustered; `client` STRING ('desktop'|'mobile'), `is_root_page` BOOL,
--     `page` STRING (URL of the page under test), `url` STRING (URL of the request).
--     Source: https://har.fyi/reference/tables/requests/
--   - Sample tables for cheap prototyping: `httparchive.sample_data.requests_10k`.
--     Source: https://har.fyi/guides/minimizing-costs/
--
-- RE2 NOTES (BigQuery regex engine)
--   - Patterns below are pre-validated RE2-safe: no lookaround, no backreferences
--     (the generator rejects them). Case-insensitivity is applied with one '(?i)'
--     prefix on each combined per-category regex, matching how the JS/Python
--     consumers compile these same patterns with /i and re.IGNORECASE.
--   - Patterns are embedded as raw strings (r'...') so backslashes are literal.
--
-- COST GUARD (do not skip)
--   - ALWAYS dry-run first and abort if the estimate exceeds your budget:
--       bq query --nouse_legacy_sql --dry_run < data/queries/03_tracking_request_share.sql
--     Only `page` + `url` (plus the filter columns `date`, `client`, `is_root_page`)
--     are read, but for a full month/client that is still a large scan (the requests
--     table has billions of rows per crawl). Prototype by swapping
--     the table for `httparchive.sample_data.requests_10k` (keep the same WHERE),
--     or uncomment the TABLESAMPLE line.
--   - LIMIT does NOT reduce bytes scanned (applied after the scan) — per
--     https://har.fyi/guides/minimizing-costs/
--   - The CROSS JOIN below multiplies regex evaluations (rows x categories), which
--     costs CPU/slot time, not bytes — still, validate on the sample table first.
--
-- RUN (see data/README.md for the full pipeline)
--   bq query --nouse_legacy_sql --format=csv --max_rows=100000 \
--     < data/queries/03_tracking_request_share.sql \
--     > data/export/input/tracking_request_share.csv

WITH
  vendor_patterns AS (
    SELECT * FROM UNNEST([
      -- [ 66 vendor fingerprints redacted for publication ]
      -- One STRUCT per vendor: (vendor key, category, URL match pattern).
      -- The roster of vendors and categories is published at /reference/fingerprints.
      -- Everything downstream of this CTE is verbatim.
    ])
  ),

  -- One combined alternation per category (plus '__any__' across all vendors) so each
  -- request URL is tested against ~10 regexes instead of 66.
  category_regexes AS (
    SELECT category, '(?i)' || STRING_AGG('(?:' || pattern || ')', '|') AS regex
    FROM vendor_patterns
    GROUP BY category
    UNION ALL
    SELECT '__any__', '(?i)' || STRING_AGG('(?:' || pattern || ')', '|')
    FROM vendor_patterns
  ),

  requests AS (
    SELECT page, url
    FROM `httparchive.crawl.requests`
    -- Prototype first: FROM `httparchive.sample_data.requests_10k`
    -- TABLESAMPLE SYSTEM (0.01 PERCENT)  -- optional cheap full-table prototype
    WHERE date = '2026-06-01'  -- EDIT: latest published crawl (partition filter, required)
      AND client = 'mobile'
      AND is_root_page
  ),

  per_page AS (
    SELECT
      r.page,
      c.category,
      COUNT(*) AS requests_total,
      COUNTIF(REGEXP_CONTAINS(r.url, c.regex)) AS requests_matched
    FROM requests AS r
    CROSS JOIN category_regexes AS c
    GROUP BY r.page, c.category
  )

SELECT
  DATE '2026-06-01' AS date,  -- keep in sync with the partition filter above
  category,
  COUNT(*) AS pages_total,
  COUNTIF(requests_matched > 0) AS pages_with_match,
  ROUND(SAFE_DIVIDE(COUNTIF(requests_matched > 0), COUNT(*)), 6) AS share_of_pages,
  ROUND(AVG(requests_matched), 4) AS avg_matched_requests_per_page,
  ROUND(AVG(SAFE_DIVIDE(requests_matched, requests_total)), 6) AS avg_share_of_requests,
  ROUND(APPROX_QUANTILES(SAFE_DIVIDE(requests_matched, requests_total), 100)[OFFSET(50)], 6)
    AS p50_share_of_requests,
  ROUND(APPROX_QUANTILES(SAFE_DIVIDE(requests_matched, requests_total), 100)[OFFSET(90)], 6)
    AS p90_share_of_requests
FROM per_page
GROUP BY category
ORDER BY category;

Feeds §4’s domain table. Per third-party registrable domain: pages seen on, total requests, and how many responses carry Set-Cookie.

-- 04_third_party_domains.sql — third-party domain prevalence + Set-Cookie share
--
-- PURPOSE
--   For one crawl month, find the registrable third-party domains reached from the
--   most root pages, and for each: how many requests to it carry a Set-Cookie response
--   header (a direct signal of third-party cookie setting). Feeds the "who is on every
--   page, and who is still setting cookies" section of the report.
--
-- VERIFIED SCHEMA ASSUMPTIONS (checked against har.fyi on 2026-07-18):
--   - `httparchive.crawl.requests`: partitioned by `date` (required in WHERE),
--     clustered; columns used: `date`, `client` STRING, `is_root_page` BOOL,
--     `page` STRING (URL of the page under test), `url` STRING (request URL),
--     `response_headers` ARRAY<STRUCT<name STRING, value STRING>>.
--     Sources: https://har.fyi/reference/tables/requests/ and
--     https://har.fyi/reference/structs/header/
--   - Third-party = registrable domain of the request differs from that of the page,
--     via BigQuery's NET.REG_DOMAIN (public-suffix-list based).
--   - Header names are matched case-insensitively (LOWER(h.name) = 'set-cookie');
--     header name casing in the crawl is not guaranteed.
--
-- COST GUARD (do not skip)
--   - This reads `page`, `url`, `response_headers` AND the filter columns (`date`,
--     `client`, `is_root_page`) for one month/client of the
--     requests table — the headers column is wide, so expect the priciest scan in
--     this pipeline. ALWAYS dry-run and abort if over budget:
--       bq query --nouse_legacy_sql --dry_run < data/queries/04_third_party_domains.sql
--   - Prototype on `httparchive.sample_data.requests_10k`, or uncomment TABLESAMPLE
--     for a cheap full-table shape check (https://har.fyi/guides/minimizing-costs/).
--   - The final LIMIT trims output rows only — it does NOT reduce bytes scanned.
--
-- RUN (see data/README.md)
--   bq query --nouse_legacy_sql --format=csv --max_rows=100000 \
--     < data/queries/04_third_party_domains.sql \
--     > data/export/input/third_party_domains.csv

WITH
  requests AS (
    SELECT
      page,
      NET.REG_DOMAIN(url) AS request_domain,
      NET.REG_DOMAIN(page) AS page_domain,
      EXISTS(
        SELECT 1 FROM UNNEST(response_headers) AS h
        WHERE LOWER(h.name) = 'set-cookie'
      ) AS sets_cookie
    FROM `httparchive.crawl.requests`
    -- Prototype first: FROM `httparchive.sample_data.requests_10k`
    -- TABLESAMPLE SYSTEM (0.01 PERCENT)  -- optional cheap full-table prototype
    WHERE date = '2026-06-01'  -- EDIT: latest published crawl (partition filter, required)
      AND client = 'mobile'
      AND is_root_page
  ),

  page_totals AS (
    SELECT COUNT(DISTINCT page) AS pages_total
    FROM requests
  ),

  third_party AS (
    SELECT request_domain, page, sets_cookie
    FROM requests
    WHERE request_domain IS NOT NULL
      AND page_domain IS NOT NULL
      AND request_domain != page_domain
  )

SELECT
  DATE '2026-06-01' AS date,  -- keep in sync with the partition filter above
  tp.request_domain,
  COUNT(DISTINCT tp.page) AS pages_seen_on,
  pt.pages_total,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT tp.page), pt.pages_total), 6) AS share_of_pages,
  COUNT(*) AS requests_total,
  COUNTIF(tp.sets_cookie) AS requests_setting_cookies
FROM third_party AS tp
CROSS JOIN page_totals AS pt
GROUP BY tp.request_domain, pt.pages_total
ORDER BY pages_seen_on DESC
LIMIT 200;

Materializes one working table so §4–8 never re-scan the full crawl. Reads response_headers.name only — never .value, which alone is ~800GB.

-- Anatomy of Tracking — page×domain extract (docs/15).
-- Materializes ONE scan of httparchive.crawl.requests into the project's own
-- `anatomy` dataset; the aggregations (11/13) then run for pennies.
--
-- COST DESIGN (learned the expensive way): BigQuery bills nested STRUCT fields
-- as separate columns. This query touches response_headers.NAME only (an EXISTS
-- probe, same footprint as SoWT query 04 ≈ 660 GB). It must NEVER read
-- response_headers.value — the header-VALUES sub-column alone is ~800 GB; the
-- cookie-name census that needs it lives in 12_cookie_census.sql on a 10%
-- TABLESAMPLE instead. Dry-run cap in run_extract.sh: 750 GB.

CREATE SCHEMA IF NOT EXISTS anatomy OPTIONS (location = 'US');

CREATE OR REPLACE TABLE anatomy.tp_requests_202606 AS
SELECT
  page,
  NET.REG_DOMAIN(url) AS reg,
  COUNT(*) AS n_requests,
  COUNTIF(EXISTS(
    SELECT 1 FROM UNNEST(response_headers) AS h
    WHERE LOWER(h.name) = 'set-cookie'
  )) AS n_setcookie
FROM `httparchive.crawl.requests`
WHERE date = '2026-06-01'  -- keep in sync with sowt.json
  AND client = 'mobile'
  AND is_root_page
  AND NET.REG_DOMAIN(url) IS NOT NULL
  AND NET.REG_DOMAIN(page) IS NOT NULL
  AND NET.REG_DOMAIN(url) != NET.REG_DOMAIN(page)
GROUP BY page, reg;

Feeds §4 and §8. Groups domains by parent entity, deduplicating multi-domain entities at page level.

-- Entity reach — % of pages where ANY domain of a parent entity appears.
-- Runs against the anatomy extract (cheap; ~20 GB table, not the crawl).
-- The inline map mirrors data/tracker-analysis/entities.json (multi-domain
-- entities only — single-domain entities take reach straight from the existing
-- third_party_domains.csv, no page-level dedup needed).

WITH entity_map AS (
  SELECT * FROM UNNEST([
    STRUCT('googleapis.com' AS reg, 'Google (Alphabet)' AS entity), ('gstatic.com','Google (Alphabet)'), ('googletagmanager.com','Google (Alphabet)'), ('google.com','Google (Alphabet)'), ('google-analytics.com','Google (Alphabet)'), ('doubleclick.net','Google (Alphabet)'), ('googlesyndication.com','Google (Alphabet)'), ('youtube.com','Google (Alphabet)'), ('googleadservices.com','Google (Alphabet)'), ('ytimg.com','Google (Alphabet)'), ('adtrafficquality.google','Google (Alphabet)'), ('googletagservices.com','Google (Alphabet)'), ('googleusercontent.com','Google (Alphabet)'), ('merchant-center-analytics.goog','Google (Alphabet)'), ('googlevideo.com','Google (Alphabet)'), ('ggpht.com','Google (Alphabet)'), ('firebaseapp.com','Google (Alphabet)'), ('blogger.com','Google (Alphabet)'), ('ampproject.org','Google (Alphabet)'),
    ('facebook.net','Meta'), ('facebook.com','Meta'), ('fbcdn.net','Meta'),
    ('bing.com','Microsoft'), ('clarity.ms','Microsoft'), ('linkedin.com','Microsoft'), ('licdn.com','Microsoft'), ('adnxs.com','Microsoft'),
    ('amazonaws.com','Amazon'), ('cloudfront.net','Amazon'), ('amazon-adsystem.com','Amazon'),
    ('cloudflare.com','Cloudflare'), ('cloudflareinsights.com','Cloudflare'),
    ('wp.com','Automattic'), ('w.org','Automattic'), ('gravatar.com','Automattic'),
    ('shopify.com','Shopify'), ('shopifysvc.com','Shopify'), ('shop.app','Shopify'), ('shopifycdn.com','Shopify'),
    ('wixstatic.com','Wix'), ('parastorage.com','Wix'), ('wix.com','Wix'), ('wixapps.net','Wix'),
    ('squarespace.com','Squarespace'), ('squarespace-cdn.com','Squarespace'), ('sqspcdn.com','Squarespace'),
    ('yandex.ru','Yandex'), ('yandex.com','Yandex'), ('yastatic.net','Yandex'), ('yadro.ru','Yandex'),
    ('typekit.net','Adobe'), ('demdex.net','Adobe'), ('everesttech.net','Adobe'),
    ('criteo.com','Criteo'), ('criteo.net','Criteo'),
    ('tiktok.com','TikTok (ByteDance)'), ('tiktokw.us','TikTok (ByteDance)'),
    ('hubspot.com','HubSpot'), ('hs-banner.com','HubSpot'), ('hs-analytics.net','HubSpot'), ('hs-scripts.com','HubSpot'), ('hsforms.com','HubSpot'), ('hscollectedforms.net','HubSpot'),
    ('rlcdn.com','LiveRamp'), ('pippio.com','LiveRamp'),
    ('id5-sync.com','ID5'), ('eu-1-id5-sync.com','ID5'),
    ('stripe.com','Stripe'), ('stripe.network','Stripe'),
    ('secureserver.net','GoDaddy'), ('wsimg.com','GoDaddy'),
    ('sentry-cdn.com','Sentry'), ('sentry.io','Sentry'),
    ('newrelic.com','New Relic'), ('nr-data.net','New Relic'),
    ('hotjar.com','Contentsquare (Hotjar)'), ('hotjar.io','Contentsquare (Hotjar)'),
    ('cdn-cookieyes.com','CookieYes'), ('cookieyes.com','CookieYes'),
    ('onetrust.com','OneTrust'), ('cookielaw.org','OneTrust'),
    ('fwmrm.net','Comcast (FreeWheel)'), ('bidr.io','Comcast (FreeWheel)')
  ])
)

SELECT
  m.entity,
  COUNT(DISTINCT r.page) AS pages_reached,
  SUM(r.n_requests) AS requests_total,
  SUM(r.n_setcookie) AS requests_setting_cookies
FROM anatomy.tp_requests_202606 AS r
JOIN entity_map AS m USING (reg)
GROUP BY m.entity
ORDER BY pages_reached DESC;

Feeds §5. Aggregates Set-Cookie response headers by cookie name on a 10% block sample, with declared-lifetime buckets.

-- Cookie census — most-set third-party cookie names, from a 10% BLOCK SAMPLE.
--
-- This is the ONLY query that reads response_headers.VALUE (the ~800 GB
-- sub-column — see 10_extract.sql's cost note). TABLESAMPLE SYSTEM (10 PERCENT)
-- cuts the read to ~10% (~145 GB) while still covering tens of millions of
-- Set-Cookie responses — ample for a name-level census. ALL COUNTS IN THE
-- OUTPUT ARE SAMPLED: the report multiplies by 10 and labels them estimates
-- (build.py sets cookieCensusSample=0.10). Shares/ratios are sample-safe as-is.
-- Dry-run cap in run_extract.sh: 250 GB.

WITH sc AS (
  SELECT
    r.page,
    NET.REG_DOMAIN(r.url) AS reg,
    h.value AS set_cookie
  FROM `httparchive.crawl.requests` AS r
  TABLESAMPLE SYSTEM (10 PERCENT),
  UNNEST(r.response_headers) AS h
  WHERE r.date = '2026-06-01'  -- keep in sync with sowt.json
    AND r.client = 'mobile'
    AND r.is_root_page
    AND LOWER(h.name) = 'set-cookie'
    AND NET.REG_DOMAIN(r.url) IS NOT NULL
    AND NET.REG_DOMAIN(r.page) IS NOT NULL
    AND NET.REG_DOMAIN(r.url) != NET.REG_DOMAIN(r.page)
),

named AS (
  SELECT
    page,
    reg,
    TRIM(SPLIT(set_cookie, '=')[SAFE_OFFSET(0)]) AS cookie_name,
    SAFE_CAST(REGEXP_EXTRACT(set_cookie, r'(?i)max-age=(-?[0-9]+)') AS INT64) AS max_age_s,
    REGEXP_CONTAINS(set_cookie, r'(?i)expires=') AS has_expires
  FROM sc
  WHERE TRIM(SPLIT(set_cookie, '=')[SAFE_OFFSET(0)]) != ''
)

SELECT
  cookie_name,
  COUNT(DISTINCT reg) AS setter_domains,
  ARRAY_AGG(reg ORDER BY reg LIMIT 1)[OFFSET(0)] AS any_setter,
  APPROX_TOP_COUNT(reg, 1)[OFFSET(0)].value AS top_setter,
  COUNT(DISTINCT page) AS pages_est,
  COUNT(*) AS times_set,
  COUNTIF(max_age_s IS NULL AND NOT has_expires) AS n_session,
  COUNTIF(max_age_s IS NOT NULL AND max_age_s <= 86400) AS n_1d,
  COUNTIF(max_age_s > 86400 AND max_age_s <= 2592000) AS n_30d,
  COUNTIF(max_age_s > 2592000 AND max_age_s <= 31536000) AS n_1y,
  COUNTIF(max_age_s > 31536000) AS n_over1y,
  COUNTIF(max_age_s IS NULL AND has_expires) AS n_expires_only
FROM named
GROUP BY cookie_name
HAVING COUNT(*) >= 2000   -- sampled threshold ≈ 20k real sets
ORDER BY times_set DESC
LIMIT 400;

Feeds §6. Distinct third-party domains and request counts per page, as quantiles and a histogram.

-- Per-page third-party burden — how many distinct third parties the median
-- page talks to, and the distribution. Runs against the anatomy extract.

WITH per_page AS (
  SELECT
    page,
    COUNT(DISTINCT reg) AS tp_domains,
    SUM(n_requests) AS tp_requests,
    SUM(n_setcookie) AS setcookie_responses
  FROM anatomy.tp_requests_202606
  GROUP BY page
)

SELECT 'histogram' AS row_type,
  CAST(LEAST(tp_domains, 40) AS STRING) AS k,
  COUNT(*) AS n, NULL AS p25, NULL AS p50, NULL AS p75, NULL AS p90, NULL AS p99
FROM per_page
GROUP BY k

UNION ALL

SELECT 'quantiles' AS row_type, 'tp_domains' AS k, COUNT(*) AS n,
  APPROX_QUANTILES(tp_domains, 100)[OFFSET(25)],
  APPROX_QUANTILES(tp_domains, 100)[OFFSET(50)],
  APPROX_QUANTILES(tp_domains, 100)[OFFSET(75)],
  APPROX_QUANTILES(tp_domains, 100)[OFFSET(90)],
  APPROX_QUANTILES(tp_domains, 100)[OFFSET(99)]
FROM per_page

UNION ALL

SELECT 'quantiles' AS row_type, 'tp_requests' AS k, COUNT(*) AS n,
  APPROX_QUANTILES(tp_requests, 100)[OFFSET(25)],
  APPROX_QUANTILES(tp_requests, 100)[OFFSET(50)],
  APPROX_QUANTILES(tp_requests, 100)[OFFSET(75)],
  APPROX_QUANTILES(tp_requests, 100)[OFFSET(90)],
  APPROX_QUANTILES(tp_requests, 100)[OFFSET(99)]
FROM per_page

UNION ALL

SELECT 'quantiles' AS row_type, 'setcookie_responses' AS k, COUNT(*) AS n,
  APPROX_QUANTILES(setcookie_responses, 100)[OFFSET(25)],
  APPROX_QUANTILES(setcookie_responses, 100)[OFFSET(50)],
  APPROX_QUANTILES(setcookie_responses, 100)[OFFSET(75)],
  APPROX_QUANTILES(setcookie_responses, 100)[OFFSET(90)],
  APPROX_QUANTILES(setcookie_responses, 100)[OFFSET(99)]
FROM per_page

ORDER BY row_type, k;

Feeds §7. Pairwise page counts for detected technologies, plus how many analytics tools run on the same page.

-- Technology co-occurrence + analytics "stacking" — one scan of crawl.pages
-- (technologies column only; same order as query 01, ~70-150 GB upper bound).
-- Outputs three row types in one result:
--   total  : full-crawl page counts per technology (incl. the small analytics
--            platforms that never make a bucket's top-15 in query 02)
--   pair   : pages running BOTH technologies (upper-triangle pairs)
--   stack  : histogram of DISTINCT analytics tools per page (double-tracking)
-- Wappalyzer names not present in the crawl simply match nothing.

BEGIN

CREATE TEMP TABLE page_techs AS
SELECT
  page,
  ARRAY_AGG(DISTINCT t.technology) AS techs,
  ARRAY_AGG(DISTINCT IF('Analytics' IN UNNEST(t.categories), t.technology, NULL) IGNORE NULLS) AS analytics_techs
FROM `httparchive.crawl.pages` AS p,
  UNNEST(p.technologies) AS t
WHERE p.date = '2026-06-01'  -- keep in sync with sowt.json
  AND p.client = 'mobile'
  AND p.is_root_page
  AND t.technology IN (
    'Google Analytics','Google Tag Manager','Tealium','Adobe Experience Platform Launch',
    'Adobe Analytics','Matomo Analytics','Matomo Tag Manager','Plausible','Fathom','Umami',
    'Piwik PRO','Snowplow Analytics','Amplitude','Mixpanel','Heap','PostHog','Segment',
    'Hotjar','Microsoft Clarity','FullStory','LogRocket','Smartlook','Mouseflow','Lucky Orange',
    'Facebook Pixel','TikTok Pixel','LinkedIn Insight Tag','Yandex.Metrika','comScore',
    'Chartbeat','Quantcast Measure','New Relic','Datadog','Cloudflare Browser Insights',
    'OneTrust','Cookiebot','Usercentrics','Didomi','iubenda','CookieYes','Osano','Termly',
    'Google Ads Conversion Tracking','Marfeel','Adjust','Simple Analytics','GoatCounter',
    'Cloudflare Web Analytics','Fathom Analytics'
  )
GROUP BY page;

SELECT 'total' AS row_type, tech AS a, CAST(NULL AS STRING) AS b, COUNT(*) AS pages
FROM page_techs, UNNEST(techs) AS tech
GROUP BY tech

UNION ALL

SELECT 'pair', a, b, COUNT(*)
FROM page_techs, UNNEST(techs) AS a, UNNEST(techs) AS b
WHERE a < b
GROUP BY a, b
HAVING COUNT(*) >= 5000

UNION ALL

SELECT 'stack', CAST(LEAST(ARRAY_LENGTH(analytics_techs), 6) AS STRING), NULL, COUNT(*)
FROM page_techs
GROUP BY 2

ORDER BY row_type, pages DESC;

END;