Defining KPIs

Why it matters

"How are we doing?" is the question behind most analytics work, and the answer depends entirely on how you measure. Two analysts asked for "conversion rate" will often produce two different numbers: one divides orders by sessions, the other buyers by visitors; one includes refunds, the other does not. Then a meeting is spent arguing about whose number is right instead of what to do. Mid-level analysts are expected to prevent this by writing precise metric definitions and making them the single source of truth.

Metrics, KPIs and goals

• A metric is any quantity you measure: page views, orders, average delivery time.
• A KPI (key performance indicator) is a metric the organisation has chosen to track progress toward a goal. There should be few of them.
• A goal or target is the value you want the KPI to reach: "raise monthly purchase conversion from 18% to 22% by March."

Frameworks help pick KPIs. A common one is a North Star metric (the single number that best captures value delivered to customers, such as weekly active teams for a collaboration tool) supported by input metrics the team can influence directly (sign-up completion, activation rate, invitations sent). Another is the HEART framework from Google (Happiness, Engagement, Adoption, Retention, Task success) for product experience. You do not need a framework to start, but you do need to know why each KPI exists.

What a good KPI definition contains

Write a short specification for every KPI:

The formula must be specific enough that two people writing SQL independently get the same number. Most disagreements hide in the exclusions, the counting unit (rows vs distinct people) and the time zone.

Worked example: from words to SQL

The definition above becomes this query:

WITH visitors AS (
  SELECT substr(session_date, 1, 7) AS month,
         COUNT(DISTINCT user_id)   AS visitors
  FROM sessions
  GROUP BY 1
),
buyers AS (
  SELECT substr(order_date, 1, 7) AS month,
         COUNT(DISTINCT user_id)  AS buyers
  FROM orders
  WHERE status = 'completed'          -- exclusions written down in the spec
  GROUP BY 1
)
SELECT v.month, v.visitors, COALESCE(b.buyers, 0) AS buyers,
       ROUND(100.0 * COALESCE(b.buyers, 0) / v.visitors, 1) AS conversion_pct
FROM visitors v
LEFT JOIN buyers b ON b.month = v.month
ORDER BY v.month;

Points to notice:

• COUNT(DISTINCT user_id) counts people, not sessions or orders.
• The LEFT JOIN keeps months with visitors but no buyers (conversion 0%, not missing).
• 100.0 * forces decimal division; in many SQL dialects integer ÷ integer truncates to 0.
• Each CTE maps to one part of the written definition, which makes review easy.

Ratio metrics need care

Most KPIs are ratios, and ratios have traps:

• Numerator and denominator must match. If the denominator excludes staff accounts, the numerator must too.
• Aggregate before dividing. Quarterly conversion is total quarterly buyers ÷ total quarterly visitors, not the average of three monthly percentages.
• Watch small denominators. A region with 4 visitors and 2 buyers has 50% conversion that means very little. Show counts alongside rates.

Good KPIs are actionable and hard to game

• Actionable: a team can influence it. "Total registered users ever" only goes up and changes no decisions.
• Balanced: pair a KPI with a guardrail. If support is measured on tickets closed per hour, also track reopen rate, or people will close tickets without solving them.
• Timely: available often enough to act on.

Common mistakes

• Counting rows when the definition says people.
• Forgetting refunds, test data or bots.
• Averaging percentages across groups.
• Changing a definition silently. Version it and annotate the dashboard when it changes.

Quick recap

• A KPI is a metric tied to a goal; keep the list short.
• Write a full spec: formula, exclusions, grain, unit, source, owner.
• Translate the spec directly into SQL, one CTE per part.
• Aggregate before dividing, show counts with rates, and add guardrails.

Try it

In the Do tab, fix a first-draft query so it matches a written KPI definition exactly.