FOR YOUR ANALYST

The formulas

One row per customer, one timestamp per stage

-- orders: order_id, customer_id, created_at, cancelled_at,
--         source ('web','exchange','replacement', ...)
-- fulfillments: order_id, shipped_at, delivered_at
-- first_use: customer_id, signal_at  (your chosen signal: how-to clicks,
--            check-in replies, logins, QR scans, reviews)
CREATE VIEW customer_stages AS
WITH paid AS (
  SELECT order_id, customer_id, created_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY created_at) AS n
  FROM orders
  WHERE cancelled_at IS NULL
    AND source NOT IN ('exchange','replacement')
),
firsts AS (
  SELECT customer_id, order_id, created_at AS first_at
  FROM paid WHERE n = 1
)
SELECT f.customer_id,
       date_trunc('month', f.first_at)                  AS cohort,
       f.first_at,
       (SELECT MIN(fl.delivered_at) FROM fulfillments fl
         WHERE fl.order_id = f.order_id)                AS delivered_at,
       (SELECT MIN(u.signal_at) FROM first_use u
         WHERE u.customer_id = f.customer_id
           AND u.signal_at >= f.first_at)               AS first_use_at,
       (SELECT p.created_at FROM paid p
         WHERE p.customer_id = f.customer_id AND p.n = 2) AS second_at,
       (SELECT p.created_at FROM paid p
         WHERE p.customer_id = f.customer_id AND p.n = 3) AS third_at
FROM firsts f;

Exclude exchange and replacement orders, or every exchange looks like a second order. If your returns app creates orders with a normal source, flag them by tag or by a zero-value total.

The cohort table, nested

-- a customer counts at a stage only if they passed every earlier stage
WITH s AS (
  SELECT cohort,
    delivered_at <= first_at + INTERVAL '14 days'    AS d,
    first_use_at <= first_at + INTERVAL '30 days'    AS u,
    second_at    <= first_at + INTERVAL '120 days'   AS o2,
    third_at     <= first_at + INTERVAL '12 months'  AS o3
  FROM customer_stages
  WHERE first_at < CURRENT_DATE - INTERVAL '12 months'  -- windows closed
)
SELECT cohort,
       COUNT(*)                                            AS first_orders,
       COUNT(*) FILTER (WHERE d)                           AS delivered,
       COUNT(*) FILTER (WHERE d AND u)                     AS first_use,
       COUNT(*) FILTER (WHERE d AND u AND o2)              AS second_order,
       COUNT(*) FILTER (WHERE d AND u AND o2 AND o3)       AS third_order,
       COUNT(*) FILTER (WHERE o2)                          AS second_any
FROM s
GROUP BY cohort
ORDER BY cohort;

Comparisons with a null timestamp come back null, which the filters treat as false. The last column tests your first-use signal: if second_any is much larger than second_order, many customers reorder without the signal, and the signal is too weak to use as a stage.

Time in stage

-- median and 75th-percentile days, among customers who moved on
SELECT cohort,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY
    EXTRACT(EPOCH FROM first_use_at - delivered_at) / 86400)
    FILTER (WHERE first_use_at >= delivered_at)       AS use_median,
  percentile_cont(0.75) WITHIN GROUP (ORDER BY
    EXTRACT(EPOCH FROM first_use_at - delivered_at) / 86400)
    FILTER (WHERE first_use_at >= delivered_at)       AS use_p75,
  COUNT(*) FILTER (WHERE first_use_at >= delivered_at) AS timed
FROM customer_stages
GROUP BY cohort
ORDER BY cohort;

Repeat the pattern for each pair of stages.

Collision days

-- email_events: customer_id, sent_at, channel ('email','sms'),
--   kind ('transactional','flow','campaign','review','problem')
--   Union in SMS, tracking-app and review-app logs before running.
WITH m AS (
  SELECT e.customer_id,
         (e.sent_at::date - cs.first_at::date) AS day,
         e.kind
  FROM email_events e
  JOIN customer_stages cs USING (customer_id)
  WHERE e.sent_at >= cs.first_at
    AND e.sent_at <  cs.first_at + INTERVAL '30 days'
    AND cs.first_at >= CURRENT_DATE - INTERVAL '90 days'
),
d AS (
  SELECT customer_id, day,
         COUNT(*) FILTER (WHERE kind IN ('flow','campaign')) AS marketing,
         COUNT(*) FILTER (WHERE kind = 'problem')            AS problems
  FROM m
  GROUP BY customer_id, day
)
SELECT COUNT(DISTINCT customer_id)
         FILTER (WHERE marketing >= 3
                    OR (problems > 0 AND marketing > 0)) * 1.0
       / COUNT(DISTINCT customer_id)        AS share_with_a_collision_day
FROM d;

Tag delay, failed-payment and open-ticket notices as problem. Customers who received no messages drop out of the denominator; count them separately, because they’re your silent stretch.

Contacts by stage

-- support_tickets: ticket_id, customer_id, order_id, created_at, reason
SELECT CASE
         WHEN cs.delivered_at IS NULL OR t.created_at < cs.delivered_at
           THEN '1 before delivery'
         WHEN cs.first_use_at IS NULL OR t.created_at < cs.first_use_at
           THEN '2 delivered, no first use'
         WHEN cs.second_at IS NULL OR t.created_at < cs.second_at
           THEN '3 after first use'
         ELSE '4 after second order'
       END                AS stage,
       t.reason,
       COUNT(*)           AS tickets
FROM support_tickets t
JOIN customer_stages cs USING (customer_id)
WHERE t.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

Divide by orders in the same 90 days, times 100, for contacts per 100 orders. For repeat contacts, count tickets on the same order within seven days of an earlier one.