Why your Metabase MRR is wrong (and how to actually fix it)

The first MRR query somebody writes against a Stripe-backed database almost always looks the same. It picks up active subscriptions, sums the price, divides annual plans by 12, and calls it done. Three months later, the finance team and the growth team are looking at the same dashboard and getting numbers 8% apart, and nobody can explain why.

I have rebuilt this query for half a dozen SaaS clients and the gap is always the same handful of edge cases. None of them are exotic. They are just things that the naive version silently gets wrong, and they compound.

This post walks through the broken version, then layers in fixes for annual contracts, grace periods, refunds, churn, and the difference between gross and net MRR. The schema below is the standard Fivetran / Airbyte Stripe export, which is what most teams I work with end up with.

The naive query everyone starts with

You inherit a Metabase. There is a card called "MRR" that looks like this:

SELECT
  date_trunc('month', current_date) AS month,
  SUM(
    CASE
      WHEN p.interval = 'month' THEN p.unit_amount / 100.0
      WHEN p.interval = 'year'  THEN p.unit_amount / 100.0 / 12
    END
  ) AS mrr
FROM stripe.subscription s
JOIN stripe.price p ON p.id = s.plan_id
WHERE s.status = 'active';

It is not exactly wrong. It returns a number that vaguely tracks reality. The issue is that "active" hides four different states, the price might be discounted, and there is no historical version of this number, so you can never go back and explain what changed.

Let's fix it one layer at a time.

Annual contracts: monthly recognition done correctly

Dividing annual ARR by 12 is the right idea, but only for the months the contract actually covers. A subscription that started on March 15 should not count as full-month MRR in March, and it should not count in February at all. The naive query treats every active sub as if it has been active forever.

To do this correctly you need to expand each subscription into its monthly slices. The cleanest way is a date spine joined to subscription periods:

WITH months AS (
  SELECT generate_series(
    date_trunc('month', '2023-01-01'::date),
    date_trunc('month', current_date),
    interval '1 month'
  )::date AS month_start
),
sub_periods AS (
  SELECT
    s.id                                      AS subscription_id,
    s.customer_id,
    p.unit_amount / 100.0                     AS price,
    p.interval,
    s.current_period_start::date              AS period_start,
    s.current_period_end::date                AS period_end,
    s.status,
    s.cancel_at_period_end
  FROM stripe.subscription s
  JOIN stripe.price p ON p.id = s.plan_id
)
SELECT
  m.month_start,
  SUM(
    CASE
      WHEN sp.interval = 'month' THEN sp.price
      WHEN sp.interval = 'year'  THEN sp.price / 12
    END
  ) AS mrr
FROM months m
LEFT JOIN sub_periods sp
  ON sp.period_start <= (m.month_start + interval '1 month - 1 day')::date
 AND sp.period_end   >= m.month_start
WHERE sp.status IN ('active', 'trialing', 'past_due')
GROUP BY 1
ORDER BY 1;

Two things changed. First, MRR is computed for every month from a fixed start, not just the current one. Second, a subscription only contributes to a month's MRR if its period overlaps that month. The annual plan that started on March 15 will appear in March, not in February.

If you want strict daily-prorated MRR (some boards do, most do not), you compute the overlap in days and weight the monthly value. For most reporting purposes the "active during this month, count as 1 month" rule is fine, and it avoids confusing the finance team with fractional days.

Grace periods: canceled but still paying

A common mistake: filtering on s.status = 'active' and treating everything else as gone. In Stripe, a customer who cancels at the end of the current period stays in active status with cancel_at_period_end = true. They are not gone yet. They are paying through the end of the month.

The opposite case: past_due. Stripe keeps the subscription in past_due while it retries the payment. Whether you count this as MRR depends on what question you are answering. Finance might want it out (it's not collected revenue). Growth might want it in (the customer hasn't quit yet).

The honest fix is to expose both:

SELECT
  m.month_start,
  SUM(CASE WHEN sp.status = 'active'    THEN sp.monthly_value END) AS mrr_collected,
  SUM(CASE WHEN sp.status = 'trialing'  THEN sp.monthly_value END) AS mrr_trialing,
  SUM(CASE WHEN sp.status = 'past_due'  THEN sp.monthly_value END) AS mrr_at_risk,
  SUM(CASE WHEN sp.status IN ('active','past_due') THEN sp.monthly_value END) AS mrr_blended
FROM months m
LEFT JOIN sub_periods sp
  ON sp.period_start <= (m.month_start + interval '1 month - 1 day')::date
 AND sp.period_end   >= m.month_start
GROUP BY 1
ORDER BY 1;

Now the dashboard has four columns and the finance team can pick the one that matches how they recognize revenue. More importantly, when somebody asks "why is MRR different from what Stripe showed last week", you can point to the gap between mrr_collected and mrr_blended and explain it.

Refunds: full, partial, and how Stripe records them

The naive query never touches refunds. It just looks at active subscriptions and sums prices. But MRR for a given month should subtract refunds issued for that month's invoices, otherwise you are reporting numbers that finance will reconcile against bank deposits and find higher than reality.

In the Stripe schema, refunds live in stripe.refund, joined back to stripe.charge and from there to stripe.invoice and the originating stripe.subscription. Partial refunds are common: a customer downgrades mid-cycle, you refund half the month.

WITH refunds_by_month AS (
  SELECT
    date_trunc('month', i.period_start)::date AS month_start,
    i.subscription_id,
    SUM(r.amount) / 100.0                     AS refunded
  FROM stripe.refund r
  JOIN stripe.charge  c ON c.id = r.charge_id
  JOIN stripe.invoice i ON i.charge_id = c.id
  WHERE i.subscription_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  m.month_start,
  SUM(sp.monthly_value) - COALESCE(SUM(rf.refunded), 0) AS mrr_net_refunds
FROM months m
LEFT JOIN sub_periods    sp ON sp.period_start <= (m.month_start + interval '1 month - 1 day')::date
                            AND sp.period_end   >= m.month_start
LEFT JOIN refunds_by_month rf ON rf.subscription_id = sp.subscription_id
                              AND rf.month_start    = m.month_start
WHERE sp.status IN ('active', 'past_due')
GROUP BY 1
ORDER BY 1;

Two things to watch here. First, partial refunds get spread across the original invoice's period, not the refund date. A refund issued on April 5 for a March invoice belongs to March MRR, not April. Second, if you also do credit notes (stripe.credit_note), they need the same treatment. Most teams I have seen ignore credit notes entirely and then wonder why their MRR drifts from finance's books.

Churn: gross, net, voluntary, involuntary

Churn is where most MRR dashboards fall apart, because there are at least four definitions of it and people use the word interchangeably.

• Gross churn rate: MRR lost from cancellations and downgrades, divided by start-of-period MRR.
• Net churn rate: Gross churn minus expansion (upgrades, seat additions, etc).
• Voluntary churn: Customer canceled.
• Involuntary churn: Payment failed and never recovered (the past_due to canceled transition).

Computing these correctly requires a "what changed this month" view, not a snapshot. The cleanest pattern is to compare each customer's MRR contribution this month to the prior month:

WITH monthly_customer_mrr AS (
  SELECT
    m.month_start,
    sp.customer_id,
    SUM(sp.monthly_value) AS customer_mrr
  FROM months m
  LEFT JOIN sub_periods sp
    ON sp.period_start <= (m.month_start + interval '1 month - 1 day')::date
   AND sp.period_end   >= m.month_start
  WHERE sp.status IN ('active', 'past_due')
  GROUP BY 1, 2
),
mrr_movements AS (
  SELECT
    curr.month_start,
    curr.customer_id,
    COALESCE(prev.customer_mrr, 0) AS mrr_prev,
    COALESCE(curr.customer_mrr, 0) AS mrr_curr,
    CASE
      WHEN prev.customer_mrr IS NULL AND curr.customer_mrr > 0 THEN 'new'
      WHEN curr.customer_mrr IS NULL OR curr.customer_mrr = 0  THEN 'churned'
      WHEN curr.customer_mrr > prev.customer_mrr               THEN 'expansion'
      WHEN curr.customer_mrr < prev.customer_mrr               THEN 'contraction'
      ELSE 'flat'
    END AS movement
  FROM monthly_customer_mrr curr
  LEFT JOIN monthly_customer_mrr prev
    ON prev.customer_id = curr.customer_id
   AND prev.month_start = curr.month_start - interval '1 month'
)
SELECT
  month_start,
  SUM(CASE WHEN movement = 'new'         THEN mrr_curr - mrr_prev END) AS new_mrr,
  SUM(CASE WHEN movement = 'expansion'   THEN mrr_curr - mrr_prev END) AS expansion_mrr,
  SUM(CASE WHEN movement = 'contraction' THEN mrr_prev - mrr_curr END) AS contraction_mrr,
  SUM(CASE WHEN movement = 'churned'     THEN mrr_prev END)            AS churned_mrr
FROM mrr_movements
GROUP BY 1
ORDER BY 1;

This is the version your CFO actually wants. Now you can show net new MRR, churn rate, and quick ratio without arguing about which column is which.

A few traps to watch out for here:

• A customer with two subscriptions counts once if you aggregate on customer_id. If they cancel one and keep the other, that's contraction, not churn. The naive version reports it as churn.
• Subscription pauses (status = 'paused') need a decision: count as churn or as flat? Pick one and document it.
• Trial-to-paid conversions are not "new MRR" if you were counting trials in MRR already. Pick one or the other.

Putting it all together

The full version of the MRR query for a Stripe-backed Metabase is roughly 100 lines. It is more than most teams want in a single saved question, and that is the point. You break it into:

1. A monthly_customer_mrr model (CTE or dbt model) that returns one row per customer per month with their net MRR.
2. A mrr_movements model on top that classifies each row's change.
3. Dashboard cards on top that aggregate the movements into the four standard MRR views: total, components, growth, churn.

Once that lives in dbt or even in Metabase as a single trusted "model" question, every other dashboard references it. You stop having seven copies of MRR with seven different answers, which was point 2 of the audit post.

Why this still goes wrong in production

Even with the right query, MRR will drift from Stripe's number for three reasons:

1. Stripe's MRR includes things you might not want. Setup fees, one-time charges that got attached to subscriptions, and tax in some jurisdictions. Your version is more "what's the recurring revenue commitment", Stripe's is "what subscriptions are billing".
2. Currency conversion. If you have non-USD subs and convert at month-end FX, your MRR will move when nobody added or canceled. Pick a snapshot rate and document it.
3. Late updates. A customer adds seats on April 30. The webhook lands May 1. April's MRR retroactively goes up. Either you accept that historical numbers can shift, or you snapshot MRR at month-end and never recompute. Both are valid; pick one.

These are the questions I get asked on every audit. They are not bugs in the query, they are policy decisions that should be written down somewhere a finance team can find them.

What MetaLens does about it

When MetaLens scans a Metabase, the MRR-style queries are the ones it pulls apart most carefully. It flags every saved question that looks like it's computing recurring revenue, compares them against each other, and surfaces the ones that disagree. Half the time the problem is one of the cases above. The other half it is a cell-by-cell discrepancy that nobody has the time to track down manually.

If your finance and growth dashboards have been off by a few percent for as long as you can remember, it is almost certainly one of these five things. Start with the naive query, and walk it through annual contracts, grace periods, refunds, and movements in that order. If you only fix one, fix the movements query. That is the one that lets you explain a number, instead of just reporting it.