SaaS Metrics in PostgreSQL: SQL Queries for MRR, ARR, Churn, and Cohort Retention

Search for SaaS analytics and you will mostly find tool recommendations: ChartMogul, Baremetrics, or a data warehouse with dbt and a BI layer on top. Those tools have their place. But if your subscription data already lives in PostgreSQL, you can compute your SaaS metrics in PostgreSQL itself, with SQL you control and no additional infrastructure. We have set this up as the first analytics layer on several client projects, and it usually holds until well past product-market fit. This post walks through the queries: MRR with proration handling, ARR, logo and revenue churn, net revenue retention, cohort retention, and LTV to CAC, plus the details that quietly skew each number.

Why SaaS metrics in PostgreSQL beat a tool at the start

A dedicated analytics tool gives you dashboards on day one. It also gives you a second copy of your revenue data and one more sync job that can silently drift. Every number the tool shows was computed from events your own database already stores, so until your reporting needs grow complicated, the shortest path is to query the source directly.

Writing the queries yourself has a second benefit: it forces you to define your metrics precisely. Does a paused subscription count as churned? Is a downgrade to the free plan churn or contraction? A tool makes those decisions for you, and its definitions may not match how your investors read the numbers. SQL makes each definition explicit, and anyone on the team can review it.

The data model the queries assume

Everything below runs against one subscription events table:

create table subscription_events (
  id          bigint generated always as identity primary key,
  customer_id uuid not null,
  event_type  text not null, -- created, upgraded, downgraded, canceled, reactivated
  plan_id     text not null,
  mrr_cents   integer not null, -- the customer's monthly value after this event
  currency    text not null default 'EUR',
  occurred_at timestamptz not null
);

create index on subscription_events (customer_id, occurred_at);

Each row records the state after the event, not the delta. A customer's MRR at any moment is the mrr_cents of their most recent event before that moment, which keeps every point-in-time query simple. If you only have a current-state subscriptions table today, start writing events now. You cannot reconstruct history you never recorded.

Almost every metric needs the same intermediate result, each customer's MRR at each month end, so it is worth defining that once as a view:

create view customer_month_mrr as
select
  m.month_start,
  e.customer_id,
  case
    when (array_agg(e.event_type order by e.occurred_at desc))[1] = 'canceled' then 0
    else (array_agg(e.mrr_cents order by e.occurred_at desc))[1]
  end as mrr_cents
from generate_series(
       date_trunc('month', now()) - interval '23 months',
       date_trunc('month', now()),
       interval '1 month'
     ) as m(month_start)
join subscription_events e
  on e.occurred_at < m.month_start + interval '1 month'
group by m.month_start, e.customer_id;

The array_agg(... order by occurred_at desc) trick picks the latest event per customer and month without a window function, and canceled customers carry an MRR of zero from their cancellation month on.

Monthly recurring revenue

With the view in place, MRR is short:

select month_start::date as month, sum(mrr_cents) / 100.0 as mrr
from customer_month_mrr
group by month_start
order by month_start;

The proration question comes up on every project. A customer upgrades from 50 to 90 euros on the 20th, so what is their MRR that month? For MRR the answer is 90. MRR is a snapshot of recurring run rate at a point in time, not a revenue recognition figure, so the prorated mid-month invoice belongs in your billing system, not here. The related mistake we see most often is summing invoice amounts and calling the result MRR. One-time setup fees and annual prepayments make that number jump around and overstate the run rate.

ARR, and which ARR you mean

For monthly-billing products, ARR is MRR times twelve. Report it that way and say so. If you sell annual contracts, there is a second number: contracted ARR, the sum of active contract values. The two diverge when monthly churn is high, because run-rate ARR assumes today's MRR survives the next twelve months. Investors will ask which one you are quoting, so compute both if both exist in your business.

Churn: logo versus revenue

Logo churn counts the customers you lost. Revenue churn counts the MRR they took with them. You need both, because losing ten customers paying 20 euros a month is a very different event from losing one paying 2,000.

select
  curr.month_start::date as month,
  round(100.0 * count(*) filter (where prev.mrr_cents > 0 and curr.mrr_cents = 0)
    / nullif(count(*) filter (where prev.mrr_cents > 0), 0), 2) as logo_churn_pct,
  round(100.0 * sum(prev.mrr_cents - curr.mrr_cents)
      filter (where prev.mrr_cents > 0 and curr.mrr_cents = 0)
    / nullif(sum(prev.mrr_cents) filter (where prev.mrr_cents > 0), 0), 2) as revenue_churn_pct
from customer_month_mrr curr
join customer_month_mrr prev
  on prev.customer_id = curr.customer_id
 and prev.month_start = curr.month_start - interval '1 month'
group by curr.month_start
order by curr.month_start;

Decide once whether a downgrade to a free plan counts as churn and encode the decision here. Whatever you pick, the SQL is the documentation.

Net revenue retention

NRR answers a question churn alone cannot: for the customers we already had a month ago, how did their combined MRR develop, including upgrades and downgrades?

select
  curr.month_start::date as month,
  round(100.0 * sum(curr.mrr_cents) / nullif(sum(prev.mrr_cents), 0), 1) as nrr_pct
from customer_month_mrr curr
join customer_month_mrr prev
  on prev.customer_id = curr.customer_id
 and prev.month_start = curr.month_start - interval '1 month'
where prev.mrr_cents > 0
group by curr.month_start
order by curr.month_start;

New customers are excluded by the join on the previous month, and that is the point: NRR measures what happens to existing revenue. Above 100 percent, expansion outweighs churn and contraction, and the business grows even with zero new sales. For B2B SaaS this is one of the first numbers an investor checks.

Cohort retention

Retention by signup cohort shows whether the product keeps customers better over time, which the aggregate churn rate hides.

with cohorts as (
  select customer_id, date_trunc('month', min(occurred_at)) as cohort_month
  from subscription_events
  group by customer_id
)
select
  c.cohort_month::date as cohort,
  (extract(year from age(cm.month_start, c.cohort_month)) * 12
   + extract(month from age(cm.month_start, c.cohort_month)))::int as month_offset,
  count(*) filter (where cm.mrr_cents > 0) as active_customers
from cohorts c
join customer_month_mrr cm using (customer_id)
where cm.month_start >= c.cohort_month
group by 1, 2
order by 1, 2;

Each row is one cell of the classic cohort heatmap: which cohort, how many months in, and how many customers still pay. Divide by each cohort's month-zero count to get percentages.

LTV and CAC

Customer lifetime value comes from a formula rather than a big query: average revenue per account, times gross margin, divided by monthly revenue churn. The first two come from your books, the third from the churn query above. Treat the result with suspicion until you have at least a year of churn history, because early churn rates are noisy.

CAC needs one small extra table with monthly marketing and sales spend:

select
  date_trunc('month', s.spent_on)::date as month,
  round(sum(s.amount_cents) / 100.0
    / nullif(count(distinct e.customer_id)
        filter (where e.event_type = 'created'), 0), 2) as cac
from marketing_spend s
left join subscription_events e
  on date_trunc('month', e.occurred_at) = date_trunc('month', s.spent_on)
group by 1
order by 1;

An LTV to CAC ratio around 3 is the number usually quoted as healthy for B2B SaaS. The trend matters more than the target, and so does honesty in the inputs, especially counting sales salaries as part of the spend.

The gotchas that skew every number above

Timezones first. date_trunc('month', occurred_at) truncates in the session timezone, so the same query returns different month boundaries for a connection set to UTC and one set to Europe/Berlin. Pin it explicitly with occurred_at at time zone 'Europe/Berlin' and use the same zone your billing runs in.

Trials second. A trialing customer with mrr_cents = 0 falls out of every revenue metric on its own, which is what you want. The mistake is recording the would-be plan price during the trial. Record zero until money is committed, otherwise your MRR includes revenue that does not exist and your churn spikes whenever a batch of trials expires.

Currency third. Store amounts in cents with an explicit currency column and never sum across currencies in a metric query. If you bill in more than one currency, add a monthly exchange rate table and normalize to your reporting currency at aggregation time. Keep the rates monthly and fixed, or reported MRR will move without any customer doing anything.

Putting the numbers on a Grafana dashboard

All of these queries drop into Grafana's PostgreSQL data source unchanged. MRR and NRR work as time series panels with month as the time column. Current MRR and logo churn fit stat panels showing the latest row. For the cohort query, use a table panel with a color scale on active_customers, or the heatmap panel with the cohort on one axis and month_offset on the other. Set the dashboard refresh to something slow like six hours. These are monthly metrics, and each query scans the full events table.

If the dashboard queries start to hurt, take that seriously. In code quality and performance audits we regularly find analytics queries running on the production primary, competing with checkout traffic. A read replica solves this for years.

When this stops being enough

Computing SaaS metrics in PostgreSQL like this is the minimum viable analytics layer, and for many teams it lasts years. You outgrow it when finance needs auditable revenue recognition, when marketing wants attribution across ad platforms and product events, or when people outside engineering need to build their own reports. At that point the answer is a warehouse with a BI tool on top, and the SQL above ports over almost unchanged. Deciding when that investment is worth it is the kind of question we work through in tech stack strategy engagements, and if you are building the subscription system itself, our custom software development page describes how we approach it.

If you want a second pair of eyes on your own metrics queries, or the numbers they produce look off and you cannot tell why, write to hello@wolf-tech.io. More about how we work is at wolf-tech.io.