Metrics & Diagnosis Guide

Every other article on this site is about computing numbers. This one is about choosing the numbers and reading them — the judgment layer of DA work. It covers two skills: designing a metric that actually measures what the business cares about, and diagnosing a metric that suddenly moved. The second one is also the most common DA interview question in existence.

All examples use the parking system business: stations, sessions, payments. For the SQL behind the examples see SQL Patterns; for whether a difference is real or noise see Statistics Notebook.

Part 1 — Designing Metrics

A Metric Needs Four Parts

"Repeat customer rate" is not a metric definition — it is a name. A real definition pins down:

Part	Question	Example
Numerator	Counting what?	Plates with ≥ 2 paid sessions
Denominator	Out of what?	All plates with ≥ 1 paid session
Window	Over what period?	Trailing 90 days
Filters	Excluding what?	Monthly-pass plates excluded

Repeat customer rate = plates with 2+ paid sessions in the trailing 90 days ÷ plates with 1+ paid session in the trailing 90 days, excluding monthly-pass plates.

Write the definition down before writing any SQL. Most "the numbers don't match" arguments between teams are two people using the same name for different definitions.

Common Metric Families

Family	Question	Parking examples
Volume	How much activity?	Daily sessions, kWh charged, new plates seen
Conversion	What share completes the step?	Paid rate (paid sessions ÷ entries)
Retention	Do they come back?	Month-1 retention, repeat customer rate
Revenue	How much money, per what?	Revenue per session, revenue per active plate
Efficiency	Output per unit input?	Revenue per stall, utilization rate

Counts answer "how much"; rates answer "how well". A report needs both — a rising paid rate on collapsing volume is not good news.

Leading vs Lagging

Lagging metrics (monthly revenue, churn) tell you what already happened. Leading metrics (first-visit-to-second-visit rate, sessions per active plate) move earlier and give you time to act. For every lagging metric you report, know which leading metric predicts it.

North Star + Guardrails

Pick one primary metric that best captures delivered value — for the parking business, something like weekly paid sessions — and a small set of guardrails that must not degrade while you push it (avg amount per session, complaint rate, utilization). A north star without guardrails invites gaming: pushing paid sessions with deep discounts "works" until the revenue guardrail catches it.

This is Goodhart's law: when a measure becomes a target, it stops being a good measure. Guardrails are the defense.

The Metric Tree

Decompose a headline metric into factors you can act on:

Monthly revenue
  = sessions × paid rate × avg amount per paid session

sessions
  = active plates × sessions per plate

active plates
  = new plates + retained plates

Each leaf is owned by a different lever (acquisition, pricing, retention). The tree is also the map you will walk during diagnosis — build it before the emergency.

Part 2 — Diagnosis: "Revenue Dropped 20%"

Work the steps in order. Each one either finds the cause or narrows where it can hide.

Step 0 — Is the Drop Real?

Before analyzing, rule out measurement problems — they cause a large share of "incidents":

• Pipeline: did yesterday's data load fully? Compare row counts to the prior weeks.
• Definition: did the metric, a filter, or a tracking event change recently?
• Comparison window: is this "down 20% vs last week" comparing a holiday Monday to a normal one? Partial periods (month-to-date vs full month) are the classic false alarm.

-- quick completeness check: daily row counts, recent vs typical
SELECT DATE(paid_time) AS day, COUNT(*) AS rows_loaded
FROM payment_history
WHERE paid_time >= CURDATE() - INTERVAL 21 DAY
GROUP BY DATE(paid_time)
ORDER BY day;

Step 1 — Shape the Drop in Time

SELECT DATE(paid_time)      AS day,
       SUM(amount_received) AS revenue
FROM payment_history
WHERE paid_time >= CURDATE() - INTERVAL 60 DAY
GROUP BY DATE(paid_time)
ORDER BY day;

Plot it and read the shape:

Shape	Suggests
Sudden cliff on one day	An event: deploy, price change, station closure, tracking break
Gradual slide over weeks	Behavior: churn, competition, seasonality
Same dip every 7 days	Day-of-week mix — compare to the same weekday, not yesterday
Dip a year ago too	Seasonality — compare YoY, not MoM

A cliff has a date — go find what shipped or changed that day. A slide has a trend — go find which segment is eroding.

Step 2 — Cut by Segment

The goal: is the drop concentrated (one station, one payment method, one category) or broad (everything down a little)? Concentrated points to a local cause; broad points to seasonality, market, or measurement.

-- which station explains the gap between this week and last week?
SELECT station_code,
       SUM(CASE WHEN paid_time >= CURDATE() - INTERVAL 7 DAY
                THEN amount_received ELSE 0 END)  AS this_week,
       SUM(CASE WHEN paid_time >= CURDATE() - INTERVAL 14 DAY
                 AND paid_time <  CURDATE() - INTERVAL 7 DAY
                THEN amount_received ELSE 0 END)  AS last_week
FROM payment_history
WHERE paid_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY station_code
ORDER BY (this_week - last_week);    -- biggest drop first

Compute each segment's share of the total change, not its own percentage change. A tiny station down 80% matters less than the biggest station down 8%. Cut by one dimension at a time — station, city, payment method, parking type, weekday, hour — and stop at the first dimension where the change is clearly concentrated.

Step 3 — Walk the Metric Tree

Once you know where, decompose what kind of drop it is:

revenue = sessions × paid rate × avg amount

SELECT
    CASE WHEN ph.entry_time >= CURDATE() - INTERVAL 7 DAY
         THEN 'this_week' ELSE 'last_week' END        AS period,
    COUNT(*)                                          AS sessions,
    AVG(CASE WHEN pay.parking_id IS NOT NULL THEN 1.0 ELSE 0 END) AS paid_rate,
    AVG(pay.amount_received)                          AS avg_amount
FROM parking_history ph
LEFT JOIN payment_history pay ON ph.parking_id = pay.parking_id
WHERE ph.entry_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY period;

Each factor implicates a different cause: sessions down → demand or access problem; paid rate down → product, payment, or tracking problem; avg amount down → mix or pricing problem. With three numbers you have turned "revenue is down" into a specific question.

Step 4 — Internal vs External

With the location and the factor known, run the cause checklist:

• Internal: deploys and releases, price or rule changes, station maintenance, marketing campaigns ending, a dashboard or ETL change.
• External: weather, holidays and school calendar, a competitor opening nearby, road construction, macro events.

Confirm with evidence, not vibes: an affected/unaffected comparison (did stations without the price change also drop?), timing alignment to the day, and — if the change was an experiment — the significance tests from Statistics Notebook.

Writing It Up

One paragraph, four sentences: what moved, where it is concentrated, which factor in the tree, most likely cause with evidence. Then the recommendation.

Revenue is down 18% WoW. The drop is concentrated in ST003 and ST007 (together 85% of the gap). Sessions are flat; paid rate fell from 92% to 71% starting June 9 — the day the new payment terminal firmware shipped. Recommend rolling back firmware at the two stations and re-measuring for 3 days.

Common Mistakes

1. Vanity Metrics

Cumulative totals ("10 million sessions all-time!") only go up and inform no decision. Prefer rates and per-period numbers that can actually move both ways.

2. Averages Hiding the Story

Average amount can rise because every customer pays more — or because the cheapest customers left. Check the distribution (median, percentiles) and the mix before celebrating. See the histogram section of Visualization Selection Guide.

3. Rates Without Volumes

"Conversion improved from 8% to 12%" means little if entries fell by half. Always show the rate and its denominator together.

4. Mix Shift / Simpson's Paradox

The overall rate can fall while every segment's rate rises, just because the mix shifted toward low-rate segments. Any time an overall ratio moves but segment ratios disagree, decompose into mix vs rate — see Simpson's paradox in Statistics Notebook.

5. Comparing Non-Comparable Windows

Month-to-date vs last full month, a 4-weekend month vs a 5-weekend month, Lunar New Year vs a normal week. Compare same-length windows, same weekday composition — or same period last year.

6. Alerting on Noise

A daily metric that swings ±15% normally will breach a ±10% alert constantly. Set thresholds from the metric's own historical variance (e.g. mean ± 3σ of daily changes), not from a round number. See the rolling statistics in Time Series Notebook.

7. Stopping at the First Plausible Story

"Probably the rain" feels done but isn't checked. The discipline: every diagnosis names the evidence that would falsify it, and you go look. The affected/unaffected comparison from Step 4 is usually the cheapest test.

---

The SQL building blocks for everything here — conditional aggregation, period-over-period, funnels, cohorts — are in SQL Patterns. For grouping customers rather than diagnosing metrics, see RFM Analysis Notebook and Clustering Notebook.