How to Track Subscription and Billing Metrics Directly from Your Database

Most SaaS finance dashboards pull data from Stripe, Chargebee, or Recurlywhatever payment processor you use. Those dashboards work for standard metrics, but they have real blind spots: they do not know which plan a user downgraded from, they do not capture how billing correlates with in-app usage, and they cannot tell you which customers are approaching a usage limit before they churn.

All of that data lives in your application database. This guide shows you how to pull it out, build live dashboards around it, and set up automated alertswithout SQL expertise or a dedicated data engineering team.

The Metrics That Actually Matter for Subscription Businesses

Before writing a single query, get clear on what you are measuring. The most useful subscription metrics fall into four categories:

Revenue metrics

Growth metrics

Health metrics

Billing-specific metrics

Most of these require joining your subscriptions, users, invoices, and events tableswhich is exactly where the payment processor's own dashboard falls short.

Setting Up Your First MRR Query

Assuming a standard subscription schema, here is a basic MRR query:

SELECT
  DATE_TRUNC('month', s.current_period_start) AS month,
  SUM(s.amount / 100.0) AS mrr_usd
FROM subscriptions s
WHERE s.status = 'active'
  AND s.billing_interval = 'month'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

For annual subscriptions, divide by 12 to normalise to monthly:

SELECT
  DATE_TRUNC('month', s.current_period_start) AS month,
  SUM(
    CASE
      WHEN s.billing_interval = 'month' THEN s.amount / 100.0
      WHEN s.billing_interval = 'year' THEN (s.amount / 100.0) / 12
      ELSE 0
    END
  ) AS mrr_usd
FROM subscriptions s
WHERE s.status = 'active'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

These are starting points. Your actual schema will have different column names and the interval logic may varybut the pattern is the same.

With AI for Database, you would ask "What is our MRR trend over the last 12 months?" and get this query generated automatically, adapted to your actual schema. You do not need to know the column names in advance.

Building a Live Subscription Dashboard

Once you have the core metrics working as queries, you can build a dashboard that refreshes automatically. Here is a practical set of panels for a subscription metrics dashboard:

The churn rate query is worth examining explicitly because it is often built incorrectly:

-- Churn rate: customers who cancelled this month / customers active at start of month
WITH start_count AS (
  SELECT COUNT(*) AS total
  FROM subscriptions
  WHERE status = 'active'
    AND current_period_start <= DATE_TRUNC('month', CURRENT_DATE)
    AND (cancelled_at IS NULL OR cancelled_at >= DATE_TRUNC('month', CURRENT_DATE))
),
churned_count AS (
  SELECT COUNT(*) AS churned
  FROM subscriptions
  WHERE cancelled_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND cancelled_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
)
SELECT
  ROUND(churned::numeric / NULLIF(total, 0) * 100, 2) AS churn_rate_pct
FROM start_count, churned_count;

The common mistake is dividing churned customers by total customers at end of month rather than start of month, which understates churn during periods of fast growth.

In AI for Database, this dashboard refreshes on a schedule you sethourly, daily, or weeklywithout manual re-running.

Automating Billing Anomaly Alerts

Dashboards tell you what happened. Alerts tell you when something needs attention right now. For subscription businesses, the most valuable automated alerts are:

Failed payment spike If failed payments increase by more than 20% day-over-day, something may be wrong with your payment processor integration or a card BIN is being blocked.

Churn rate crossing a threshold If weekly churn exceeds your historical baseline by more than 1.5x, you want to know before month-end.

Account approaching plan limit If a customer is at 90% of their usage quota, proactively reaching out converts to expansion MRR far better than waiting for them to hit the wall.

High-value account going dark If a customer paying over $500 per month has not logged in for 14 days, that is an early churn signal worth acting on immediately.

Here is the query for accounts approaching their usage limit:

SELECT
  u.email,
  u.company_name,
  s.plan_name,
  s.amount / 100.0 AS mrr,
  a.queries_used,
  s.query_limit,
  ROUND(a.queries_used::numeric / NULLIF(s.query_limit, 0) * 100, 1) AS usage_pct
FROM accounts a
JOIN users u ON u.id = a.owner_id
JOIN subscriptions s ON s.account_id = a.id
WHERE s.status = 'active'
  AND a.queries_used::float / NULLIF(s.query_limit, 0) > 0.9
ORDER BY mrr DESC;

With AI for Database's action workflows, you can turn this into an automated Slack message or email that fires whenever a new account crosses the 90% thresholdno cron jobs, no stored procedures, no infrastructure to maintain.

Connecting Billing Data to Usage Data

The real value in querying your application databaserather than just using Stripe's dashboardis joining billing data to usage data.

For example: which accounts on the basic plan are actively using features that are only in the premium plan? That is an expansion opportunity you cannot find in Stripe.

SELECT
  u.email,
  u.company_name,
  s.plan_name,
  COUNT(DISTINCT e.feature_name) AS premium_features_used,
  s.amount / 100.0 AS current_mrr
FROM events e
JOIN users u ON u.id = e.user_id
JOIN subscriptions s ON s.account_id = u.account_id
WHERE e.feature_name IN ('advanced_export', 'api_access', 'custom_dashboards')
  AND s.plan_name = 'basic'
  AND e.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.email, u.company_name, s.plan_name, s.amount
HAVING COUNT(DISTINCT e.feature_name) >= 2
ORDER BY premium_features_used DESC;

This is the kind of query a revenue team should run every week. With AI for Database, they can run it themselvesin plain Englishwithout filing a ticket with the engineering team.

Working Without a Data Engineering Team

The traditional path to this kind of analysis involves a data engineer setting up a pipeline, loading data into a warehouse, building transformation models, and then building a BI dashboard on top. That is months of work and ongoing maintenance.

For most SaaS companies under $10M ARR, this is more infrastructure than you need. Your application database already has everything you need. The bottleneck is SQL knowledge and tooling.

AI for Database lets anyone on your teamfinance, ops, growthask questions in plain English and get answers directly from the database. They can save the questions they run regularly as dashboard panels, set them to refresh automatically, and configure alerts for the conditions they care about.

The schema context is handled automatically. The SQL is generated, validated, and executed in one step. The results come back as a table or chart. No data warehouse, no pipeline, no DBA required.

Start Knowing What Your Subscription Data Actually Says

Most SaaS teams are making decisions based on incomplete billing data because the full picture is locked in a database they cannot easily query. The queries in this article are a starting point, but the real value comes from asking the questions specific to your businessthe ones you would normally wait days or weeks for an engineer to answer.

AI for Database connects to your existing database, generates accurate SQL from natural language, and lets you build the dashboards and alerts your team needs. Try it free at aifordatabase.com.