Defining KPI Calculation Rules

Who this is for

BI Developers, Analytics Engineers, Product Analysts, and anyone responsible for turning stakeholder asks into trustworthy metrics and dashboards.

Prerequisites

• Basic SQL (SELECT, JOIN, GROUP BY)
• Understanding of data grains (row-level detail vs aggregated)
• Familiarity with business domains you support (e.g., e-commerce, SaaS, support)

Learning path

1. Learn the KPI specification template.
2. Practice mapping vague asks to precise rules.
3. Validate edge cases and align on grain, filters, and time.
4. Draft SQL logic and test with sample data.
5. Document and get stakeholder sign-off.

Why this matters

In real BI work, you will be asked to report “Conversion Rate,” “Retention,” or “Gross Margin.” Without clear calculation rules, two teams will produce different numbers and lose trust. Precise KPI rules ensure:

• Reproducibility across dashboards and teams
• Accurate decision-making and A/B test evaluation
• Faster delivery: fewer back-and-forths

Concept explained simply

A KPI is a business question answered by a number. To make it trustworthy, you must define exactly what data to use, how to filter it, how to aggregate it, and what to do with messy cases (nulls, returns, time zones, late data).

Mental model

Think of KPI rules as a contract between business and data:

• Business intent: What success looks like
• Data contract: Where the data comes from and how to transform it
• Reconciliation: How to check it keeps working over time

KPI specification template

Use this template to define any KPI:

• Name: Clear, versioned label (e.g., Conversion Rate v2)
• Business Question: What decision this number informs
• Domain: e.g., E-commerce / SaaS / Support
• Grain: The atomic unit (session, order, customer, ticket, day)
• Time Dimension: Event date vs booking date; time zone; window
• Numerator: Exact events/rows counted; filters
• Denominator: Exact population; filters
• Aggregation: SUM, COUNT DISTINCT, AVG; how to roll up
• Filters: Inclusion/exclusion (e.g., exclude test traffic, refunds)
• Edge Cases: Nulls, zeros, duplicates, late-arriving facts
• Data Sources: Tables/views and join keys
• Quality Checks: Sanity thresholds and reconciliation rules
• Owner & Sign-off: Who approves definition changes

Worked examples

1) E-commerce Conversion Rate

• Name: Site Conversion Rate v1
• Grain: Session
• Time: Session start date, UTC, calendar day
• Numerator: Sessions with at least one attributed order within 30 minutes of session end; exclude cancelled/refunded orders
• Denominator: All non-bot, non-internal sessions; exclude QA/test traffic
• Aggregation: SUM(converted_sessions)/COUNT(all_sessions)
• Sources: web.sessions s, commerce.orders o (join by attributed_session_id)
• Edge: If denominator is 0 for a day, return NULL

WITH base AS (
  SELECT s.session_id,
         s.session_start::date AS d,
         CASE WHEN EXISTS (
            SELECT 1 FROM commerce.orders o
            WHERE o.attributed_session_id = s.session_id
              AND o.status = 'completed'
              AND o.created_at <= s.session_end + interval '30 minutes'
         ) THEN 1 ELSE 0 END AS converted
  FROM web.sessions s
  WHERE s.is_bot = false AND s.is_internal = false AND s.is_test = false
)
SELECT d,
       CASE WHEN COUNT(*)=0 THEN NULL
            ELSE 1.0*SUM(converted)/COUNT(*) END AS conversion_rate
FROM base
GROUP BY d;

2) SaaS 3-Month Customer Retention

• Name: M3 Customer Retention v2
• Grain: Customer cohort by signup month
• Time: Cohort month; retained if active in month+3 (any billable activity)
• Numerator: Customers with at least one billable day in month+3
• Denominator: New customers in cohort month (exclude trials that never activate)
• Aggregation: COUNT DISTINCT
• Edge: If customer upgrades/downgrades, still retain if billable

3) Gross Margin %

• Name: Gross Margin % v1
• Grain: Order
• Time: Order booked date, merchant local time
• Numerator: Net Revenue = (Item price - discounts - refunds) + shipping income
• Denominator: Net Revenue - COGS; GM% = (Net Revenue - COGS) / Net Revenue
• Filters: Exclude test orders, internal comps
• Edge: If Net Revenue <= 0, set GM% NULL for that order; aggregate weighted by revenue

4) Support First Contact Resolution (FCR)

• Name: FCR v1
• Grain: Ticket
• Time: Ticket creation date, support center local time
• Numerator: Tickets resolved with exactly one agent response within 24 hours
• Denominator: All customer-originated tickets (exclude auto-generated)
• Edge: Merged tickets inherit earliest child; count once

How to define a KPI: step-by-step

1. Clarify intent: Ask which decision this KPI supports and what "good" means.
2. Choose grain: The smallest level keeping logic consistent.
3. Pin time: Event timestamp, time zone, and rolling vs calendar windows.
4. Write numerator and denominator in plain language; then add precise filters.
5. List edge cases and decide behaviors for each.
6. Draft SQL sketch; test with small date range; compare to stakeholder expectations.
7. Document and version the definition; get sign-off.

Exercises

Do these now. A quick test is available at the end. Anyone can take it; only logged-in users will have progress saved.

Exercise 1: Turn a vague KPI into a precise rule

Stakeholder ask: "We need Signup Conversion from marketing sessions to signups." Produce a KPI spec using the template.

• Grain, Time, Numerator, Denominator, Filters, Edge cases, Sources

Exercise 2: Resolve conflicting definitions

Team A defines Active Users as users with at least one session in the last 30 days. Team B defines it as users with any event (including email open) in the last 28 days. Propose a single canonical KPI rule and justify it.

Exercise checklist

• [ ] Grain is explicit and matches the business question
• [ ] Time dimension and window are explicit (date, zone, rolling/cal)
• [ ] Numerator and denominator are traceable to named sources
• [ ] Filters are explicit and symmetric where needed
• [ ] Edge cases have defined behaviors (zeros, nulls, late data)
• [ ] SQL sketch or test approach is described
• [ ] Ownership and versioning noted

Common mistakes and self-check

• Inconsistent filters: Numerator excludes something the denominator includes. Self-check: Read filters side-by-side and ensure symmetry where intended.
• Wrong grain: Mixing orders and customers. Self-check: Each row should represent the same unit across numerator and denominator unless otherwise justified.
• Time drift: Comparing calendar month to rolling 30 days. Self-check: Confirm window type and time zone.
• Divide-by-zero: Returning 0 when denominator is 0 hides data issues. Self-check: Return NULL and alert.
• Hidden duplicates: Counting users across merged tickets/sessions. Self-check: Define dedup keys and run DISTINCT tests.
• Late data volatility: KPI keeps changing. Self-check: Define a data freshness lag and backfill policy.

Practical projects

1. Define and document two KPIs for your domain (e.g., Product Adoption Rate and Churn Rate). Include SQL sketches and edge-case rules.
2. Create a "KPI Validation Report" that compares your new definitions with legacy ones over the last 90 days and explains differences.
3. Build a lightweight data quality checklist for your KPIs (freshness, duplicates, volume thresholds) and run it weekly.

Mini challenge

You measure Weekly Active Users (WAU). Marketing wants to include users who only clicked an email but never opened the app. Engineering wants only in-app sessions. Propose two variants with clear names and rules so both can be tracked without confusion, and explain when to use each.

Next steps

• Review your last dashboard: pick one KPI and rewrite its spec with this template.
• Run the quick test below to check understanding.
• Share a one-page KPI definition with your stakeholder for sign-off.