Appendix A

FOR YOUR ANALYST

The fields the machine needs, and five pulls that build every table in this book.

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

FieldOnRule
first_order_atCustomerDate of the first paid order. Set once, never overwritten.
first_productsCustomerProduct IDs in the first order.
is_first_orderOrderTrue only for the customer’s first paid order.
net_revenueOrderPaid, after discounts, plus shipping charged, before tax.
cm2OrderNet revenue minus product cost, pick and pack, shipping paid, payment fees and refunds, from finance’s cost tables.
sales_channelOrderOwn site, marketplace, retail, wholesale. Cohort work uses own site unless stated.
holdout_digitCustomerRandom 0 to 9, set once at profile creation, used to hold out flows and campaigns.

Five pulls

  1. 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.
  2. CM2 by first productFor first orders in the last 12 months: count, average net revenue and average CM2 by first product, ranked by count.
  3. 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.
  4. 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.
  5. 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.

If this is your store

See where your first-time buyers stall.

The free Klaviyo audit reads your own account and scores it. Start with your store’s address. A read-only key gets the full report.

Get the free audit →

Written by Andrew Lauchner, a growth and retention operator for consumer brands. The paid work is one ninety-day Sprint.

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.