Lesson 5 of 5 · 10 min · Putting it together
Diagnosing a metric drop, step by step
What you will learn
- A seven-step method for diagnosing any metric drop
- Check data quality and definitions before looking for causes
- Decompose a metric into parts with a metric tree
- Segment to find where the drop is concentrated
- Present the finding with evidence and a recommendation
In simple words
Your manager says, "Orders fell 20 percent this week. Why?" Panic makes people guess. A good analyst works like a doctor: first check the instruments are working, then narrow down where the problem is, then look for what changed there, and only then say what the cause probably is. This lesson gives that method, with a worked example.
Words to know
- Metric tree: a breakdown of a metric into the parts that multiply or add up to it.
- Segment: a slice of the data, such as mobile users or one city.
- Mix shift: a change in the proportions of segments that moves the overall number.
- Hypothesis: an explanation you can test with data.
The method
- Confirm and size the drop. Is it real, how big, since when?
- Check the data and the definition. Is the pipeline healthy? Did the definition change?
- Decompose the metric with a metric tree.
- Segment the part that moved.
- Look for events around the start of the drop.
- Test hypotheses and rule them out.
- Report the cause, the evidence, the confidence and the next action.
Step 1: confirm and size the drop
Ask: compared with what? Week on week, or the same week last year (to allow for seasonality)? Is the drop larger than the usual ups and downs? Plot at least 8 to 12 weeks, and mark holidays and campaigns. A 20 percent fall in a metric that normally varies by 15 percent is a different story from one that normally varies by 2 percent. Note the exact start date and whether the fall was a sudden step (a release or outage), a gradual slide (a slow problem) or a one-off dip.
Step 2: check the data and the definition
Most "drops" in real life are data problems, so check before investigating the business:
- Did a data pipeline fail or lag? Compare against a raw source (payment gateway totals, server logs).
- Is the latest period incomplete (today's data, late-arriving events)?
- Did tracking break (a new app version stopped sending the event)? Look for an event that vanished from one platform.
- Did someone change the definition or filter (new test-user exclusion, new timezone)?
- Is it a duplicate or missing-row problem after a join (see the SQL course on fan-out)?
A fast cross-check: do two independent sources show the same drop? If payments in the gateway dashboard are flat but your table fell, the problem is in your data, not in the business.
Step 3: decompose with a metric tree
Break the metric into a formula and see which part moved:
Revenue = Sessions x Conversion rate x Average order value
Worked example. Weekly numbers:
| Last week | This week | Change | |
|---|---|---|---|
| Sessions | 40,000 | 40,000 | 0% |
| Orders | 2,000 | 1,600 | -20% |
| Conversion (orders / sessions) | 5.0% | 4.0% | -1.0 point |
| Average order value | Rs 500 | Rs 500 | 0% |
| Revenue | Rs 10,00,000 | Rs 8,00,000 | -20% |
Traffic and order value did not change; the drop is entirely in conversion. That immediately rules out "marketing brought less traffic" and "customers are buying cheaper items" and focuses the work. If instead sessions had fallen, you would investigate traffic sources.
Step 4: segment where it moved
Break the changed part down by channel, device, platform, city, product, new versus returning, plan, and payment method. Look for a segment where the fall is concentrated, and for segments that did not fall (they are your control group).
| Segment | Sessions (both weeks) | Orders last week | Orders this week | Conversion last | Conversion this |
|---|---|---|---|---|---|
| Desktop | 10,000 | 600 | 600 | 6.0% | 6.0% |
| Mobile | 30,000 | 1,400 | 1,000 | 4.7% | 3.3% |
Desktop is flat; all the lost orders (400) are on mobile. Now narrow again: mobile web or the app? Android or iOS? Which app version? Which city or network? Keep drilling until the segment is small and specific, like "Android app version 4.2 users on the checkout payment step".
In SQL:
SELECT s.device,
DATE_TRUNC('week', s.session_date)::date AS week,
COUNT(DISTINCT s.session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
ROUND(100.0 * COUNT(DISTINCT o.order_id) / COUNT(DISTINCT s.session_id), 2) AS conversion_pct
FROM sessions s
LEFT JOIN orders o ON o.session_id = s.session_id
WHERE s.session_date >= '2025-10-06' AND s.session_date < '2025-10-20'
GROUP BY s.device, DATE_TRUNC('week', s.session_date)
ORDER BY s.device, week;
Beware mix shift: an overall rate can fall just because the share of low-converting traffic grew (more mobile visitors from a new campaign) even if each segment's rate is unchanged. Compare rates within segments, and separate "rate changed" from "mix changed".
Step 5: look for events
When did it start, to the day (or hour)? Line up a timeline of everything that changed:
- Releases and feature flags (app, website, backend).
- Outages and slow responses (error rates, payment failures, page load times).
- Pricing, offers, fees and policies.
- Marketing campaigns starting or ending, a change in ad targeting.
- External: holidays and festivals, exams, competitor launches, weather, news, regulation, payment-provider issues.
- Data and tracking changes.
If the drop begins in the same hour as the Android 4.2 rollout and affects only that version, you have a strong lead.
Step 6: test hypotheses
Write each explanation as a testable claim and check what the data would look like if it were true:
| Hypothesis | If true, we would see | Result |
|---|---|---|
| Traffic quality fell | lower conversion across all channels | Not seen: desktop and web flat |
| Payment failures on Android | higher failed-payment rate on the new version only | Seen: failures 2% to 28% on 4.2 |
| Pricing change | order value or conversion change on all platforms | Not seen |
| Tracking broke | sessions or events missing, orders unchanged in the payment gateway | Not seen: gateway orders match |
Keep a note of what you ruled out as well as what you found: it stops the same suggestions returning. Where possible, confirm with a second source (logs, support tickets, user reports) and, after the fix, check that the metric recovers.
Step 7: report it
A clear one-paragraph summary beats a long deck:
What happened: Weekly orders fell 20% (2,000 to 1,600) starting 14 October. Where: Sessions and order value are unchanged; the entire drop is in mobile conversion (4.7% to 3.3%), concentrated on Android app version 4.2. Why (likely): payment failures on that version rose from 2% to 28% after the 14 October release; server logs show timeouts calling the payment SDK. Confidence: high for the location, medium for the cause until engineering confirms. Impact: about Rs 2,00,000 of revenue lost per week. Next: roll back or hotfix 4.2; I will monitor failed-payment rate and conversion daily and report recovery.
Include the chart that shows the step change and the segment comparison, the size of the impact, what you ruled out, your confidence, and one recommended action with an owner.
Common mistakes
- Guessing a cause before checking the data and definitions.
- Looking only at the overall number and never segmenting.
- Ignoring seasonality and normal variation.
- Confusing mix shift with a rate change.
- Stopping at the first plausible story without ruling out alternatives.
- Presenting findings without impact, confidence or a next step.
Try it yourself
- Take the revenue formula above and invent a drop in each of the three parts; for each, list the segments you would check.
- Build a metric tree for a metric you know (monthly active students, support tickets).
- Using any dataset with a date and a category, find a week where a metric fell and locate the segment where it was concentrated.
- Write the one-paragraph report for a drop you analysed, including confidence and next action.
Check yourself
What should you check first when a metric suddenly drops?
Whether the data or definition is wrong: pipeline failures, incomplete periods, tracking changes or a changed filter.
What is a metric tree for?
Breaking a metric into parts that multiply or add up to it, so you can see which part changed.
What is a mix shift?
A change in the proportions of segments that moves the overall metric even if each segment's own rate stayed the same.
Why keep segments that did not drop?
They act as a control group and help rule out explanations that would affect everyone.
Interview questions
Daily active users dropped 10 percent. How would you investigate?
Confirm and size the change versus normal variation and seasonality, check data and tracking, break DAU into new, returning and resurrected users, segment by platform, city and version, line up releases, campaigns and outages, test hypotheses, and report the cause, impact and recommendation.
Conversion is down but traffic and order value are stable. What does that suggest?
The issue is somewhere in the steps between visit and purchase; segment by device, channel and funnel step to find where users are dropping out, and check for technical problems such as payment failures.
How do you distinguish a data issue from a real business problem?
Reconcile with an independent source such as payment gateway totals or raw logs, check for missing events or recently changed definitions, and see whether the drop appears in all related metrics.
Summary
- Confirm the drop, check data and definitions, decompose, segment, look for events, test hypotheses, report.
- A metric tree shows which component moved; segments show where and for whom.
- Watch for mix shifts, seasonality and normal variation; use unaffected segments as a control.
- Report the cause, evidence, impact, confidence and one recommended action.
Going deeper
- Handling overload and cascading failures (Google SRE Book): a systematic approach to isolating where a system degrades.
- PostgreSQL aggregate expressions: conditional counts for segment comparisons.
- pandas groupby: segment breakdowns in Python.
See how much of this you can explain
A free mock interview asks you new questions on Metrics, KPIs and business analysis for Data Analyst and scores every answer, with what to fix.