How to Calculate MRR, Churn, and LTV in PostgreSQL

Every SaaS founder eventually hits the same wall. Payments are flowing, subscriptions are growing, and your billing provider's dashboard gives you a decent overview. But then someone asks: "What's our MRR trend over the last 6 months?" or "What's our average customer lifetime value?" — and suddenly you're exporting CSVs or clicking through UI tabs trying to piece it together.

Whether you're using Stripe, Paddle, QuickBooks, or any other billing provider, their dashboards show you what happened. But the metrics that actually drive SaaS decisions — MRR, churn rate, LTV, net revenue retention — require querying your billing data flexibly. Joining it with your own application data. Slicing it by cohort, plan, or time period. That's not something any dashboard gives you.

Most metric guides give you formulas. This one gives you copy-paste SQL that works on real billing tables — whether your data comes from Stripe, Paddle, QuickBooks, or any other provider. Get your billing data into PostgreSQL, run these queries, and you'll have a clearer picture of your SaaS than any third-party analytics tool can provide.

Why PostgreSQL for SaaS Metrics?

Your billing provider's dashboard shows you what happened. PostgreSQL lets you ask why.

Custom time ranges — monthly, weekly, daily, or any arbitrary period. Not limited to what your billing UI offers.
JOIN with your app data — correlate billing events with user behavior, feature usage, or support tickets. Which plan tier has the lowest churn? Which signup source has the highest LTV? You can only answer these by joining billing data with your own tables.
Cohort analysis — group customers by signup month and track how each cohort retains over time. This is nearly impossible through an API.
No rate limits — run the same query a hundred times while tweaking your analysis. No API throttling, no pagination, no waiting.
Shareable and reproducible — SQL queries can be saved, version-controlled, and rerun. They become your team's source of truth for metrics.

Getting Your Billing Data into PostgreSQL

Before you can query anything, you need your billing data in a database. You can build a custom integration (handle API polling, pagination, schema mapping, and ongoing maintenance) or use a tool that does it for you.

Here's the quick setup using Codeless Sync:

Connect your database — paste your PostgreSQL connection string (works with Supabase, Neon, Railway, Render, AWS RDS, or any Postgres host)
Select your provider — choose Stripe, Paddle, QuickBooks, Xero, or another supported billing provider and pick a data type (customers, subscriptions, transactions, etc.)
Add your API key — generate a read-only key from your provider's developer settings
Create the table — click Auto-Create Table to generate the correct schema automatically
Sync — hit Sync Now and your data appears as a regular Postgres table

Set up a scheduled sync (hourly or daily) and the data stays current automatically. Subsequent syncs are incremental — only changed records are fetched.

You'll want to sync at least customers, subscriptions, and transactions to run the queries below.

Note: The SQL queries below use generic table names (subscriptions, customers, transactions). Your actual table names will depend on your billing provider — for example, stripe_subscriptions, paddle_subscriptions, etc. Column names may also vary slightly between providers. Adjust as needed for your schema.

MRR (Monthly Recurring Revenue)

MRR is the baseline metric for any subscription business. Here's how to calculate it from your billing data.

Current MRR — what you're earning right now:

SELECT
  COUNT(*) AS active_subscriptions,
  currency_code,
  SUM(
    CASE
      WHEN billing_cycle_interval = 'month'
        THEN recurring_amount / 100.0
      WHEN billing_cycle_interval = 'year'
        THEN recurring_amount / 100.0 / 12
    END
  ) AS mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY currency_code;

MRR trend over the last 12 months:

WITH months AS (
  SELECT generate_series(
    DATE_TRUNC('month', NOW() - INTERVAL '11 months'),
    DATE_TRUNC('month', NOW()),
    '1 month'
  )::date AS month
),
monthly_mrr AS (
  SELECT
    m.month,
    s.currency_code,
    SUM(
      CASE
        WHEN s.billing_cycle_interval = 'month'
          THEN s.recurring_amount / 100.0
        WHEN s.billing_cycle_interval = 'year'
          THEN s.recurring_amount / 100.0 / 12
      END
    ) AS mrr
  FROM months m
  JOIN subscriptions s
    ON s.started_at <= (m.month + INTERVAL '1 month' - INTERVAL '1 day')
    AND (s.canceled_at IS NULL OR s.canceled_at > m.month)
  WHERE s.status IN ('active', 'canceled')
  GROUP BY m.month, s.currency_code
)
SELECT month, currency_code, ROUND(mrr, 2) AS mrr
FROM monthly_mrr
ORDER BY month DESC;

This gives you a clear picture of how revenue is trending — are you growing, plateauing, or declining? It accounts for both monthly and annual subscriptions by normalizing annual plans to their monthly equivalent.

Churn Rate

Churn tells you how fast you're losing customers. A small difference in churn rate compounds massively over time.

Monthly churn rate:

WITH monthly_stats AS (
  SELECT
    DATE_TRUNC('month', canceled_at) AS month,
    COUNT(*) AS churned
  FROM subscriptions
  WHERE status = 'canceled'
    AND canceled_at >= NOW() - INTERVAL '12 months'
  GROUP BY month
),
monthly_active AS (
  SELECT
    m.month,
    COUNT(*) AS active_at_start
  FROM generate_series(
    DATE_TRUNC('month', NOW() - INTERVAL '11 months'),
    DATE_TRUNC('month', NOW()),
    '1 month'
  ) AS m(month)
  JOIN subscriptions s
    ON s.started_at < m.month
    AND (s.canceled_at IS NULL OR s.canceled_at >= m.month)
  GROUP BY m.month
)
SELECT
  a.month,
  a.active_at_start,
  COALESCE(c.churned, 0) AS churned,
  ROUND(
    COALESCE(c.churned, 0)::numeric / NULLIF(a.active_at_start, 0) * 100, 2
  ) AS churn_rate_pct
FROM monthly_active a
LEFT JOIN monthly_stats c ON c.month = a.month
ORDER BY a.month DESC;

For SaaS benchmarks: under 5% monthly churn is decent, under 3% is good, under 1% is excellent. If you're above 5%, this is the first metric to focus on.

Customer Lifetime Value (LTV)

LTV tells you how much revenue a customer generates on average before they leave. It's essential for understanding how much you can spend on acquisition.

Average LTV across all customers:

WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(amount / 100.0) AS total_revenue,
    MIN(created_at) AS first_transaction,
    MAX(created_at) AS last_transaction
  FROM transactions
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT
  COUNT(*) AS total_customers,
  ROUND(AVG(total_revenue), 2) AS avg_ltv,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue), 2) AS median_ltv,
  ROUND(MAX(total_revenue), 2) AS max_ltv,
  ROUND(AVG(
    EXTRACT(EPOCH FROM (last_transaction - first_transaction)) / 86400
  ), 0) AS avg_lifetime_days
FROM customer_revenue
WHERE total_revenue > 0;

LTV by signup cohort — are newer customers more or less valuable?

WITH customer_revenue AS (
  SELECT
    c.id AS customer_id,
    DATE_TRUNC('month', c.created_at) AS cohort_month,
    COALESCE(SUM(t.amount / 100.0), 0) AS total_revenue
  FROM customers c
  LEFT JOIN transactions t
    ON t.customer_id = c.id
    AND t.status = 'completed'
  GROUP BY c.id, cohort_month
)
SELECT
  cohort_month,
  COUNT(*) AS customers,
  ROUND(AVG(total_revenue), 2) AS avg_ltv,
  ROUND(SUM(total_revenue), 2) AS total_cohort_revenue
FROM customer_revenue
GROUP BY cohort_month
ORDER BY cohort_month DESC;

If LTV is trending down across cohorts, it could mean you're attracting less committed customers, or that your pricing needs adjustment. If it's trending up, your product is getting stickier.

Net Revenue Retention (NRR)

NRR tells you whether your existing customers are spending more or less over time. Over 100% means expansion revenue outpaces churn — the gold standard for SaaS.

WITH monthly_cohort_revenue AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount / 100.0) AS revenue
  FROM transactions
  WHERE status = 'completed'
  GROUP BY customer_id, month
),
retention AS (
  SELECT
    prev.month + INTERVAL '1 month' AS month,
    SUM(prev.revenue) AS previous_revenue,
    SUM(COALESCE(curr.revenue, 0)) AS current_revenue
  FROM monthly_cohort_revenue prev
  LEFT JOIN monthly_cohort_revenue curr
    ON curr.customer_id = prev.customer_id
    AND curr.month = prev.month + INTERVAL '1 month'
  GROUP BY prev.month
)
SELECT
  month,
  ROUND(previous_revenue, 2) AS previous_month_revenue,
  ROUND(current_revenue, 2) AS current_month_revenue,
  ROUND(current_revenue / NULLIF(previous_revenue, 0) * 100, 1) AS nrr_pct
FROM retention
ORDER BY month DESC
LIMIT 12;

NRR above 100% means your existing customers are growing. Below 100% means you're leaking revenue from your current base — even if new sales look healthy, the bucket has a hole.

Bonus: New vs Churned MRR

This shows where your MRR growth is coming from — and whether new revenue is outpacing losses.

WITH new_mrr AS (
  SELECT
    DATE_TRUNC('month', started_at) AS month,
    SUM(
      CASE
        WHEN billing_cycle_interval = 'month' THEN recurring_amount / 100.0
        WHEN billing_cycle_interval = 'year' THEN recurring_amount / 100.0 / 12
      END
    ) AS new_mrr
  FROM subscriptions
  WHERE started_at >= NOW() - INTERVAL '12 months'
  GROUP BY month
),
churned_mrr AS (
  SELECT
    DATE_TRUNC('month', canceled_at) AS month,
    SUM(
      CASE
        WHEN billing_cycle_interval = 'month' THEN recurring_amount / 100.0
        WHEN billing_cycle_interval = 'year' THEN recurring_amount / 100.0 / 12
      END
    ) AS lost_mrr
  FROM subscriptions
  WHERE canceled_at >= NOW() - INTERVAL '12 months'
    AND status = 'canceled'
  GROUP BY month
)
SELECT
  n.month,
  ROUND(n.new_mrr, 2) AS new_mrr,
  ROUND(COALESCE(c.lost_mrr, 0), 2) AS churned_mrr,
  ROUND(n.new_mrr - COALESCE(c.lost_mrr, 0), 2) AS net_mrr_change
FROM new_mrr n
LEFT JOIN churned_mrr c ON c.month = n.month
ORDER BY n.month DESC;

If net_mrr_change is consistently positive, you're growing. If churned MRR regularly exceeds new MRR, you've got a retention problem that no amount of marketing will fix.

Keeping Your Metrics Current

These queries are only as good as the data behind them. Stale data means stale metrics.

With scheduled syncs running hourly or daily, your billing tables stay current automatically. Each sync is incremental — only changed records are fetched — so it's fast and stays well within your provider's rate limits.

Your SaaS metrics dashboard becomes a set of saved SQL queries that always return up-to-date numbers. No manual exports, no API scripts to maintain, no data pipelines to babysit.

Ready to get your billing data into PostgreSQL? codelesssync.com has a free tier to get started.

Frequently Asked Questions

What is MRR and how do you calculate it?

MRR (Monthly Recurring Revenue) is the total predictable revenue your subscription business earns each month. To calculate it, sum the monthly value of all active subscriptions — converting annual plans to their monthly equivalent by dividing by 12. In PostgreSQL, you can query this directly from your billing data using the SQL examples in this guide. Tools like Codeless Sync keep your subscription data current so MRR calculations always reflect the latest state.

What is a good churn rate for SaaS?

For SaaS businesses, under 5% monthly customer churn is considered acceptable, under 3% is good, and under 1% is excellent. Early-stage startups often see higher churn (5-10%) as they find product-market fit. The key is tracking churn consistently over time — which requires your billing data in a queryable database rather than relying on dashboard snapshots.

How do you calculate customer lifetime value in SQL?

Customer lifetime value (LTV) is the total revenue a customer generates before they leave. The simplest SQL approach is to sum all completed transactions per customer and take the average. For a more nuanced view, calculate LTV by signup cohort to see whether newer customers are more or less valuable than earlier ones. Both queries are included in this guide and work with any billing provider's data synced to PostgreSQL.

What is net revenue retention and why does it matter?

Net Revenue Retention (NRR) measures whether your existing customers are spending more or less over time, expressed as a percentage. NRR above 100% means expansion revenue (upgrades, add-ons) outpaces churn and contractions — your revenue grows even without new customers. Below 100% means you're losing revenue from your existing base. Top-performing SaaS companies aim for 110-130% NRR.

Do I need to build a data pipeline to run these queries?

No. You can get your billing data into PostgreSQL in about 5 minutes using Codeless Sync — connect your database, select your billing provider (Stripe, Paddle, QuickBooks, or Xero), and the tables are created and populated automatically. Scheduled syncs keep the data current so your metric queries always return up-to-date numbers without any pipeline maintenance.

Related: