How well do you retain customers month over month?

WITH first_purchase AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(created_at))::date AS cohort_month
  FROM shopify_orders
  WHERE financial_status IN ('paid', 'partially_refunded')
    AND cancelled_at IS NULL
    AND customer_id IS NOT NULL
  GROUP BY customer_id
),
subsequent AS (
  SELECT
    fp.cohort_month,
    EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', o.created_at), fp.cohort_month)) * 12
      + EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), fp.cohort_month))
                                    AS months_since_first,
    COUNT(DISTINCT o.customer_id)  AS returning_customers
  FROM shopify_orders o
  JOIN first_purchase fp ON fp.customer_id = o.customer_id
  WHERE o.financial_status IN ('paid', 'partially_refunded')
    AND o.cancelled_at IS NULL
  GROUP BY fp.cohort_month, months_since_first
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM first_purchase
  GROUP BY cohort_month
)
SELECT
  TO_CHAR(s.cohort_month, 'YYYY-MM')        AS cohort,
  cs.cohort_size,
  s.months_since_first,
  s.returning_customers,
  ROUND(s.returning_customers * 100.0
    / NULLIF(cs.cohort_size, 0), 1)          AS retention_pct
FROM subsequent s
JOIN cohort_sizes cs USING (cohort_month)
WHERE s.months_since_first BETWEEN 0 AND 11
  AND s.cohort_month >= NOW() - INTERVAL '12 months'
ORDER BY s.cohort_month, s.months_since_first