How to Analyze Stripe Subscriptions with SQL

Stripe's dashboard tells you how many active subscriptions you have. It does not tell you which signup cohort has the worst retention, whether your annual plans churn faster than monthly ones, or how many past-due subscriptions actually recover after a failed payment. For that, you need SQL against the raw data.

The queries below assume your Stripe data is in a SQL-queryable store. The tables: subscriptions, invoices, and charges, with Stripe's standard column names. If you're pulling from a Stripe replica via Fivetran or Airbyte, the schema is close to what's shown here. Fastero connects directly to Stripe and lands the data into DuckDB, so you can query it without setting up a separate warehouse or exporting CSVs.

One thing before we start: Stripe stores all monetary amounts in cents. Every query below divides by 100. Miss that once and your MRR looks like $4.7 million instead of $47,000.

Current MRR

The simplest useful metric, and the one most often calculated wrong. You can't just sum plan_amount across active subscriptions because annual plans inflate the number. A customer paying $12,000/year is $1,000/month of MRR, not $12,000.

SELECT
    ROUND(SUM(
        CASE
            WHEN plan_interval = 'year'  THEN plan_amount / 100.0 / 12
            WHEN plan_interval = 'month' THEN plan_amount / 100.0
        END
    ), 2) AS current_mrr,
    COUNT(*) AS active_subscriptions
FROM subscriptions
WHERE status = 'active';

This deliberately excludes trialing and past_due. Trials haven't paid yet. Past-due might recover, might not. If your finance team wants a more aggressive number, add 'past_due' to the filter, but label it separately. Mixing confirmed revenue with hope-revenue in the same figure is how board decks get embarrassing. For the full edge-case treatment (coupons, metered billing, multi-currency), see How to Calculate Real MRR from the Stripe API.

Subscription status breakdown

Before you analyze anything else, know what you're working with:

SELECT
    status,
    COUNT(*) AS subscription_count,
    ROUND(SUM(plan_amount / 100.0), 2) AS total_plan_value
FROM subscriptions
GROUP BY status
ORDER BY subscription_count DESC;

A healthy SaaS has mostly active, a thin slice of trialing, a small past_due tail, and canceled as the largest non-active bucket. If past_due is more than 5-8% of your active count, your dunning flow needs work. If trialing subscriptions outnumber active ones, your trial-to-paid conversion is broken or your trial length is too long.

Signup cohorts and retention

When did each batch of subscribers join, and how many are still around? This is the query that usually surprises people the most.

SELECT
    DATE_TRUNC('month', created) AS cohort_month,
    COUNT(*) AS started,
    COUNT(*) FILTER (WHERE status = 'active') AS still_active,
    COUNT(*) FILTER (WHERE status = 'canceled') AS canceled,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status = 'active') / COUNT(*),
        1
    ) AS retention_pct
FROM subscriptions
GROUP BY cohort_month
ORDER BY cohort_month;

Read the retention_pct column carefully. If your January cohort retains at 72% but your April cohort is at 41%, something changed in the product, the onboarding, or the acquisition channel between those months. The aggregate churn number hides this completely.

One caveat: this uses current status, not a point-in-time snapshot. A subscription created in January and canceled in March shows as canceled in the January cohort regardless of when you run the query. For proper cohort retention curves over time, you'd need a subscription status history table or event log, which Stripe doesn't provide natively.

Churn analysis

Aggregate churn rate matters, but the distribution of when people cancel matters more. Do they bail in the first week? After three months? After the annual renewal?

SELECT
    CASE
        WHEN cancel_at_period_end = true THEN 'end_of_period'
        ELSE 'immediate'
    END AS cancellation_type,
    ROUND(AVG(
        EXTRACT(EPOCH FROM (canceled_at - created)) / 86400.0
    ), 0) AS avg_days_to_cancel,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (canceled_at - created)) / 86400.0
    ), 0) AS median_days_to_cancel,
    COUNT(*) AS cancel_count
FROM subscriptions
WHERE canceled_at IS NOT NULL
GROUP BY cancellation_type;

The split between end_of_period and immediate cancels is worth watching. End-of-period cancellations are deliberate: the customer decided they don't want to renew. Immediate cancellations often signal frustration, a billing dispute, or a failed onboarding. If your median time-to-cancel is under 14 days, people are signing up, not finding value, and leaving. That's an activation problem, not a retention problem.

Revenue by plan

Which pricing tiers actually drive your business?

SELECT
    plan_interval,
    plan_amount / 100 AS plan_price,
    COUNT(*) AS sub_count,
    ROUND(SUM(
        CASE
            WHEN plan_interval = 'year'  THEN plan_amount / 100.0 / 12
            WHEN plan_interval = 'month' THEN plan_amount / 100.0
        END
    ), 2) AS mrr_contribution,
    ROUND(
        100.0 * SUM(
            CASE
                WHEN plan_interval = 'year'  THEN plan_amount / 100.0 / 12
                WHEN plan_interval = 'month' THEN plan_amount / 100.0
            END
        ) / NULLIF((
            SELECT SUM(
                CASE
                    WHEN plan_interval = 'year'  THEN plan_amount / 100.0 / 12
                    WHEN plan_interval = 'month' THEN plan_amount / 100.0
                END
            ) FROM subscriptions WHERE status = 'active'
        ), 0),
        1
    ) AS pct_of_total_mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY plan_interval, plan_amount
ORDER BY mrr_contribution DESC;

Most SaaS companies discover that 60-80% of MRR comes from one or two plan tiers. The other tiers exist, have a handful of customers each, and add complexity to your pricing page without moving revenue. If a tier contributes less than 5% of MRR and has fewer than 10 subscribers, seriously consider sunsetting it.

Note: this query doesn't handle metered billing subscriptions, which have no fixed plan_amount. For those, you'd join to invoices and use the most recent invoice amount as a proxy.

Dunning recovery: past-due outcomes

A subscription enters past_due when a payment fails. Some recover after the card is updated. Some churn. Knowing your recovery rate tells you whether to invest in better dunning emails or accept the loss.

WITH past_due_subs AS (
    SELECT
        s.id AS subscription_id,
        s.customer_id,
        s.status AS current_status,
        MIN(c.created) FILTER (WHERE c.status = 'failed') AS first_failure,
        MAX(c.created) FILTER (WHERE c.status = 'succeeded'
            AND c.created > (
                SELECT MIN(c2.created) FROM charges c2
                WHERE c2.customer_id = s.customer_id AND c2.status = 'failed'
            )
        ) AS recovery_charge
    FROM subscriptions s
    JOIN charges c ON c.customer_id = s.customer_id
    WHERE s.status IN ('active', 'past_due', 'canceled')
      AND EXISTS (
          SELECT 1 FROM charges c3
          WHERE c3.customer_id = s.customer_id AND c3.status = 'failed'
      )
    GROUP BY s.id, s.customer_id, s.status
)
SELECT
    CASE
        WHEN current_status = 'active' AND recovery_charge IS NOT NULL THEN 'recovered'
        WHEN current_status = 'canceled' THEN 'churned_after_failure'
        WHEN current_status = 'past_due' THEN 'still_past_due'
        ELSE 'other'
    END AS outcome,
    COUNT(*) AS subscription_count
FROM past_due_subs
GROUP BY outcome
ORDER BY subscription_count DESC;

Industry benchmarks put involuntary churn (failed payments that never recover) at 2-4% of subscribers annually. If your churned_after_failure count is high relative to recovered, look at your retry schedule. Stripe's Smart Retries help, but they're not magic. Sometimes the fix is a well-timed email to the customer asking them to update their card, sent before the final retry fails. For alerts when payment failures cross a threshold, see How to Get Slack Alerts When Business Metrics Change.

Trial conversion by month

The last query ties the funnel together. How many trials convert to paying subscriptions?

SELECT
    DATE_TRUNC('month', created) AS trial_month,
    COUNT(*) AS trials_started,
    COUNT(*) FILTER (WHERE status = 'active') AS converted_to_active,
    COUNT(*) FILTER (WHERE status = 'canceled') AS canceled_from_trial,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status = 'active') / NULLIF(COUNT(*), 0),
        1
    ) AS conversion_rate_pct
FROM subscriptions
WHERE current_period_start IS NOT NULL
  AND created < current_period_end  -- had at least one period
GROUP BY trial_month
ORDER BY trial_month;

Same caveat as the cohort query: this uses current status, so a trial that converted and later canceled shows as canceled, not as a conversion. If you need true trial-to-active conversion, filter to subscriptions where canceled_at is either NULL or greater than current_period_start of the second billing period. It's messier, but it separates "converted then churned later" from "never converted at all."

A healthy trial conversion rate for B2B SaaS sits around 15-25% for freemium trials, 40-60% for opt-in trials with a credit card required. If you're below those ranges, the problem is almost never the trial length. It's what happens (or doesn't happen) during the trial.

Putting it together

These six queries give you the core subscription analytics that Stripe's dashboard doesn't: MRR with proper interval normalization, cohort retention, churn timing and type, plan-level revenue concentration, dunning outcomes, and trial conversion trends. Each one answers a question that leads to a specific action.

If you want these queries running on a schedule with the results landing in a dashboard or a Slack alert, Fastero pulls your Stripe data into a DuckDB store and lets you query, visualize, and automate without stitching together a pipeline. The queries above work as-is. For reconciling Stripe revenue against your CRM, see How to Reconcile Stripe Revenue with Your CRM.

Try Fastero free — connect Stripe and start querying your subscription data in minutes. No credit card required.