How to Track MRR and ARR Directly From Your Database

Every SaaS founder has the same experience at some point: you open your Stripe dashboard, see a revenue number, then wonder if it actually matches what's in your database. Then you open a spreadsheet that someone updated last Tuesday and compare it to a Baremetrics chart that uses slightly different churn definitions. By the time you have a number you trust, you've spent 45 minutes on something that should take 30 seconds.

The problem isn't your tools. It's that MRR and ARR live in your database in subscription records, payment events, plan changes and most analytics tools only get a copy of that data, delayed and filtered through their own opinionated definitions.

This guide walks through how to calculate MRR and ARR directly from your database, what queries actually look like, and how tools like AI for Database let you pull these numbers in plain English without setting up a data warehouse or hiring a data analyst.

What MRR and ARR Actually Are (and Why Definitions Matter)

Monthly Recurring Revenue (MRR) is the normalized monthly revenue from active subscriptions. Annual Recurring Revenue (ARR) is MRR multiplied by 12.

Simple in theory. Messy in practice.

The definition disagreements happen at the edges:

Most SaaS companies settle on the same conventions: active paid subscriptions only, annual plans divided by 12 (normalized), discounts applied, trials excluded. But "most" isn't "all," and if your billing system uses different logic than your analytics tool, you'll always have two numbers that don't match.

The answer is to calculate MRR from your source of truth your database using your own definitions. That way there's one number, and it's yours.

What Your Subscription Data Looks Like

Most SaaS databases have a subscriptions table that looks something like this:

CREATE TABLE subscriptions (
  id           UUID PRIMARY KEY,
  customer_id  UUID NOT NULL,
  plan_id      VARCHAR(100) NOT NULL,
  status       VARCHAR(50) NOT NULL, , active, canceled, trialing, past_due
  amount       DECIMAL(10,2) NOT NULL,, in cents or dollars
  currency     VARCHAR(3) NOT NULL,
  billing_cycle VARCHAR(10) NOT NULL,, monthly, annual
  started_at   TIMESTAMP NOT NULL,
  canceled_at  TIMESTAMP,
  trial_ends_at TIMESTAMP
);

Some teams store amount in cents (Stripe does this), others in dollars. Some teams use a separate plans table and join on it. The structure varies, but the logic is similar across most setups.

Calculating Current MRR

Here's a straightforward query for current MRR, assuming amounts are stored in cents:

SELECT
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 AS current_mrr
FROM subscriptions
WHERE
  status = 'active'
  AND (trial_ends_at IS NULL OR trial_ends_at < NOW());

This handles the two main cases: monthly subscribers counted at face value, annual subscribers normalized to monthly. Trials are excluded by checking that the trial period has ended.

If you want ARR, just multiply:

SELECT
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 * 12 AS current_arr
FROM subscriptions
WHERE
  status = 'active'
  AND (trial_ends_at IS NULL OR trial_ends_at < NOW());

MRR Over Time: The Trend That Actually Matters

A single MRR number is useful. MRR over time tells you whether your business is growing, stagnating, or sliding. Here's how to get MRR by month for the last 12 months:

WITH months AS (
  SELECT
    DATE_TRUNC('month', gs) AS month
  FROM
    generate_series(
      DATE_TRUNC('month', NOW() - INTERVAL '11 months'),
      DATE_TRUNC('month', NOW()),
      '1 month'::interval
    ) AS gs
),
monthly_mrr AS (
  SELECT
    m.month,
    SUM(
      CASE
        WHEN s.billing_cycle = 'annual' THEN s.amount / 12
        ELSE s.amount
      END
    ) / 100.0 AS mrr
  FROM months m
  LEFT JOIN subscriptions s ON
    s.status = 'active'
    AND s.started_at <= m.month + INTERVAL '1 month'
    AND (s.canceled_at IS NULL OR s.canceled_at > m.month)
    AND (s.trial_ends_at IS NULL OR s.trial_ends_at < m.month)
  GROUP BY m.month
)
SELECT
  TO_CHAR(month, 'YYYY-MM') AS month,
  COALESCE(mrr, 0) AS mrr
FROM monthly_mrr
ORDER BY month;

This generates a row for each of the last 12 months and calculates what MRR was at each point, looking at which subscriptions were active during that window. It handles the edge case where a subscription that's currently canceled was active in earlier months.

Breaking Down MRR by Plan

Aggregate MRR tells you the total. Broken down by plan, it tells you which products are driving growth:

SELECT
  plan_id,
  COUNT(*) AS subscriber_count,
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 AS plan_mrr,
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 / SUM(SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0) OVER () * 100 AS pct_of_total
FROM subscriptions
WHERE
  status = 'active'
  AND (trial_ends_at IS NULL OR trial_ends_at < NOW())
GROUP BY plan_id
ORDER BY plan_mrr DESC;

If you have an enterprise plan driving 60% of ARR but only 5% of customers, that tells you something very specific about where to focus.

New MRR vs. Churned MRR: The Growth Equation

Total MRR growth is the net of new MRR coming in and churned MRR leaving. To understand growth, you need both sides of that equation.

New MRR in the last 30 days:

SELECT
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 AS new_mrr_30d
FROM subscriptions
WHERE
  status = 'active'
  AND started_at >= NOW() - INTERVAL '30 days'
  AND (trial_ends_at IS NULL OR trial_ends_at < NOW());

Churned MRR in the last 30 days:

SELECT
  SUM(
    CASE
      WHEN billing_cycle = 'annual' THEN amount / 12
      ELSE amount
    END
  ) / 100.0 AS churned_mrr_30d
FROM subscriptions
WHERE
  status = 'canceled'
  AND canceled_at >= NOW() - INTERVAL '30 days';

Net new MRR = new MRR − churned MRR. If that number is positive, you're growing. If it's negative, you're shrinking even if total MRR looks okay because you started the period high.

Asking Your Database in Plain English

The queries above work, but they require someone who knows SQL to write them, debug them when data structure changes, and adapt them when you ask a new question. Most founders and ops teams are not that person.

AI for Database solves this by letting you ask questions in plain English directly against your database. You connect your PostgreSQL, MySQL, or other database, and then ask:

The AI translates your question into SQL, runs it, and returns either a table or a chart. You don't have to know what a generate_series does. You don't have to remember whether your amounts are in cents. You ask, it answers.

You can also set up a self-refreshing dashboard that shows MRR, ARR, new MRR, churned MRR, and growth rate all pulling live from your database, updating automatically. No more opening five tabs and reconciling numbers.

Setting Up Alerts When MRR Changes

Tracking MRR passively is useful. Being notified when something notable happens is better.

With AI for Database's action workflows, you can define conditions and get notified automatically:

These workflows watch your database directly no stored procedures, no DBA, no Zapier chains. You define the condition in plain English and specify what should happen when it's met.

Common Mistakes in MRR Calculation

A few things that trip people up:

Counting trials as revenue. A customer in a 14-day trial shows up in your subscriptions table as active, but they haven't paid yet. Filter by trial_ends_at to exclude them.

Double-counting upgrades. If you create a new subscription record when a customer upgrades instead of updating the existing one, you might count both the old and new plan. Check your billing system's behavior and add deduplication logic if needed.

Ignoring past_due status. Subscriptions that fail payment often go to past_due before being canceled. Whether you count these as MRR depends on your policy some companies do (the customer still intends to pay), some don't (they've effectively churned). Pick a consistent rule.

Annual plan accounting. Recognize annual subscriptions at full value in the month paid vs. normalize them to monthly these are different numbers. For financial reporting, your accountant will want specific treatment. For growth tracking, normalized monthly is more useful.

Your MRR Is Already in Your Database

The data you need to answer "how is the business doing?" is sitting in your subscriptions table right now. You don't need a data warehouse, a BI tool, or a dedicated analyst to access it.

A few SQL queries or a few plain-English questions to AI for Database will give you MRR, ARR, growth rate, plan breakdown, and churn analysis from your actual data, using your own definitions.

Try AI for Database free at aifordatabase.com and connect your subscriptions database in minutes.