Full Subscription Forecast Model

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', convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(i.CREATED::NUMBER)) >= (SELECT range_start FROM date_params)
      AND convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.CREATED::NUMBER)) >= DATEADD('day', -21, CURRENT_DATE)
      AND convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.BILLING_CYCLE_ANCHOR::NUMBER)) < cal_month
             AND (s.TRIAL_END IS NULL OR convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.TRIAL_END::NUMBER)) < cal_month)
             AND (s.ENDED_AT IS NULL OR convert_timezone('UTC','America/Los_Angeles',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', convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.ENDED_AT::NUMBER))) = cal_month
             AND (s.TRIAL_END IS NULL OR convert_timezone('UTC','America/Los_Angeles',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, CONVERT_TIMEZONE(
            'UTC',
            'America/Los_Angeles',
            MIN(TO_TIMESTAMP(CREATED::NUMBER))
        ) 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', convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.CREATED::NUMBER))),
               DATE_TRUNC('month', fi.first_paid)
           ), 2) AS lag_months
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    INNER JOIN first_invoices fi ON fi.SUBSCRIPTION = s.ID
    WHERE DATE_TRUNC('month', convert_timezone('UTC','America/Los_Angeles',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', convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.BILLING_CYCLE_ANCHOR::NUMBER))) AS first_billing_month,
        CASE
            WHEN s.ENDED_AT IS NOT NULL THEN DATE_TRUNC('month', convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.ENDED_AT::NUMBER)))
            WHEN s.CANCEL_AT IS NOT NULL THEN DATE_TRUNC('month', convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.CANCEL_AT::NUMBER)))
            WHEN s.CANCEL_AT_PERIOD_END = TRUE THEN DATE_TRUNC('month', convert_timezone('UTC','America/Los_Angeles',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', convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.CREATED::NUMBER)) >= (SELECT range_start FROM date_params)
      AND convert_timezone('UTC','America/Los_Angeles',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', fi.first_paid)::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 fi.first_paid >= (SELECT range_start FROM date_params)
      AND fi.first_paid < 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', convert_timezone('UTC','America/Los_Angeles',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 convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(i.CREATED::NUMBER)) != fi.first_paid
      AND s.PLAN:amount::NUMBER > 0
      AND s.PLAN:interval::TEXT IN ('month', 'year')
      AND convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(i.CREATED::NUMBER)) >= (SELECT range_start FROM date_params)
      AND convert_timezone('UTC','America/Los_Angeles',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 as "Billing Month",
    a.revenue                                 AS "Actual Revenue",
    ROUND(COALESCE(e.rev_with_churn, 0)
        + COALESCE(nm.revenue, 0)
        + COALESCE(na.revenue, 0), 2)         AS "Projected Future Revenue",
    ROUND(COALESCE(e.rev_with_churn, 0), 2)   AS existing_only_with_churn,
    ROUND(COALESCE(e.rev_no_churn, 0), 2)     AS "Recurring Revenue (Existing Subscriptions)",
    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,        fcc.created_mo,        0) AS created_mo,
    COALESCE(hcc.created_ann,       fcc.created_ann,       0) AS created_ann,
    COALESCE(hcv.converted_mo,      fcvm.converted_mo,     0) AS "New Monthly Subscriptions",
    COALESCE(hcv.converted_ann,     fcva.converted_ann,    0) AS "New Annual Subscriptions",
    COALESCE(heb.exist_billing_mo,  fem.exist_billing_mo,  0) AS "Active Monthly Subscriptions",
    COALESCE(heb.exist_billing_ann, fea.exist_billing_ann, 0) AS "Active Annual Subscriptions",
    m.month_idx
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

where m.month_idx >= 6

ORDER BY m.billing_month;

Overview

Produces a 25-month view of subscription revenue and subscriber counts, anchored on the most recently completed month. The first 13 months (month_idx 0–12) are historical; the final 12 months (month_idx 13–24) are projected. The anchor month (month_idx 12) is the most recently completed month.

All timestamps are converted from UTC to America/Los_Angeles before aggregation.

The output can also be used to calculate MRR and ARR: the Recurring Revenue (Existing Subscriptions) column (pre-churn existing revenue) provides the standard recurring revenue baseline for the 12 historical months.

The model tracks two subscriber populations separately:

• Existing subscribers: Subscriptions already in Stripe, projected forward using billing cadence, trial conversion rates, and churn decay.
• New subscribers: Future cohorts estimated from trailing acquisition run-rates, trial lag distributions, and the December holiday multiplier.

---

Model Parameters

All parameters are computed dynamically from recent data rather than hardcoded, with two exceptions:

Parameter	Value	Source
Monthly churn rate	Computed from trailing 6 completed months (month_idx 7–12), weighted by subscriber count	churn_calc / churn_rate CTEs
Trial conversion rate	Computed from all resolved trials (trial end date in the past)	trial_conversion CTE
Daily monthly acquisition rate	Trailing 21-day count of new paid monthly subs ÷ 21	new_sub_params CTE
Daily annual acquisition rate	Trailing 21-day count of new paid annual subs ÷ 21	new_sub_params CTE
Avg monthly plan revenue	$9.99 (hardcoded)	new_sub_params CTE
Avg annual plan revenue	$99.99 (hardcoded)	new_sub_params CTE
Trial lag distribution	Computed from trailing 6-month cohorts (excluding last 2 months and Nov/Dec 2025), capped at 2 months	lag_sample / monthly_lag / annual_lag CTEs
December holiday multiplier	3.8× (hardcoded) — see Holiday Seasonality note below	holiday_season CTE

Permanent exclusions

• December 2025 is excluded from churn rate calculations — it was a launch anomaly with an unusually small paying base and atypically high churn (10.3%) that would distort the trailing rate.
• November and December 2025 are excluded from trial lag distribution calculations — extended trials from the launch period would distort the lag weights.

---

Output Columns

Column	Description
Billing Month	Calendar month
Actual Revenue	Actual paid invoice revenue for historical months; NULL for forecast months
Projected Future Revenue	Total projected revenue for forecast months (existing + new monthly + new annual contributions); NULL for historical months
existing_only_with_churn	Projected revenue from existing subscriptions after churn decay
Recurring Revenue (Existing Subscriptions)	Projected revenue from existing subscriptions before churn decay — useful for understanding the theoretical ceiling
new_monthly_contrib	Projected revenue contribution from newly acquired monthly subscribers
new_annual_contrib	Projected revenue contribution from newly acquired annual subscribers
created_mo	New paid monthly subscriptions created (historical actual / forecast projected)
created_ann	New paid annual subscriptions created (historical actual / forecast projected)
New Monthly Subscriptions	Trial-to-paid conversions on the monthly plan
New Annual Subscriptions	Trial-to-paid conversions on the annual plan
Active Monthly Subscriptions	Recurring monthly subscribers billing this month (historical actual / forecast projected)
Active Annual Subscriptions	Annual subscribers billing this month (historical actual / forecast projected)
month_idx	Integer index: 0 = range start, 12 = anchor (most recently completed month), 13–24 = forecast

---

CTEs

Date scaffolding

• date_params — Computes range_start (13 months before current month) and range_end (11 months after).
• all_months — Generates the 25-month spine with month_idx 0–24.

Model inputs

• actual — Sums paid invoice amounts by month for the historical window.
• trial_conversion — Computes the fraction of resolved trials that resulted in a paid invoice.
• new_sub_params — Derives daily acquisition rates and hardcoded plan prices from the trailing 21 days.
• holiday_season — Hardcoded 3.8× December multiplier, based on observed December 2025 gift subscription volume (~34K paid subs vs. ~9K steady-state). This is the most assumption-heavy part of the model. The December 2025 multiplier is the only data point available, and it's unclear how representative it will be going forward. Revisit this as fall order volume starts to ramp in future years — that should provide an earlier signal on December seasonality.
• churn_calc / churn_rate — Computes weighted monthly churn from month_idx 7–12, excluding December 2025 and months with fewer than 1,000 paying subscribers.
• first_invoices — Finds the earliest paid invoice per subscription; shared foundation for lag distributions and historical counts.
• lag_sample / monthly_lag / annual_lag — Computes the distribution of months between subscription creation and first paid invoice, separately for monthly and annual plans. Capped at 2 months; weights normalized to 1.

Existing subscriber revenue

• all_subs — Pulls all non-expired subscriptions with their billing interval, amount, first billing month, and last billing month (using ENDED_AT, CANCEL_AT, or CANCEL_AT_PERIOD_END to determine end).
• existing_expected — Projects each existing subscription's revenue across the 25-month window. Applies churn decay (POWER(1 - monthly_churn, month_idx - 12)) only for forecast months; applies trial conversion rate to trialing subscriptions.

New subscriber revenue

• cohort_months — Defines the 12 forecast cohorts (one per future month) and computes days-in-month for acquisition count scaling. The current month cohort uses only remaining days.
• new_monthly_sub_rev — Projects monthly revenue from future monthly subscriber cohorts: acquisition count × trial conversion × lag weight × plan price × churn decay.
• new_annual_sub_rev — Projects annual revenue from future annual subscriber cohorts at two points: initial payment and 12-month renewal, both churn-adjusted.

Historical subscriber counts

• hist_created_counts — Actual new paid subscriptions created per month, by interval.
• hist_converted_counts — Actual trial-to-paid conversions per month (first paid invoice date), by interval.
• hist_existing_billings — Actual recurring (non-first) paid invoices per month, by interval.

Forecast subscriber counts

• forecast_created_counts — Projected new subscriptions per forecast month using daily rates and the December multiplier.
• all_created_counts — Union of historical and forecast created counts; used as a shared input for lag-based conversion lookups.
• forecast_converted_mo / forecast_converted_ann — Lag-weighted trial-to-paid conversion counts for forecast months. Split into separate CTEs to avoid a cartesian product between the monthly and annual lag tables.
• all_converted_counts — Union of historical and forecast conversion counts; used as input for annual anniversary billing lookups.
• paying_mo_pool_init — Starting pool of active paying monthly subscribers at the forecast boundary.
• forecast_exist_monthly — Recursive CTE. Projects the monthly paying subscriber pool forward month by month: each month's pool = prior pool × (1 − churn) + new conversions. Produces exist_billing_mo and carries next_pool forward.
• forecast_exist_annual — Anniversary-based annual renewal projection: conversions from 12 months prior × churn decay over 12 months.

---

Current Month Pro-Rating

The first forecast month (month_idx = 13) is a hybrid of actual and projected data to avoid double-counting subscriptions already in Stripe:

• Created counts: actual creations so far this month + forecast for remaining days
• New sub revenue: forecast uses only remaining days (CURRENT_DATE to month end), not the full month
• Existing sub revenue: projected normally from all subs currently in Stripe, including those created earlier 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 days not yet elapsed.

---

Notes

• Historical vs. forecast columns are mutually exclusive. Actual Revenue is only populated for month_idx 0–12; Projected Future Revenue only for month_idx 13–24. The final SELECT uses COALESCE to merge historical and forecast subscriber count columns into unified output columns.
• The recursive CTE (forecast_exist_monthly) requires Snowflake's WITH RECURSIVE syntax, declared at the top of the query.
• Churn is applied as exponential decay (POWER(1 - monthly_churn, n)) where n is months since the forecast anchor. This means churn compounds — a subscriber projected 12 months out has been exposed to 12 months of churn probability.
• Annual renewals are modeled as a single anniversary event at month 12, not spread across the year.
• The output is filtered to month_idx >= 6, trimming the earliest 6 historical months from the display window.