Querying the CrUX BigQuery Dataset

The monthly CrUX export in BigQuery is the only public dataset containing field performance for origins other than your own, which makes it the only way to answer “how do we compare” with evidence. It is also large enough to produce a memorable bill if queried carelessly. This guide sits under CrUX & Public Field Data and covers the table layout, queries that scan gigabytes instead of terabytes, reconstructing a percentile from the published histogram, and turning it all into a peer report someone will read.

Prerequisites

• A Google Cloud project with BigQuery enabled and billing attached.
• A peer set chosen by a written rule — same market, same order of magnitude of traffic — rather than by name.
• The reconciliation understanding from CrUX & Public Field Data, so the numbers are read correctly.

How to query it

Step 1 — Understand the table layout

The dataset publishes one table per month, named by year and month, plus convenience views. Each row is an origin (or a URL, in the materialized tables) crossed with a form factor and an effective connection type, carrying a histogram per metric.

The materialized views are the ones to reach for first. They are far smaller, they already contain p75 values, and most benchmarking questions never need the raw histograms.

Step 2 — Query the summary tables first

-- Cheap: the materialized device summary already has p75 per origin/month.
SELECT
  origin,
  yyyymm,
  device,
  ROUND(p75_lcp) AS p75_lcp_ms,
  ROUND(p75_inp) AS p75_inp_ms,
  ROUND(p75_cls, 3) AS p75_cls
FROM `chrome-ux-report.materialized.device_summary`
WHERE origin IN UNNEST(@peers)
  AND device = 'phone'
  AND yyyymm BETWEEN 202601 AND 202607
ORDER BY origin, yyyymm;

Parameterising the origin list keeps the query reusable and keeps the peer set in one place — a file, not a query someone edits in the console.

Step 3 — Control the cost before you run anything

BigQuery bills on bytes scanned, and the raw monthly tables are large. Four habits keep the bill proportionate.

Query one month at a time. Wildcard queries across chrome-ux-report.all.* scan every month ever published.

Select only the columns you need. BigQuery is columnar, so SELECT * on a table with nested histograms for five metrics is dramatically more expensive than selecting one.

Filter on origin early. Origin is the highest-selectivity predicate available.

Dry-run everything. The estimate is free and takes a second.

# Always dry-run a new query. The byte estimate is the bill.
bq query --use_legacy_sql=false --dry_run \
  'SELECT origin, largest_contentful_paint.histogram.bin
   FROM `chrome-ux-report.all.202607`
   WHERE origin = "https://example.com" AND form_factor.name = "phone"'

Step 4 — Reconstruct a percentile when you need the raw table

The raw tables carry histograms rather than percentiles, and the histogram is more informative — it tells you the share in each rating band, which is what actually moves when you ship a fix.

-- p75 from the published bins, plus the rating shares that explain it.
WITH bins AS (
  SELECT
    origin,
    bin.start AS bin_start,
    bin.end   AS bin_end,
    bin.density AS density,
    SUM(bin.density) OVER (PARTITION BY origin ORDER BY bin.start) AS cumulative
  FROM `chrome-ux-report.all.202607`,
    UNNEST(largest_contentful_paint.histogram.bin) AS bin
  WHERE origin IN UNNEST(@peers)
    AND form_factor.name = 'phone'
)
SELECT
  origin,
  MIN(IF(cumulative >= 0.75, bin_start, NULL))            AS p75_bin_start_ms,
  SUM(IF(bin_end <= 2500, density, 0))                    AS good_share,
  SUM(IF(bin_start >= 4000, density, 0))                  AS poor_share
FROM bins
GROUP BY origin
ORDER BY p75_bin_start_ms;

The good_share column is the one to lead a report with: an origin at 74% Good is close to passing, and one at 55% is not, even when both report the same p75 bin.

Step 5 — Build the peer report as a scheduled query

Run it monthly, write to a small table of your own, and chart from that. Re-querying the public dataset every time someone opens the dashboard is how the cost gets away from you.

-- Scheduled monthly. Writes ~20 rows; charts read only this table.
CREATE OR REPLACE TABLE `analytics.crux_peers`
PARTITION BY DATE(month_start) AS
SELECT
  PARSE_DATE('%Y%m', CAST(yyyymm AS STRING)) AS month_start,
  origin,
  device,
  ROUND(p75_lcp) AS p75_lcp_ms,
  ROUND(p75_inp) AS p75_inp_ms,
  ROUND(p75_cls, 3) AS p75_cls,
  -- Rank within the peer set: the number that survives a hostile read.
  RANK() OVER (PARTITION BY yyyymm, device ORDER BY p75_lcp) AS lcp_rank,
  COUNT(*) OVER (PARTITION BY yyyymm, device)                AS peer_count
FROM `chrome-ux-report.materialized.device_summary`
WHERE origin IN UNNEST(@peers) AND device IN ('phone', 'desktop');

Reading the result honestly

Three cautions that keep a peer report from being dismissed.

Absence is information, not an error. An origin missing from the dataset did not clear the traffic threshold for that month and form factor. Note it; do not silently drop it, and do not treat it as a zero.

Traffic mix differs between origins. A competitor whose audience is 80% desktop will look faster on any blended number. Compare within a form factor, always.

Rank is more stable than distance. “We are fourth of six on phone LCP, at the peer median” survives next month’s data. “We are 880 ms behind Competitor A” changes when Competitor A ships something, and invites a debate about whether they are comparable at all.

The most useful chart to maintain is not the current month at all — it is your rank over twelve months. It answers the only question the report exists to answer: are we improving relative to the market, or just relative to ourselves?

Verifying it works

1. Your own origin’s number matches the API for the same month and form factor. If it does not, you are reading the wrong table or the wrong grain.
2. The peer set is stable month to month, and the selection rule is stored next to the query.
3. Byte estimates are checked before each new query shape runs, and the scheduled query’s cost is known and small.
4. Missing origins are reported explicitly rather than dropping out of the chart silently.
5. Rating shares are carried alongside percentiles, because the share moves first when a fix lands.

Presenting the report to people who did not build it

A peer benchmark lands differently depending on how it is framed, and the framing is worth as much thought as the query.

Lead with rank and direction, not the absolute number. “We moved from fifth to third of six on phone LCP over two quarters” is a story with a trajectory. “Our p75 is 2.86 s” is a fact with no context, and the first question will be whether that is good.

Show the Good share next to the percentile. It is the number that maps onto the pass or fail assessment, and it moves earlier than the percentile when a fix lands — which matters when you are reporting progress mid-programme.

Name the peer set and its rule on the chart. Someone will ask why a particular competitor is or is not included, and the answer needs to predate the results.

Do not over-claim causation. A competitor whose numbers improved may have shipped a rebuild, changed markets, or simply had their traffic mix shift. The dataset shows what happened, not why.

Used this way the report supports a budget conversation: a market position, a trend, and a gap expressed in the same units as the objectives in Performance Budgets & SLOs. Used badly, it becomes a monthly ritual of explaining why one number moved.

Edge cases and gotchas

• The export is monthly, not rolling. It is not the same freshness as the API, and a fix shipped mid-month shows up in the following month’s table.
• Origins include the scheme and no trailing slash. https://example.com matches; example.com and https://www.example.com are different rows or no row at all.
• www and apex are separate origins. Check which one carries your traffic before benchmarking on the wrong one.
• Country tables have their own thresholds. An origin present in all may be absent from a specific country table.
• Histogram bins are fixed but metric-specific. Do not assume the LCP bin boundaries apply to CLS.
• A wildcard query across every month is the single most expensive mistake available here, and it is one autocomplete away.

FAQ

How much does a peer query cost?

Against the materialized summaries, fractions of a gigabyte — effectively free. Against the raw monthly table with SELECT *, hundreds of gigabytes per query. Always dry-run first.

Can I get URL-level data for a competitor?

Only for URLs that clear the traffic threshold, and only in the tables that carry URL grain. For most sites, most URLs are absent.

How do I choose a peer set defensibly?

By a written rule — market, traffic band, business model — applied before you see the results, and stored alongside the query. A set chosen after seeing the numbers is not a benchmark.

Should this replace my own RUM?

No. It has no route dimension, no attribution and monthly granularity. It answers how you compare; your own data answers what to fix.

Related

• CrUX & Public Field Data — what the dataset measures and how it differs from your own.
• Using the CrUX API for Origin and URL Data — the daily-updated, single-origin counterpart.
• Designing a BigQuery Schema for RUM Events — the same cost discipline applied to your own data.