Data AnalyticsIntermediate14 min readUpdated March 2025

KPIs, Metrics & Data Modeling for BI

A KPI is only useful if everyone defines it the same way. Learn how to choose meaningful KPIs, define them precisely in SQL and model data in a star schema so every report agrees.

Metrics vs KPIs

A metric is any measurement (page views, orders). A KPI is a metric tied directly to a business goal, with a target and an owner.

A good KPI is *specific, measurable, comparable over time, and actionable* — if it moves, someone knows what to do.

Common Business KPIs

Examples by function:

Sales — Revenue, average order value, conversion rate, win rate.
Marketing — Customer acquisition cost (CAC), click-through rate, ROAS.
Product — Daily/monthly active users, retention, feature adoption.
Finance — Gross margin, MRR/ARR, burn rate.
Support — First response time, CSAT, ticket resolution time.

Defining KPIs Precisely in SQL

Ambiguity creates conflicting dashboards. Write each KPI definition once, in code, with explicit rules:

sql

-- Monthly Recurring Revenue and churn
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', period_start) AS month,
        SUM(CASE WHEN status = 'active' THEN monthly_price END) AS mrr,
        COUNT(DISTINCT CASE WHEN status = 'active' THEN customer_id END) AS active_customers,
        COUNT(DISTINCT CASE WHEN status = 'cancelled' THEN customer_id END) AS churned_customers
    FROM subscriptions
    GROUP BY 1
)
SELECT
    month,
    mrr,
    active_customers,
    ROUND(100.0 * churned_customers
          / NULLIF(LAG(active_customers) OVER (ORDER BY month), 0), 2) AS churn_rate_pct,
    ROUND(mrr / NULLIF(active_customers, 0), 2) AS arpu
FROM monthly
ORDER BY month;

Star Schema for Reporting

A star schema places numeric events in a central *fact* table surrounded by descriptive *dimension* tables. It keeps queries simple and fast:

sql

CREATE TABLE dim_customer (
    customer_key  INT PRIMARY KEY,
    customer_name VARCHAR(120),
    segment       VARCHAR(30),
    country       VARCHAR(60)
);

CREATE TABLE dim_date (
    date_key   INT PRIMARY KEY,   -- e.g. 20250131
    full_date  DATE,
    month_name VARCHAR(12),
    quarter    INT,
    year       INT
);

CREATE TABLE fact_orders (
    order_id     BIGINT,
    date_key     INT REFERENCES dim_date(date_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    quantity     INT,
    revenue      NUMERIC(12,2)
);

Key Takeaways

A KPI is a metric tied to a goal, a target and an owner.
Define each KPI once, in code, with explicit rules.
Star schemas keep BI queries simple, fast and consistent.
Shared definitions prevent conflicting dashboards.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com