Subscription Renewal Rate

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Subscription Renewal Rate by renewal month from native billing tables.
-- Base: every paid term due in the month (cancelled-before-due terms stay in as non-renewals).
-- Table and column names (subscription_periods, subscriptions, invoices; is_trial, is_paused, is_test,
-- termination_reason, billing_reason, period_start, paid_at) are a normalized billing schema: map them to your source (e.g. Stripe, Recharge).
WITH due_terms AS (
  SELECT
    p.subscription_id,
    p.period_end,
    DATE_TRUNC(DATE(p.period_end), MONTH) AS renewal_month
  FROM `your-project.billing.subscription_periods` AS p
  JOIN `your-project.billing.subscriptions` AS s
    ON s.subscription_id = p.subscription_id
  WHERE p.is_trial = FALSE
    AND COALESCE(p.is_paused, FALSE) = FALSE  -- paused at the due date: out of the base until it resumes
    AND s.is_test = FALSE
    AND COALESCE(s.termination_reason, '') NOT IN ('fraud', 'merchant_terminated')
    AND p.period_end >= TIMESTAMP(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH), MONTH))
    -- whole renewal months only, reported once the last due date plus the 7-day grace window has passed
    AND DATE(p.period_end) < DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), MONTH)
),
renewed_terms AS (
  SELECT DISTINCT d.subscription_id, d.period_end
  FROM due_terms AS d
  JOIN `your-project.billing.invoices` AS i
    ON i.subscription_id = d.subscription_id
   AND i.billing_reason IN ('subscription_cycle', 'subscription_update')  -- plan / interval changes at renewal count as renewed
   AND i.period_start = d.period_end  -- invoice covers the next term
   AND i.status = 'paid'
   AND i.paid_at >= TIMESTAMP_SUB(d.period_end, INTERVAL 1 DAY)
   AND i.paid_at < TIMESTAMP_ADD(d.period_end, INTERVAL 7 DAY)
)
SELECT
  d.renewal_month,
  COUNT(*) AS terms_due_for_renewal,
  COUNT(r.subscription_id) AS terms_renewed,
  ROUND(100 * SAFE_DIVIDE(COUNT(r.subscription_id), COUNT(*)), 2) AS subscription_renewal_rate_pct
FROM due_terms AS d
LEFT JOIN renewed_terms AS r
  ON r.subscription_id = d.subscription_id
 AND r.period_end = d.period_end
GROUP BY d.renewal_month
ORDER BY d.renewal_month;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Subscription Renewal Rate by renewal month from billing datasets ingested into AEP.
-- Base: every paid term due in the month (cancelled-before-due terms stay in as non-renewals).
-- Table and column names (billing_subscription_periods, billing_subscriptions, billing_invoices; is_trial, is_paused, is_test,
-- termination_reason, billing_reason, period_start, paid_at) are a normalized billing schema: map them to your source (e.g. Stripe, Recharge).
WITH due_terms AS (
  SELECT
    p.subscription_id,
    p.period_end,
    DATE_TRUNC('month', p.period_end) AS renewal_month
  FROM billing_subscription_periods AS p
  JOIN billing_subscriptions AS s
    ON s.subscription_id = p.subscription_id
  WHERE p.is_trial = FALSE
    AND COALESCE(p.is_paused, FALSE) = FALSE  -- paused at the due date: out of the base until it resumes
    AND s.is_test = FALSE
    AND COALESCE(s.termination_reason, '') NOT IN ('fraud', 'merchant_terminated')
    AND p.period_end >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12' MONTH)
    -- whole renewal months only, reported once the last due date plus the 7-day grace window has passed
    AND p.period_end < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '7' DAY)
),
renewed_terms AS (
  SELECT DISTINCT d.subscription_id, d.period_end
  FROM due_terms AS d
  JOIN billing_invoices AS i
    ON i.subscription_id = d.subscription_id
   AND i.billing_reason IN ('subscription_cycle', 'subscription_update')  -- plan / interval changes at renewal count as renewed
   AND i.period_start = d.period_end  -- invoice covers the next term
   AND i.status = 'paid'
   AND i.paid_at >= d.period_end - INTERVAL '1' DAY
   AND i.paid_at < d.period_end + INTERVAL '7' DAY
)
SELECT
  d.renewal_month,
  COUNT(*) AS terms_due_for_renewal,
  COUNT(r.subscription_id) AS terms_renewed,
  ROUND(100.0 * COUNT(r.subscription_id) / NULLIF(COUNT(*), 0), 2) AS subscription_renewal_rate_pct
FROM due_terms AS d
LEFT JOIN renewed_terms AS r
  ON r.subscription_id = d.subscription_id
 AND r.period_end = d.period_end
GROUP BY d.renewal_month
ORDER BY d.renewal_month;