The formulas

Add-on outcomes by checkout version

-- orders in a split test of the add-on box: ticked vs unticked
-- order_lines: sku 'PKG-PROTECT' marks the add-on
-- checkout_variants: order_id, variant ('ticked' / 'unticked')
-- tickets: one row per ticket, with order_id and body
SELECT v.variant,
       COUNT(DISTINCT o.order_id)                               AS orders,
       COUNT(DISTINCT ol.order_id)                              AS took_addon,
       COUNT(DISTINCT r.order_id)                               AS addon_refunded,
       COUNT(DISTINCT t.order_id)                               AS addon_tickets,
       COUNT(DISTINCT d.order_id)                               AS disputes
FROM orders o
JOIN checkout_variants v ON v.order_id = o.order_id
LEFT JOIN order_lines ol ON ol.order_id = o.order_id AND ol.sku = 'PKG-PROTECT'
LEFT JOIN refunds r      ON r.order_id = o.order_id AND r.line_sku = 'PKG-PROTECT'
LEFT JOIN tickets t      ON t.order_id = o.order_id
                        AND t.body ILIKE '%protection%'
LEFT JOIN disputes d     ON d.order_id = o.order_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '35 days'
GROUP BY v.variant;

Default-only takers are the difference in took_addon between variants. Divide the difference in refunds and tickets by it to get the complaint rate for the tool in chapter 6. Allow a week for late tickets and disputes.

First-renewal outcome by arrival

-- subscriptions: arrival = 'preselected' or 'chose' (tag at checkout)
-- subscription_charges: one row per renewal attempt, with charge_number
SELECT s.arrival,
       COUNT(*)                                                     AS subscribers,
       AVG(CASE WHEN s.canceled_at < c.scheduled_at THEN 1 ELSE 0 END) AS canceled_before_first_renewal,
       AVG(CASE WHEN c.status = 'paid' THEN 1 ELSE 0 END)           AS first_renewal_paid,
       AVG(CASE WHEN c.refunded THEN 1 ELSE 0 END)                  AS first_renewal_refunded,
       AVG(CASE WHEN c.disputed THEN 1 ELSE 0 END)                  AS first_renewal_disputed
FROM subscriptions s
LEFT JOIN subscription_charges c
  ON c.subscription_id = s.subscription_id AND c.charge_number = 2
WHERE s.created_at BETWEEN CURRENT_DATE - INTERVAL '150 days'
                       AND CURRENT_DATE - INTERVAL '60 days'
GROUP BY s.arrival;

Charge number 2 is the first renewal. Refunded and disputed renewals are the clearest sign of a default nobody chose.

Real reorder interval, for the default cadence

-- median days between first and second order of the same product,
-- one-time buyers only
WITH firsts AS (
  SELECT o.customer_id, ol.product_id, o.created_at,
         ROW_NUMBER() OVER (PARTITION BY o.customer_id, ol.product_id
                            ORDER BY o.created_at) AS n
  FROM orders o
  JOIN order_lines ol ON ol.order_id = o.order_id
  WHERE o.subscription_id IS NULL
)
SELECT a.product_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(DAY FROM b.created_at - a.created_at)) AS median_days,
       COUNT(*) AS repeaters
FROM firsts a
JOIN firsts b ON b.customer_id = a.customer_id
             AND b.product_id = a.product_id AND b.n = 2
WHERE a.n = 1
GROUP BY a.product_id
HAVING COUNT(*) >= 50;

This counts only people who came back, so treat it as the fastest sensible default and round up.

Offer recurrence

-- promotions: promo_id, offer_key (what's offered), discount_pct,
-- starts_at, ends_at, called_final (true if billed as final/last chance)
SELECT p.promo_id, p.offer_key, p.ends_at,
       MIN(q.starts_at)                                  AS next_same_or_better,
       EXTRACT(DAY FROM MIN(q.starts_at) - p.ends_at)    AS days_until_it_returned
FROM promotions p
LEFT JOIN promotions q
  ON q.offer_key = p.offer_key
 AND q.discount_pct >= p.discount_pct
 AND q.starts_at > p.ends_at
WHERE p.called_final
  AND p.ends_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY p.promo_id, p.offer_key, p.ends_at
ORDER BY days_until_it_returned NULLS LAST;

Rows under 30 days go into the scorer in chapter 12. Expect to build the promotions table by hand from the email calendar the first time.