SaaS Metric Dashboards with SQL & Excel

Why SaaS metrics are harder than they look

Every SaaS founder and CFO wants the same three numbers: ARR, MRR, and churn. But behind each is a data-modeling problem most teams underestimate. Do you count bookings or recognized revenue? Is a downgrade churn or contraction? When does a trial convert into a paying MRR entry?

Definitions drift between teams, Excel models quietly diverge from the product database, and by Series B the "single source of truth" is five different spreadsheets. The fix isn't a fancier BI tool — it's a clean SQL data model everyone agrees on.

The subscription events data model

Treat every change to a subscription as an event. This event-sourced approach is the only way to get ARR, MRR, and churn to reconcile without manual patching every month.

-- events/subscription_events.sql
CREATE TABLE subscription_events (
  event_id        BIGINT PRIMARY KEY,
  customer_id     VARCHAR(64)  NOT NULL,
  subscription_id VARCHAR(64)  NOT NULL,
  event_type      VARCHAR(32)  NOT NULL, -- NEW, EXPANSION, CONTRACTION, CHURN, REACTIVATION
  event_date      DATE         NOT NULL,
  amount_delta    NUMERIC(18,2) NOT NULL, -- monthly delta in base currency
  plan            VARCHAR(64),
  currency        CHAR(3)      NOT NULL
);

The amount_delta is the monthly recurring impact of this event. A new $1,200 annual plan on 1 Jan is an event on 1 Jan with amount_delta = 100 (monthly equivalent). When that customer cancels on 15 Jun, the CHURN event on 15 Jun carries amount_delta = -100.

Calculating MRR with SQL

MRR at any point is the running sum of deltas up to that point. For a month-end snapshot, aggregate by customer and truncate to month.

-- metrics/mrr.sql
WITH monthly_events AS (
  SELECT
    DATE_TRUNC('month', event_date) AS month,
    customer_id,
    subscription_id,
    SUM(amount_delta) AS delta
  FROM subscription_events
  GROUP BY 1, 2, 3
),
mrr_by_month AS (
  SELECT
    month,
    SUM(delta) AS mrr
  FROM monthly_events
  GROUP BY 1
)
SELECT * FROM mrr_by_month
ORDER BY month;

This gives you MRR per month. To get net-new, expansion, contraction, and churn as separate lines, keep theevent_type in the aggregation instead of collapsing it. Leadership usually wants all four movements on one slide.

From MRR to ARR

ARR is simply the last month's MRR multiplied by 12. But the implementation choice that matters is which "last month" you pick.

• End-of-month ARR — MRR on the last day of the fiscal month × 12. Stable, easy to audit.
• Committed ARR (CARR) — contracted future recurring revenue, including multi-year deals at full value. Higher number, more volatile, requires disclosure.

Pick one definition, write it into the SQL view as a comment, and never change it mid-quarter. When the board compares Q1 to Q2 they are comparing the same metric, not two different definitions wearing the same label.

Churn: logo vs. revenue

Churn is not one number. You need both logo churn (customers lost) and revenue churn (MRR lost), because they tell different stories.

-- metrics/churn.sql
WITH period AS (
  SELECT
    DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AS start_month,
    DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'   AS end_month
),
prior_customers AS (
  SELECT DISTINCT customer_id
  FROM subscription_events
  WHERE event_date < (SELECT start_month FROM period)
    AND event_type != 'CHURN'
),
churned_customers AS (
  SELECT DISTINCT customer_id
  FROM subscription_events
  WHERE event_type = 'CHURN'
    AND event_date BETWEEN (SELECT start_month FROM period)
                       AND (SELECT end_month FROM period)
)
SELECT
  COUNT(DISTINCT c.customer_id)                     AS logos_churned,
  COUNT(DISTINCT c.customer_id)::FLOAT /
    NULLIF(COUNT(DISTINCT p.customer_id), 0)         AS logo_churn_rate,
  SUM(e.amount_delta)                               AS revenue_churned,
  ABS(SUM(e.amount_delta)) /
    NULLIF((SELECT mrr FROM metrics.mrr WHERE month = (SELECT start_month FROM period)), 0)
                                                      AS revenue_churn_rate
FROM churned_customers c
LEFT JOIN prior_customers p ON p.customer_id = c.customer_id
LEFT JOIN subscription_events e ON e.customer_id = c.customer_id
                                AND e.event_type = 'CHURN'
                                AND e.event_date BETWEEN (SELECT start_month FROM period)
                                                   AND (SELECT end_month FROM period);

Revenue churn can be negative (net negative churn) when expansions from retained customers exceed the MRR lost from churned ones. If your revenue churn rate is below your logo churn rate, your existing base is expanding — the healthiest SaaS signal there is.

Excel as the presentation layer

Just like with MIS automation, Excel still wins for board packs and investor updates. Connect the SQL model to Excel with Power Query, drop a pivot on the metrics.mrr andmetrics.churn views, and let the numbers flow through automatically.

My rules for SaaS metrics in Excel:

• One named query per metric view — MRR, churn, ARR, net retention.
• No manual adjustments in the workbook. If the number looks wrong, fix the SQL model, not the cell.
• Version the SQL in Git so every number is traceable to a commit.
• Add a reconciliation check that alerts when SQL totals diverge from the billing system by more than a threshold.

Common modeling traps

These are the mistakes I see most often when teams move from spreadsheet SaaS metrics to SQL:

1. Annual plans treated as one-month spikes — spread the revenue into twelve MRR events, or your MRR graph becomes useless.
2. Downgrades treated as churn — contraction and churn are different movements. Mixing them inflates churn and hides expansion health.
3. Free trials counted in MRR — only paid subscriptions belong in MRR. Trials convert on payment date, not signup date.
4. Mid-month cancellations prorated inconsistently — pick proration or no proration, document it, and apply it everywhere.

What good looks like

A well-built SaaS metrics stack gives the CFO a single Excel file that refreshes in under thirty seconds, produces numbers that reconcile to the billing system down to the cent, and surfaces trends the spreadsheet version never caught — cohort curves, expansion velocity, and logo-churn by segment.

The competitive advantage isn't the dashboard. It's the trust: when the board asks a question, you answer in minutes because the model is clean, not hours because you're checking four Excel files for consistency.