Skip to content

Subscription Revenue Forecast Model

Overview

This query produces 25-months of subscription revenue data based on historical Stripe data and forecast assumptions.

The output includes:

  • Historical revenue (actual Stripe invoices)
  • Projected revenue from existing subscriptions
  • Projected revenue from forecasted new subscription cohorts
  • Subscriber creation, conversion, and billing counts
  • 12 month outputs for the existing_no_churn can be used to calculated monthly or annual recurring revenue (MRR or ARR)

The model anchors on the most recently completed month and generates:

  • 12 historical months
  • 1 anchor month
  • 12 forecast months

Total window: 25 months.

Data Sources

Stripe data is sourced from Snowflake tables:

Table Purpose
RAW_DB.STRIPE.INVOICES Paid invoices used to calculate historical revenue
RAW_DB.STRIPE.SUBSCRIPTIONS Subscription lifecycle data
first_invoices CTE Derived earliest paid invoice per subscription

Forecast Methodology

The forecast combines three revenue components.

1. Historical Revenue

Revenue from paid invoices:

RAW_DB.STRIPE.INVOICES

Filtered to:

  • STATUS = 'paid'
  • SUBSCRIPTION IS NOT NULL

2. Existing Subscription Revenue

Existing subscribers generate recurring revenue based on:

  • billing interval
  • billing amount
  • churn probability

Churn Rate

Calculated from the trailing 6 completed months of paying subscribers.

Key rules:

  • months must have ≥1000 subscribers
  • Dec 2025 excluded due to launch anomaly
  • trial non-conversions excluded

Churn is applied only to forecast months.

3. New Subscriber Revenue

Forecast new subscriptions based on recent acquisition velocity.

Acquisition Rate

Derived from trailing 21 days of subscription creation:

daily_monthly_rate (21-day rate at which new MONTHLY subscriptions are being CREATED)
daily_annual_rate (21-day rate at which new ANNUAL subscriptions are being CREATED)

Monthly forecasts scale by:

daily_rate × days_in_month

Trial Conversion Modeling

Trial subscriptions convert to paid using a calculated trial conversion rate.

Conversion rate:

active + past_due
------------------
total resolved trials

This rate is applied to both:

  • new subscription cohorts
  • existing trialing subscriptions

Trial Lag Distribution

Subscriptions may convert to paid with delay.

Lag distribution calculated from:

  • trailing 6 months of subscription cohorts
  • excluding the most recent 2 months
  • excluding Nov/Dec 2025 launch anomalies

Lag buckets:

0 months
1 month
2 months

Separate distributions are computed for:

  • monthly plans
  • annual plans

Holiday Seasonality

December acquisitions receive a multiplier:

3.8x

This is the dumbest assumption in the whole forecast, but I honestly believe we have no solid foundation for estimating how big December seasonality will be going forward. It was based on December 2025's rate of new subscription creations compared to January & February 2026. This will need to be revisited--we can probably start to get a sense of how many subscriptions will be created in December as orders start to ramp up in fall?

Revenue Components

Final revenue forecast includes:

Component Description
existing_only_with_churn revenue from current subscribers with churn applied
existing_no_churn baseline recurring revenue
new_monthly_contrib revenue from new monthly subscriptions
new_annual_contrib revenue from new annual subscriptions

Subscriber Metrics

The model also produces subscriber-level metrics:

Metric Description
created_mo new monthly subscriptions created
created_ann new annual subscriptions created
converted_mo trial conversions to monthly
converted_ann trial conversions to annual
exist_billing_mo recurring monthly renewals
exist_billing_ann annual renewals

Historical values come from Stripe data; forecast values come from the model.

Output

Each row represents a billing month.

Key columns:

Column Description
billing_month calendar month
actual_revenue historical Stripe revenue
expected_full_model total forecast revenue
existing_only_with_churn projected recurring revenue with churn
existing_no_churn recurring revenue (standard definition)
new_monthly_contrib forecast from new monthly subscriptions
new_annual_contrib forecast from new annual subscriptions

Current Month Pro-Rating

The first forecast month (the current incomplete month) is a hybrid of actual and forecast data to avoid double-counting subscriptions that already exist in Stripe:

  • Created counts: actual creations so far this month + forecast for remaining days
  • New sub revenue: forecast uses only the remaining days in the month (CURRENT_DATE to month end), not the full month
  • Existing sub revenue: projected normally from all subs currently in Stripe (including those created this month)
  • Converted / existing billing counts: handled by the forecast model for the full month (lag-weighted conversions and pool-based billing)

This ensures subs created earlier in the current month appear only in the existing projection, while the new-sub forecast covers only the days not yet elapsed.

Model Notes

  • Forecast horizon: 12 months
  • Lag distribution capped at 2 months
  • Churn applied only to forecast period
  • December acquisition surge modeled via multiplier
  • Annual renewals modeled via 12-month cohort anniversaries

Author

Pete Z

Query

-- Subscription Revenue Forecast Model
-- See subscription_revenue_forecast_model.md for documentation


WITH RECURSIVE
-- ============================================================
-- DYNAMIC DATE RANGE
-- Anchors on the most recently completed month, then extends
-- 12 months back and 12 months forward (25 months total).
--
-- month_idx  0..12 = historical (actual data expected)
-- month_idx 13..24 = forecast   (projected from model)
-- month_idx 12     = anchor     (most recently completed month)
-- ============================================================
date_params AS (
    SELECT DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))::DATE AS range_start,
           DATEADD('month',  11, DATE_TRUNC('month', CURRENT_DATE))::DATE AS range_end
),

all_months AS (
    SELECT DATEADD('month', SEQ4(), (SELECT range_start FROM date_params)) AS billing_month,
           SEQ4() AS month_idx
    FROM TABLE(GENERATOR(ROWCOUNT => 25))
),

-- ACTUAL: paid subscription invoices
actual AS (
    SELECT DATE_TRUNC('month', TO_TIMESTAMP(i.CREATED::NUMBER)) AS billing_month,
           SUM(i.AMOUNT_PAID / 100.0) AS revenue
    FROM RAW_DB.STRIPE.INVOICES i
    WHERE i.STATUS = 'paid' AND i.SUBSCRIPTION IS NOT NULL
      AND TO_TIMESTAMP(i.CREATED::NUMBER) >= (SELECT range_start FROM date_params)
      AND TO_TIMESTAMP(i.CREATED::NUMBER) < DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY 1
),

-- TRIAL CONVERSION RATE: % of resolved trials that converted to paying
trial_conversion AS (
    SELECT ROUND(
        SUM(CASE WHEN STATUS IN ('active','past_due') THEN 1 ELSE 0 END) * 1.0 / COUNT(*),
    4) AS rate
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS
    WHERE TRIAL_END IS NOT NULL
      AND TO_TIMESTAMP(TRIAL_END::NUMBER) < CURRENT_TIMESTAMP
),

-- NEW SUB ACQUISITION PARAMS: trailing 3-week daily rates
-- Counts paid-plan subs created in the last 21 days, derives a daily rate.
-- Each forecast month multiplies daily rate x days-in-month for its count.
new_sub_params AS (
    SELECT
        COUNT(CASE WHEN s.PLAN:interval::TEXT = 'month' THEN 1 END) / 21.0
            AS daily_monthly_rate,
        COUNT(CASE WHEN s.PLAN:interval::TEXT = 'year' THEN 1 END) / 21.0
            AS daily_annual_rate,
        9.99  AS avg_monthly_rev,
        99.99 AS avg_annual_rev
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE TO_TIMESTAMP(s.CREATED::NUMBER) >= DATEADD('day', -21, CURRENT_DATE)
      AND TO_TIMESTAMP(s.CREATED::NUMBER) < CURRENT_DATE
      AND s.PLAN:interval::TEXT IS NOT NULL
      AND s.PLAN:amount::NUMBER > 0
      AND s.STATUS NOT IN ('incomplete_expired')
),

-- HOLIDAY SEASONALITY: December multiplier for gift subscriptions
-- Christmas Day 2025 alone drove 13,793 paid subs (40% of Dec total)
-- Dec 2025 total ~33,946 paid subs vs ~8,980 steady-state = 3.8x
holiday_season AS (
    SELECT 3.8 AS dec_multiplier
),

-- ============================================================
-- CHURN RATE: weighted avg for PAYING subs (post-trial only)
-- Trailing 6 completed months (month_idx 7..12), excluding Dec 2025
-- (launch anomaly: 10.3% churn on small base, one-off).
-- paying_at_start >= 1000 filter drops any months with too few subs.
-- Trial non-conversions are handled separately by trial_conversion rate.
-- ============================================================
churn_calc AS (
    SELECT cal_month,
        COUNT(DISTINCT CASE
            WHEN TO_TIMESTAMP(s.BILLING_CYCLE_ANCHOR::NUMBER) < cal_month
             AND (s.TRIAL_END IS NULL OR TO_TIMESTAMP(s.TRIAL_END::NUMBER) < cal_month)
             AND (s.ENDED_AT IS NULL OR TO_TIMESTAMP(s.ENDED_AT::NUMBER) >= cal_month)
            THEN s.ID END) AS paying_at_start,
        COUNT(DISTINCT CASE
            WHEN s.ENDED_AT IS NOT NULL
             AND DATE_TRUNC('month', TO_TIMESTAMP(s.ENDED_AT::NUMBER)) = cal_month
             AND (s.TRIAL_END IS NULL OR TO_TIMESTAMP(s.TRIAL_END::NUMBER) < cal_month)
            THEN s.ID END) AS churned_paying
    FROM (
        SELECT billing_month AS cal_month
        FROM all_months
        WHERE month_idx BETWEEN 7 AND 12
          AND billing_month != '2025-12-01'::DATE  -- permanent exclusion: launch anomaly
    ) months
    CROSS JOIN RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE s.STATUS NOT IN ('incomplete_expired')
    GROUP BY cal_month
),
churn_rate AS (
    SELECT ROUND(SUM(churned_paying) * 1.0 / SUM(paying_at_start), 5) AS monthly_churn
    FROM churn_calc
    WHERE paying_at_start >= 1000
),

-- ============================================================
-- FIRST INVOICES: earliest paid invoice per subscription
-- Shared foundation for lag distributions and historical counts
-- ============================================================
first_invoices AS (
    SELECT SUBSCRIPTION, MIN(CREATED) AS first_paid
    FROM RAW_DB.STRIPE.INVOICES
    WHERE STATUS = 'paid' AND AMOUNT_PAID > 0 AND SUBSCRIPTION IS NOT NULL
    GROUP BY SUBSCRIPTION
),

-- ============================================================
-- TRIAL LAG: dynamic distributions from trailing 6 months
-- Excludes last 2 completed months (lag not yet fully resolved)
-- and Nov/Dec 2025 forever (extended trials from launch anomaly).
-- Lag = months between sub creation and first paid invoice.
-- Capped at 2 months; weights normalized to sum to 1.
-- ============================================================
lag_sample AS (
    SELECT s.ID,
           s.PLAN:interval::TEXT AS billing_interval,
           LEAST(DATEDIFF('month',
               DATE_TRUNC('month', TO_TIMESTAMP(s.CREATED::NUMBER)),
               DATE_TRUNC('month', TO_TIMESTAMP(fi.first_paid::NUMBER))
           ), 2) AS lag_months
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    INNER JOIN first_invoices fi ON fi.SUBSCRIPTION = s.ID
    WHERE DATE_TRUNC('month', TO_TIMESTAMP(s.CREATED::NUMBER)) IN (
        SELECT billing_month FROM all_months
        WHERE month_idx BETWEEN 5 AND 10          -- 6-month window, ending 2 months before current
          AND billing_month NOT IN ('2025-11-01'::DATE, '2025-12-01'::DATE)  -- permanent exclusion
    )
      AND s.PLAN:interval::TEXT IN ('month', 'year')
      AND s.PLAN:amount::NUMBER > 0
      AND s.STATUS NOT IN ('incomplete_expired')
),

monthly_lag AS (
    SELECT lag_months AS lag,
           ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (), 4) AS weight
    FROM lag_sample
    WHERE billing_interval = 'month'
    GROUP BY lag_months
),

annual_lag AS (
    SELECT lag_months AS lag,
           ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (), 4) AS weight
    FROM lag_sample
    WHERE billing_interval = 'year'
    GROUP BY lag_months
),

-- ============================================================
-- EXISTING SUBS: billing cadence + trial conversion + churn
-- Uses SUBSCRIPTIONS.PLAN for interval/amount (no SUBSCRIPTION_ITEMS join needed)
-- Churn applied only to forecast months (month_idx > 12)
-- ============================================================
all_subs AS (
    SELECT s.ID AS subscription_id, s.STATUS,
        s.PLAN:interval::TEXT AS billing_interval,
        s.PLAN:interval_count::NUMBER AS interval_count,
        s.QUANTITY * s.PLAN:amount::NUMBER / 100.0 AS billing_amount,
        DATE_TRUNC('month', TO_TIMESTAMP(s.BILLING_CYCLE_ANCHOR::NUMBER)) AS first_billing_month,
        CASE
            WHEN s.ENDED_AT IS NOT NULL THEN DATE_TRUNC('month', TO_TIMESTAMP(s.ENDED_AT::NUMBER))
            WHEN s.CANCEL_AT IS NOT NULL THEN DATE_TRUNC('month', TO_TIMESTAMP(s.CANCEL_AT::NUMBER))
            WHEN s.CANCEL_AT_PERIOD_END = TRUE THEN DATE_TRUNC('month', TO_TIMESTAMP(s.CURRENT_PERIOD_END::NUMBER))
            ELSE (SELECT range_end FROM date_params)
        END AS last_billing_month
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE s.PLAN:interval::TEXT IS NOT NULL
      AND s.STATUS NOT IN ('incomplete_expired')
),

existing_expected AS (
    SELECT m.billing_month, m.month_idx,
        SUM(CASE WHEN a.STATUS = 'trialing' THEN a.billing_amount * tc.rate ELSE a.billing_amount END
            * CASE WHEN m.month_idx <= 12 THEN 1.0
                   ELSE POWER(1.0 - cr.monthly_churn, m.month_idx - 12) END
        ) AS rev_with_churn,
        SUM(CASE WHEN a.STATUS = 'trialing' THEN a.billing_amount * tc.rate
             ELSE a.billing_amount END) AS rev_no_churn
    FROM all_months m
    CROSS JOIN all_subs a
    CROSS JOIN trial_conversion tc
    CROSS JOIN churn_rate cr
    WHERE m.billing_month >= a.first_billing_month AND m.billing_month <= a.last_billing_month
      AND MOD(DATEDIFF('month', a.first_billing_month, m.billing_month),
              CASE a.billing_interval WHEN 'month' THEN a.interval_count
                   WHEN 'year' THEN 12 * a.interval_count END) = 0
    GROUP BY 1, 2
),

-- ============================================================
-- NEW SUBS: each forecast month spawns a cohort
-- Trial lag delays when subs start paying
-- Trial conversion rate applied to all new subs
-- Churn applied from first payment date forward
-- ============================================================
-- Each forecast month is a cohort; precompute days-in-month for count scaling
cohort_months AS (
    SELECT m.month_idx AS acq_idx,
           m.billing_month AS acq_month,
           CASE WHEN m.month_idx = 13
                THEN GREATEST(DATEDIFF('day', CURRENT_DATE, DATEADD('month', 1, m.billing_month)), 0)
                ELSE DATEDIFF('day', m.billing_month, DATEADD('month', 1, m.billing_month))
           END AS days_in_month
    FROM all_months m
    WHERE m.month_idx >= 13
),

-- New MONTHLY subs: daily_rate x days_in_month -> cohort count
-- Pay after trial lag, then every month with churn
-- December cohorts get holiday_season.dec_multiplier applied to count
new_monthly_sub_rev AS (
    SELECT m.billing_month, m.month_idx,
        SUM(
            p.daily_monthly_rate * cm.days_in_month
            * CASE WHEN MONTH(cm.acq_month) = 12
                   THEN hs.dec_multiplier ELSE 1.0 END
            * tc.rate * ml.weight * p.avg_monthly_rev
            * POWER(1.0 - cr.monthly_churn, m.month_idx - cm.acq_idx - ml.lag)
        ) AS revenue
    FROM all_months m
    CROSS JOIN cohort_months cm
    CROSS JOIN new_sub_params p
    CROSS JOIN trial_conversion tc
    CROSS JOIN churn_rate cr
    CROSS JOIN monthly_lag ml
    CROSS JOIN holiday_season hs
    WHERE m.month_idx >= cm.acq_idx + ml.lag
      AND m.month_idx >= 13
    GROUP BY 1, 2
),

-- New ANNUAL subs: daily_rate x days_in_month -> cohort count
-- Pay after trial lag, then again 12 months later with churn
-- December cohorts get holiday count multiplier (same lag as other months)
new_annual_sub_rev AS (
    SELECT m.billing_month, m.month_idx,
        SUM(
            CASE
                WHEN m.month_idx = cm.acq_idx + al.lag THEN
                    p.daily_annual_rate * cm.days_in_month
                    * CASE WHEN MONTH(cm.acq_month) = 12
                           THEN hs.dec_multiplier ELSE 1.0 END
                    * tc.rate * al.weight * p.avg_annual_rev
                WHEN m.month_idx = cm.acq_idx + al.lag + 12 THEN
                    p.daily_annual_rate * cm.days_in_month
                    * CASE WHEN MONTH(cm.acq_month) = 12
                           THEN hs.dec_multiplier ELSE 1.0 END
                    * tc.rate * al.weight * p.avg_annual_rev
                    * POWER(1.0 - cr.monthly_churn, 12)
                ELSE 0
            END
        ) AS revenue
    FROM all_months m
    CROSS JOIN cohort_months cm
    CROSS JOIN new_sub_params p
    CROSS JOIN trial_conversion tc
    CROSS JOIN churn_rate cr
    CROSS JOIN annual_lag al
    CROSS JOIN holiday_season hs
    WHERE m.month_idx >= 13
      AND (m.month_idx = cm.acq_idx + al.lag OR m.month_idx = cm.acq_idx + al.lag + 12)
    GROUP BY 1, 2
),

-- ============================================================
-- SUBSCRIBER COUNTS: historical (from actual data)
-- ============================================================

-- Created: paid-plan subs created each month, by interval
hist_created_counts AS (
    SELECT DATE_TRUNC('month', TO_TIMESTAMP(s.CREATED::NUMBER))::DATE AS billing_month,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'month' THEN 1 ELSE 0 END) AS created_mo,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'year'  THEN 1 ELSE 0 END) AS created_ann
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE s.PLAN:amount::NUMBER > 0
      AND s.STATUS NOT IN ('incomplete_expired')
      AND s.PLAN:interval::TEXT IN ('month', 'year')
      AND TO_TIMESTAMP(s.CREATED::NUMBER) >= (SELECT range_start FROM date_params)
      AND TO_TIMESTAMP(s.CREATED::NUMBER) < CURRENT_DATE
    GROUP BY 1
),

-- Converted: first paid invoice landed this month (trial-to-paid conversion)
hist_converted_counts AS (
    SELECT DATE_TRUNC('month', TO_TIMESTAMP(fi.first_paid::NUMBER))::DATE AS billing_month,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'month' THEN 1 ELSE 0 END) AS converted_mo,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'year'  THEN 1 ELSE 0 END) AS converted_ann
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    JOIN first_invoices fi ON fi.SUBSCRIPTION = s.ID
    WHERE s.PLAN:amount::NUMBER > 0
      AND s.PLAN:interval::TEXT IN ('month', 'year')
      AND TO_TIMESTAMP(fi.first_paid::NUMBER) >= (SELECT range_start FROM date_params)
      AND TO_TIMESTAMP(fi.first_paid::NUMBER) < DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY 1
),

-- Existing billings: recurring (non-first) paid invoices per month
hist_existing_billings AS (
    SELECT DATE_TRUNC('month', TO_TIMESTAMP(i.CREATED::NUMBER))::DATE AS billing_month,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'month' THEN 1 ELSE 0 END) AS exist_billing_mo,
           SUM(CASE WHEN s.PLAN:interval::TEXT = 'year'  THEN 1 ELSE 0 END) AS exist_billing_ann
    FROM RAW_DB.STRIPE.INVOICES i
    JOIN RAW_DB.STRIPE.SUBSCRIPTIONS s ON i.SUBSCRIPTION = s.ID
    JOIN first_invoices fi ON fi.SUBSCRIPTION = s.ID
    WHERE i.STATUS = 'paid'
      AND i.AMOUNT_PAID > 0
      AND i.CREATED != fi.first_paid
      AND s.PLAN:amount::NUMBER > 0
      AND s.PLAN:interval::TEXT IN ('month', 'year')
      AND TO_TIMESTAMP(i.CREATED::NUMBER) >= (SELECT range_start FROM date_params)
      AND TO_TIMESTAMP(i.CREATED::NUMBER) < DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY 1
),

-- ============================================================
-- SUBSCRIBER COUNTS: forecast (from model parameters)
-- ============================================================

-- Forecast created counts: daily_rate x days_in_month x dec_multiplier
forecast_created_counts AS (
    SELECT cm.acq_month AS billing_month,
           cm.acq_idx   AS month_idx,
           ROUND(p.daily_monthly_rate * cm.days_in_month
                 * CASE WHEN MONTH(cm.acq_month) = 12 THEN hs.dec_multiplier ELSE 1.0 END
           ) AS created_mo,
           ROUND(p.daily_annual_rate * cm.days_in_month
                 * CASE WHEN MONTH(cm.acq_month) = 12 THEN hs.dec_multiplier ELSE 1.0 END
           ) AS created_ann
    FROM cohort_months cm
    CROSS JOIN new_sub_params p
    CROSS JOIN holiday_season hs
),

-- Union historical + forecast created counts (needed for lag lookups)
all_created_counts AS (
    -- Fully historical months
    SELECT m.month_idx, m.billing_month,
           COALESCE(hc.created_mo, 0)  AS created_mo,
           COALESCE(hc.created_ann, 0) AS created_ann
    FROM all_months m
    LEFT JOIN hist_created_counts hc ON hc.billing_month = m.billing_month
    WHERE m.month_idx < 13
    UNION ALL
    -- Current month: actual partial + forecast remainder
    SELECT fc.month_idx, fc.billing_month,
           fc.created_mo + COALESCE(hc.created_mo, 0) AS created_mo,
           fc.created_ann + COALESCE(hc.created_ann, 0) AS created_ann
    FROM forecast_created_counts fc
    LEFT JOIN hist_created_counts hc ON hc.billing_month = fc.billing_month
    WHERE fc.month_idx = 13
    UNION ALL
    -- Future forecast months
    SELECT fc.month_idx, fc.billing_month, fc.created_mo, fc.created_ann
    FROM forecast_created_counts fc
    WHERE fc.month_idx > 13
),

-- Forecast converted counts: lag-weighted cohort conversions
-- Split into monthly/annual to avoid cartesian product between lag tables
forecast_converted_mo AS (
    SELECT m.billing_month, m.month_idx,
        COALESCE(ROUND(SUM(acc.created_mo * tc.rate * ml.weight)), 0) AS converted_mo
    FROM all_months m
    CROSS JOIN trial_conversion tc
    CROSS JOIN monthly_lag ml
    JOIN all_created_counts acc ON acc.month_idx = m.month_idx - ml.lag
    WHERE m.month_idx >= 13
    GROUP BY 1, 2
),

forecast_converted_ann AS (
    SELECT m.billing_month, m.month_idx,
        COALESCE(ROUND(SUM(acc.created_ann * tc.rate * al.weight)), 0) AS converted_ann
    FROM all_months m
    CROSS JOIN trial_conversion tc
    CROSS JOIN annual_lag al
    JOIN all_created_counts acc ON acc.month_idx = m.month_idx - al.lag
    WHERE m.month_idx >= 13
    GROUP BY 1, 2
),

-- Union historical + forecast converted counts (needed for annual anniversary lookup)
all_converted_counts AS (
    SELECT m.month_idx, m.billing_month,
           COALESCE(hcv.converted_mo, 0)  AS converted_mo,
           COALESCE(hcv.converted_ann, 0) AS converted_ann
    FROM all_months m
    LEFT JOIN hist_converted_counts hcv ON hcv.billing_month = m.billing_month
    WHERE m.month_idx < 13
    UNION ALL
    SELECT fcm.month_idx, fcm.billing_month, fcm.converted_mo, fca.converted_ann
    FROM forecast_converted_mo fcm
    JOIN forecast_converted_ann fca ON fca.month_idx = fcm.month_idx
),

-- ============================================================
-- FORECAST EXISTING BILLINGS
-- Monthly: pool-based (recursive) -- each month's existing billings
--   = prior pool surviving churn; pool grows by new conversions
-- Annual: anniversary-based -- renewals from 12 months prior
-- ============================================================

-- Initial pool: paying monthly subs at forecast start
paying_mo_pool_init AS (
    SELECT COUNT(*) AS pool_size
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE s.STATUS IN ('active', 'past_due')
      AND s.PLAN:amount::NUMBER > 0
      AND s.PLAN:interval::TEXT = 'month'
),

-- Recursive CTE: pool-based monthly existing billings
forecast_exist_monthly AS (
    -- Base case: first forecast month (month_idx = 13)
    SELECT
        m.billing_month,
        m.month_idx,
        ROUND(pi.pool_size * (1.0 - cr.monthly_churn)) AS exist_billing_mo,
        ROUND(pi.pool_size * (1.0 - cr.monthly_churn))
            + COALESCE(fc.converted_mo, 0) AS next_pool
    FROM all_months m
    CROSS JOIN paying_mo_pool_init pi
    CROSS JOIN churn_rate cr
    LEFT JOIN forecast_converted_mo fc ON fc.month_idx = m.month_idx
    WHERE m.month_idx = 13

    UNION ALL

    -- Recursive step: each subsequent month
    SELECT
        m.billing_month,
        m.month_idx,
        ROUND(prev.next_pool * (1.0 - cr.monthly_churn)) AS exist_billing_mo,
        ROUND(prev.next_pool * (1.0 - cr.monthly_churn))
            + COALESCE(fc.converted_mo, 0) AS next_pool
    FROM forecast_exist_monthly prev
    JOIN all_months m ON m.month_idx = prev.month_idx + 1
    CROSS JOIN churn_rate cr
    LEFT JOIN forecast_converted_mo fc ON fc.month_idx = m.month_idx
    WHERE prev.month_idx < 24
),

-- Anniversary-based annual existing billings: cohort from 12 months prior
forecast_exist_annual AS (
    SELECT m.billing_month, m.month_idx,
        COALESCE(
            ROUND(acc.converted_ann * POWER(1.0 - cr.monthly_churn, 12)),
            0
        ) AS exist_billing_ann
    FROM all_months m
    CROSS JOIN churn_rate cr
    LEFT JOIN all_converted_counts acc ON acc.month_idx = m.month_idx - 12
    WHERE m.month_idx >= 13
)

-- ============================================================
-- FINAL OUTPUT: revenue + subscriber counts for all 25 months
-- ============================================================
SELECT
    m.billing_month,
    ROUND(a.revenue,2)          AS actual_revenue,
    ROUND(COALESCE(e.rev_with_churn, 0)
        + COALESCE(nm.revenue, 0)
        + COALESCE(na.revenue, 0), 2)         AS expected_full_model,
    ROUND(COALESCE(e.rev_with_churn, 0), 2)   AS existing_only_with_churn,
    ROUND(COALESCE(e.rev_no_churn, 0), 2)     AS existing_no_churn,
    ROUND(COALESCE(nm.revenue, 0), 2)         AS new_monthly_contrib,
    ROUND(COALESCE(na.revenue, 0), 2)         AS new_annual_contrib,
    -- Subscriber counts (historical from actual data, forecast from model)
    COALESCE(hcc.created_mo, 0) + COALESCE(fcc.created_mo, 0) AS created_mo,
    COALESCE(hcc.created_ann, 0) + COALESCE(fcc.created_ann, 0) AS created_ann,
    COALESCE(hcv.converted_mo,      fcvm.converted_mo,     0) AS converted_mo,
    COALESCE(hcv.converted_ann,     fcva.converted_ann,    0) AS converted_ann,
    COALESCE(heb.exist_billing_mo,  fem.exist_billing_mo,  0) AS exist_billing_mo,
    COALESCE(heb.exist_billing_ann, fea.exist_billing_ann, 0) AS exist_billing_ann
FROM all_months m
LEFT JOIN actual a ON a.billing_month = m.billing_month
LEFT JOIN existing_expected e ON e.billing_month = m.billing_month
LEFT JOIN new_monthly_sub_rev nm ON nm.billing_month = m.billing_month
LEFT JOIN new_annual_sub_rev na ON na.billing_month = m.billing_month
-- Historical subscriber counts
LEFT JOIN hist_created_counts hcc ON hcc.billing_month = m.billing_month
LEFT JOIN hist_converted_counts hcv ON hcv.billing_month = m.billing_month
LEFT JOIN hist_existing_billings heb ON heb.billing_month = m.billing_month
-- Forecast subscriber counts
LEFT JOIN forecast_created_counts fcc ON fcc.billing_month = m.billing_month
LEFT JOIN forecast_converted_mo fcvm ON fcvm.billing_month = m.billing_month
LEFT JOIN forecast_converted_ann fcva ON fcva.billing_month = m.billing_month
LEFT JOIN forecast_exist_monthly fem ON fem.billing_month = m.billing_month
LEFT JOIN forecast_exist_annual fea ON fea.billing_month = m.billing_month
ORDER BY m.billing_month;