FOR YOUR ANALYST

The formulas

Weekly series, with limits

-- weekly revenue and orders, excluding promotion weeks from the baseline
-- orders: order_id, created_at, total_price, cancelled_at
-- promo_weeks: week_start (one row per promotion week)
WITH weekly AS (
  SELECT DATE_TRUNC('week', created_at)::date AS week_start,
         COUNT(*)                            AS orders,
         SUM(total_price)                    AS revenue
  FROM orders
  WHERE cancelled_at IS NULL
  GROUP BY 1
),
base AS (
  SELECT w.*, ABS(revenue - LAG(revenue) OVER (ORDER BY week_start)) AS mr
  FROM weekly w
  WHERE week_start NOT IN (SELECT week_start FROM promo_weeks)
    AND week_start < DATE_TRUNC('week', CURRENT_DATE)
  ORDER BY week_start DESC
  LIMIT 12
)
SELECT AVG(revenue)                     AS central_line,
       AVG(revenue) - 2.66 * AVG(mr)    AS lower_limit,
       AVG(revenue) + 2.66 * AVG(mr)    AS upper_limit,
       3.268 * AVG(mr)                  AS moving_range_limit
FROM base;

The moving ranges here are computed before the twelve weeks are chosen, so the oldest week’s range reaches back one normal week; that’s fine. If a promotion week sits between two normal weeks, the range spans it, which is also fine. Swap revenue for orders or a rate to chart the others. Refunds belong in a separate series from the refunds table, not netted into revenue, so a returns problem shows on its own chart.

Campaign spread, for the estimator

-- last 30 email campaigns: order rate per recipient, its spread, typical audience
-- campaign_sends: campaign_id, channel, sent_at, recipients
-- campaign_orders: campaign_id, order_id (orders attributed to the campaign)
WITH c AS (
  SELECT s.campaign_id, s.recipients,
         COUNT(o.order_id)::numeric / NULLIF(s.recipients, 0) AS rate
  FROM campaign_sends s
  LEFT JOIN campaign_orders o USING (campaign_id)
  WHERE s.channel = 'email'
  GROUP BY s.campaign_id, s.recipients, s.sent_at
  ORDER BY s.sent_at DESC
  LIMIT 30
)
SELECT AVG(rate) * 100          AS avg_rate_pct,
       STDDEV_SAMP(rate) * 100  AS spread_pts,
       AVG(recipients)          AS typical_audience
FROM c;

Use the same attribution window for every campaign. Mixing sends to the whole list with sends to small segments inflates the spread; run it separately for each.

Launch reference class

-- first-90-day revenue against plan, for every launch
-- launches: launch_id, product_id, launched_on, plan_revenue_90d
-- order_lines: order_id, product_id, price, quantity; orders: order_id, created_at
WITH actual AS (
  SELECT l.launch_id, l.plan_revenue_90d,
         SUM(ol.price * ol.quantity) AS revenue_90d
  FROM launches l
  JOIN order_lines ol ON ol.product_id = l.product_id
  JOIN orders o       ON o.order_id = ol.order_id
                     AND o.created_at >= l.launched_on
                     AND o.created_at <  l.launched_on + INTERVAL '90 days'
  WHERE l.launched_on < CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
)
SELECT PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY revenue_90d) AS p20,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue_90d) AS median,
       PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY revenue_90d) AS p80,
       PERCENTILE_CONT(0.5) WITHIN GROUP
         (ORDER BY revenue_90d / NULLIF(plan_revenue_90d, 0))    AS median_actual_to_plan,
       CORR(LN(plan_revenue_90d), LN(revenue_90d))              AS forecast_tracking
FROM actual
WHERE revenue_90d > 0 AND plan_revenue_90d > 0;

forecast_tracking is the tracking figure for the tool in chapter 9. With fewer than about ten launches it’s rough; round it down.

Rank persistence

-- does last quarter's ranking by cost per first order hold this quarter?
-- channel_quarters: channel, quarter, spend, first_orders
WITH r AS (
  SELECT channel, quarter,
         RANK() OVER (PARTITION BY quarter
                      ORDER BY spend / NULLIF(first_orders, 0)) AS rnk
  FROM channel_quarters
  WHERE quarter IN ('2026-Q1', '2026-Q2') AND first_orders >= 30
)
SELECT CORR(a.rnk, b.rnk) AS rank_persistence
FROM r a
JOIN r b ON a.channel = b.channel
WHERE a.quarter = '2026-Q1' AND b.quarter = '2026-Q2';

The same query works for creators, ad sets or campaign types; change the table. The first-orders floor keeps tiny channels, whose rank is almost pure chance, out of the comparison.