FOR YOUR ANALYST

The formulas

Repeat share by product

-- orders containing each product in the last 90 days, and the share
-- placed by customers who had ordered before
WITH ranked AS (
  SELECT o.order_id, o.customer_id, o.created_at,
         ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.created_at) AS nth
  FROM orders o
  WHERE o.cancelled_at IS NULL
)
SELECT ol.product_id,
       COUNT(DISTINCT r.order_id)                               AS orders,
       COUNT(DISTINCT r.order_id) FILTER (WHERE r.nth > 1)       AS repeat_orders,
       ROUND(COUNT(DISTINCT r.order_id) FILTER (WHERE r.nth > 1)::numeric
             / NULLIF(COUNT(DISTINCT r.order_id), 0), 3)        AS repeat_share
FROM ranked r
JOIN order_lines ol ON ol.order_id = r.order_id
WHERE r.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY ol.product_id
ORDER BY repeat_orders DESC;

Sort by repeat orders, not repeat share: a product with a 95% repeat share and ten orders a quarter isn’t where the exposure is. Joined to daily stock snapshots, the same numbers give the repeat-weighted in-stock rate in the table above.

Dependents of one product

-- customers who pass any of the three tests for :product_id
WITH spend AS (
  SELECT o.customer_id,
         SUM(ol.price * ol.quantity)                                          AS total,
         SUM(ol.price * ol.quantity) FILTER (WHERE ol.product_id = :product_id) AS this,
         COUNT(DISTINCT o.order_id)  FILTER (WHERE ol.product_id = :product_id) AS orders_with
  FROM orders o
  JOIN order_lines ol ON ol.order_id = o.order_id
  WHERE o.cancelled_at IS NULL
    AND o.created_at >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY o.customer_id
),
subs AS (
  SELECT DISTINCT customer_id FROM subscriptions
  WHERE product_id = :product_id AND status = 'active'
)
SELECT s.customer_id,
       (s.orders_with >= 2)                        AS reorders_it,
       (s.this >= 0.5 * s.total)                   AS most_of_spend,
       (sb.customer_id IS NOT NULL)                AS subscribes,
       ((s.orders_with >= 2)::int + (s.this >= 0.5 * s.total)::int
         + (sb.customer_id IS NOT NULL)::int)      AS tests_passed
FROM spend s
LEFT JOIN subs sb ON sb.customer_id = s.customer_id
WHERE s.this > 0
ORDER BY tests_passed DESC, s.this DESC;

Two or more tests passed is a core dependent; one is a dependent; zero is a trier. Export it as a segment, with consent status, before any message goes out.

What a stockout did, against a comparison group

-- exposed: bought the stocked-out product in the 180 days before it ran out
-- comparison: bought a similar product that stayed in stock, same window,
--             and not the stocked-out one
-- :out_date is the first day at zero stock
SELECT grp,
       COUNT(*)                                          AS customers,
       AVG((next_order IS NOT NULL)::int)                AS ordered_within_90d
FROM (
  SELECT c.customer_id, c.grp,
         (SELECT MIN(o.created_at) FROM orders o
           WHERE o.customer_id = c.customer_id
             AND o.cancelled_at IS NULL
             AND o.created_at >= :out_date
             AND o.created_at <  :out_date + INTERVAL '90 days') AS next_order
  FROM stockout_groups c   -- customer_id, grp ('exposed' or 'comparison')
) t
GROUP BY grp;

Match the groups roughly on how recently and how often they’d ordered. The gap between the two rates replaces the 22% default in the stockout tool. After a retirement or reformulation, use the announcement date and the dependents as the exposed group.

Basket role of each product

-- for each product: how often its buyers buy it again, how often it's in a
-- first order, and the contribution of the other items in its orders
WITH ranked AS (
  SELECT order_id, customer_id,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth
  FROM orders
  WHERE cancelled_at IS NULL AND created_at >= CURRENT_DATE - INTERVAL '12 months'
),
lines AS (
  SELECT r.order_id, r.customer_id, r.nth, ol.product_id, ol.contribution
  FROM ranked r JOIN order_lines ol ON ol.order_id = r.order_id
)
SELECT p.product_id,
       COUNT(DISTINCT p.customer_id)                                   AS buyers,
       ROUND(COUNT(DISTINCT p.customer_id) FILTER (WHERE pc.n_orders >= 2)::numeric
             / COUNT(DISTINCT p.customer_id), 3)                     AS reorder_rate,
       ROUND(AVG((p.nth = 1)::int)::numeric, 3)                         AS first_order_share,
       ROUND(AVG(other.contrib)::numeric, 2)                            AS other_items_contribution
FROM lines p
JOIN (SELECT customer_id, product_id, COUNT(DISTINCT order_id) AS n_orders
      FROM lines GROUP BY customer_id, product_id) pc
  ON pc.customer_id = p.customer_id AND pc.product_id = p.product_id
LEFT JOIN LATERAL (
  SELECT COALESCE(SUM(l2.contribution), 0) AS contrib
  FROM lines l2 WHERE l2.order_id = p.order_id AND l2.product_id <> p.product_id
) other ON TRUE
GROUP BY p.product_id
ORDER BY buyers DESC;

The syntax is Postgres. contribution is price less discounts, cost of goods and a share of fulfillment; if you don’t have it, use revenue times your average margin. The roles are in chapter 12.