Percentile Normalization Across Metric Distributions #

Problem Framing #

Core Web Vitals and layout-stability metrics do not behave like the well-mannered bell curves that composite scoring formulas often assume. Largest Contentful Paint is right-skewed by a heavy tail of slow first-visit renders; Cumulative Layout Shift is zero for the majority of page loads and then spikes hard on the templates that inject late content. Feed either of these raw into a z-score or a linear min-max scale and the result is either a normalization that is dominated by a handful of outliers, or one so compressed that a genuinely bad page and a mediocre one land within a rounding error of each other. This is part of the broader Metric Scoring & Data Normalization topic area, which covers the full set of techniques for making heterogeneous metrics comparable before they feed a composite score.

Percentile normalization sidesteps the distribution-shape problem entirely. Instead of asking "how many standard deviations from the mean," it asks "what fraction of a rolling reference population does this observation beat." A LCP of 3,100 ms on a template where 3,100 ms is roughly the median produces a rank near 50; the same 3,100 ms on a template where it sits in the tail produces a rank near 90. The rank is bounded, interpretable to a non-statistician, and comparable across metrics measured in entirely different units. This page walks through building the rolling reference distribution, winsorizing it against sensor noise, computing percentile ranks on a schedule, and storing the result so it can feed a downstream composite score or an alerting layer without re-deriving the reference distribution from scratch every time.

---

Prerequisites & Environment Setup #

The percentile-ranking pipeline needs a queryable store of raw metric samples (Postgres, BigQuery, or an equivalent columnar warehouse), a Python runtime for the winsorization and ranking logic, and object storage for the reference-distribution and ranked-output artifacts. Pin every dependency and keep configuration out of the codebase.

Pinned tool versions

Required environment variables

# /etc/percentile-normalization/env  — sourced by the cron wrapper
export METRICS_DB_URL="postgresql://normalizer:${METRICS_DB_PASS}@db.internal:5432/audit_metrics"
export REFERENCE_BUCKET="gs://site-audit-reference-dists-prod"
export RANKED_OUTPUT_BUCKET="gs://site-audit-percentile-ranks-prod"
export NORMALIZATION_CONFIG="/opt/normalization/percentile_config.json"
export NORMALIZATION_ENV="production"
export TZ="UTC"

Lockfile pattern — commit requirements.txt generated with pip-compile (pip-tools):

pip-compile \
  --generate-hashes \
  --output-file /opt/normalization/requirements.txt \
  /opt/normalization/requirements.in

---

Step 1 — Initialization: Reference Distribution Builder #

A percentile rank is only as good as the reference distribution it is measured against, so the first job is to build that distribution deterministically from raw samples — never from an already-scored or already-aggregated table, which would compound rounding and hide the true tail shape.

Pull the trailing window with a single windowed SQL query, segmented by device class so mobile and desktop never share a reference population:

-- /opt/normalization/sql/reference_window.sql
-- Portable across Postgres and BigQuery; adjust date functions as needed.
SELECT
  device_segment,
  metric_name,
  metric_value
FROM raw_metric_samples
WHERE
  sampled_at >= (CURRENT_DATE - INTERVAL '28 days')
  AND sampled_at < CURRENT_DATE
  AND metric_value IS NOT NULL
  AND metric_name IN ('lcp_ms', 'cls_score', 'inp_ms', 'ttfb_ms')
ORDER BY device_segment, metric_name, metric_value;

Load the query result, winsorize each segment's tail against sensor noise (a stray 90,000 ms LCP from a hung tab should not distort the whole distribution), and persist the sorted sample array — sorted arrays make percentile lookups a simple binary search later.

# /opt/normalization/build_reference.py
# Requires: pandas==2.2.2, numpy==1.26.4, psycopg==3.1.19, pyarrow==16.0.0
from __future__ import annotations
import json
import os
from datetime import datetime, timezone
from pathlib import Path

import numpy as np
import pandas as pd
import psycopg


def load_raw_window(sql_path: Path, window_days: int) -> pd.DataFrame:
    """Execute the windowed reference query and return raw samples."""
    query = sql_path.read_text().replace("28 days", f"{window_days} days")
    with psycopg.connect(os.environ["METRICS_DB_URL"]) as conn:
        return pd.read_sql(query, conn)


def winsorize_segment(values: np.ndarray, winsor_pct: float) -> np.ndarray:
    """Clip both tails at the given percentile before building the reference array."""
    lo = np.percentile(values, winsor_pct)
    hi = np.percentile(values, 100 - winsor_pct)
    return np.clip(values, lo, hi)


def build_reference_distributions(
    raw: pd.DataFrame,
    winsor_pct: float,
    min_reference_samples: int,
) -> dict[str, dict[str, list[float]]]:
    """
    Build a sorted, winsorized reference array per (device_segment, metric_name).
    Segments below min_reference_samples are flagged for fallback, not dropped.
    """
    references: dict[str, dict[str, list[float]]] = {}
    for (segment, metric), group in raw.groupby(["device_segment", "metric_name"]):
        values = group["metric_value"].to_numpy(dtype=float)
        if len(values) < min_reference_samples:
            print(f"WARN: {segment}/{metric} has only {len(values)} samples — flagged for fallback")
        clipped = winsorize_segment(values, winsor_pct)
        references.setdefault(segment, {})[metric] = sorted(clipped.tolist())
    return references


def write_reference_artifact(references: dict, bucket: str, window_days: int) -> str:
    build_time = datetime.now(tz=timezone.utc)
    manifest = {
        "built_at": build_time.isoformat(),
        "window_days": window_days,
        "sample_counts": {
            seg: {metric: len(arr) for metric, arr in metrics.items()}
            for seg, metrics in references.items()
        },
    }
    path = f"{bucket}/reference/{build_time.strftime('%Y/%m/%d')}/reference_distributions.json"
    payload = {"manifest": manifest, "distributions": references}
    Path("/tmp/reference_distributions.json").write_text(json.dumps(payload))
    print(f"Reference distribution artifact staged for upload to {path}")
    return path

The reference distributions land as sorted arrays keyed by segment and metric, ready for the ranking step. For a production BigQuery version of this same window-and-winsorize logic with a MERGE upsert, see computing rolling p75 baselines in BigQuery, which extends this pattern to a specific p75 baseline used for Core Web Vitals pass/fail decisions.

---

Step 2 — Core Configuration: Window, Winsorization, and Segmentation #

Three parameters govern the entire pipeline: how far back the reference window looks, how aggressively the tails are clipped before ranking, and how the population is split by segment. Get these wrong and either the reference distribution goes stale (window too short) or the rank stops meaning anything (segmentation too coarse).

Key parameters table

Store these as versioned JSON tracked in Git, never hardcoded in the ranking script:

// /opt/normalization/percentile_config.json  (version-controlled)
{
  "version": "1.4.0",
  "window_days": 28,
  "winsor_pct": 1.0,
  "segment_by": ["device_segment"],
  "min_reference_samples": 200,
  "rank_method": "empirical",
  "rebuild_schedule": "nightly",
  "metrics": ["lcp_ms", "cls_score", "inp_ms", "ttfb_ms"],
  "fallback_order": {
    "geo_region": "device_segment",
    "device_segment": "global"
  }
}

The fallback_order map matters as much as the primary segmentation: it defines what a thin segment rolls up to when it falls below min_reference_samples, rather than silently ranking against a near-empty array. Device-level segmentation is the floor, not the ceiling — see normalizing performance data across device types for the broader stratification approach this config's segment_by list draws from.

Once the reference distribution and config are in place, ranking a new observation is a binary search against the sorted array for its segment and metric:

# /opt/normalization/rank.py
# Requires: numpy==1.26.4
from __future__ import annotations
import numpy as np


def percentile_rank(value: float, reference_sorted: list[float], method: str = "empirical") -> float:
    """
    Rank a single observation against a sorted, winsorized reference array.
    Returns a 0-100 percentile rank.
    """
    arr = np.asarray(reference_sorted)
    n = len(arr)
    if n == 0:
        raise ValueError("Empty reference distribution — check min_reference_samples fallback")

    if method == "interpolated":
        # Linear interpolation between the two nearest sorted values
        idx = np.searchsorted(arr, value)
        if idx == 0:
            return 0.0
        if idx >= n:
            return 100.0
        lower, upper = arr[idx - 1], arr[idx]
        frac = 0.0 if upper == lower else (value - lower) / (upper - lower)
        return 100.0 * (idx - 1 + frac) / (n - 1)

    # Empirical: fraction of the reference distribution the value beats or ties
    rank_count = np.searchsorted(arr, value, side="right")
    return 100.0 * rank_count / n

Because worse-is-lower for latency and layout-shift metrics, invert the rank at the call site (100 - percentile_rank(...)) before it feeds a health score, so that a high value always means "healthier" going into a downstream weighted composite — see designing custom health score algorithms for how these inverted ranks combine with other signals into a single number.

---

Reference Distribution and Ranking Data Flow #

The diagram below shows how raw samples become a winsorized reference distribution, how a new observation is ranked against that distribution, and where thin segments fall back to a broader reference before the rank is emitted.

---

Step 3 — Execution & Scheduling #

Two schedules run independently: a nightly full rebuild of the reference distribution from the trailing window, and a percentile-rank computation on every scoring run that reads the most recent reference artifact rather than rebuilding it inline. Decoupling the two means a burst of new observations never has to wait on a full window rebuild to get ranked.

#!/usr/bin/env bash
# /opt/normalization/run_percentile_pipeline.sh
set -euo pipefail

MODE="${1:?Usage: run_percentile_pipeline.sh [rebuild|rank]}"
LOCKFILE="/var/lock/percentile-normalization-${MODE}.lock"
LOG="/var/log/percentile-normalization/${MODE}-$(date -u +%Y%m%d-%H%M%S).log"
SCRIPT_DIR="/opt/normalization"

exec 200>"${LOCKFILE}"
flock -n 200 || { echo "${MODE} already running — exiting." >&2; exit 1; }

mkdir -p "$(dirname "${LOG}")"

{
  echo "=== ${MODE} run started at $(date -u +%Y-%m-%dT%H:%M:%SZ) ==="
  echo "NORMALIZATION_CONFIG=${NORMALIZATION_CONFIG}"

  if [[ "${MODE}" == "rebuild" ]]; then
    python3 "${SCRIPT_DIR}/build_reference.py" --config "${NORMALIZATION_CONFIG}"
  else
    python3 "${SCRIPT_DIR}/rank_batch.py" --config "${NORMALIZATION_CONFIG}"
  fi

  echo "=== ${MODE} run completed at $(date -u +%Y-%m-%dT%H:%M:%SZ) ==="
} 2>&1 | tee "${LOG}"

Cron entries — add to /etc/cron.d/percentile-normalization:

# Nightly full reference-distribution rebuild at 02:30 UTC
30 2 * * *   normalizer-user bash /opt/normalization/run_percentile_pipeline.sh rebuild

# Rank new observations every 15 minutes against the latest reference artifact
*/15 * * * * normalizer-user bash /opt/normalization/run_percentile_pipeline.sh rank

Running this inside CI instead of cron works the same way — use a workflow-level concurrency key scoped to the mode so a rebuild run and a rank run never contend for the same lock unnecessarily, but two rebuild runs still cannot overlap.

---

Step 4 — Artifact Capture & Storage #

Every rank computation reads a specific reference-distribution artifact, so tag the output with that artifact's build timestamp and sample counts. Without this tag, a historical percentile rank cannot be explained months later when someone asks why a page scored 62 instead of 80 on a given day.

# /opt/normalization/artifact.py
# Requires: pyarrow==16.0.0, pandas==2.2.2
from __future__ import annotations
from datetime import datetime, timezone
from pathlib import Path

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

RETENTION_DAYS_RANKED = 90     # ranked-output retention
RETENTION_DAYS_REFERENCE = 400 # keep reference snapshots long enough to reproduce any historical rank


def write_ranked_artifact(
    ranked_df: pd.DataFrame,
    reference_built_at: str,
    reference_sample_counts: dict,
    bucket: str,
) -> str:
    run_time = datetime.now(tz=timezone.utc)
    partition = run_time.strftime("%Y/%m/%d")

    metadata = {
        b"reference_built_at": reference_built_at.encode(),
        b"reference_sample_counts": str(reference_sample_counts).encode(),
        b"ranked_at": run_time.isoformat().encode(),
        b"retention_days": str(RETENTION_DAYS_RANKED).encode(),
    }

    table = pa.Table.from_pandas(ranked_df).replace_schema_metadata(metadata)
    path = f"{bucket}/ranked/{partition}/percentile_ranks_{run_time.strftime('%H%M%S')}.parquet"
    pq.write_table(table, path, compression="snappy")
    print(f"Ranked artifact written: {path}")
    return path

Retain reference-distribution snapshots roughly four times longer than ranked output — a ranked artifact only stays reproducible for as long as the reference distribution it was scored against is still around. The building score aggregation pipelines guide picks up ranked output directly at this point, rolling per-URL percentile ranks up into section- and site-level composites.

---

Verification Checklist #

Confirm each run is correct before treating percentile ranks as authoritative:

1. Check the pipeline log for both === rebuild run completed and === rank run completed lines within the expected window — a missing rebuild line means ranks are being computed against a stale reference artifact.
2. Verify the reference-distribution manifest sample counts are all at or above min_reference_samples: python3 -c "import json; m=json.load(open('/tmp/reference_distributions.json'))['manifest']; print(m['sample_counts'])".
3. Assert every emitted rank is within [0, 100] — a value outside that range indicates a bug in the winsorization step or a reference array that was not sorted before the binary search.
4. Spot-check one manually: pick a raw value near the reference distribution's median and confirm percentile_rank() returns something close to 50.
5. Confirm the reference_built_at metadata tag on a ranked Parquet artifact points to a reference snapshot that still exists in REFERENCE_BUCKET — a missing snapshot breaks reproducibility.
6. Compare the segment list actually used in a run's output against segment_by in the config — an unexpected fallback segment name signals a thin population that needs the fallback_order map extended.

---

Troubleshooting #

Percentile ranks cluster near 50 for almost every page

Root cause: the reference distribution was built from too short a window, so nearly every new observation lands right in the middle of a sample set that barely differs from itself.

# Check the reference window's actual date span
python3 -c "
import json
m = json.load(open('/tmp/reference_distributions.json'))['manifest']
print('window_days:', m['window_days'])
print('built_at:', m['built_at'])
"

Fix: increase window_days to at least 28 and confirm the rebuild job actually ran on the expected schedule rather than reusing a cached artifact.

---

One outlier session collapses the whole reference distribution

Root cause: winsor_pct is set to 0 (no winsorization) or too low, so a single 60,000 ms LCP from a hung mobile session stretches the reference array's upper bound and compresses every legitimate rank downward.

# Inspect the max value in a segment's reference array before and after winsorization
python3 -c "
import json
d = json.load(open('/tmp/reference_distributions.json'))['distributions']
arr = d['mobile']['lcp_ms']
print('min:', min(arr), 'max:', max(arr), 'p99:', sorted(arr)[int(len(arr)*0.99)])
"

Fix: set winsor_pct to at least 1.0 in percentile_config.json and re-run the rebuild.

---

A thin segment silently falls back and nobody notices

Root cause: a low-traffic geography or template segment drops below min_reference_samples, falls back per fallback_order, and the fallback is not surfaced anywhere downstream.

# Grep rebuild logs for fallback warnings
grep "flagged for fallback" /var/log/percentile-normalization/rebuild-*.log | tail -20

Fix: add the resolved segment name to the ranked-output schema (not just the requested segment) so dashboards and alerts can distinguish a true per-segment rank from a rolled-up one.

---

Ranks computed today don't match ranks recomputed against the same day's data next week

Root cause: the reference distribution is rolling by design — a rank computed against a 28-day window that included last Tuesday will differ from one computed a week later once that Tuesday has aged out of the window.

# Confirm which reference snapshot a historical rank actually used
python3 -c "
import pyarrow.parquet as pq
m = pq.read_metadata('/path/to/percentile_ranks_143000.parquet').metadata
print(m[b'reference_built_at'])
"

Fix: this is expected behavior, not a bug — document it clearly for stakeholders, and if a fixed comparison baseline is required for release-gating, freeze a specific reference snapshot rather than always ranking against the rolling one.

---

Empirical and interpolated rank methods disagree noticeably near the tails

Root cause: rank_method was changed from "empirical" to "interpolated" (or vice versa) without re-scoring historical output, so a trend line shows a discontinuity that looks like a real regression.

# Compare both methods for the same value against the same reference array
python3 -c "
from rank import percentile_rank
import json
d = json.load(open('/tmp/reference_distributions.json'))['distributions']
arr = d['desktop']['cls_score']
v = 0.18
print('empirical:', percentile_rank(v, arr, 'empirical'))
print('interpolated:', percentile_rank(v, arr, 'interpolated'))
"

Fix: treat rank_method as a versioned, release-gated config change — bump version in percentile_config.json, note the cutover date, and avoid comparing ranks computed with different methods across that boundary.

---

Reference rebuild job and rank job deadlock on the same lockfile

Root cause: both modes were pointed at one shared lockfile path instead of a mode-specific one, so a slow nightly rebuild blocks every 15-minute rank run until it finishes.

# Confirm each mode has its own lockfile
ls -l /var/lock/percentile-normalization-*.lock

Fix: use the mode-scoped lockfile path shown in run_percentile_pipeline.sh (percentile-normalization-${MODE}.lock), never a single shared name.

---

Why use percentile rank instead of a z-score for latency and layout metrics?

Latency and layout-shift metrics are heavily right-skewed, so a z-score assumes a symmetric distribution that does not exist in the data and produces distorted scores in the upper tail. Percentile rank makes no distributional assumption — it only asks where an observation falls relative to the empirical reference distribution, which stays interpretable and stable even when the underlying metric is heavily skewed. It also translates directly into plain language: a rank of 90 means "faster than 90% of the reference population," which a non-statistician stakeholder can act on without a units conversion.

How do I choose window_days for the reference distribution?

Start with 28 days: long enough to smooth weekday/weekend traffic cycles, short enough to reflect a recent redesign or infrastructure change. Widen it toward 56–90 days for low-traffic segments that would otherwise have too few samples, and shorten it toward 14 days only for sites with very high, stable traffic where recency matters more than sample size. Whatever value you pick, keep it fixed across a comparison period — changing window_days mid-quarter invalidates any percentile-rank trend line spanning the change.

Should percentile ranks be computed globally or per device segment?

Per device segment. Mobile and desktop have structurally different latency and layout-shift distributions, so a single global reference distribution ranks mobile pages unfairly against a desktop-heavy baseline. Segment by at least device class, and add connection type or geography if you have enough samples to keep each segment's reference distribution statistically meaningful. Going finer than your traffic supports just produces noisy, unstable ranks that swing on small sample counts.

What happens when a segment has too few samples for a reliable reference distribution?

Below the configured min_reference_samples floor, fall back to the next broader segment — for example roll a thin geography segment up to its device-class parent — rather than computing a percentile rank against a handful of noisy points. Record which segment actually served the rank in the output artifact so downstream consumers know a fallback occurred, and revisit fallback_order whenever traffic volume shifts enough that a previously thin segment now clears the floor on its own.

---

Related #

• Metric Scoring & Data Normalization — parent section covering the full normalization and scoring topic area
• Computing Rolling p75 Baselines in BigQuery — a production BigQuery implementation of this window-and-winsorize pattern using PERCENTILE_CONT
• Normalizing Performance Data Across Device Types — the device stratification approach this page's segment_by config builds on
• Building Score Aggregation Pipelines — how ranked output rolls up into section- and site-level composite scores