How to Track SaaS Churn and Retention Directly From Your Database

Churn rate is the number that keeps SaaS founders up at night. But most teams track it badly — relying on a CRM field someone forgot to update, a spreadsheet that's two weeks stale, or a third-party analytics tool that only shows aggregate numbers and hides the "why."

The ground truth lives in your database. Every cancellation, every subscription status change, every last login timestamp — it's all in there. The challenge is getting that data out without depending on an engineer every time you need an answer.

This guide walks through what to track, how to write the SQL to get it, and how tools like AI for Database let non-technical teams access these numbers directly.

What Churn and Retention Actually Mean in Your Database

Before you can query anything, you need to know what churn looks like in your specific schema. For most SaaS products, the key tables are:

Retention and churn are two sides of the same coin:

Understanding which table holds subscription state and which holds billing changes is the first step. The exact schema varies by how you've integrated Stripe, Paddle, or your own billing logic — but the underlying data is always there.

The Core SQL Queries You Need

Let's say your subscriptions are stored in a table called subscriptions with columns: user_id, status, plan, mrr, created_at, cancelled_at.

Monthly churn rate for the last 6 months:

SELECT
  DATE_TRUNC('month', cancelled_at) AS month,
  COUNT(*) AS churned_customers,
  ROUND(COUNT(*) * 100.0 / (
    SELECT COUNT(*)
    FROM subscriptions
    WHERE created_at < DATE_TRUNC('month', s.cancelled_at)
      AND (cancelled_at IS NULL OR cancelled_at >= DATE_TRUNC('month', s.cancelled_at))
  ), 2) AS churn_rate_pct
FROM subscriptions s
WHERE status = 'cancelled'
  AND cancelled_at >= NOW() - INTERVAL '6 months'
GROUP BY month
ORDER BY month;

Cohort retention — how many users from each signup month are still active:

SELECT
  DATE_TRUNC('month', u.created_at) AS cohort_month,
  COUNT(DISTINCT u.id) AS cohort_size,
  COUNT(DISTINCT CASE WHEN s.status = 'active' THEN u.id END) AS still_active,
  ROUND(
    COUNT(DISTINCT CASE WHEN s.status = 'active' THEN u.id END) * 100.0
    / NULLIF(COUNT(DISTINCT u.id), 0),
  1) AS retention_pct
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.id
GROUP BY cohort_month
ORDER BY cohort_month;

These queries work well, but writing them from scratch every week — or explaining the results to a non-technical ops manager — is friction that slows teams down. Most SaaS companies end up with churn data that's weeks old because nobody has time to keep the reporting current.

Why Most Teams Get Churn Data Wrong

The standard approach has several failure modes.

The CRM lag problem. Sales or customer success teams update the CRM manually. But if a customer self-cancels through your billing portal, the CRM might not reflect that for days, if at all. Your database always reflects the truth.

The tool gap. Standard analytics platforms like Mixpanel or Amplitude track front-end events well, but subscription lifecycle data lives in your billing tables. You end up with churn data in one tool and behavioural data in another, and correlating them requires engineering time you don't have.

The one-off report trap. An engineer writes a churn report once, drops it in a Google Sheet, and it's never updated again. By the time someone looks at it, it's outdated and the schema has probably changed.

The fix is connecting directly to your database and querying subscription state in real time — then automating the refresh so the numbers stay current without manual intervention.

Setting Up Automated Churn Monitoring

Rather than pulling churn data manually each week, you can set up monitoring that watches your subscriptions table and alerts you when something changes.

Using AI for Database's action workflows, you define a condition in plain English:

"When the 7-day rolling churn rate exceeds 3%, send a Slack message to #product-alerts"

Under the hood, the system runs a query like this on a schedule:

SELECT
  ROUND(
    COUNT(*) * 100.0 / NULLIF((
      SELECT COUNT(*)
      FROM subscriptions
      WHERE status = 'active'
        OR cancelled_at > NOW() - INTERVAL '7 days'
    ), 0),
  2) AS weekly_churn_pct
FROM subscriptions
WHERE status = 'cancelled'
  AND cancelled_at > NOW() - INTERVAL '7 days';

When the result crosses the threshold, the alert fires automatically. No cron jobs, no stored procedures, no DevOps involvement required.

What to Put on a Churn Dashboard

A useful churn and retention dashboard typically includes:

The "at-risk" query is particularly useful for customer success teams:

SELECT
  u.email,
  u.created_at AS signed_up,
  u.last_active_at,
  s.plan,
  s.mrr,
  EXTRACT(DAY FROM NOW() - u.last_active_at) AS days_inactive
FROM users u
JOIN subscriptions s ON s.user_id = u.id
WHERE s.status = 'active'
  AND u.last_active_at < NOW() - INTERVAL '30 days'
ORDER BY days_inactive DESC
LIMIT 50;

With AI for Database, a non-technical customer success manager can ask "show me all active customers who haven't logged in for 30 days" and get that list directly — they never need to see the SQL.

Going Beyond Churn: Expansion and Net Revenue Retention

Churn rate alone misses the full picture. Net Revenue Retention (NRR) tells you whether your revenue base is growing even as some customers leave — because upgrades and expansions can more than offset cancellations.

SELECT
  DATE_TRUNC('month', changed_at) AS month,
  SUM(CASE WHEN new_mrr > old_mrr THEN new_mrr - old_mrr ELSE 0 END) AS expansion_mrr,
  SUM(CASE WHEN new_mrr < old_mrr AND new_mrr > 0 THEN old_mrr - new_mrr ELSE 0 END) AS contraction_mrr,
  SUM(CASE WHEN new_mrr = 0 THEN old_mrr ELSE 0 END) AS churned_mrr,
  SUM(CASE WHEN new_mrr > old_mrr THEN new_mrr - old_mrr ELSE 0 END)
    - SUM(CASE WHEN new_mrr = 0 THEN old_mrr ELSE 0 END)
    - SUM(CASE WHEN new_mrr < old_mrr AND new_mrr > 0 THEN old_mrr - new_mrr ELSE 0 END)
  AS net_mrr_movement
FROM subscription_changes
WHERE changed_at >= NOW() - INTERVAL '6 months'
GROUP BY month
ORDER BY month;

This assumes a subscription_changes table that logs MRR transitions — a common pattern in Stripe-integrated SaaS products. If you don't have that table, you can derive it from Stripe webhook logs or by joining on invoice and subscription records in a billing events table.

Start querying your database for free → Connect in 2 minutes at aifordatabase.com, no SQL required.