3. Crash Frequency and Trend Analysis Dashboard

Objective and Data Contract

This job answers a single question with a time axis: for each build, device model, and OS version, how does crash frequency evolve over time, and where does it step up? The output is a set of aggregated time-series tables plus a trend view that flags regressions. The input is the normalized crash table produced upstream, so we assume one row per crash event with the following columns available:

• crash_id — unique event identifier.
• signature — stable deduplication hash (already computed).
• build — build number, e.g. 2024.11.3.
• device_model — e.g. Pixel 7, SM-G991B.
• os_version — e.g. Android 14, iOS 17.2.
• occurred_at — UTC timestamp of the crash.
• session_id — session in which the crash occurred (needed for a rate denominator).

Crash count alone is misleading: a build that ships to twice as many devices will show twice the crashes without any quality change. We therefore compute two metrics side by side — raw count and crashes per 1,000 sessions — and use the rate for regression detection. The session denominator comes from a companion sessions table with the same build, device_model, os_version, and a day bucket.

Aggregation Design

Two decisions dominate the design. First, the time bucket: daily buckets are too noisy for low-volume device/OS combinations, so we aggregate to ISO week (date_trunc('week', occurred_at)) and keep daily counts as an intermediate for drill-down. Second, the grouping key: (build, device_model, os_version, week). Builds are the primary regression axis because a regression is introduced by a code change, but a build can regress only on a subset of devices or OS versions, so the finer key is what makes the dashboard actionable.

The aggregation is a single SQL statement that joins crashes to sessions and produces both metrics. It is written to be idempotent per week partition so the job can be re-run safely.

WITH crash_weekly AS (
  SELECT
    build,
    device_model,
    os_version,
    date_trunc('week', occurred_at)::date AS week_start,
    COUNT(*)                              AS crash_count,
    COUNT(DISTINCT session_id)            AS crashing_sessions
  FROM crashes
  WHERE occurred_at >= :window_start
    AND occurred_at <  :window_end
  GROUP BY 1, 2, 3, 4
),
session_weekly AS (
  SELECT
    build,
    device_model,
    os_version,
    date_trunc('week', day)::date AS week_start,
    SUM(session_count)            AS total_sessions
  FROM sessions
  WHERE day >= :window_start
    AND day <  :window_end
  GROUP BY 1, 2, 3, 4
)
SELECT
  c.build,
  c.device_model,
  c.os_version,
  c.week_start,
  c.crash_count,
  c.crashing_sessions,
  s.total_sessions,
  ROUND(1000.0 * c.crash_count / NULLIF(s.total_sessions, 0), 3)
    AS crashes_per_1k_sessions
FROM crash_weekly c
LEFT JOIN session_weekly s
  USING (build, device_model, os_version, week_start)
ORDER BY c.build, c.device_model, c.os_version, c.week_start;

Two details matter for correctness. The LEFT JOIN keeps crash rows even when the session table has no matching bucket (a data gap should surface as NULL rate, not a dropped row). The NULLIF prevents division by zero when a device/OS combination has crashes but zero recorded sessions — a real symptom of telemetry loss that should be visible, not silently zeroed.

Regression Detection on the Series

With the weekly series in hand, a regression is a sustained upward shift in the rate, not a single spike. We compute, per series, a baseline from the trailing four weeks and compare the current week against it. A series is flagged when the rate exceeds the baseline by more than a relative threshold and the absolute increase is meaningful, which suppresses noise on tiny denominators.

import pandas as pd

def flag_regressions(df, rel_threshold=0.5, min_abs=0.2, baseline_weeks=4):
    df = df.sort_values(["build", "device_model", "os_version", "week_start"])
    keys = ["build", "device_model", "os_version"]
    out = []
    for key, g in df.groupby(keys, dropna=False):
        g = g.copy()
        g["baseline"] = (
            g["crashes_per_1k_sessions"]
            .shift(1)
            .rolling(baseline_weeks, min_periods=2)
            .median()
        )
        g["delta"] = g["crashes_per_1k_sessions"] - g["baseline"]
        g["rel_delta"] = g["delta"] / g["baseline"]
        g["regression"] = (
            (g["rel_delta"] > rel_threshold)
            & (g["delta"] > min_abs)
            & g["baseline"].notna()
        )
        out.append(g)
    return pd.concat(out, ignore_index=True)

flagged = flag_regressions(weekly_df)
regressions = flagged[flagged["regression"]]

The median baseline is deliberate: a single bad week inside the trailing window should not drag the baseline up and mask the very regression we are hunting. The min_abs guard is what stops a jump from 0.05 to 0.10 per 1,000 sessions (a 100% relative increase on a tiny base) from paging anyone.

Trend View and Filtering

The dashboard renders one line per (device_model, os_version) within the selected build, with the x-axis as week_start and the y-axis as crashes_per_1k_sessions. Flagged weeks are marked with a point and a vertical guide so the eye lands on the shift. Filters are applied before aggregation where possible (build, OS version, date window) and after aggregation only for derived filters such as "regressions only".

The SVG below shows the data flow from the two source tables through the weekly aggregation into the two consumers — the trend chart and the regression flag list — and makes explicit where the session denominator enters and where the threshold logic sits.

Verification and Failure Symptoms

Before trusting the dashboard, verify the aggregation against a hand-checkable slice. Pick one build, one device, one OS version, and one week; count crash rows and session rows directly and confirm the rate matches the job output to three decimals. Then confirm the regression logic on a synthetic series: a flat baseline of 1.0 per 1,000 followed by a jump to 1.8 should flag; a jump from 0.05 to 0.10 should not, because the absolute delta is below min_abs.