Stripe Subscription Lifecycle Rules

Use this guide for ad hoc Stripe subscription questions that need a default counting or classification rule.

Canonical models

Use RAW_DB.STRIPE.SUBSCRIPTIONS for subscription lifecycle reporting. It is the default source for subscription creation, current status, plan amount and interval, trial fields, cancellation fields, end dates, and billing cadence.
Prefer RAW_DB.STRIPE.SUBSCRIPTIONS over RAW_DB.TINCAN.STRIPE_SUBSCRIPTION for default lifecycle counts. The published subscription guides in this workspace are built from the raw Stripe subscription object.
Use RAW_DB.STRIPE.INVOICES for paid billing events, cash-basis subscription revenue, and paid billing-cycle counts.
Use item-level Stripe tables only when the question specifically needs item-level detail that is not already present on the subscription object.

Filter to LIVEMODE = true by default.
Exclude STATUS = 'incomplete_expired' from default subscription lifecycle reporting.
Convert Stripe timestamps from UTC to America/Los_Angeles before daily or monthly bucketing.

Question	Default rule
Free vs paid	`PLAN:amount::NUMBER = 0` => free; `PLAN:amount::NUMBER > 0` => paid
Monthly vs annual	`PLAN:interval::VARCHAR = 'month'` => monthly; `PLAN:interval::VARCHAR = 'year'` => annual
Trials on paid plans	A `trialing` subscription with `PLAN:amount > 0` stays in the paid-plan population, but it should be shown separately from paying subscriptions

Use case	Default logic
Current or end-of-period total subscriptions	Count subscriptions that have not ended as of the reporting date
Current or end-of-period paid-plan subscriptions	Count paid-plan subscriptions that have not ended as of the reporting date. It is fine to include both paying and trialing paid-plan subscriptions in the total, and it is best practice to show the split
Paying subscribers	Count paid-plan subscriptions that are past trial and have not ended as of the reporting date. In practice this usually includes `active` and `past_due`, and excludes current `trialing`
Trial subscriptions	Count `STATUS = 'trialing'` on paid-price plans
Free subscriptions	Count subscriptions on `PLAN:amount = 0` that have not ended as of the reporting date

Use ENDED_AT to decide whether a subscription is still active on a given day or month end.
Use CANCELED_AT to timestamp cancellation events.

Default new subscriptions, new activations, or new adoption to RAW_DB.STRIPE.SUBSCRIPTIONS.CREATED, after converting to Pacific time.
In this workspace, new device activations has been explicitly defined as a new subscription being created.
CREATED measures when the Stripe subscription was created. It does not measure hardware sale date, fulfillment date, or first payment date.
For paid conversion questions, use the paid conversion event instead of CREATED:
successful trial-to-paid conversion: TRIAL_END when the subscription was not cancelled or ended before that date
first paid billing event: the first qualifying paid invoice in RAW_DB.STRIPE.INVOICES

Count paid billing cycles from RAW_DB.STRIPE.INVOICES.
The default qualifying invoice is:
LIVEMODE = true
SUBSCRIPTION IS NOT NULL
invoice is paid (STATUS = 'paid' or PAID = true)
AMOUNT_PAID > 0
Count one qualifying invoice as one completed paid billing cycle.
Zero-dollar invoices do not count as paid billing cycles.
For cash-basis Stripe subscription revenue, bucket revenue by the invoice CREATED timestamp after Pacific-time conversion and sum AMOUNT_PAID / 100.0.
To split billing cycles or revenue into monthly vs annual, join back to RAW_DB.STRIPE.SUBSCRIPTIONS and use PLAN:interval.

When reporting subscription totals, show the overall total plus a breakout for free subscriptions, paid subscriptions currently in trial, and paid subscriptions currently paying.
When the user says paying subscribers, exclude current trials by default.
When the user asks for subscription activations, treat that as subscription creation unless they explicitly ask for paid conversion or invoice-based activation.