SaaS Metrics Dashboard | Bootspring Docs

The SaaS Metrics workflow helps you build a comprehensive metrics dashboard to track the health and growth of your business. From MRR to CAC/LTV ratios, this guide provides SQL queries, calculation methods, and benchmarks.

Overview#

Property	Value
Metrics	15+ key SaaS metrics
Tier	Free
Typical Duration	1-2 weeks to implement
Best For	SaaS businesses tracking growth

Outcomes#

A successful metrics implementation delivers:

Real-time metrics dashboard
Automated metric calculations
Historical trend analysis
Benchmarking against industry standards
Data-driven decision making

Core Metrics Framework#

                    SAAS METRICS HIERARCHY

                         ┌──────────────┐
                         │     ARR      │
                         │   Revenue    │
                         └──────┬───────┘
                                │
              ┌─────────────────┼─────────────────┐
              │                 │                 │
       ┌──────▼──────┐   ┌──────▼──────┐   ┌──────▼──────┐
       │  New MRR    │   │ Expansion   │   │   Churn     │
       │             │   │    MRR      │   │    MRR      │
       └──────┬──────┘   └─────────────┘   └─────────────┘
              │
    ┌─────────┴─────────┐
    │                   │
┌───▼───┐          ┌────▼────┐
│  CAC  │          │  LTV    │
│       │          │         │
└───────┘          └─────────┘

Revenue Metrics#

Monthly Recurring Revenue (MRR)#

Definition: Total predictable revenue normalized to one month

-- Current MRR
SELECT
  SUM(
    CASE plan_interval
      WHEN 'monthly' THEN amount
      WHEN 'yearly' THEN amount / 12
    END
  ) as mrr
FROM subscriptions
WHERE status = 'active';

MRR Components:

-- MRR breakdown by type
WITH mrr_changes AS (
  SELECT
    DATE_TRUNC('month', created_at) as month,
    customer_id,
    SUM(amount) as amount,
    LAG(SUM(amount)) OVER (
      PARTITION BY customer_id
      ORDER BY DATE_TRUNC('month', created_at)
    ) as prev_amount,
    MIN(DATE_TRUNC('month', created_at)) OVER (
      PARTITION BY customer_id
    ) as first_month
  FROM payments
  GROUP BY DATE_TRUNC('month', created_at), customer_id
)
SELECT
  month,
  SUM(CASE WHEN month = first_month THEN amount ELSE 0 END) as new_mrr,
  SUM(CASE
    WHEN month > first_month AND amount > COALESCE(prev_amount, 0)
    THEN amount - COALESCE(prev_amount, 0)
    ELSE 0
  END) as expansion_mrr,
  SUM(CASE
    WHEN month > first_month AND amount < COALESCE(prev_amount, 0)
    THEN COALESCE(prev_amount, 0) - amount
    ELSE 0
  END) as contraction_mrr,
  SUM(CASE
    WHEN prev_amount > 0 AND amount = 0
    THEN prev_amount
    ELSE 0
  END) as churned_mrr
FROM mrr_changes
GROUP BY month
ORDER BY month;

Annual Recurring Revenue (ARR)#

// Simple calculation
const arr = mrr * 12;

// With trailing average for smoothing
async function getARR() {
  const last3MonthsMRR = await prisma.$queryRaw`
    SELECT AVG(mrr) as avg_mrr
    FROM (
      SELECT
        DATE_TRUNC('month', created_at) as month,
        SUM(amount) as mrr
      FROM payments
      WHERE created_at > NOW() - INTERVAL '3 months'
      GROUP BY DATE_TRUNC('month', created_at)
    ) monthly
  `;

  return last3MonthsMRR[0].avg_mrr * 12;
}

Net Revenue Retention (NRR)#

Definition: Revenue from existing customers compared to same period last year

-- NRR calculation
WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(CASE
      WHEN payment_date BETWEEN '2023-01-01' AND '2023-12-31'
      THEN amount ELSE 0
    END) as revenue_2023,
    SUM(CASE
      WHEN payment_date BETWEEN '2024-01-01' AND '2024-12-31'
      THEN amount ELSE 0
    END) as revenue_2024
  FROM payments
  GROUP BY customer_id
  HAVING SUM(CASE
    WHEN payment_date BETWEEN '2023-01-01' AND '2023-12-31'
    THEN amount ELSE 0
  END) > 0  -- Only customers who existed in 2023
)
SELECT
  SUM(revenue_2024) / NULLIF(SUM(revenue_2023), 0) * 100 as nrr_percent
FROM customer_revenue;

NRR Benchmarks:

NRR	Rating	Interpretation
>120%	Excellent	Strong expansion
100-120%	Good	Healthy growth
90-100%	Fair	Maintaining
<90%	Poor	Revenue declining

Gross Revenue Retention (GRR)#

-- GRR (excludes expansion, only looks at retention)
SELECT
  SUM(LEAST(revenue_2024, revenue_2023)) / NULLIF(SUM(revenue_2023), 0) * 100
    as grr_percent
FROM customer_revenue;

Customer Metrics#

Customer Acquisition Cost (CAC)#

Definition: Cost to acquire one customer

// lib/metrics/cac.ts
export async function calculateCAC(period: { start: Date; end: Date }) {
  // Get marketing + sales spend
  const spend = await prisma.expense.aggregate({
    where: {
      category: { in: ['marketing', 'sales'] },
      date: { gte: period.start, lte: period.end },
    },
    _sum: { amount: true },
  });

  // Get new customers in period
  const newCustomers = await prisma.customer.count({
    where: {
      firstPurchaseDate: { gte: period.start, lte: period.end },
    },
  });

  return {
    totalSpend: spend._sum.amount || 0,
    newCustomers,
    cac: newCustomers > 0
      ? (spend._sum.amount || 0) / newCustomers
      : 0,
  };
}

CAC by Channel:

-- CAC broken down by acquisition channel
SELECT
  attribution_source,
  SUM(marketing_cost) as total_spend,
  COUNT(DISTINCT customer_id) as customers,
  SUM(marketing_cost) / NULLIF(COUNT(DISTINCT customer_id), 0) as cac
FROM (
  SELECT
    c.id as customer_id,
    c.attribution_source,
    COALESCE(m.spend, 0) as marketing_cost
  FROM customers c
  LEFT JOIN marketing_spend m
    ON c.attribution_source = m.channel
    AND DATE_TRUNC('month', c.created_at) = m.month
  WHERE c.created_at > NOW() - INTERVAL '3 months'
) channel_data
GROUP BY attribution_source
ORDER BY cac;

Lifetime Value (LTV)#

Definition: Total revenue expected from a customer

// Simple LTV calculation
function calculateLTV(arpu: number, grossMargin: number, churnRate: number) {
  // LTV = (ARPU × Gross Margin) / Churn Rate
  return (arpu * grossMargin) / churnRate;
}

// Example:
// ARPU: $100/month
// Gross Margin: 80%
// Monthly Churn: 5%
// LTV = ($100 × 0.80) / 0.05 = $1,600

Cohort-based LTV:

-- LTV by signup cohort
WITH customer_value AS (
  SELECT
    c.id,
    DATE_TRUNC('month', c.created_at) as cohort_month,
    SUM(p.amount) as total_revenue,
    COUNT(DISTINCT DATE_TRUNC('month', p.created_at)) as months_active
  FROM customers c
  LEFT JOIN payments p ON c.id = p.customer_id
  GROUP BY c.id, DATE_TRUNC('month', c.created_at)
)
SELECT
  cohort_month,
  COUNT(*) as customers,
  AVG(total_revenue) as avg_ltv,
  AVG(months_active) as avg_lifetime_months,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue) as median_ltv
FROM customer_value
GROUP BY cohort_month
ORDER BY cohort_month;

LTV:CAC Ratio#

// lib/metrics/ltv-cac.ts
export async function getLTVCACRatio() {
  const ltv = await calculateLTV();
  const cac = await calculateCAC();

  const ratio = cac > 0 ? ltv / cac : 0;

  return {
    ltv,
    cac,
    ratio,
    health: ratio >= 3 ? 'healthy' : ratio >= 1 ? 'warning' : 'critical',
    interpretation: getInterpretation(ratio),
  };
}

function getInterpretation(ratio: number): string {
  if (ratio >= 5) return 'Excellent - may be under-investing in growth';
  if (ratio >= 3) return 'Healthy - sustainable unit economics';
  if (ratio >= 1) return 'Warning - improve retention or reduce CAC';
  return 'Critical - losing money on each customer';
}

LTV:CAC Benchmarks:

Ratio	Rating	Action
>5:1	Over-performing	Invest more in growth
3-5:1	Healthy	Maintain and optimize
1-3:1	Warning	Improve before scaling
<1:1	Critical	Stop spending, fix fundamentals

CAC Payback Period#

// Months to recover CAC
function calculatePayback(cac: number, arpu: number, grossMargin: number) {
  return cac / (arpu * grossMargin);
}

// Example:
// CAC: $500
// ARPU: $100/month
// Gross Margin: 80%
// Payback = $500 / ($100 × 0.80) = 6.25 months

Churn Metrics#

Logo Churn (Customer Churn)#

-- Monthly customer churn rate
WITH monthly_status AS (
  SELECT
    DATE_TRUNC('month', d.date) as month,
    COUNT(DISTINCT CASE WHEN s.status = 'active' THEN s.customer_id END) as active_start,
    COUNT(DISTINCT CASE
      WHEN s.status = 'cancelled'
        AND s.cancelled_at BETWEEN d.date AND d.date + INTERVAL '1 month'
      THEN s.customer_id
    END) as churned
  FROM generate_series(
    DATE_TRUNC('month', NOW() - INTERVAL '12 months'),
    DATE_TRUNC('month', NOW()),
    INTERVAL '1 month'
  ) d(date)
  CROSS JOIN subscriptions s
  GROUP BY DATE_TRUNC('month', d.date)
)
SELECT
  month,
  active_start,
  churned,
  ROUND(churned::numeric / NULLIF(active_start, 0) * 100, 2) as churn_rate
FROM monthly_status
ORDER BY month;

Revenue Churn#

-- Monthly revenue churn rate
SELECT
  month,
  starting_mrr,
  churned_mrr,
  ROUND(churned_mrr / NULLIF(starting_mrr, 0) * 100, 2) as revenue_churn_rate
FROM mrr_monthly
ORDER BY month;

Churn Benchmarks:

Segment	Good Monthly Churn	Great Monthly Churn
SMB	<5%	<3%
Mid-Market	<3%	<2%
Enterprise	<1%	<0.5%

Growth Metrics#

Growth Rate#

-- Month-over-month growth
SELECT
  month,
  mrr,
  LAG(mrr) OVER (ORDER BY month) as prev_mrr,
  ROUND((mrr - LAG(mrr) OVER (ORDER BY month)) /
    NULLIF(LAG(mrr) OVER (ORDER BY month), 0) * 100, 2) as mom_growth
FROM mrr_monthly
ORDER BY month;

Quick Ratio#

Definition: (New MRR + Expansion MRR) / (Churned MRR + Contraction MRR)

SELECT
  month,
  (new_mrr + expansion_mrr) / NULLIF(churned_mrr + contraction_mrr, 0) as quick_ratio
FROM mrr_components
ORDER BY month;

Quick Ratio Benchmarks:

Ratio	Rating
>4	Excellent growth
2-4	Good growth
1-2	Slow growth
<1	Declining

Dashboard Implementation#

Metrics API#

// app/api/metrics/route.ts
import { NextRequest, NextResponse } from 'next/server';

export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url);
  const period = searchParams.get('period') || '30d';

  const [
    mrr,
    arr,
    nrr,
    cac,
    ltv,
    churn,
    growth,
  ] = await Promise.all([
    getMRR(),
    getARR(),
    getNRR(),
    getCAC(period),
    getLTV(),
    getChurnRate(period),
    getGrowthRate(period),
  ]);

  return NextResponse.json({
    revenue: {
      mrr,
      arr,
      nrr,
      mrrGrowth: growth.mrr,
    },
    customers: {
      total: await getCustomerCount(),
      new: await getNewCustomers(period),
      churned: await getChurnedCustomers(period),
      churnRate: churn,
    },
    unitEconomics: {
      cac,
      ltv,
      ltvCacRatio: ltv / cac,
      paybackMonths: cac / (mrr / await getCustomerCount() * 0.8),
    },
    growth: {
      momGrowth: growth.mom,
      yoyGrowth: growth.yoy,
      quickRatio: growth.quickRatio,
    },
  });
}

Dashboard Component#

// components/MetricsDashboard.tsx
'use client';

import { useMetrics } from '@/hooks/useMetrics';

export function MetricsDashboard() {
  const { data: metrics, isLoading } = useMetrics();

  if (isLoading) return <DashboardSkeleton />;

  return (
    <div className="space-y-6">
      <div className="grid grid-cols-4 gap-4">
        <MetricCard
          title="MRR"
          value={formatCurrency(metrics.revenue.mrr)}
          change={metrics.revenue.mrrGrowth}
          trend={metrics.revenue.mrrGrowth > 0 ? 'up' : 'down'}
        />
        <MetricCard
          title="ARR"
          value={formatCurrency(metrics.revenue.arr)}
          subtitle="Annual Recurring Revenue"
        />
        <MetricCard
          title="NRR"
          value={`${metrics.revenue.nrr}%`}
          health={metrics.revenue.nrr >= 100 ? 'good' : 'warning'}
        />
        <MetricCard
          title="Churn Rate"
          value={`${metrics.customers.churnRate}%`}
          health={metrics.customers.churnRate < 5 ? 'good' : 'warning'}
        />
      </div>

      <div className="grid grid-cols-3 gap-4">
        <MetricCard
          title="CAC"
          value={formatCurrency(metrics.unitEconomics.cac)}
        />
        <MetricCard
          title="LTV"
          value={formatCurrency(metrics.unitEconomics.ltv)}
        />
        <MetricCard
          title="LTV:CAC"
          value={`${metrics.unitEconomics.ltvCacRatio.toFixed(1)}x`}
          health={metrics.unitEconomics.ltvCacRatio >= 3 ? 'good' : 'warning'}
        />
      </div>

      <MRRChart data={metrics.charts.mrr} />
      <CohortTable data={metrics.charts.cohorts} />
    </div>
  );
}

Recommended Agents#

Phase	Agent	Purpose
Database	database-expert	SQL queries optimization
Backend	backend-expert	API implementation
Frontend	frontend-expert	Dashboard UI
Analytics	analytics-expert	Metric definitions

Deliverables#

Deliverable	Description
Metrics API	Endpoints for all key metrics
Dashboard UI	Visual metrics display
SQL queries	Optimized queries for each metric
Documentation	Metric definitions and formulas
Alerts	Threshold-based notifications

Best Practices#

Automate everything - Manual calculations lead to errors
Track trends, not just snapshots - Direction matters more than absolute
Segment your data - Averages hide important patterns
Benchmark against peers - Context matters
Review regularly - Weekly at minimum
Act on insights - Data without action is useless

Common Pitfalls#

Vanity metrics - Focus on actionable metrics
Wrong timeframes - Match to your sales cycle
Ignoring cohorts - New and old customers behave differently
Over-precision - Don't optimize for decimal places
Delayed data - Real-time > batch for critical metrics

Product-Market Fit - Validate PMF with metrics
Retention - Improve retention metrics
Acquisition - Optimize CAC
Fundraising Readiness - Metrics investors want