12 min read

Setting Dynamic Alert Baselines with Rolling Percentiles

A single static threshold — "alert if LCP p75 exceeds 2500 ms" — treats every URL segment as if it behaved identically, which is rarely true. A product listing page with heavy third-party tag load has a naturally noisier p75 than a static marketing page, and a threshold tuned to avoid paging on the noisy segment will miss real regressions on the quiet one. This page is part of Configuring Alert Thresholds and Routing and walks through replacing that single fixed number with a rolling-percentile baseline computed independently per segment, so the alert threshold adapts to each segment's own recent history instead of a single global guess.

The technique borrows directly from how the scoring pipeline already handles percentile normalization across metric distributions: instead of comparing a raw metric to a fixed cutoff, you rank it against a reference window built from the segment's own trailing data. The difference here is the reference window is recomputed daily and used purely to decide whether to fire an alert, not to normalize a health score.

Rolling Percentile Baseline Pipeline Diagram showing a rolling 28-day sample window per URL segment feeding a p95 baseline calculation, which is compared against today's live value using a deviation multiplier to decide whether to emit an alert or persist a new baseline. Sample Window 28-day history per URL segment Baseline Calc percentile(samples, 95) min 20 samples Compare live value vs baseline × 1.15 Decision alert candidate or persist baseline

Environment Isolation and Dependency Declaration

Pin the rolling-window parameters as environment variables so the evaluation job, the backfill script, and the dashboard that shows "current baseline" all agree on the same numbers. A silent mismatch between a job using a 28-day window and a dashboard rendering a 14-day window is a common source of "the graph doesn't match the alert" tickets.

# /opt/audit/baseline_alerts — absolute working directory for all scripts
export BASELINE_DB_DSN="postgresql://[email protected]:5432/metrics"
export BASELINE_WINDOW_DAYS=28
export BASELINE_PERCENTILE=95
export BASELINE_MIN_SAMPLES=20
export BASELINE_DEVIATION_MULTIPLIER=1.15
export BASELINE_SEGMENT_COLUMN=url_segment
export BASELINE_METRIC_COLUMN=lcp_p75_ms
# requirements.txt — pin every direct dependency
pandas==2.2.2
numpy==1.26.4
psycopg2-binary==2.9.9
SQLAlchemy==2.0.30

set -euo pipefail
cd /opt/audit/baseline_alerts
python3 -m venv .venv
.venv/bin/pip install --quiet -r requirements.txt

BASELINE_DEVIATION_MULTIPLIER is the knob you will tune most often after launch. A value of 1.15 means a segment must exceed its own rolling p95 by 15% before it is treated as an alert candidate — a margin wide enough to absorb the ordinary day-to-day wobble a p95 baseline already has, without waiting for a full standard deviation of drift.

Implementation

The job below is intentionally a single script: it loads the configuration, pulls the rolling window per segment, computes the baseline, compares it against today's live value, and writes both the refreshed baseline and any alert candidates back to the database in one transaction.

#!/usr/bin/env python3
# /opt/audit/baseline_alerts/rolling_percentile_baseline.py
"""
Compute a rolling p95 baseline per URL segment and flag segments whose
live value exceeds baseline * BASELINE_DEVIATION_MULTIPLIER.
Reads BASELINE_* config from the environment; reads/writes via BASELINE_DB_DSN.
"""

import os
import sys
from datetime import date, timedelta

import numpy as np
import pandas as pd
from sqlalchemy import create_engine, text

# ── 1. load environment-injected configuration ───────────────────────────────
DSN = os.environ["BASELINE_DB_DSN"]
WINDOW_DAYS = int(os.environ["BASELINE_WINDOW_DAYS"])          # e.g. 28
PERCENTILE = float(os.environ["BASELINE_PERCENTILE"])          # e.g. 95
MIN_SAMPLES = int(os.environ["BASELINE_MIN_SAMPLES"])          # e.g. 20
DEVIATION_MULT = float(os.environ["BASELINE_DEVIATION_MULTIPLIER"])  # e.g. 1.15
SEGMENT_COL = os.environ["BASELINE_SEGMENT_COLUMN"]            # e.g. url_segment
METRIC_COL = os.environ["BASELINE_METRIC_COLUMN"]              # e.g. lcp_p75_ms

engine = create_engine(DSN)
today = date.today()
window_start = today - timedelta(days=WINDOW_DAYS)

# ── 2. pull the rolling sample window per segment, excluding today ──────────
# One row per (segment, day) is expected — the daily scoring job already
# writes this table, so no new ingestion path is needed for this job.
history = pd.read_sql(
    text(f"""
        SELECT {SEGMENT_COL} AS segment, sample_date, {METRIC_COL} AS value
        FROM daily_segment_metrics
        WHERE sample_date >= :window_start AND sample_date < :today
    """),
    engine,
    params={"window_start": window_start, "today": today},
)

# ── 3. compute the rolling p95 baseline per segment, gated on sample count ──
def segment_baseline(group: pd.DataFrame):
    if len(group) < MIN_SAMPLES:
        return pd.Series({"baseline": np.nan, "n_samples": len(group)})
    return pd.Series({
        "baseline": np.percentile(group["value"].dropna(), PERCENTILE),
        "n_samples": len(group),
    })

baselines = (
    history.groupby("segment", group_keys=True)
    .apply(segment_baseline, include_groups=False)
    .reset_index()
)

# segments below MIN_SAMPLES fall back to the site-wide baseline until they
# accumulate enough history of their own
site_wide_baseline = np.percentile(history["value"].dropna(), PERCENTILE)
baselines["baseline"] = baselines["baseline"].fillna(site_wide_baseline)

# ── 4. pull today's live value per segment ───────────────────────────────────
live = pd.read_sql(
    text(f"""
        SELECT {SEGMENT_COL} AS segment, {METRIC_COL} AS value
        FROM daily_segment_metrics
        WHERE sample_date = :today
    """),
    engine,
    params={"today": today},
)

merged = live.merge(baselines, on="segment", how="left")
merged["threshold"] = merged["baseline"] * DEVIATION_MULT
merged["is_alert_candidate"] = merged["value"] > merged["threshold"]
merged["evaluated_on"] = today

# ── 5. persist the refreshed baseline and any alert candidates ──────────────
with engine.begin() as conn:
    for row in merged.itertuples():
        conn.execute(text("""
            INSERT INTO segment_baselines (segment, baseline, evaluated_on)
            VALUES (:segment, :baseline, :evaluated_on)
            ON CONFLICT (segment) DO UPDATE
              SET baseline = EXCLUDED.baseline,
                  evaluated_on = EXCLUDED.evaluated_on
        """), {"segment": row.segment, "baseline": row.baseline, "evaluated_on": row.evaluated_on})

        if row.is_alert_candidate:
            conn.execute(text("""
                INSERT INTO alert_candidates
                    (dedup_key, segment, metric, value, baseline, evaluated_on)
                VALUES (:dedup_key, :segment, :metric, :value, :baseline, :evaluated_on)
                ON CONFLICT (dedup_key) DO NOTHING
            """), {
                "dedup_key": f"{row.segment}:{METRIC_COL}:{row.evaluated_on}",
                "segment": row.segment,
                "metric": METRIC_COL,
                "value": row.value,
                "baseline": row.baseline,
                "evaluated_on": row.evaluated_on,
            })

n_candidates = int(merged["is_alert_candidate"].sum())
print(f"Evaluated {len(merged)} segments, {n_candidates} alert candidate(s), "
      f"site-wide fallback baseline = {site_wide_baseline:.1f}")
sys.exit(0)

Line-by-line, the parts worth calling out: step 3 gates every segment's own percentile behind MIN_SAMPLES and silently falls back to a site_wide_baseline rather than alerting (or failing to alert) on a percentile computed from a handful of rows — a low-traffic segment with three samples should not get its own noisy baseline. Step 5's dedup_key is segment:metric:evaluated_on, which means re-running the job for the same day is idempotent: the ON CONFLICT DO NOTHING clause means a retried run never double-inserts the same candidate, and the routing layer described in Configuring Alert Thresholds and Routing can safely poll alert_candidates without deduplicating again downstream.

Verification and Smoke Test

Run this after every deploy of the baseline job, and again after any change to BASELINE_WINDOW_DAYS or BASELINE_PERCENTILE — both changes shift every segment's baseline and deserve a fresh sanity pass before the job runs unattended.

set -euo pipefail
cd /opt/audit/baseline_alerts

# 1. run the baseline job against today's data
.venv/bin/python rolling_percentile_baseline.py

# 2. confirm every segment got a non-null baseline
psql "$BASELINE_DB_DSN" -c "
  SELECT count(*) AS null_baselines
  FROM segment_baselines
  WHERE baseline IS NULL;
"

# 3. sanity-check that baselines are in a plausible range for the metric
psql "$BASELINE_DB_DSN" -c "
  SELECT min(baseline), max(baseline), avg(baseline)
  FROM segment_baselines
  WHERE evaluated_on = CURRENT_DATE;
"

# 4. confirm alert candidates carry a stable, unique dedup_key
psql "$BASELINE_DB_DSN" -c "
  SELECT dedup_key, count(*)
  FROM alert_candidates
  WHERE evaluated_on = CURRENT_DATE
  GROUP BY dedup_key
  HAVING count(*) > 1;
"

Expected output: null_baselines is 0 (the site-wide fallback should have filled every gap), the min/max/avg row shows values in the metric's real-world range rather than 0 or NaN, and the fourth query returns no rows — any row it does return means the unique constraint on dedup_key is missing or was bypassed.

Failure Modes

A newly launched segment alerts constantly in its first week

With fewer than BASELINE_MIN_SAMPLES samples, the job should be using the site-wide fallback baseline, not the segment's own thin percentile. If it is still alerting constantly, the fallback logic did not trigger.

# diagnostic: check sample count and which baseline source was used
psql "$BASELINE_DB_DSN" -c "
  SELECT segment, evaluated_on, baseline
  FROM segment_baselines
  WHERE segment = 'new-product-launch';
"
# fix: verify MIN_SAMPLES is set correctly and re-run for the affected segment
export BASELINE_MIN_SAMPLES=20
.venv/bin/python rolling_percentile_baseline.py

A multi-day regression stops alerting after day two or three

Once a regression's own bad samples enter the 28-day rolling window, the p95 baseline creeps upward and absorbs the regression as "normal." This is the single biggest risk of any rolling-percentile baseline and must be handled explicitly.

# diagnostic: check whether recent confirmed-alert days are still in the window
psql "$BASELINE_DB_DSN" -c "
  SELECT sample_date, value
  FROM daily_segment_metrics d
  WHERE segment = 'checkout-flow'
    AND EXISTS (
      SELECT 1 FROM alert_candidates a
      WHERE a.segment = d.segment AND a.evaluated_on = d.sample_date
    )
  ORDER BY sample_date DESC;
"
# fix: exclude days with a confirmed alert from the rolling window query
# (add: AND sample_date NOT IN (SELECT evaluated_on FROM confirmed_alerts))

Baseline recompute and live evaluation disagree on "today"

If the job runs across a timezone boundary, date.today() on the application server may not match CURRENT_DATE in the database, silently comparing yesterday's baseline against a partial day of live data.

# diagnostic: compare app-server date vs database date
date -u +%F
psql "$BASELINE_DB_DSN" -c "SELECT CURRENT_DATE;"
# fix: pin both to UTC explicitly rather than relying on local server time
export TZ=UTC
.venv/bin/python rolling_percentile_baseline.py

FAQ

Why use a rolling p95 baseline instead of a fixed threshold or a simple average?

A fixed threshold is calibrated once and drifts out of date as a segment's traffic mix, template, or third-party scripts change. An average is worse because it is pulled down by the segment's own good days, making the baseline too optimistic. The rolling p95 already encodes what "a bad but still normal day" looks like for that specific segment, so a genuine regression only trips the alert when it is worse than the segment's own recent worst-case behavior.

How many days of history do I need before a baseline is trustworthy?

Use BASELINE_MIN_SAMPLES as the gate, not calendar days. Twenty independent daily samples per segment is a reasonable floor; below that, percentile estimates swing wildly with each new observation. New or low-traffic segments should fall back to a site-wide default baseline until they accumulate enough history, rather than alerting on a percentile computed from three data points.

What happens to the baseline during a real regression that lasts multiple days?

This is the core weakness of any rolling-window baseline: if the regression persists long enough, its own bad samples enter the window and slowly raise the p95, making the alert less sensitive to the ongoing problem. Guard against this by excluding days that already triggered a confirmed alert from the rolling window calculation, so a persistent regression cannot rehabilitate its own baseline.

Should deploy-day traffic wobble be handled here, or somewhere else?

Handle it separately. This job answers "is today's value unusual for this segment," not "did we just deploy." Layering a deploy-window suppression on top — as covered in Suppressing Alert Noise During Deploy Windows — keeps the two concerns independent, so tightening one does not require touching the other's configuration.