Handling Missing & Outlier Metric Data #

Every scoring pipeline eventually meets the same two problems, and they arrive together. Some pages have no measurement at all — the crawl timed out, the field dataset excluded them for low traffic, the collector dropped a batch. Others have a measurement that is real in the sense that a number was recorded and false in the sense that nothing a user experienced produced it: a 90-second largest paint from a hung tab, a layout shift of 4.1 from a lazy-loaded advert that never settled. This page is part of Metric Scoring & Data Normalization and covers the layer that decides what to do with both, before any percentile or composite is computed on top of them.

Both problems share a property that makes them dangerous rather than merely annoying: neither one produces an error. A missing row is silently absent from an average. An extreme row is silently included in one. The pipeline runs, the artifact is written, the score is plausible, and the number describes a population that does not match the site.

Prerequisites & environment setup #

export DQ_INVENTORY_PATH="/data/inventory/urls_current.parquet"
export DQ_COVERAGE_FLOOR=0.70          # below this, a group is suppressed
export DQ_MIN_HISTORY_DAYS=14          # history needed for self-imputation
export DQ_MAD_FENCE=3.5                # robust fence, in MAD units
export DQ_WINSOR_PCT=1.0               # tail clipped before any imputation
export DQ_IMPUTE_MAX_PCT=0.30          # refuse to impute past this share

DQ_IMPUTE_MAX_PCT is the guard that separates a data-quality stage from a fiction generator. Past thirty percent imputed, the aggregate is describing the imputation method more than it is describing the site, and the correct response is to suppress the group and fix collection rather than to fill harder.

Step 1 — Measure completeness before touching anything #

Completeness is a ratio, and the denominator is the part that goes wrong. Counting "how many of the rows we received have a value" always looks excellent, because rows that were never received are not in the set being counted. The denominator has to come from an inventory of URLs the site is known to have.

#!/usr/bin/env python3
# /opt/audit/dq/completeness.py
from __future__ import annotations
import os
import pandas as pd

FLOOR = float(os.environ.get("DQ_COVERAGE_FLOOR", "0.70"))


def coverage(measured: pd.DataFrame, inventory: pd.DataFrame,
             metric: str, group_key: str = "template") -> pd.DataFrame:
    """Coverage per group against the KNOWN url count, not the received count."""
    known = (inventory.groupby(group_key)["url"]
             .nunique().rename("known_urls"))
    present = (measured.dropna(subset=[metric])
               .groupby(group_key)["url"].nunique().rename("measured_urls"))
    out = pd.concat([known, present], axis=1).fillna({"measured_urls": 0})
    out["coverage"] = out["measured_urls"] / out["known_urls"]
    out["trusted"] = out["coverage"] >= FLOOR
    return out.reset_index()

The fillna on measured_urls rather than on the whole frame is deliberate: a group present in the inventory and entirely absent from the measurements must appear with a coverage of zero, not vanish from the output. A group that silently disappears from a coverage report is indistinguishable from a group that is fine.

Step 2 — Cap outliers, before anything is filled #

Outlier handling has to run before imputation, because imputation reads the surrounding data. Fill first and a single hung-tab measurement becomes the source for every gap near it.

The test itself should be robust. A three-sigma fence is computed from a mean and a standard deviation that the outlier itself inflates, which is why it quietly stops catching anything on a dataset with several extreme values.

# /opt/audit/dq/outliers.py — robust fence + winsorization.
from __future__ import annotations
import numpy as np
import pandas as pd

def mad_fence(s: pd.Series, k: float = 3.5) -> tuple[float, float]:
    """Median absolute deviation fence. 1.4826 scales MAD to a
    standard-deviation equivalent for a normal distribution."""
    med = s.median()
    mad = (s - med).abs().median()
    if mad == 0:                      # degenerate: fall back to IQR
        q1, q3 = s.quantile([0.25, 0.75])
        iqr = q3 - q1
        return q1 - 1.5 * iqr, q3 + 1.5 * iqr
    scaled = 1.4826 * mad
    return med - k * scaled, med + k * scaled


def winsorize(df: pd.DataFrame, metric: str, group_key: str,
              pct: float = 1.0) -> pd.DataFrame:
    """Clip each group's tails to its own percentile bounds. Clipping, not
    dropping: a slow page is real data and must keep its weight."""
    def _clip(g: pd.DataFrame) -> pd.DataFrame:
        lo, hi = g[metric].quantile([pct / 100, 1 - pct / 100])
        g[metric] = g[metric].clip(lo, hi)
        return g
    return df.groupby(group_key, group_keys=False).apply(_clip)

Winsorizing rather than dropping matters. A genuinely slow page is a finding, and removing it from the distribution is how a site with many slow templates in its tail reports an excellent p75. Clipping preserves the row and its weight while removing its ability to distort the group.

The mad == 0 branch is not theoretical. A group where more than half the values are identical — a template that returns a cached response with a constant timing, a synthetic fixture — produces a median absolute deviation of exactly zero, and every value that is not the median then sits outside a zero-width fence.

Step 3 — Impute what remains, from the narrowest source available #

# /opt/audit/dq/impute.py
from __future__ import annotations
import os
import pandas as pd

MIN_HISTORY = int(os.environ.get("DQ_MIN_HISTORY_DAYS", "14"))
MAX_IMPUTED = float(os.environ.get("DQ_IMPUTE_MAX_PCT", "0.30"))


def impute(current: pd.DataFrame, history: pd.DataFrame,
           metric: str) -> pd.DataFrame:
    df = current.copy()
    df["imputed_from"] = pd.NA

    # 1. The URL's own trailing percentile, when it has enough history.
    own = (history.groupby("url")[metric]
           .agg(["quantile", "count"]).rename(columns={"quantile": "p75"}))
    own = own[own["count"] >= MIN_HISTORY]["p75"]
    fill = df["url"].map(own)
    take = df[metric].isna() & fill.notna()
    df.loc[take, metric] = fill[take]
    df.loc[take, "imputed_from"] = "url_history"

    # 2. Otherwise the template median, when the template is well populated.
    tmpl = df.dropna(subset=[metric]).groupby("template")[metric].median()
    fill2 = df["template"].map(tmpl)
    take2 = df[metric].isna() & fill2.notna()
    df.loc[take2, metric] = fill2[take2]
    df.loc[take2, "imputed_from"] = "template_median"

    # 3. Anything still missing stays missing. It is counted, not invented.
    imputed_share = df["imputed_from"].notna().mean()
    if imputed_share > MAX_IMPUTED:
        raise SystemExit(
            f"FAIL: {imputed_share:.0%} imputed, ceiling is {MAX_IMPUTED:.0%} — "
            "fix collection rather than filling harder")
    return df

imputed_from is not a debugging aid, it is part of the output schema. Every downstream consumer — a dashboard tile, an alert threshold, a release gate — needs to be able to answer "how much of this number was measured" without re-deriving it, and a column that records the provenance of each value is the only way to make that cheap.

Step 4 — Artifact capture and what the schema must carry #

Write the data-quality stage output with three columns that did not exist on the input: imputed_from, winsorized (a boolean per row), and a per-group coverage. Retention follows the pattern in storing and versioning crawl artifacts in cloud storage, with the pre-quality raw partition kept alongside the cleaned one for at least one full baseline window — without it, a suspected imputation defect cannot be investigated, only argued about.

Verification checklist #

1. Coverage denominators come from the inventory. Assert the inventory file's modification time is within the last collection cycle; a stale inventory inflates every coverage figure in the run.
2. No group is silently absent. The coverage output row count equals the inventory group count. A missing group means a join dropped it rather than reporting zero.
3. Winsorization clipped rather than dropped. Row counts before and after the outlier stage are identical.
4. Imputed share is under the ceiling. df["imputed_from"].notna().mean() is below DQ_IMPUTE_MAX_PCT for every group, not just overall — an overall figure hides one badly-covered group inside a well-covered site.
5. Percentiles are ordered. p50 < p75 < p90 per group. Inverted percentiles mean nulls reached the quantile computation.
6. Suppressed groups are visibly suppressed. A group below the coverage floor appears in the output with a null score and a reason, never as an absent row that a dashboard renders as healthy.

Troubleshooting #

Coverage is 0.95 and the site is clearly not that well measured. The inventory is stale. Coverage is measured against the URL set the crawl graph knew about at inventory-build time, and a site that has grown since is measured against its own past. Rebuild the inventory on the same cadence as the crawl and assert its freshness in the run.

The MAD fence excludes almost everything in one group. Fewer than half the values in the group are distinct, so the median absolute deviation collapsed to zero and the fallback IQR branch did not trigger because the code path predates it. Confirm with df.groupby("template")[metric].nunique() and treat a group with a handful of distinct values as unsuitable for outlier testing at all.

The p75 improves after adding imputation. Almost always a sign that gaps are being filled from a source healthier than the missing pages actually are — commonly the site median standing in for a slow template that stopped reporting precisely because it is slow. Check the correlation between missingness and template: if missing rows cluster in one template, imputing them from anywhere else is fabricating an improvement.

Two runs over the same day disagree. Imputation reads trailing history, and trailing history moves. This is expected for a rolling source and a defect for a fixed one; if reproducibility matters, pin the imputation source to a frozen snapshot rather than a rolling window, exactly as described for percentile normalization across metric distributions.

A group flips between trusted and suppressed on alternate days. Its coverage sits on the floor. Add hysteresis — require two consecutive runs above the floor to become trusted, and two below to be suppressed — so a group at 0.699 does not oscillate the dashboard.

FAQ #

Why does completeness have to be measured against an inventory rather than the received rows?

Because rows that were never received are not in the set you would be counting. Measuring "how many of the rows we have carry a value" is almost always excellent, and says nothing at all about the pages that produced no row. The denominator has to be the URL count the site is known to have, taken from a current inventory, or coverage silently measures the collector rather than the site.

Why use a median absolute deviation fence instead of three standard deviations?

Because the standard deviation is computed from the same data the outlier is in, so the outlier inflates the fence that is supposed to catch it. On a dataset with several extreme values this compounds: each one that escapes widens the boundary for the next, and the filter quietly stops catching anything. The median and the median absolute deviation are both unmoved by a small number of extreme values, so the fence stays where the bulk of the data is.

Should outliers be dropped or clipped?

Clipped. A genuinely slow page is a finding, and dropping it is how a site with many slow templates in its tail reports an excellent p75. Winsorizing keeps the row and its weight in the distribution while removing its ability to distort the group. Dropping is only appropriate when the value is not a measurement at all — a timed-out crawl artifact rather than a slow page — and that should be classified upstream, not handled by the outlier fence.

How much of a dataset can be imputed before the score stops meaning anything?

A practical ceiling is around thirty percent, and lower is better. Past that the aggregate is describing the imputation method more than the site, and the correct response is to suppress the group and fix collection rather than to fill harder. The ceiling should be enforced per group rather than overall, because a site-wide figure comfortably hides one badly covered section inside a well covered whole.

---

Related #

• Metric Scoring & Data Normalization — the parent section covering ingestion, normalisation, scoring and alerting end to end
• Percentile Normalization Across Metric Distributions — the reference distribution this stage feeds, and why winsorization has to happen first
• Building Score Aggregation Pipelines — the coverage floor and suppression behaviour applied at the rollup layer
• Normalizing Performance Data Across Device Types — device stratification, which has to happen before completeness is meaningful