Leading indicators & the playbook

You can now build a tree (b1) and name its shape - product, sum, or bridge (b2). But a tree at rest is just a diagram. Its value shows up the morning a number is down and everyone is looking at you. This session gives you the two things that turn a static tree into a diagnostic reflex: leading indicators that warn you early from near the leaves, and a four-step playbook you run the same way every single time. By the end you will have diagnosed a live GMV drop end to end - tree, then SQL, then a named cause.

A top-line metric is lagging - it reports after the fact. A leading indicator is a driver that moves first, giving you lead time to act before the lagging number lands. But not every early number is useful. A good leading indicator passes three tests: it is predictive (it correlates with the future outcome), early (it moves with real lead time, not the same day), and controllable (a team can actually push it). Miss any one and you have a distraction, not a signal.

When a top line drops, panic makes people freelance - they jump to a favourite theory and go hunting for confirming numbers. The playbook replaces that with a fixed order of four questions, run the same way every time: is it real, which branch, which segment, cause confirmed? The order matters. Skip "is it real?" and you fire-drill over noise. Skip "which branch?" and you segment the wrong driver. Discipline in the sequence is the whole edge.

{
  "unit": "$",
  "root": {
    "label": "GMV", "op": "x",
    "children": [
      { "label": "Traffic",    "value": 120000, "unit": "visits" },
      { "label": "Conversion", "value": 0.028,  "pct": true },
      { "label": "AOV",        "value": 62,     "unit": "$" }
    ]
  }
}

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;

Step 1 of the playbook - "is it real?" - lives and dies on the baseline you compare against. Week-over-week catches fast breaks but trips on weekly seasonality. Year-over-year controls for seasonality but hides recent trend. Versus-plan tells you if you are off target but not why. The classic trap is comparing a Monday to a Saturday and declaring a crisis. Pick the baseline that matches the metric's natural rhythm before you call anything a drop.

WITH monthly AS (
  SELECT substr(o.order_date, 1, 7)          AS month,
         SUM(oi.quantity * oi.unit_price)     AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  WHERE o.status = 'completed'
  GROUP BY substr(o.order_date, 1, 7)
)
SELECT month,
       ROUND(revenue, 0)                                      AS revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 0) AS vs_prior_month
FROM monthly
ORDER BY month;

Now all four questions in one pass. Simulate a drop on the tree (steps 1 and 2), then segment the guilty driver in SQL (step 3), then read the outlier and name a cause (step 4). This is exactly the sequence you will run the next time a real dashboard is red - practise it until the order is muscle memory.

{
  "unit": "$",
  "root": {
    "label": "GMV", "op": "x",
    "children": [
      { "label": "Traffic",    "value": 120000, "unit": "visits" },
      { "label": "Conversion", "value": 0.028,  "pct": true },
      { "label": "AOV",        "value": 62,     "unit": "$" }
    ]
  }
}

SELECT o.channel,
       COUNT(DISTINCT o.order_id)                 AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
       ROUND(SUM(oi.quantity * oi.unit_price) * 1.0
             / COUNT(DISTINCT o.order_id), 2)     AS avg_order_value
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;

Two live queries and one prose answer. First check whether a drop is concentrated in one channel, then check this period against a baseline, then define a leading indicator for a metric you actually own.

SELECT channel,
       COUNT(*)                                              AS all_orders,
       COUNT(*) FILTER (WHERE status IN ('refunded','cancelled')) AS lost_orders,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status IN ('refunded','cancelled'))
             / COUNT(*), 1)                                  AS lost_rate_pct
FROM orders
GROUP BY channel
ORDER BY lost_rate_pct DESC;

SELECT substr(o.order_date, 1, 7)               AS month,
       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 substr(o.order_date, 1, 7)
ORDER BY month;

• Write your team's diagnosis playbook as four named steps on one card. Pin it where the metric review happens. Next red number, follow it in order out loud.
• For your top-line metric, list two candidate leading indicators and score each on predictive / early / controllable. Keep the one that passes all three.
• Decide the right baseline for each of your dashboards - WoW, YoY, or vs plan - and write it next to the chart so nobody argues the wrong comparison.
• Find one place your dashboard shows a drop with no baseline. Add the comparison. Half of "the number is down" panics die there.
• Bring a real ecommerce-shaped metric to b4 - we go two levels deep on the full GMV tree and diagnose the classic ecommerce drops.

Leading indicators and the diagnosis runbook are distilled from a handful of durable frameworks, applied here live instead of described. This page draws on: