FOR YOUR ANALYST

The formulas

Share of wallet, from the survey

-- survey_responses: customer_id, category_spend_12m, brands_used, our_rank
WITH ours AS (
  SELECT o.customer_id, SUM(ol.net_amount) AS our_spend_12m
  FROM orders o
  JOIN order_lines ol ON ol.order_id = o.id
  JOIN products p     ON p.id = ol.product_id
  WHERE p.category = 'coffee'                      -- the hero's category
    AND o.created_at >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY o.customer_id
)
SELECT s.our_rank, s.brands_used, COUNT(*) AS customers,
       AVG(LEAST(1, COALESCE(ours.our_spend_12m, 0)
                    / NULLIF(s.category_spend_12m, 0)))     AS actual_share,
       AVG((1 - s.our_rank::numeric / (s.brands_used + 1))
           * (2.0 / s.brands_used))                         AS rule_share,
       SUM(s.category_spend_12m - COALESCE(ours.our_spend_12m, 0)) AS spend_elsewhere
FROM survey_responses s
LEFT JOIN ours USING (customer_id)
WHERE s.brands_used BETWEEN 1 AND 20
  AND s.our_rank BETWEEN 1 AND s.brands_used
GROUP BY s.our_rank, s.brands_used
ORDER BY s.brands_used, s.our_rank;

If actual and rule shares roughly agree across rows, rank is a usable tracker. Stated spend is noisy: use medians in the calculator.

The selection check

-- groups customers by when they first bought outside their first category
WITH lines AS (
  SELECT o.customer_id, o.created_at, p.category,
         ol.net_amount - COALESCE(rf.amount, 0) AS net
  FROM orders o
  JOIN order_lines ol ON ol.order_id = o.id
  JOIN products p     ON p.id = ol.product_id
  LEFT JOIN (SELECT order_line_id, SUM(amount) AS amount
             FROM refunds GROUP BY order_line_id) rf
         ON rf.order_line_id = ol.id
),
firsts AS (
  SELECT customer_id, MIN(created_at) AS first_at FROM orders GROUP BY customer_id
),
first_cat AS (
  SELECT DISTINCT ON (l.customer_id) l.customer_id, l.category
  FROM lines l JOIN firsts f USING (customer_id)
  WHERE l.created_at = f.first_at
  ORDER BY l.customer_id, l.net DESC
),
crossed AS (
  SELECT l.customer_id, MIN(l.created_at) AS crossed_at
  FROM lines l JOIN first_cat c USING (customer_id)
  WHERE l.category <> c.category
  GROUP BY l.customer_id
),
per AS (
  SELECT f.customer_id, f.first_at, x.crossed_at,
         SUM(l.net) FILTER (WHERE l.created_at < f.first_at + INTERVAL '180 days') AS early,
         SUM(l.net) FILTER (WHERE l.created_at >= f.first_at + INTERVAL '365 days'
                              AND l.created_at <  f.first_at + INTERVAL '730 days') AS outcome
  FROM firsts f
  JOIN lines l USING (customer_id)
  LEFT JOIN crossed x USING (customer_id)
  WHERE f.first_at < CURRENT_DATE - INTERVAL '730 days'
  GROUP BY f.customer_id, f.first_at, x.crossed_at
),
grouped AS (
  SELECT *, CASE
    WHEN crossed_at IS NULL OR crossed_at >= first_at + INTERVAL '730 days' THEN 'single'
    WHEN crossed_at >= first_at + INTERVAL '180 days'
     AND crossed_at <  first_at + INTERVAL '365 days' THEN 'multi'
  END AS grp
  FROM per
)
SELECT grp, COUNT(*) AS customers,
       AVG(COALESCE(early, 0))   AS early_spend,     -- days 0 to 179
       AVG(COALESCE(outcome, 0)) AS outcome_spend    -- days 365 to 729
FROM grouped
WHERE grp IS NOT NULL
GROUP BY grp;

Customers who crossed in their first 180 days, or in the outcome year, are left out, so both groups’ early windows are clean. The four averages go into the chapter 7 calculator. Syntax is Postgres.

Contribution per customer

-- last 12 months. order_contribution: one row per order, with customer_id,
-- created_at, categories (array), and contribution = revenue after discounts
-- and refunds, minus product, fulfillment, shipping, payment and return costs.
SELECT c.customer_id,
       (SELECT COUNT(DISTINCT cat)
          FROM order_contribution c2, unnest(c2.categories) AS cat
         WHERE c2.customer_id = c.customer_id
           AND c2.created_at >= CURRENT_DATE - INTERVAL '12 months') AS categories,
       SUM(c.contribution)
         - COALESCE(MAX(t.tickets), 0) * :cost_per_ticket AS contribution
FROM order_contribution c
LEFT JOIN (SELECT customer_id, COUNT(*) AS tickets FROM support_tickets
           WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
           GROUP BY customer_id) t USING (customer_id)
WHERE c.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY c.customer_id;

Wrap it to count customers with negative contribution among those with two or more categories, and their share of all negative contribution. That’s your version of Shah and colleagues’ 10% to 35% and 39% to 88%.

The holdout readout

-- flow_assignments: customer_id, arm ('treat' or 'holdout'), qualified_at
WITH w AS (
  SELECT a.customer_id, a.arm,
         BOOL_OR('cold_brew' = ANY(c.categories)) AS bought_new,
         COALESCE(SUM(c.contribution), 0)        AS contribution
  FROM flow_assignments a
  LEFT JOIN order_contribution c
    ON c.customer_id = a.customer_id
   AND c.created_at >= a.qualified_at
   AND c.created_at <  a.qualified_at + INTERVAL '90 days'
  WHERE a.qualified_at < CURRENT_DATE - INTERVAL '90 days'
  GROUP BY a.customer_id, a.arm
)
SELECT arm, COUNT(*) AS customers,
       AVG(COALESCE(bought_new, false)::int) AS bought_new_category,
       AVG(contribution)                     AS contribution_per_customer
FROM w GROUP BY arm;

Count every assigned customer, including those who never opened a message. Comparing openers with the holdout brings the selection problem back.