Compute the monthly return rate by first-purchase cohort.

“I’ll read ‘return’ as a repeat purchase, because the question groups customers by their first purchase and asks by month, which is the shape of a retention curve, and I’d confirm that before typing. If you meant products sent back, the cohorts stay the same, but I’d divide returned orders by the orders shipped to the cohort that month, and date each return by its original order.”

“I’ll treat canceled orders and test accounts as if they never happened, for both the cohort and the return. First I collapse to one row per customer per month, then assign each customer the month of their first order across all history.”

WITH purchases AS (         -- one row per customer per month
  SELECT DISTINCT o.customer_id,
         date_trunc('month', o.ordered_at)::date AS order_month
  FROM orders o
  JOIN customers cu USING (customer_id)
  WHERE o.status <> 'canceled'
    AND NOT cu.is_test
),
cohorts AS (                -- no date filter here, on purpose
  SELECT customer_id, min(order_month) AS cohort_month
  FROM purchases
  GROUP BY customer_id
),
cohort_size AS (
  SELECT cohort_month, count(*) AS customers
  FROM cohorts
  GROUP BY cohort_month
),
returns AS (
  SELECT c.cohort_month,
         ((extract(year FROM p.order_month) - extract(year FROM c.cohort_month)) * 12
           + extract(month FROM p.order_month) - extract(month FROM c.cohort_month))::int
           AS month_offset,
         count(*) AS returners
  FROM purchases p
  JOIN cohorts c USING (customer_id)
  WHERE p.order_month > c.cohort_month
  GROUP BY 1, 2
),
grid AS (                   -- only months that are complete
  SELECT s.cohort_month, s.customers, k AS month_offset
  FROM cohort_size s
  CROSS JOIN generate_series(1, 12) AS k
  WHERE s.cohort_month + make_interval(months => k) < date_trunc('month', current_date)
)
SELECT g.cohort_month,
       g.month_offset,
       g.customers                      AS cohort_size,
       coalesce(r.returners, 0)         AS returners,
       round(coalesce(r.returners, 0)::numeric / g.customers, 3) AS return_rate
FROM grid g
LEFT JOIN returns r USING (cohort_month, month_offset)
ORDER BY g.cohort_month, g.month_offset;

“A few things I’d point at while I run it.”

“The grid is why a month where nobody came back shows 0 instead of vanishing, and why a month that hasn’t finished shows no row at all. Without it the chart silently drops the bad months and the curve looks better than it is. The grid stops at month 12; I’d widen it if the chart needs a longer tail.”

“Pivoted for the chart, with cohorts down the side and months since first purchase across the top, the last three cohorts of a run partway through September look like this. Read down a column to compare cohorts at the same age. The blank triangle is months that haven’t finished, not zero, and the August cohort has no row yet, because its month one is September, which is still running.”

“The denominator is the full cohort. Because purchases is already one row per customer per month, count(*) in returns counts people, not orders.”

“These are calendar-month offsets, so a second order the next morning, across a month end, counts as a month-one return. If the business means time since first purchase, I’d keep each customer’s first order timestamp and bucket the days elapsed into 30-day windows instead.”

“date_trunc on a timestamptz uses the session time zone, so if the session runs in UTC, an order placed late in the evening on the last day of a month in New York lands in the next month. If the business reports by a local calendar, I’d convert first with ordered_at AT TIME ZONE 'America/New_York'; the PostgreSQL documentation for date_trunc describes that behavior.”

“To check it, I take one cohort and count by hand: its customers from cohorts, then how many of them have any order in the month after. If that matches the row for month one, I trust the rest.”

“For ‘ever returned by month k’, I’d take each customer’s first month after their cohort month, then use a running count of those over the offsets. The per-month rate can go up and down; the cumulative one can never fall.”

“On refunds: if a fully refunded first order shouldn’t count, a recent cohort can still change until the refund window closes, so I’d mark the last few cohorts as provisional on the chart rather than quietly restate them later.”

“The last thing I’d ask about is identity. If the same person has two customer_ids, one from a guest checkout and one from an account, they land in two cohorts: the first customer_id looks like a customer who never came back, and the second like a new customer. I’d ask whether there’s a merged identity table, and assign cohorts on that ID instead.”