Daily Subscriptions

Query

With

--grab all subscriptions and identify dates created, cancelled, ended, and ended. Identify type of subscription

subs AS (
    SELECT
        ID,
        convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(CREATED))::DATE as created_date,
        --TO_TIMESTAMP(CREATED)::DATE AS created_date,
        CASE WHEN CANCELED_AT IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(CANCELED_AT::NUMBER))::DATE END AS canceled_date,
        CASE WHEN ENDED_AT   IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(ENDED_AT::NUMBER))::DATE END AS ended_date,
        CASE WHEN TRIAL_END  IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(TRIAL_END::NUMBER))::DATE END AS trial_end_date,
        CASE
            WHEN PLAN:amount::NUMBER = 0                                      THEN 'free'
            WHEN PLAN:interval::VARCHAR = 'year'                              THEN 'annual'
            WHEN PLAN:interval::VARCHAR = 'month' AND PLAN:amount::NUMBER > 0 THEN 'monthly'
            ELSE 'other'
        END AS plan_type
    FROM RAW_DB.STRIPE.SUBSCRIPTIONS
    WHERE LIVEMODE = true
      AND STATUS != 'incomplete_expired'
)

--Count active subscriptions by date and type as well as creations, cancellations, and conversions from trial to paid by date of event

SELECT
    id.dt,

    -- Daily Events
    SUM(CASE WHEN s.created_date = id.dt
        THEN 1 ELSE 0 END) AS new_subs,
    SUM(CASE WHEN s.created_date = id.dt AND s.plan_type = 'free'
        THEN 1 ELSE 0 END) AS new_subs_free,
    SUM(CASE WHEN s.created_date = id.dt AND s.plan_type IN ('monthly','annual')
        THEN 1 ELSE 0 END) AS new_subs_paid,
    SUM(CASE WHEN s.created_date = id.dt AND s.plan_type = 'monthly'
        THEN 1 ELSE 0 END) AS new_subs_monthly,
    SUM(CASE WHEN s.created_date = id.dt AND s.plan_type = 'annual'
        THEN 1 ELSE 0 END) AS new_subs_annual,

    SUM(CASE WHEN s.trial_end_date = id.dt
              AND s.plan_type IN ('monthly','annual')
              AND (s.canceled_date IS NULL OR s.canceled_date > s.trial_end_date)
              AND (s.ended_date   IS NULL OR s.ended_date   > s.trial_end_date)
        THEN 1 ELSE 0 END) AS trial_to_paid_conversions,

    SUM(CASE WHEN s.trial_end_date = id.dt
              AND s.plan_type = 'monthly'
              AND (s.canceled_date IS NULL OR s.canceled_date > s.trial_end_date)
              AND (s.ended_date   IS NULL OR s.ended_date   > s.trial_end_date)
        THEN 1 ELSE 0 END) AS trial_to_paid_conversions_monthly,

    SUM(CASE WHEN s.trial_end_date = id.dt
              AND s.plan_type = 'annual'
              AND (s.canceled_date IS NULL OR s.canceled_date > s.trial_end_date)
              AND (s.ended_date   IS NULL OR s.ended_date   > s.trial_end_date)
        THEN 1 ELSE 0 END) AS trial_to_paid_conversions_annual,

    SUM(CASE WHEN s.canceled_date = id.dt
        THEN 1 ELSE 0 END) AS cancellations,
    SUM(CASE WHEN s.canceled_date = id.dt AND s.plan_type IN ('monthly','annual')
        THEN 1 ELSE 0 END) AS cancellations_paid,
    SUM(CASE WHEN s.canceled_date = id.dt AND s.plan_type = 'monthly'
        THEN 1 ELSE 0 END) AS cancellations_monthly,
    SUM(CASE WHEN s.canceled_date = id.dt AND s.plan_type = 'annual'
        THEN 1 ELSE 0 END) AS cancellations_annual,
    SUM(CASE WHEN s.canceled_date = id.dt AND s.plan_type = 'free'
        THEN 1 ELSE 0 END) AS cancellations_free,

    -- Point-in-Time Counts (end of day)
    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
        THEN 1 ELSE 0 END) AS active_subs_eod,

    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
              AND s.plan_type IN ('monthly','annual')
              AND (s.trial_end_date IS NULL OR s.trial_end_date <= id.dt)
        THEN 1 ELSE 0 END) AS active_paid_subs_eod,

    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
              AND s.plan_type = 'monthly'
              AND (s.trial_end_date IS NULL OR s.trial_end_date <= id.dt)
        THEN 1 ELSE 0 END) AS active_paid_monthly_subs_eod,

    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
              AND s.plan_type = 'annual'
              AND (s.trial_end_date IS NULL OR s.trial_end_date <= id.dt)
        THEN 1 ELSE 0 END) AS active_paid_annual_subs_eod,

    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
              AND s.trial_end_date IS NOT NULL
              AND s.trial_end_date > id.dt
        THEN 1 ELSE 0 END) AS active_trials_eod,

    SUM(CASE WHEN (s.ended_date IS NULL OR s.ended_date > id.dt)
              AND s.plan_type = 'free'
        THEN 1 ELSE 0 END) AS active_free_subs_eod

FROM included_dates id
left outer join subs s
  ON s.created_date <= id.dt
WHERE id.dt <= CURRENT_DATE
GROUP BY ALL
ORDER BY 1;

Overview

Joins all live Stripe subscriptions onto the date spine to produce a daily record of subscription activity and point-in-time counts. Each row represents a single calendar date and includes both event-based metrics (things that happened on that day) and end-of-day snapshots (how many subscriptions were active at close of day).

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

Depends on: included_dates (date_spine)

Plan Types

Subscriptions are classified into four plan types based on Stripe's PLAN JSON field:

Plan Type	Definition
`free`	`plan_amount = 0`
`monthly`	`plan_interval = 'month'` and `plan_amount > 0`
`annual`	`plan_interval = 'year'`
`other`	Any subscription not matching the above

Output Columns

Daily event metrics

Counts of subscriptions where the relevant event (creation, trial conversion, cancellation) occurred on dt.

Column	Description
`dt`	Calendar date
`new_subs`	All new subscriptions created
`new_subs_free`	New free plan subscriptions
`new_subs_paid`	New paid subscriptions (monthly + annual)
`new_subs_monthly`	New monthly subscriptions
`new_subs_annual`	New annual subscriptions
`trial_to_paid_conversions`	Trials that converted to paid on `dt` (monthly + annual, not yet cancelled or ended)
`trial_to_paid_conversions_monthly`	Trial-to-paid conversions on the monthly plan
`trial_to_paid_conversions_annual`	Trial-to-paid conversions on the annual plan
`cancellations`	All cancellations
`cancellations_paid`	Cancellations of paid subscriptions (monthly + annual)
`cancellations_monthly`	Cancellations of monthly subscriptions
`cancellations_annual`	Cancellations of annual subscriptions
`cancellations_free`	Cancellations of free subscriptions

End-of-day snapshot metrics

Point-in-time counts of active subscriptions at the close of dt. A subscription is active on a given date if it has not yet ended (ended_date IS NULL OR ended_date > dt).

Column	Description
`active_subs_eod`	All active subscriptions
`active_paid_subs_eod`	Active paid subscriptions (monthly + annual) whose trial has ended or never existed
`active_paid_monthly_subs_eod`	Active monthly subscriptions past trial
`active_paid_annual_subs_eod`	Active annual subscriptions past trial
`active_trials_eod`	Active subscriptions still within a trial
`active_free_subs_eod`	Active free subscriptions

Notes

Trial conversion logic: A conversion is only counted if the subscription has not been cancelled or ended before the trial end date — i.e. the customer successfully converted to paid.
ended_date vs. canceled_date: ended_date is used for point-in-time active counts (Stripe sets this when a subscription fully terminates). canceled_date is used for cancellation event metrics (set when the customer cancels, which may precede the actual end date).
other plan type: Subscriptions classified as other are included in active_subs_eod and new_subs totals but will not appear in any plan-specific breakdowns. Worth monitoring if unexpected plan configurations appear in Stripe.