Annual cohorts retain more than 100% of their revenue while losing accounts.

Net and gross retention by billing

WITH price AS (
  SELECT id,
         seat_price_usd_month        AS monthly,
         seat_price_usd_year / 12.0  AS annual_per_month
  FROM plans
),
activation AS (
  SELECT e.subscription_id, s.billing, e.at AS t0,
         strftime('%Y-%m', e.at) AS cohort,
         e.seats * CASE s.billing WHEN 'annual' THEN p.annual_per_month ELSE p.monthly END AS mrr0
  FROM subscription_events e
  JOIN subscriptions s ON s.id = e.subscription_id
  JOIN price p         ON p.id = e.plan_id
  WHERE e.kind = 'activate'
    AND datetime(e.at, '+12 months') <= datetime((SELECT MAX(at) FROM subscription_events))
),
state12 AS (
  SELECT a.*,
    (SELECT e.plan_id FROM subscription_events e
      WHERE e.subscription_id = a.subscription_id
        AND e.kind IN ('activate', 'plan_change', 'seats_change')
        AND datetime(e.at) <= datetime(a.t0, '+12 months')
      ORDER BY e.at DESC LIMIT 1) AS plan12,
    (SELECT e.seats FROM subscription_events e
      WHERE e.subscription_id = a.subscription_id
        AND e.kind IN ('activate', 'plan_change', 'seats_change')
        AND datetime(e.at) <= datetime(a.t0, '+12 months')
      ORDER BY e.at DESC LIMIT 1) AS seats12,
    EXISTS (SELECT 1 FROM subscription_events e
      WHERE e.subscription_id = a.subscription_id
        AND e.kind = 'cancel'
        AND datetime(e.at) <= datetime(a.t0, '+12 months')) AS gone
  FROM activation a
),
priced AS (
  SELECT s.billing, s.cohort, s.mrr0,
         CASE WHEN s.gone THEN 0
              ELSE s.seats12 * CASE s.billing WHEN 'annual' THEN p.annual_per_month ELSE p.monthly END
         END AS mrr12
  FROM state12 s JOIN price p ON p.id = s.plan12
)
SELECT billing,
       COUNT(*)                                        AS accounts,
       ROUND(100.0 * SUM(mrr12) / SUM(mrr0), 1)        AS net_retention_pct,
       ROUND(100.0 * SUM(MIN(mrr0, mrr12)) / SUM(mrr0), 1) AS gross_retention_pct
FROM priced
GROUP BY billing;

holds on the current window