Writing KPI Queries For Dashboards

Why this matters

Dashboards live or die by clean, reliable SQL. As a BI Analyst, you will repeatedly write time-series KPIs like revenue, active users, conversion rates, and retention. Good KPI queries must handle missing dates, correct filters (e.g., only completed orders), and be fast enough for refresh.

• Marketing: daily spend, leads, conversion rate to signup and purchase.
• Product: DAU/WAU/MAU, feature adoption, retention curves.
• Revenue: orders, revenue, AOV, MRR/churn for subscriptions.

Who this is for

• BI Analysts and Data Analysts building dashboards and reports.
• Anyone turning raw event/order data into business KPIs.

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY, JOIN.
• Basics of window functions (ROW_NUMBER, SUM/AVG OVER ORDER BY).
• Familiarity with dates, casting to date, and simple CASE expressions.

Concept explained simply

You want a number per period (day/week/month) that matches the exact business definition. The safe pattern:

1. Build a date spine (a list of all dates in range).
2. Aggregate facts per date with correct filters (e.g., status = 'completed').
3. LEFT JOIN your aggregates to the date spine (so missing days show zeros).
4. Compute rates and rolling averages in a final SELECT.

Mental model

• Date spine = the canvas.
• Aggregations = the paint (counts, sums, uniques).
• Windows = blending tools (moving averages, cumulative totals).
• Filters = the rules of your metric (what counts, what doesn’t).

Core patterns you will reuse

• Date spine via calendar table (dim_date) or generator.
• LEFT JOIN to avoid losing zero-activity days.
• Rate = numerator / denominator with division-by-zero guard.
• Rolling window = AVG/SUM OVER (ORDER BY date ROWS BETWEEN x PRECEDING AND CURRENT ROW).
• De-duplication via DISTINCT or first-event per user/order when needed.

Worked examples

Assume tables:

• dim_date(d DATE)
• orders(id, user_id, created_at, status, total_amount)
• events(user_id, event_name, event_time)
• subscriptions(user_id, monthly_amount, start_date, end_date)

Example 1 — Daily orders, revenue, 7-day moving average

-- Adjust DATE() casting to your SQL dialect if needed
WITH date_spine AS (
  SELECT d
  FROM dim_date
  WHERE d BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'
),
agg AS (
  SELECT
    ds.d AS dt,
    COALESCE(SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END), 0) AS orders,
    COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END), 0) AS revenue
  FROM date_spine ds
  LEFT JOIN orders o
    ON DATE(o.created_at) = ds.d
  GROUP BY ds.d
)
SELECT
  dt,
  orders,
  revenue,
  AVG(revenue) OVER (
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_ma7
FROM agg
ORDER BY dt;

Notes: LEFT JOIN preserves missing days; CASE ensures only completed orders count.

Example 2 — Daily visit-to-purchase conversion rate

WITH date_spine AS (
  SELECT d AS dt
  FROM dim_date
  WHERE d BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'
),
visits AS (
  SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS visitors
  FROM events
  WHERE event_name = 'session_start'
  GROUP BY DATE(event_time)
),
purchasers AS (
  SELECT DATE(created_at) AS dt, COUNT(DISTINCT user_id) AS purchasers
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE(created_at)
)
SELECT
  ds.dt,
  COALESCE(v.visitors, 0) AS visitors,
  COALESCE(p.purchasers, 0) AS purchasers,
  CASE WHEN COALESCE(v.visitors, 0) = 0 THEN 0
       ELSE 1.0 * COALESCE(p.purchasers, 0) / v.visitors END AS conversion_rate
FROM date_spine ds
LEFT JOIN visits v ON v.dt = ds.dt
LEFT JOIN purchasers p ON p.dt = ds.dt
ORDER BY ds.dt;

Guard against division by zero and use distinct users for both sides.

Example 3 — Monthly Recurring Revenue (MRR)

-- This uses a month spine; adjust DATE_TRUNC/DATE_ADD to your SQL dialect
WITH month_spine AS (
  SELECT DISTINCT DATE_TRUNC(d, MONTH) AS month_start
  FROM dim_date
  WHERE d BETWEEN DATE '2024-01-01' AND DATE '2024-03-31'
)
SELECT
  ms.month_start,
  COALESCE(SUM(s.monthly_amount), 0) AS mrr
FROM month_spine ms
JOIN subscriptions s
  ON s.start_date < DATE_ADD(ms.month_start, INTERVAL 1 MONTH)
 AND (s.end_date IS NULL OR s.end_date >= ms.month_start)
GROUP BY ms.month_start
ORDER BY ms.month_start;

Condition logic: a subscription is active for a month if it starts before next month and hasn’t ended before the month starts.

-- Example (PostgreSQL): generate_series for days
WITH date_spine AS (
  SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day')::date AS d
)
SELECT * FROM date_spine;

Reusable query templates

-- Using dim_date
SELECT d
FROM dim_date
WHERE d BETWEEN DATE 'YYYY-MM-DD' AND DATE 'YYYY-MM-DD';

WITH date_spine AS (...),
base AS (...),
agg AS (...)
SELECT ... FROM ...;

CASE WHEN denom = 0 THEN 0 ELSE 1.0 * num / denom END AS rate

AVG(value) OVER (ORDER BY dt ROWS BETWEEN N PRECEDING AND CURRENT ROW) AS value_ma

Performance and reliability tips

• Filter early (date range, statuses) to reduce scanned rows.
• Avoid SELECT * when joining large tables; select the needed columns.
• Pre-aggregate heavy event data before joining to a calendar.
• Use window functions wisely; avoid unnecessary nested windows.
• Ensure consistent timezone handling; convert to business timezone before aggregating by date.

Make KPIs dashboard-ready

• Return one row per period with stable column names.
• Include units where relevant (e.g., revenue_usd, rate as decimal 0–1 or percentage column).
• Prefer lowercase_snake_case for field names.
• Provide zero rows for periods with no activity.

Exercises (do these before the test)

These mirror the tasks in the Exercises section below. Try them here first.

Exercise 1 — Daily orders and revenue with 7-day moving average

Tables: dim_date(d), orders(id, user_id, created_at, status, total_amount).

• Build a date spine for 2024-01-01 to 2024-01-31.
• Compute daily completed order count and revenue.
• Add a 7-day moving average of revenue.

Exercise 2 — Signup-to-first-order conversion within 14 days (by signup week)

Tables: users(id, signed_up_at), orders(id, user_id, created_at, status).

• For each signup_week, count signups.
• Find each user’s first completed order date (if any).
• Flag if the first order occurred within 14 days of signup.
• Return signup_week, signups, converted_14d, conversion_rate_14d.

Common mistakes and how to self-check

• Inner joining to facts and losing days with zero activity. Self-check: does every date in the range appear?
• Counting canceled/pending orders. Self-check: filter status to the business definition (e.g., status = 'completed').
• Double-counting revenue when joining order_items without grouping. Self-check: aggregate at the right grain before joining.
• Division by zero or integer division. Self-check: cast to decimal and guard denominator.
• Timezone drift. Self-check: convert datetimes to business timezone before DATE() extraction.
• Inconsistent definitions across dashboards. Self-check: keep metric definitions in the query comments and reuse CTE patterns.

Checklist before shipping a KPI query

• Clear definition: status filters, date range, and grain are explicit.
• Date spine + LEFT JOIN used for time series.
• Rates handle zero denominators.
• Columns are named and typed for the dashboard (units, decimals).
• Spot-check results vs a known day/week and totals match expectations.

Practical projects

• 90-Day Growth Board: DAU/WAU/MAU, new signups, orders, revenue, AOV, and 7-day moving averages.
• Subscription Health: MRR, new MRR, churned MRR, net MRR change, and logo churn rate by month.
• Funnel Insights: visits → signups → first order within 14 days, with daily rates and weekly cohort breakdown.

Mini challenge

Produce a daily table for the last 60 days with: visitors, purchasers, conversion_rate, revenue, revenue_ma7. Ensure days with no activity appear with zeros. Keep column names dashboard-friendly. Aim for a single query with CTEs.

Learning path

• Aggregate basics (COUNT, SUM, DISTINCT) at the correct grain.
• Date handling and calendar tables; building date/month spines.
• Rates and moving windows; cumulative and rolling metrics.
• Funnel and cohort patterns; first-touch/last-touch logic.
• Performance tuning and pre-aggregation for dashboards.

Next steps

• Convert one of your current metrics to use a date spine and rolling window.
• Document your KPI definitions inside the query using comments.
• Schedule a refresh and validate numbers against a manual spot-check.

Quick Test

Ready to check your understanding? Take the quick test below. It is available to everyone. Only logged-in users will have their progress saved.