12 min read

Computing Rolling p75 Baselines in BigQuery

A static alert threshold on Largest Contentful Paint eventually stops meaning anything — a site that ships a heavier hero image in one release and a lighter one in the next needs a baseline that moves with it. This page is part of Percentile Normalization Across Metric Distributions and walks through a single production BigQuery pattern: a rolling 28-day p75 computed with a PERCENTILE_CONT window function, upserted into a baseline table with a MERGE statement that is safe to rerun for the same day without creating duplicate rows.

The pattern assumes you already land raw Core Web Vitals telemetry — LCP, INP, and CLS — per page and per device category in a BigQuery table, the same telemetry that feeds normalizing performance data across device types. What this page adds is the layer that turns that raw stream into a stable, queryable reference distribution that alerting and dashboards can read without recomputing percentiles on every request.

Rolling p75 Baseline Pipeline Diagram showing raw CrUX telemetry rows filtered into a trailing 28-day window, then passed through PERCENTILE_CONT partitioned by page and device, then upserted with a MERGE statement into a baseline table keyed on snapshot date. Raw telemetry phone + desktop rows, per page 28-day window trailing, ending on snapshot_date (UTC) PERCENTILE_CONT p75, partitioned by page_url + device MERGE upsert keyed on page, device, snapshot_date Baseline table one row per page, device, and day

Implementation

The query below does three things in one script: it builds a trailing window over raw telemetry, computes the p75 with a window function scoped per page and device, and upserts the result into a durable baseline table. Save it as a single .sql file so it can be invoked identically from a scheduled query, a bq CLI wrapper, or a CI job.

-- File: /opt/audit/bq_baselines/rolling_p75.sql
-- Computes a 28-day rolling p75 baseline per (page_url, device_category)
-- and upserts it into metric_p75_baselines, keyed by snapshot_date so the
-- job can be safely rerun for the same day.

DECLARE window_days INT64 DEFAULT 28;
DECLARE snapshot_date DATE DEFAULT CURRENT_DATE("UTC");  -- pin the timezone explicitly

-- 1. Trailing window: constrain raw telemetry to the days that feed this
--    snapshot, and never let device categories mix in the same row.
WITH windowed_source AS (
  SELECT
    page_url,
    device_category,
    metric_date,
    lcp_ms,
    inp_ms,
    cls
  FROM `proj.dataset.crux_page_metrics`
  WHERE metric_date BETWEEN DATE_SUB(snapshot_date, INTERVAL window_days DAY)
                        AND snapshot_date
    AND device_category IN ('phone', 'desktop')
),

-- 2. PERCENTILE_CONT is an analytic (window) function in BigQuery — it must
--    be called with OVER(...), never with GROUP BY. Partitioning by both
--    page_url and device_category keeps mobile and desktop distributions
--    separate; DISTINCT collapses the per-row duplicates the window
--    function produces back to one row per partition.
percentiles AS (
  SELECT DISTINCT
    page_url,
    device_category,
    snapshot_date AS snapshot_date,
    window_days   AS window_days,
    PERCENTILE_CONT(lcp_ms, 0.75) OVER (PARTITION BY page_url, device_category) AS lcp_p75,
    PERCENTILE_CONT(inp_ms, 0.75) OVER (PARTITION BY page_url, device_category) AS inp_p75,
    PERCENTILE_CONT(cls,    0.75) OVER (PARTITION BY page_url, device_category) AS cls_p75
  FROM windowed_source
)

-- 3. Idempotent upsert: the natural key is (page_url, device_category,
--    snapshot_date). Rerunning for the same snapshot_date UPDATEs the
--    existing row in place instead of appending a duplicate — this MERGE
--    is the idempotency guard the whole pattern depends on.
MERGE `proj.dataset.metric_p75_baselines` AS target
USING percentiles AS source
ON  target.page_url        = source.page_url
AND target.device_category = source.device_category
AND target.snapshot_date   = source.snapshot_date
WHEN MATCHED THEN
  UPDATE SET
    target.window_days  = source.window_days,
    target.lcp_p75       = source.lcp_p75,
    target.inp_p75        = source.inp_p75,
    target.cls_p75        = source.cls_p75,
    target.computed_at   = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (page_url, device_category, snapshot_date, window_days, lcp_p75, inp_p75, cls_p75, computed_at)
  VALUES (source.page_url, source.device_category, source.snapshot_date, source.window_days,
          source.lcp_p75, source.inp_p75, source.cls_p75, CURRENT_TIMESTAMP());

A few details in that script matter more than they look:

  • DECLARE snapshot_date DATE DEFAULT CURRENT_DATE("UTC") pins the timezone at declaration time. If you instead write CURRENT_DATE() with no argument, BigQuery uses the query's default timezone, which for most projects is UTC but is a per-project setting — if someone changes it, every downstream metric_date BETWEEN filter silently shifts by a day.
  • The windowed_source CTE filters rows before any percentile math runs, so the window function only ever scans the 28 days it needs rather than the full history table.
  • PERCENTILE_CONT in BigQuery is an aggregate analytic function, which is why it appears with OVER (PARTITION BY ...) rather than in a GROUP BY clause — a common first mistake is writing GROUP BY page_url, device_category and getting a "SELECT list expression references column which is neither grouped nor aggregated" error.
  • The MERGE statement's ON clause is the entire idempotency contract. If you drop snapshot_date from that clause, a rerun on the same day still matches on (page_url, device_category) alone and overwrites yesterday's row instead of creating today's — which silently destroys history instead of duplicating it.

The bq CLI wrapper

Wrap the script so it can run from cron, a CI job, or a workflow trigger without pasting SQL into a shell one-liner.

#!/usr/bin/env bash
set -euo pipefail

# /opt/audit/bq_baselines/run_baseline.sh
PROJECT_ID="proj"
SQL_FILE="/opt/audit/bq_baselines/rolling_p75.sql"

bq query \
  --project_id="${PROJECT_ID}" \
  --use_legacy_sql=false \
  --location=US \
  < "${SQL_FILE}"

echo "PASS: rolling p75 MERGE completed for $(date -u +%F)"

Running this daily after the raw telemetry export lands keeps metric_p75_baselines one day behind live traffic at most, which is the same freshness window used by aggregating scores across URL segments when it rolls per-page scores up to a section level.

Verification

Run these three checks after every deploy of the script, and again after any change to the MERGE key.

set -euo pipefail

# 1. exactly one row per (page_url, device_category) for today's snapshot
bq query --use_legacy_sql=false '
  SELECT page_url, device_category, COUNT(*) AS row_count
  FROM `proj.dataset.metric_p75_baselines`
  WHERE snapshot_date = CURRENT_DATE("UTC")
  GROUP BY page_url, device_category
  HAVING row_count > 1
'
# expected: zero rows returned

# 2. rerun idempotency — capture the count, rerun, compare
BEFORE=$(bq query --use_legacy_sql=false --format=csv \
  'SELECT COUNT(*) FROM `proj.dataset.metric_p75_baselines` WHERE snapshot_date = CURRENT_DATE("UTC")' | tail -1)
/opt/audit/bq_baselines/run_baseline.sh
AFTER=$(bq query --use_legacy_sql=false --format=csv \
  'SELECT COUNT(*) FROM `proj.dataset.metric_p75_baselines` WHERE snapshot_date = CURRENT_DATE("UTC")' | tail -1)
[ "$BEFORE" -eq "$AFTER" ] && echo "PASS: rerun did not duplicate rows ($AFTER)" \
  || echo "FAIL: before=$BEFORE after=$AFTER"

# 3. sanity range — p75 values must be positive and finite
bq query --use_legacy_sql=false '
  SELECT COUNTIF(lcp_p75 <= 0 OR inp_p75 <= 0 OR cls_p75 < 0) AS bad_rows
  FROM `proj.dataset.metric_p75_baselines`
  WHERE snapshot_date = CURRENT_DATE("UTC")
'
# expected: bad_rows = 0

If check 1 fails, the MERGE key is not matching the way you expect — usually a device_category value that does not exactly match 'phone' or 'desktop' (a trailing space or a third value like 'tablet' slipping through the IN filter upstream). If check 2 fails, something outside this script is inserting rows directly instead of going through the MERGE, which defeats the idempotency guard entirely.

Failure Modes

Device categories get pooled into one distribution

If the PARTITION BY clause only lists page_url and drops device_category, phone and desktop rows blend into a single p75. Because phone traffic is usually a larger share of samples and has structurally worse LCP, the pooled baseline ends up too lenient for phone regressions and too strict for desktop ones — alerts miss real mobile regressions and fire on healthy desktop pages.

# diagnostic: compare pooled vs partitioned p75 for a sample page
bq query --use_legacy_sql=false '
  SELECT device_category, PERCENTILE_CONT(lcp_ms, 0.75) OVER (PARTITION BY device_category) AS lcp_p75
  FROM `proj.dataset.crux_page_metrics`
  WHERE page_url = "/checkout/" AND metric_date = CURRENT_DATE("UTC")
'
# fix: confirm PARTITION BY page_url, device_category in rolling_p75.sql

Timezone drift in the DATE() boundary

CURRENT_DATE() without an explicit timezone argument uses the project's default query timezone. If that default is changed — or if the script is later run from a different project with a different default — the 28-day window silently shifts by a day relative to when the upstream telemetry pipeline stamps metric_date. The symptom is a baseline that is consistently one day stale without any error being raised.

# diagnostic: check the project's default query timezone
bq show --format=prettyjson proj:dataset | grep -i defaultTimeZone

# fix: always pin the timezone in the DECLARE statement
# DECLARE snapshot_date DATE DEFAULT CURRENT_DATE("UTC");

Duplicate snapshot rows after a retried job

If the job is retried by an orchestrator (Airflow, GitHub Actions, a cron wrapper) and snapshot_date is recomputed at query time on each retry rather than fixed once at job start, a retry that crosses midnight UTC produces two different snapshot_date values for what was meant to be a single logical run — resulting in two adjacent baseline rows instead of one corrected one.

# diagnostic: find pages with baselines on two consecutive days that
# should have been a single retried run
bq query --use_legacy_sql=false '
  SELECT page_url, device_category, ARRAY_AGG(DISTINCT snapshot_date ORDER BY snapshot_date) AS dates
  FROM `proj.dataset.metric_p75_baselines`
  WHERE snapshot_date >= DATE_SUB(CURRENT_DATE("UTC"), INTERVAL 2 DAY)
  GROUP BY page_url, device_category
  HAVING ARRAY_LENGTH(dates) > 1
'
# fix: pass snapshot_date as a frozen CLI parameter at job start, e.g.
# bq query --parameter='snapshot_date:DATE:2026-07-05' < rolling_p75.sql
# instead of letting the script recompute CURRENT_DATE on each retry

FAQ

Why use PERCENTILE_CONT instead of APPROX_QUANTILES?

APPROX_QUANTILES is cheaper on very large tables but introduces approximation error near the p75 boundary, which matters when a baseline sits close to a Core Web Vitals threshold. Because this job runs once per day per page and device rather than per request, the exact computation from PERCENTILE_CONT is affordable and removes a source of alerting flakiness that shows up as baselines that jitter slightly between otherwise identical runs.

Should phone and desktop rows share one p75 baseline?

No. Pooling device categories into a single PARTITION BY page_url distribution blends two different latency populations and produces a baseline that is too lenient for phone and too strict for desktop. Always partition by page_url and device_category together, matching the segmentation used in normalizing performance data across device types.

What happens if the job runs twice for the same day?

Nothing changes on disk beyond an updated computed_at timestamp. The MERGE statement matches on page_url, device_category, and snapshot_date, so a second run for the same snapshot_date updates the existing row instead of inserting a duplicate. This is the idempotency guard the whole pattern depends on, and it is what the rerun check in the verification section is testing for.

How do I backfill baselines for a date range?

Loop the same script once per historical date, passing each date as the snapshot_date parameter instead of letting it default to CURRENT_DATE. Because the MERGE key includes snapshot_date, backfilling out of order is safe — each date lands in its own row and does not overwrite adjacent days, unlike a key that only tracked page_url and device_category.