DEV Community

Vivek Kumar
Vivek Kumar

Posted on

KPI Tracking with SQL: A Practical Starter Kit for SaaS Developers

#webdev #analytics #sql #database

You've shipped the product. Users are signing up. Subscriptions are flowing in. Now your founder, your investors, or your own curiosity is asking: how are we actually doing?

Most teams reach for a BI tool or a spreadsheet at this point. But if your data lives in a SQL database—Postgres, MySQL, Redshift, BigQuery—you already have everything you need to answer the most important product and business questions. No third-party analytics event pipeline required.

This guide gives you practical SQL queries for the KPIs that matter most in a SaaS product: revenue, engagement, retention, and growth. These aren't toy examples—they use realistic table names you'd find in a real product codebase, and they come with the gotchas that trip most developers up the first time.

Your Assumed Schema

To keep the queries grounded, here's the database schema we'll work against throughout:

Table Key Columns
users id, email, created_at, plan_id
subscriptions id, user_id, status, amount_cents, billing_cycle, started_at, canceled_at
payments id, user_id, amount_cents, paid_at, status
events id, user_id, event_name, occurred_at
trials id, user_id, started_at, converted_at

Adapt column names to match your own schema, but the patterns will transfer directly.

1. Monthly Recurring Revenue (MRR)

MRR is the single most-watched number in any subscription business. It answers: how much predictable revenue do we have right now?

SELECT
  DATE_TRUNC('month', CURRENT_DATE) AS month,
  SUM(amount_cents) / 100.0          AS mrr
FROM subscriptions
WHERE status = 'active'
  AND billing_cycle = 'monthly';
Enter fullscreen mode Exit fullscreen mode

If you have annual subscribers, normalize their value to monthly by dividing by 12:

SELECT
  SUM(
    CASE billing_cycle
      WHEN 'monthly' THEN amount_cents
      WHEN 'annual'  THEN amount_cents / 12
    END
  ) / 100.0 AS mrr
FROM subscriptions
WHERE status = 'active';
Enter fullscreen mode Exit fullscreen mode

Gotcha: Don't double-count. If a user can have multiple subscriptions (e.g. per-seat billing), make sure your subscriptions table represents the correct unit. Summing at the user level first and then aggregating is safer when your data model is complex.

2. Churn Rate

Churn tells you how fast you're losing customers. Monthly churn is typically calculated as:

Churn Rate = Customers Lost This Month ÷ Customers at Start of Month

WITH start_of_month AS (
  SELECT COUNT(DISTINCT user_id) AS total
  FROM subscriptions
  WHERE status = 'active'
    AND started_at < DATE_TRUNC('month', CURRENT_DATE)
),
churned AS (
  SELECT COUNT(DISTINCT user_id) AS lost
  FROM subscriptions
  WHERE status = 'canceled'
    AND canceled_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND canceled_at  < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
)
SELECT
  ROUND(lost * 100.0 / NULLIF(total, 0), 2) AS monthly_churn_pct
FROM start_of_month, churned;
Enter fullscreen mode Exit fullscreen mode

Gotcha: Using NULLIF(total, 0) prevents division-by-zero errors, which will otherwise silently crash your query or return NULL without warning. A 2–3% monthly churn rate is a common benchmark to aim below; above 5% is a red flag for most B2B SaaS.

3. Daily Active Users (DAU) and the Stickiness Ratio

DAU tells you engagement. But the ratio of DAU to MAU—your "stickiness ratio"—tells you whether users are building habits.

-- DAU: today
SELECT
  COUNT(DISTINCT user_id) AS dau
FROM events
WHERE occurred_at::date = CURRENT_DATE;

-- MAU: last 30 days
SELECT
  COUNT(DISTINCT user_id) AS mau
FROM events
WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days';
Enter fullscreen mode Exit fullscreen mode

Combine them to get the stickiness ratio:

WITH dau AS (
  SELECT COUNT(DISTINCT user_id) AS daily
  FROM events
  WHERE occurred_at::date = CURRENT_DATE
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS monthly
  FROM events
  WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  ROUND(daily * 100.0 / NULLIF(monthly, 0), 1) AS stickiness_pct
FROM dau, mau;
Enter fullscreen mode Exit fullscreen mode

A stickiness ratio above 20% is generally considered healthy. Slack and Facebook sit north of 50%—most B2B tools are happy at 25–40%.

Gotcha: Be precise about what counts as an "active" event. Including page loads inflates the number. Stick to intentional actions: creating a record, running a query, submitting a form.

4. Average Revenue Per User (ARPU)

ARPU helps you understand whether your pricing is working and how different segments compare.

SELECT
  ROUND(
    SUM(amount_cents) / 100.0 / NULLIF(COUNT(DISTINCT user_id), 0),
    2
  ) AS arpu
FROM subscriptions
WHERE status = 'active';
Enter fullscreen mode Exit fullscreen mode

Break it down by plan to see which tier drives the most value:

SELECT
  u.plan_id,
  COUNT(DISTINCT s.user_id)                             AS customers,
  ROUND(AVG(s.amount_cents) / 100.0, 2)                AS avg_revenue
FROM subscriptions s
JOIN users u ON u.id = s.user_id
WHERE s.status = 'active'
GROUP BY u.plan_id
ORDER BY avg_revenue DESC;
Enter fullscreen mode Exit fullscreen mode

5. Trial-to-Paid Conversion Rate

If you have a freemium or free trial model, conversion rate is often more valuable than new signup counts—it's what actually drives revenue.

WITH trial_cohort AS (
  SELECT COUNT(DISTINCT user_id) AS started
  FROM trials
  WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
),
converted AS (
  SELECT COUNT(DISTINCT user_id) AS paid
  FROM trials
  WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
    AND converted_at IS NOT NULL
)
SELECT
  ROUND(paid * 100.0 / NULLIF(started, 0), 1) AS conversion_pct
FROM trial_cohort, converted;
Enter fullscreen mode Exit fullscreen mode

Gotcha: Be careful comparing trials from this month to conversions from this month. Many trials take 14–30 days to convert. A better approach is to look at the conversion rate for trials that started 30–60 days ago, giving them time to complete their journey.

6. Revenue Growth Rate (Month-over-Month)

Revenue growth tells you if the business is accelerating, stalling, or shrinking. Track it month-over-month.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', paid_at) AS month,
    SUM(amount_cents) / 100.0    AS revenue
  FROM payments
  WHERE status = 'succeeded'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month DESC
LIMIT 6;
Enter fullscreen mode Exit fullscreen mode

This query uses a window function (LAG) to pull the previous month's value and compute the percentage change. The result looks something like:

month revenue prev_month_revenue mom_growth_pct
2026-05-01 48,200 41,500 16.1%
2026-04-01 41,500 36,800 12.8%
2026-03-01 36,800 34,100 7.9%

7. Feature Adoption Rate

If you're building a product, you need to know which features people actually use.

SELECT
  event_name                                                AS feature,
  COUNT(DISTINCT user_id)                                   AS users_used,
  (SELECT COUNT(DISTINCT user_id) FROM events
   WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days') AS total_active_users,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 /
    NULLIF(
      (SELECT COUNT(DISTINCT user_id) FROM events
       WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'),
      0
    ),
    1
  )                                                         AS adoption_pct
FROM events
WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'
  AND event_name IN ('export_report', 'invite_teammate', 'create_dashboard', 'connect_datasource')
GROUP BY event_name
ORDER BY adoption_pct DESC;
Enter fullscreen mode Exit fullscreen mode

Common Mistakes to Avoid

1. Forgetting time zones. CURRENT_DATE returns a date in your server's time zone. If your users are spread across time zones, you may be cutting off events. Store timestamps in UTC and always convert explicitly.

2. Counting users vs. counting events. Many queries should use COUNT(DISTINCT user_id), not COUNT(*). One user generating 100 events should not count as 100 active users.

3. Including test accounts. Seed data, internal team emails, and test subscriptions will skew every metric. Add a WHERE email NOT LIKE '%@yourcompany.com%' filter or flag test accounts in your users table with an is_test column.

4. Pulling raw SQL into spreadsheets. Once your KPI queries are working, don't copy-paste results into a spreadsheet every week. That workflow breaks. Connect a dashboard tool directly to your database to keep numbers fresh and reduce manual error.

5. Ignoring failed payments. When calculating MRR from payments, always filter by status = 'succeeded'. Failed payments are not revenue, and including them will inflate your numbers.

Key Takeaways

SQL is one of the most powerful tools you have for understanding your own product. You don't need a data warehouse or a dedicated analyst to get started—you need a database connection and the right queries.

Start with these seven metrics: MRR, churn rate, DAU/MAU stickiness, ARPU, trial conversion, revenue growth, and feature adoption. Each one answers a specific question about whether your product is healthy and growing.

Build them once, pin them to a dashboard you check weekly, and you'll make better product decisions with far less guesswork.

How do you track KPIs at your company? Do you query your database directly, use a BI tool, or pull from a third-party analytics service? Drop your setup in the comments — I'd love to hear what works for your team.

Top comments (0)