The brief
You have been circling this since b1: something is off in Daybreak's March. Tonight you stop circling. The brief from the board is one line - "Revenue dipped in March. Why?" - and your job is to turn that one line into a defensible answer with a recommendation attached. The method matters more than this particular dip: confirm the number, decompose it, drill to causes, explain it in one slide. That loop is the most transferable thing in this whole track.
The investigation plan 6 min live
The worst thing you can do with "why did revenue fall?" is open a query editor and start typing. Write the question tree first: the branches you will check, in order, before you touch data. It keeps you honest (you check the boring branches, not just your pet theory), it makes the work reviewable, and when the board asks "did you consider X?" the answer is already on paper.
LiveWhy "is it real?" always goes first3 min▶
Half of all metric-drop panics dissolve at branch one. Before hunting causes, rule out the boring explanations:
- Definition drift: does "revenue" here include refunded orders? Canceled ones? If the dashboard measure and your query disagree, you are investigating a ghost.
- Data problems: late-loading data, a broken pipeline, a partial month. In our tiny warehouse this cannot happen; in yours it is the first suspect.
- One weird row: a single refund, a bulk order, a test transaction. Small warehouses feel this hard - and Daybreak is about to prove it.
The Friday-panic pattern. An exec sees a dip, forwards the screenshot, and three analysts start digging in three directions. The team that wrote the question tree answers in an hour and checks all four branches. The team that did not spends two days proving their first hunch wrong. Same data, same skill - different discipline.
Self-studyWhere each branch gets its data2 min read▶
Map the tree to the Daybreak schema before you query: branch 1 reads orders.status (refunds hide inside totals); branch 2 needs orders count and basket size (order_items joined to orders); branch 3 is the same revenue measure sliced by plan, city, channel; branch 4 reads subscriptions.cancel_date. Notice that no branch needs new data - a well-modeled warehouse (b3) answers "why" questions with the same tables that answered "what".
Confirm and decompose ★ 12 min · everyone investigates
Work the tree top-down. First confirm the dip exists in the numbers (not just in the chart someone screenshotted), then split revenue into its two factors: how many orders x how big each basket. A dip in either one is a completely different story with a completely different owner.
See the dip. Month by month revenue as a line. The mini-BI marks the minimum in gold - there is March, lit up before you even squint.
Put a number on it. The same measure as a table. Read down the revenue column: it climbs Jan to Feb, takes one hard negative step into March - by far the deepest cut in the table - then bounces back in April. That one number is what you are explaining.
SELECT strftime('%Y-%m', o.order_date) AS month,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY 1
ORDER BY 1;
Decompose: fewer orders, or smaller ones? Run both lines. Orders dip in March - and so does average order value. Two forces, not one. If you had stopped at "fewer orders" you would have shipped half an answer.
Check the statuses. Branch 1 said: rule out definition problems. March's orders by status turn up a refunded order sitting inside the month - money the trend counted that Daybreak never kept. One weird row, real impact.
SELECT status, COUNT(*) AS orders
FROM orders
WHERE strftime('%Y-%m', order_date) = '2026-03'
GROUP BY 1;
Drill to the causes ★ 13 min · keep digging
The dip is real, and it is two-sided: fewer orders and smaller baskets. Now finish the tree - the churn branch, the buyer count, and one more check nobody briefs you on but the board will absolutely ask: has it recovered?
The churn check. Query every subscription that has ever been canceled. Two rows come back - and only one cancel date lands in March: Liam Ford, Basic plan, canceled 2026-03-10. A recurring monthly order that simply stopped arriving. There is your missing-orders force, with a name on it.
SELECT c.name, c.plan, s.cancel_date FROM subscriptions s JOIN customers c ON c.customer_id = s.customer_id WHERE s.cancel_date IS NOT NULL;
Cross-check with buyers. Distinct buying customers per month dips in March too - consistent with a subscriber going quiet. When two independent views agree, your confidence should rise; when they disagree, that disagreement is the next question.
The recovery check. March against April, head to head. April is back up. That single fact changes the recommendation from "emergency" to "understand and prevent" - always check whether the fire is already out.
SELECT strftime('%Y-%m', o.order_date) AS month,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE strftime('%Y-%m', o.order_date) IN ('2026-03', '2026-04')
GROUP BY 1;
Explain and recommend 7 min live
The board does not want your seven queries; it wants the answer and what to do about it. Package the investigation the b7 way: takeaway as the title, one annotated trend, the three causes as tiles, and recommendations with owners. One slide. If they want the evidence, the appendix has your queries.
LiveThe three recommendations, unpacked3 min▶
- Win-back offer for the churned subscriber. One named customer, one recurring order worth recovering. Cheap to try, easy to measure - and it shows the board the analysis reached a person, not just a chart.
- Separate "gross demand" from "net revenue after refunds" in the semantic model. This is the b4 (and leader a3) lesson landing: the dashboard blended refunds into revenue, so a definition question masqueraded as a demand question. Two governed measures, defined once, and this whole class of panic disappears.
- A basket-size watch metric. AOV was the quiet second force, and nothing on the exec dashboard was watching it. Add it with a sensible threshold so the next basket shrink announces itself.
Recommendations are what get you invited back. Plenty of analysts can find causes. The ones who get pulled into the next big question are the ones who arrive with "here are the three fixes, here is who owns each" - because that turns analysis into a decision, which is the only thing a board can actually do with it.
Run it yourself ★ 8 min · your investigation
Same warehouse, your hands on the wheel. Three exercises, rising difficulty - the third one is the real test of whether tonight stuck.
Live1 · Rerun the tree for orders instead of revenue3 min▶
Investigate "March order count fell" with the same question tree. Which branches survive? (Hint: the refund branch changes meaning - a refunded order still counts as an order placed. The tree is reusable; the branch findings are not.)
Live2 · What would the dashboard have caught earlier?2 min▶
Suppose b6's exec dashboard had shipped with a net revenue excluding refunds measure next to gross - the b4 definitions lesson. Walk it through: which of tonight's seven steps become unnecessary? Which cause would have been visible the moment the board looked, no analyst required? Good measure design is investigation done in advance.
Self-study3 · Write the board's three-bullet summary yourself3 min▶
Before you peek below: write the three bullets you would send the board. Then compare. Ours:
- March revenue fell due to three stacked causes - one refunded order, one churned Basic subscriber, and smaller average baskets - not a demand collapse; April has already recovered.
- Immediate action: win-back offer for the churned subscriber; merchandising to review basket mix.
- Structural fix: report gross demand and net revenue after refunds as separate governed measures, and add a basket-size watch metric, so the next dip explains itself.
If your bullets led with the query steps instead of the verdict, reread b7's takeaway-first rule - the board reads the first line and maybe the second.
Try it yourself - this week ◐ 30-40 min total
- Find one real metric drop at work - any dashboard, any month - and run tonight's pattern on it: confirm → decompose → drill → explain → recommend.
- Write the question tree before you run a single query. Keep it to four or five branches and check every one, especially the boring ones.
- Package the result as a one-slide story: takeaway title, annotated trend, cause tiles, recommendations with owners.
- Present that slide to one colleague and time yourself: if you cannot deliver it in two minutes, the slide is carrying too much.
- Bring to b10: one thing about your company's BI stack you have always wondered about - the finale zooms out to the whole modern stack.
Official sources covered
The capstone applies material rather than introducing it - this is the "perform analytics" layer of every major BI curriculum, exercised on a real investigation:
Three questions before you go 🎯 ◐ 90 seconds
1 · A stakeholder reports "revenue fell last month, find out why". Your first move?
Confirm before you hunt. Definition drift, refunds inside totals, and data problems explain a huge share of "drops" - and every hour spent explaining a ghost is wasted. Branch one of the tree, always.
2 · Why decompose a revenue dip into order count x basket size before drilling further?
Revenue = orders x AOV. A demand problem and a basket problem route to different teams. March had both forces - stopping at "fewer orders" would have shipped half an answer.
3 · What actually caused Daybreak's March dip?
No single villain. The refund distorted the number, the churned subscriber (Liam Ford, Basic) removed recurring orders, and remaining baskets shrank. Multi-cause dips are the norm - which is exactly why the tree checks every branch.