How to Set Up SaaS Metrics Tracking in Stripe: MRR, Churn, and LTV Without a BI Tool

Stripe is the source of truth for most SaaS companies' billing data. It knows exactly who is paying, how much, on what plan, and when they churned. The problem is that Stripe's native dashboard surfaces a limited slice of that data — enough to know your current MRR but not enough to operate a growth-stage SaaS business with rigor.

This guide covers what Stripe tracks natively, what it misses, and how to use Stripe Sigma (Stripe's built-in SQL tool) to extract the metrics that the native dashboard does not show. You will get five specific Stripe Sigma queries that produce the core SaaS metrics — MRR, churn, NRR, LTV, and cohort retention — without a dedicated analytics tool.

If you are at the stage where spending $129/month on Baremetrics is justified, skip the SQL and use a dedicated tool. But for founders under $30K MRR or technical operators who prefer working directly with data, Stripe Sigma is a powerful and underused resource.

What Stripe Tracks Natively (No Configuration Required)

Stripe's built-in Revenue dashboard (under Billing → Reporting) shows:

• MRR: Current monthly recurring revenue, with a basic breakdown of new, expansion, contraction, and churn components
• Subscriber Count: Active subscriptions at any point in time
• ARPU: Average revenue per user (subscriber)
• Net Volume: Total revenue collected over any date range
• New Subscribers / Churned Subscribers: Period-level customer movement

For a founder at $10K–$20K MRR with a simple subscription product, this is enough data to manage the business week-to-week. The moment you need to answer "why is NRR declining?" or "which acquisition cohort has the highest LTV?", the native dashboard falls short.

What Stripe Does Not Track Natively

NRR, cohort retention, and LTV by segment all require either Stripe Sigma queries or a dedicated metrics tool. CAC and Burn Multiple require data outside Stripe (ad spend, payroll) and cannot be calculated in Stripe regardless of query complexity.

Common Stripe MRR Errors

Before writing queries, understand the data quality issues that affect every Stripe-based metrics setup:

Error 1: Trial Subscribers in MRR Count

Stripe's subscriber count includes customers in free trials by default. If you have 100 active trials and 50 paid customers, Stripe shows 150 subscribers. If you calculate MRR using subscriber count × ARPU, you get inflated MRR.

Fix: In all MRR queries, filter on subscriptions.status = 'active' AND exclude subscriptions with trial_end > current_timestamp.

Error 2: Annual Plans Counted as Full MRR in Month 1

A customer on a $1,200/year plan generates $100/month in MRR, not $1,200. Stripe handles this correctly in its native MRR dashboard — it divides annual amounts by 12. But if you are writing raw SQL against the subscriptions table and pulling amount, you get the full billing amount, not the monthly recognized amount.

Fix: In SQL, calculate MRR as:

CASE 
  WHEN plans.interval = 'year' THEN plans.amount / 12.0
  WHEN plans.interval = 'month' THEN plans.amount
  ELSE plans.amount / plans.interval_count
END AS monthly_mrr

Error 3: Multi-Currency Without Normalization

Stripe stores amounts in local currency units (cents for USD, pence for GBP, etc.). A US customer paying $100/month and a UK customer paying £100/month are both stored as amount = 10000 — but £100 ≠ $100.

Fix: Normalize to your base currency in every query:

CASE 
  WHEN subscriptions.currency = 'usd' THEN plans.amount / 100.0
  WHEN subscriptions.currency = 'eur' THEN plans.amount / 100.0 * 1.08  -- use current rate
  WHEN subscriptions.currency = 'gbp' THEN plans.amount / 100.0 * 1.27  -- use current rate
  ELSE plans.amount / 100.0  -- default assumption; flag for review
END AS mrr_usd

Update exchange rates monthly and document the rates used for each reporting period.

Error 4: Failed Payments Still Active

Stripe marks subscriptions as past_due when payments fail, but they remain active until your dunning cycle completes and Stripe cancels them. If you calculate MRR on all active subscriptions, you include subscriptions that are effectively churned (just not yet formal). At 3–5% failed payment rate, this inflates MRR by 1–2%.

Fix: Filter on subscriptions.status = 'active' AND subscriptions.status != 'past_due' — or equivalently, only include subscriptions with canceled_at IS NULL and status = 'active'.

Stripe Sigma: Getting Started

Access Stripe Sigma at dashboard.stripe.com → Developers → Sigma.

Pricing: $0.02 per query, $10/month minimum. For running 5–10 queries/month, total cost is $10–$20/month.

Data freshness: Sigma queries run against data that is typically 24 hours stale. It is not real-time. For monthly reporting, this is not a problem. For daily monitoring, use the live Stripe dashboard instead.

Output: Download results as CSV or view inline. Export to Google Sheets for charting.

Stripe Sigma Query 1: MRR by Month

This is the foundational query — monthly MRR with the correct interval normalization.

SELECT
  DATE_TRUNC('month', subscriptions.current_period_start) AS month,
  COUNT(DISTINCT subscriptions.customer) AS active_customers,
  SUM(
    CASE 
      WHEN plans.interval = 'year' THEN plans.amount / 12.0 / 100.0
      WHEN plans.interval = 'month' THEN plans.amount / 100.0
      ELSE plans.amount / plans.interval_count / 100.0
    END
  ) AS mrr_usd
FROM subscriptions
JOIN plans ON subscriptions.plan = plans.id
WHERE subscriptions.status = 'active'
  AND subscriptions.trial_end IS NULL OR subscriptions.trial_end < NOW()
  AND subscriptions.currency = 'usd'  -- adjust for your primary currency
GROUP BY 1
ORDER BY 1 DESC
LIMIT 24

Run this monthly. The output gives you a clean MRR trend for the last 24 months.

Stripe Sigma Query 2: MRR Movement (Bridge)

The MRR bridge — new, expansion, contraction, churn, reactivation — requires comparing subscription states between two months.

WITH mrr_per_customer AS (
  SELECT
    subscriptions.customer,
    DATE_TRUNC('month', subscriptions.current_period_start) AS month,
    SUM(
      CASE 
        WHEN plans.interval = 'year' THEN plans.amount / 12.0 / 100.0
        WHEN plans.interval = 'month' THEN plans.amount / 100.0
        ELSE 0
      END
    ) AS mrr
  FROM subscriptions
  JOIN plans ON subscriptions.plan = plans.id
  WHERE subscriptions.status IN ('active', 'canceled')
    AND subscriptions.currency = 'usd'
  GROUP BY 1, 2
),
month_pairs AS (
  SELECT
    current_month.customer,
    current_month.month,
    current_month.mrr AS current_mrr,
    prior_month.mrr AS prior_mrr
  FROM mrr_per_customer AS current_month
  LEFT JOIN mrr_per_customer AS prior_month
    ON current_month.customer = prior_month.customer
    AND prior_month.month = DATE_TRUNC('month', DATEADD('month', -1, current_month.month))
)
SELECT
  month,
  SUM(CASE WHEN prior_mrr IS NULL AND current_mrr > 0 THEN current_mrr ELSE 0 END) AS new_mrr,
  SUM(CASE WHEN prior_mrr > 0 AND current_mrr > prior_mrr THEN current_mrr - prior_mrr ELSE 0 END) AS expansion_mrr,
  SUM(CASE WHEN prior_mrr > 0 AND current_mrr < prior_mrr AND current_mrr > 0 THEN current_mrr - prior_mrr ELSE 0 END) AS contraction_mrr,
  SUM(CASE WHEN prior_mrr > 0 AND current_mrr = 0 THEN -prior_mrr ELSE 0 END) AS churned_mrr,
  SUM(CASE WHEN prior_mrr = 0 AND current_mrr > 0 THEN current_mrr ELSE 0 END) AS reactivation_mrr
FROM month_pairs
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12

This is the most complex query but the most valuable. It gives you the MRR bridge that feeds into the SaaS metrics tracking spreadsheet Tab 1.

Stripe Sigma Query 3: Churn Rate by Month

WITH monthly_customers AS (
  SELECT
    DATE_TRUNC('month', subscriptions.current_period_start) AS month,
    subscriptions.customer,
    CASE WHEN subscriptions.status = 'active' THEN 1 ELSE 0 END AS is_active,
    CASE WHEN subscriptions.status = 'canceled' THEN 1 ELSE 0 END AS is_churned
  FROM subscriptions
  WHERE subscriptions.currency = 'usd'
),
monthly_summary AS (
  SELECT
    month,
    SUM(is_active) AS active_customers,
    SUM(is_churned) AS churned_this_month
  FROM monthly_customers
  GROUP BY 1
)
SELECT
  month,
  active_customers,
  churned_this_month,
  LAG(active_customers) OVER (ORDER BY month) AS prior_month_customers,
  ROUND(
    churned_this_month::DECIMAL / NULLIF(LAG(active_customers) OVER (ORDER BY month), 0) * 100, 
    2
  ) AS customer_churn_rate_pct
FROM monthly_summary
ORDER BY 1 DESC
LIMIT 12

This gives customer churn rate. For revenue churn rate, replace customer counts with MRR amounts — use the MRR query logic in the denominator and churned MRR from Query 2 in the numerator.

For churn rate interpretation and benchmarks, see churn rate calculator guide.

Stripe Sigma Query 4: Cohort Retention (NRR)

This is the query that most closely approximates what ChartMogul and Baremetrics show in their cohort tables.

WITH customer_first_month AS (
  SELECT
    customer,
    MIN(DATE_TRUNC('month', created)) AS cohort_month
  FROM subscriptions
  GROUP BY 1
),
monthly_mrr AS (
  SELECT
    subscriptions.customer,
    DATE_TRUNC('month', subscriptions.current_period_start) AS revenue_month,
    SUM(
      CASE 
        WHEN plans.interval = 'year' THEN plans.amount / 12.0 / 100.0
        WHEN plans.interval = 'month' THEN plans.amount / 100.0
        ELSE 0
      END
    ) AS mrr
  FROM subscriptions
  JOIN plans ON subscriptions.plan = plans.id
  WHERE subscriptions.status IN ('active', 'canceled')
    AND subscriptions.currency = 'usd'
  GROUP BY 1, 2
),
cohort_mrr AS (
  SELECT
    c.cohort_month,
    m.revenue_month,
    DATEDIFF('month', c.cohort_month, m.revenue_month) AS months_since_start,
    SUM(m.mrr) AS cohort_mrr
  FROM customer_first_month c
  JOIN monthly_mrr m ON c.customer = m.customer
  GROUP BY 1, 2, 3
),
cohort_month0 AS (
  SELECT cohort_month, cohort_mrr AS month0_mrr
  FROM cohort_mrr
  WHERE months_since_start = 0
)
SELECT
  cm.cohort_month,
  cm.months_since_start,
  cm.cohort_mrr,
  c0.month0_mrr,
  ROUND(cm.cohort_mrr / NULLIF(c0.month0_mrr, 0) * 100, 1) AS nrr_pct
FROM cohort_mrr cm
JOIN cohort_month0 c0 ON cm.cohort_month = c0.cohort_month
WHERE cm.cohort_month >= DATEADD('month', -12, DATE_TRUNC('month', CURRENT_DATE))
  AND cm.months_since_start <= 12
ORDER BY cm.cohort_month DESC, cm.months_since_start ASC

Export this to a spreadsheet and pivot it into a heatmap. The result is a month-by-month NRR retention table by cohort — the foundation of NRR analysis.

Stripe Sigma Query 5: LTV by Plan

WITH customer_plan AS (
  SELECT
    subscriptions.customer,
    plans.id AS plan_id,
    plans.nickname AS plan_name,
    MIN(subscriptions.created) AS first_sub_date,
    MAX(CASE WHEN subscriptions.status = 'canceled' THEN subscriptions.canceled_at ELSE CURRENT_TIMESTAMP END) AS last_active_date,
    AVG(
      CASE 
        WHEN plans.interval = 'year' THEN plans.amount / 12.0 / 100.0
        WHEN plans.interval = 'month' THEN plans.amount / 100.0
        ELSE 0
      END
    ) AS avg_monthly_mrr
  FROM subscriptions
  JOIN plans ON subscriptions.plan = plans.id
  WHERE subscriptions.currency = 'usd'
  GROUP BY 1, 2, 3
)
SELECT
  plan_name,
  COUNT(DISTINCT customer) AS customer_count,
  AVG(avg_monthly_mrr) AS avg_mrr,
  AVG(DATEDIFF('month', first_sub_date, last_active_date)) AS avg_lifetime_months,
  AVG(avg_monthly_mrr * DATEDIFF('month', first_sub_date, last_active_date)) AS estimated_ltv
FROM customer_plan
GROUP BY 1
ORDER BY estimated_ltv DESC

This produces estimated LTV by plan. Note: this is historical LTV based on actual customer lifetime, not projected LTV from a churn rate assumption. For growing companies, historical LTV underestimates future LTV (because newer cohorts have not had time to churn). For the LTV:CAC framework, see customer lifetime value guide.

Exporting to Your Metrics Tool

If you are using ChartMogul, Baremetrics, or another analytics platform alongside Stripe, these Sigma queries are most useful for:

1. Validation: Run Query 1 and compare the MRR output to your analytics tool. Any discrepancy points to a data normalization difference (usually trials, multi-currency, or annual plan treatment).

2. Ad hoc analysis: When your analytics tool does not have the specific slice you need — LTV by acquisition channel, churn rate for a specific plan — Sigma lets you query it directly.

3. Data warehouse feeds: If you are building a data warehouse (Snowflake, BigQuery, Redshift), Stripe Sigma exports to CSV which you can automate into your pipeline.

For the full tool comparison including what Stripe cannot do that dedicated analytics tools solve, see ChartMogul alternatives.

Building a Lightweight Dashboard from Sigma

For founders who want a metrics view without paying for an analytics tool, a practical setup:

1. Monthly cadence: Run Queries 1–3 on the first Monday of each month, export to Google Sheets
2. Cohort cadence: Run Query 4 quarterly, export and build the cohort heatmap
3. LTV audit: Run Query 5 semi-annually, compare to your LTV:CAC ratio model

Total time investment: 2–3 hours/month. This is viable at <$30K MRR. Above that, the $129/month Baremetrics subscription becomes clearly worth the time savings.

Conclusion

Stripe Sigma is a legitimate analytics tool for technically capable founders who want to work directly with their billing data. The five queries in this guide cover the core SaaS metrics that Stripe's native dashboard misses — NRR, cohort retention, LTV by plan, and accurate MRR movement.

The critical discipline is data quality before analysis: eliminate trial contamination, normalize multi-currency amounts, and exclude past-due subscriptions from your active customer counts. Wrong inputs produce confident-looking wrong outputs.

For the next step — connecting Stripe data to a financial model and forecasting forward — see the SaaS financial model template and the SaaS ARR forecasting guide. And use the calculator to model how your current Stripe metrics project forward under different churn and growth scenarios.

Frequently Asked Questions

What SaaS metrics does Stripe track natively?

Stripe tracks MRR (including new, expansion, contraction, and churn components), subscriber counts, average revenue per user (ARPU), and basic revenue trends natively. It does not calculate NRR, cohort retention, LTV, or CAC without custom Stripe Sigma queries.

What is Stripe Sigma and how much does it cost?

Stripe Sigma is a SQL-based reporting tool that gives you direct read access to your Stripe data. It costs $0.02 per query with a $10/month minimum, making it effectively free for low-query usage. You write SQL queries against Stripe's data schema, and results return as downloadable CSV or inline tables.

How do I calculate NRR in Stripe Sigma?

NRR requires comparing MRR from a customer cohort at a starting date vs. their MRR at a later date, including expansion and accounting for churn. The query groups customers by their first active month, sums their MRR at Month 0 and Month 12, and divides: NRR = Month 12 MRR / Month 0 MRR × 100. See the full SQL in this guide.

Why does my Stripe MRR not match my actual revenue?

Common causes: (1) Trials included in subscriber count but not yet billed; (2) Multi-currency amounts not normalized to base currency; (3) Annual contracts counted as full MRR in month 1 instead of monthly recognized amount; (4) Refunds not excluded from MRR calculation; (5) Failed payments still showing as active subscriptions. Each requires a specific Stripe Sigma fix.