Capstone: diagnose the drop

Every good metric review asks the same four questions in the same order. Skip one and you get a guess; run all four and you get a diagnosis.

• Q1 - Which branch moved? Build the tree, read the drop, follow the coral trail to the guilty driver. This is Demo 1.
• Q2 - Which slice fell? Segment the guilty driver by channel, plan, or cohort until one slice owns the fall. This is Demo 2.
• Q3 - Why would it fall? Form a specific, testable hypothesis about the mechanism - a bug, a price change, a lost campaign.
• Q4 - Does the data confirm it? Run the one query that proves or kills the hypothesis before you spend a cent fixing anything.

The tree said a channel moved. Confirm it in the data: split completed revenue by channel and see which slice is carrying the fall.

SELECT o.channel,
       COUNT(DISTINCT o.order_id)                 AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY o.channel
ORDER BY revenue DESC;

Hypothesis (Q3): the fallen channel's revenue dropped because refunds spiked there - a checkout or fulfilment problem, not a demand problem. Confirm it (Q4): compute the refund rate per channel and see if the guilty channel is worse.

SELECT o.channel,
       COUNT(*)                                          AS all_orders,
       COUNT(*) FILTER (WHERE o.status = 'refunded')     AS refunds,
       ROUND(100.0 * COUNT(*) FILTER (WHERE o.status = 'refunded')
             / COUNT(*), 1)                              AS refund_rate_pct
FROM orders o
GROUP BY o.channel
ORDER BY refund_rate_pct DESC;

Every metric-review narrative fits one sentence-shaped template. Six blanks, and you filled every one of them in Demos 1 and 2.

• [X%] - the top-line move, from the tree.
• [driver] - the branch that moved, from Q1.
• [segment] - the slice that owns it, from Q2.
• [confirmed cause] - the mechanism you proved, from Q3-Q4. Not a guess - a confirmed one.
• [team] - who owns the driver, so the fix has an address.
• [next step] by [date] - one action, one deadline. A diagnosis with no action is trivia.

Here is the whole diagnosis in one paragraph, using the numbers you traced today:

Read it back against the loop: 12% is the tree, conversion is Q1, app channel is Q2, the refund spike is the confirmed cause from Q3-Q4, product is the owner, and the fix-by-Friday is the action. Nothing vague, nothing unowned, nothing without a date.

Now you write one. Run the simulator's drop again in Demo 1, follow it through Demo 2's queries, and fill every blank from your own trace - not from the worked example.

• If you cannot fill a blank, you skipped a step - go back and run it. A missing [confirmed cause] means you never ran Q4.
• Keep it to one paragraph. If it needs two, you are reporting, not diagnosing - cut to the single driver that owns the fall.
• Read it out loud to a neighbor. If they know what to do next without asking a question, it is done.

The loop is not a one-off. It is a cadence.

• Weekly: the leader track's a5 turns this into the Weekly Business Review - the same four questions, run every Monday on last week's numbers.
• Monthly: a6 rolls the weeklies into the monthly review, where you look for the trend under the noise, not just the single drop.
• Everywhere: the paragraph you wrote today is the atom of both. Trees and queries are the work; the narrative is the deliverable.

What this track is not, said plainly so you are not surprised later:

• It does not build the data platform. The seed database is a teaching toy. Real pipelines, warehouses, and modeling are the data-engineering and warehouse courses.
• It does not run the statistics. "Down 12%" here is read at face value. Whether a move is signal or noise - significance testing, confidence intervals, anomaly detection - is a statistics and experimentation course, not this one.
• It does not fix the problem. The loop ends at a diagnosis and an owner. Shipping the checkout fix is the product team's job; the narrative just routes it there.
• What it does do - and does well - is turn a vague top-line drop into a specific, owned, confirmed diagnosis in minutes. That skill is the difference between arguing and acting.