FOR YOUR ANALYST

The formulas

Check the lognormal fit against last month’s actual on-time share.

On time against the date shown

-- share of delivered orders that arrived by the date shown at checkout
-- orders.promised_delivery_date must be stored when the order is placed
SELECT o.shipping_region,
       f.carrier_service,
       COUNT(*)                                                    AS delivered,
       AVG(CASE WHEN f.delivered_at::date <= o.promised_delivery_date
                THEN 1.0 ELSE 0 END)                               AS on_time_share,
       PERCENTILE_CONT(0.5) WITHIN GROUP
         (ORDER BY EXTRACT(EPOCH FROM f.delivered_at - o.created_at) / 86400) AS median_days,
       PERCENTILE_CONT(0.9) WITHIN GROUP
         (ORDER BY EXTRACT(EPOCH FROM f.delivered_at - o.created_at) / 86400) AS p90_days
FROM orders o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.delivered_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.shipping_region, f.carrier_service
ORDER BY on_time_share;

The syntax is Postgres. If you don’t store the promised date, start today; until then, reconstruct it from the shipping method and order time, and call the result an estimate. The median and 90th-percentile columns feed the date tool in chapter 4.

The failure index

-- one row per order with its worst failure, for the index and perfect-order rate
WITH flags AS (
  SELECT o.id AS order_id,
         CASE WHEN f.delivered_at IS NULL AND f.lost_at IS NOT NULL THEN 'lost'
              WHEN EXISTS (SELECT 1 FROM tickets t WHERE t.order_id = o.id
                           AND t.reason = 'damaged')                   THEN 'damaged'
              WHEN EXISTS (SELECT 1 FROM tickets t WHERE t.order_id = o.id
                           AND t.reason = 'wrong_or_missing')          THEN 'wrong_or_missing'
              WHEN f.delivered_at::date >= o.promised_delivery_date + 3   THEN 'late_3plus'
              WHEN f.delivered_at::date >  o.promised_delivery_date       THEN 'late_1_2'
              WHEN o.split_unannounced                                  THEN 'split'
              ELSE 'none' END AS failure
  FROM orders o JOIN fulfillments f ON f.order_id = o.id
  WHERE o.created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
    AND o.created_at <  DATE_TRUNC('month', CURRENT_DATE)
)
SELECT failure, COUNT(*) AS orders
FROM flags GROUP BY failure;

Count reopened contacts separately and add them in the tool. Each order counts once, at its worst failure, which keeps the perfect-order rate honest.

The failure cohort

-- 12-month repeat rate of first-time customers, by what happened to the first order
WITH firsts AS (
  SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, created_at,
         shipping_region, first_product_id
  FROM orders ORDER BY customer_id, created_at
)
SELECT DATE_TRUNC('month', fo.created_at) AS cohort_month,
       fo.shipping_region,
       fl.failure,
       COUNT(*) AS customers,
       AVG(CASE WHEN EXISTS (
             SELECT 1 FROM orders o2
             WHERE o2.customer_id = fo.customer_id
               AND o2.created_at > fo.created_at
               AND o2.created_at <= fo.created_at + INTERVAL '12 months')
           THEN 1.0 ELSE 0 END) AS repeat_12m
FROM firsts fo
JOIN flags fl ON fl.order_id = fo.order_id   -- the flags logic above, run for these orders
WHERE fo.created_at < CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

Compare each failure group with the ‘none’ group within the same month and region, then average the gaps, weighted by group size. Add whether a remedy was given to get the comparison in chapter 10. Pool quarters until groups reach a few hundred.

Contacts per order by reason

-- weekly contacts per order, by primary reason
SELECT DATE_TRUNC('week', t.created_at) AS week,
       t.reason,
       COUNT(*)::numeric / NULLIF(w.orders, 0) AS contacts_per_order
FROM tickets t
JOIN (SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS orders
      FROM orders GROUP BY 1) w
  ON w.week = DATE_TRUNC('week', t.created_at)
WHERE t.created_at >= CURRENT_DATE - INTERVAL '12 weeks'
  AND t.direction = 'inbound'
GROUP BY 1, 2, w.orders
ORDER BY 1, 3 DESC;

Table and column names are generic; rename them to match your exports. Count conversations, not messages. Repeat contact rate follows the same pattern: contacts with an earlier contact on the same order in the previous 7 days, over all contacts.