Explaining Metric Drops

Technical Explanation

When metrics change significantly, stakeholders want to know why. A good explanation provides context, rules out false causes, and identifies likely root causes.

Investigation Framework

Step	Action
1. Verify	Confirm the drop is real
2. Quantify	How big is the change?
3. Segment	Which parts are affected?
4. Correlate	What else happened?
5. Conclude	Root cause + confidence

Good Explanations Include

• Magnitude of change (specific numbers)
• Timeframe of change
• Scope (all segments or specific ones)
• Likely cause with evidence
• Recommended action

---

Code Examples

Using the CatCafe dataset:

-- Step 1: Quantify the drop
WITH current_period AS (
    SELECT SUM(total_amount) as revenue
    FROM orders
    WHERE status = 'completed'
    AND order_date >= CURRENT_DATE - INTERVAL '7 days'
),
previous_period AS (
    SELECT SUM(total_amount) as revenue
    FROM orders
    WHERE status = 'completed'
    AND order_date >= CURRENT_DATE - INTERVAL '14 days'
    AND order_date < CURRENT_DATE - INTERVAL '7 days'
)
SELECT
    current_period.revenue as current_revenue,
    previous_period.revenue as previous_revenue,
    current_period.revenue - previous_period.revenue as change,
    (current_period.revenue - previous_period.revenue) * 100.0
        / NULLIF(previous_period.revenue, 0) as change_pct
FROM current_period, previous_period;

-- Step 2: Segment by dimension
SELECT
    segment,
    SUM(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '7 days'
        THEN total_amount ELSE 0 END) as current_period,
    SUM(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '14 days'
        AND order_date < CURRENT_DATE - INTERVAL '7 days'
        THEN total_amount ELSE 0 END) as previous_period,
    SUM(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '7 days'
        THEN total_amount ELSE 0 END) -
    SUM(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '14 days'
        AND order_date < CURRENT_DATE - INTERVAL '7 days'
        THEN total_amount ELSE 0 END) as change
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY segment
ORDER BY change;

-- Step 3: Check for correlating events
SELECT
    DATE_TRUNC('day', event_date) as date,
    event_type,
    COUNT(*) as occurrences
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '14 days'
AND event_type IN ('site_outage', 'price_change', 'new_campaign', 'marketing_email')
GROUP BY DATE_TRUNC('day', event_date), event_type
ORDER BY date DESC;

---

The Cat Analogy

When cat weight drops, you investigate systematically:

Metric: Whiskers lost 2 lbs this month

Bad explanation:
  "The cat lost weight."

Good explanation:
  "Whiskers dropped from 11 lbs to 9 lbs (-18%) this month.
   This is concerning because:
   - Weight loss is isolated to Whiskers (other cats stable)
   - Food intake unchanged (not appetite issue)
   - Likely cause: Recent move to new home (stress)
   - Action: Monitor for 2 more weeks, if continues, vet visit
   Confidence: High (behavioral cause identified)"

---

Exercises

Exercise 1

What information should a good metric drop explanation include?

Exercise 2

Write a query to segment a revenue drop by customer acquisition channel.

Exercise 3

How would you distinguish between a real business problem vs a data quality issue when a metric changes?

---

Key Takeaways

• Quantify the change precisely
• Segment to find scope
• Check for correlating events
• Distinguish real issues from data problems
• Provide context and confidence level