FOR YOUR ANALYST — The Whole Machine

Every pull below runs on an orders table, an order-lines table, a refunds table and a customers table. Resolve identity first: customer ID, then normalized email, then phone. A guest who later made an account is one customer. Counting them twice turns a repeat buyer into two one-time buyers and breaks every cohort number.

The data contract

Field	On	Rule
`first_order_at`	Customer	Date of the first paid order. Set once, never overwritten.
`first_products`	Customer	Product IDs in the first order.
`is_first_order`	Order	True only for the customer’s first paid order.
`net_revenue`	Order	Paid, after discounts, plus shipping charged, before tax.
`cm2`	Order	Net revenue minus product cost, pick and pack, shipping paid, payment fees and refunds, from finance’s cost tables.
`sales_channel`	Order	Own site, marketplace, retail, wholesale. Cohort work uses own site unless stated.
`holdout_digit`	Customer	Random 0 to 9, set once at profile creation, used to hold out flows and campaigns.

Five pulls

The Monday lineFor each week: new customers, total marketing spend, revenue from first orders, total revenue, average CM2 on first orders. Divide to get MER, new-customer MER and new-customer cost.
CM2 by first productFor first orders in the last 12 months: count, average net revenue and average CM2 by first product, ranked by count.
The cohort tableCustomers grouped by first-order month; cumulative CM2 per customer at months 0, 1, 3, 6, 12 and 24. Leave a cell blank when the cohort isn’t old enough to fill it.
ConcentrationAll customers ranked by lifetime net revenue: share of revenue from the top 1%, 10% and 20%, and the minimum lifetime spend to enter each.
The step-upCustomers grouped by order count (1, 2, 3 to 5, 6 or more): customers, average lifetime net revenue, share of revenue. The step-up is the average for 2 minus the average for 1.

The cohort table in one query

-- cumulative CM2 per customer by months since first order
WITH firsts AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(ordered_at)) AS cohort
  FROM orders WHERE status = 'paid' AND sales_channel = 'own_site'
  GROUP BY customer_id
),
ages AS (
  SELECT f.cohort, f.customer_id, o.cm2,
         DATE_DIFF('month', f.cohort, DATE_TRUNC('month', o.ordered_at)) AS m
  FROM firsts f JOIN orders o USING (customer_id)
  WHERE o.status = 'paid'
),
sizes AS (SELECT cohort, COUNT(*) AS customers FROM firsts GROUP BY cohort)
SELECT a.cohort, s.customers, k.m AS months,
       SUM(CASE WHEN a.m <= k.m THEN a.cm2 ELSE 0 END) / s.customers AS cum_cm2_per_customer
FROM ages a
JOIN sizes s USING (cohort)
CROSS JOIN (SELECT 0 AS m UNION ALL SELECT 1 UNION ALL SELECT 3
            UNION ALL SELECT 6 UNION ALL SELECT 12 UNION ALL SELECT 24) k
WHERE DATE_DIFF('month', a.cohort, DATE_TRUNC('month', CURRENT_DATE)) >= k.m
GROUP BY a.cohort, s.customers, k.m
ORDER BY a.cohort, k.m;

The WHERE line is the one that stops the window trap from chapter 20: a cohort only reports a month it has lived through. Check it by eye. Reading down any column, the youngest cohorts should be missing, not zero.

This is one chapter of The Whole Machine, which is free and readable in full on a single page with no form in front of it.