FOR YOUR ANALYST

The formulas

Used versus unused repeat rate

-- first-time buyers, whether they used it within 14 days of delivery,
-- and whether they ordered again within 180 days of the first order.
-- usage_events: one row per signal of use (survey answer, activation,
--   QR scan), with customer_id, used_at, source
WITH first_orders AS (
  SELECT o.customer_id, o.id AS order_id, o.created_at,
         MIN(f.delivered_at) AS delivered_at
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  WHERE o.customer_order_index = 1          -- or ROW_NUMBER() over created_at
    AND o.created_at < CURRENT_DATE - INTERVAL '180 days'
  GROUP BY 1, 2, 3
),
use AS (
  SELECT fo.customer_id,
         MIN(u.used_at) AS first_used_at
  FROM first_orders fo
  JOIN usage_events u ON u.customer_id = fo.customer_id
                     AND u.used_at >= fo.delivered_at
  GROUP BY 1
),
repeaters AS (
  SELECT DISTINCT fo.customer_id
  FROM first_orders fo
  JOIN orders o2 ON o2.customer_id = fo.customer_id
               AND o2.created_at > fo.created_at
               AND o2.created_at <= fo.created_at + INTERVAL '180 days'
)
SELECT CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '14 days'
            THEN 'used by day 14' ELSE 'not used / unknown' END AS grp,
       COUNT(*)                                        AS buyers,
       AVG(CASE WHEN r.customer_id IS NOT NULL THEN 1.0 ELSE 0 END) AS repeat_rate_180d
FROM first_orders fo
LEFT JOIN use u       ON u.customer_id = fo.customer_id
LEFT JOIN repeaters r ON r.customer_id = fo.customer_id
GROUP BY 1;

Buyers with no signal fall into the second group, which mixes non-users with users you didn’t hear from. Run it again restricted to buyers who answered the survey, and use that version in the tool; the unrestricted one understates the gap.

Time to first use by cohort

-- cumulative share used by day 1, 3, 7, 14, 30 after delivery,
-- among first-time buyers who answered the use question
-- (reuses the first_orders and use CTEs from the query above)
SELECT DATE_TRUNC('month', fo.delivered_at) AS cohort,
       COUNT(*) AS respondents,
       AVG(CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '1 day'  THEN 1.0 ELSE 0 END) AS by_d1,
       AVG(CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '3 days' THEN 1.0 ELSE 0 END) AS by_d3,
       AVG(CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '7 days' THEN 1.0 ELSE 0 END) AS by_d7,
       AVG(CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '14 days' THEN 1.0 ELSE 0 END) AS by_d14,
       AVG(CASE WHEN u.first_used_at <= fo.delivered_at + INTERVAL '30 days' THEN 1.0 ELSE 0 END) AS by_d30
FROM first_orders fo
JOIN survey_responses s ON s.customer_id = fo.customer_id
                       AND s.question = 'first_use'
LEFT JOIN use u ON u.customer_id = fo.customer_id
GROUP BY 1
ORDER BY 1;

Map survey answers to a date before this runs: “today” is the response date, “1 to 3 days after it came” is delivery plus 2 days, and so on; “not yet” leaves first_used_at empty. Divide respondents by the cohort’s delivered first orders to get coverage for the tool in chapter 3.

Reorder regularity

-- share of repeat buyers whose latest gap is within 25% of their median gap
WITH gaps AS (
  SELECT customer_id, created_at,
         created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS gap
  FROM orders
),
per_customer AS (
  SELECT customer_id,
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM gap)) AS median_gap,
         (ARRAY_AGG(EXTRACT(EPOCH FROM gap) ORDER BY created_at DESC))[1]      AS last_gap,
         COUNT(gap) AS n_gaps
  FROM gaps WHERE gap IS NOT NULL
  GROUP BY 1
)
SELECT AVG(CASE WHEN ABS(last_gap - median_gap) <= 0.25 * median_gap THEN 1.0 ELSE 0 END) AS regular_share
FROM per_customer
WHERE n_gaps >= 3;

One-tap reorder share

-- reorders from a saved cart, reorder link or subscription
SELECT DATE_TRUNC('week', created_at) AS week,
       AVG(CASE WHEN landing_site LIKE '%utm_content=reorder%'
                  OR source_name = 'subscription'
                THEN 1.0 ELSE 0 END) AS one_tap_share
FROM orders WHERE customer_order_index > 1
GROUP BY 1 ORDER BY 1;