Who this is for
- BI Analysts and Data Analysts who translate business goals into measurable KPIs.
- Product/Operations stakeholders who need consistent metrics across teams.
- Anyone building or maintaining a KPI dictionary or BI dashboard.
Prerequisites
- Basic understanding of metrics and dimensions.
- Ability to read simple SQL or understand event names and tables.
- Familiarity with your companys data sources and tracking (at a high level).
Why this matters
- Real tasks youll face:
- Two teams report different Conversion Rates for the same product launch.
- Finance and Sales disagree about Revenue due to refunds and currency.
- Ops wants On-Time Delivery but what is on time across time zones?
- Clear KPI definitions prevent rework, speed decisions, and build trust.
Concept explained simply
A KPI is only useful if everyone calculates it the same way. That means spelling out the formula, filters, time window, and data source so theres zero ambiguity.
Mental model
- Name: short and unambiguous.
- Purpose: why we track it and what decision it supports.
- Formula: numerator, denominator, and mathematical operation.
- Scope: time window, granularity, filters, and segments.
- Entities and uniqueness: user/session/order level? distinct or total?
- Source of truth: exact table/view or event and any joins.
- Data quality rules: exclusions (test data, bots), late-arriving data handling.
- Owner and review cadence: who approves changes and how often its checked.
A checklist to clarify any KPI
- Exact formula with numerator and denominator.
- Primary time window (e.g., daily, trailing 7 days, calendar month).
- Granularity (per day, per product, per region).
- Filters and inclusions/exclusions (e.g., exclude internal traffic).
- Entity uniqueness (distinct users vs sessions vs orders).
- Event triggers (what counts as conversion , purchase , lost customer ).
- Source of truth (tables, views, events) and refresh frequency.
- Time zone and currency assumptions.
- Owner, contact, and version date.
- Known caveats (tracking gaps, historical backfills).
Worked examples
E-commerce Conversion Rate
- Name: Conversion Rate (E-comm Web)
- Purpose: Measure how effectively sessions result in orders to optimize funnel.
- Formula: distinct Orders / total Sessions
- Window: Daily; reported also as 7-day trailing average.
- Granularity: per day, sitewide; segmentable by device type and traffic source.
- Entities:
- Orders: distinct order_id with payment_status = paid .
- Sessions: all web sessions excluding internal IPs and known bots.
- Filters: country in [US, CA]; currency normalized to USD not required for rate.
- Time zone: UTC.
- Source of truth: events.orders, events.sessions; join via user_id not required.
- Notes: Using sessions (not users) makes this a session-based rate. A user-based rate would be higher; document both if needed as variants.
SaaS Customer Churn Rate (Logo Churn)
- Name: Monthly Logo Churn Rate
- Purpose: Track customer retention health for forecasting and CS actions.
- Formula: customers lost in month / customers at start of month
- Window: Calendar month.
- Granularity: monthly overall; segment by plan_tier.
- Entities:
- Lost customer: account with active_status changing from active to cancelled within month; excludes downgrades that remain paying.
- Starting customers: distinct account_id with active_status at 00:00 on the 1st of month.
- Filters: exclude trial, internal, and sandbox accounts.
- Time zone: UTC for status change timestamps.
- Source of truth: billing.accounts_status_history snapshot.
- Notes: For revenue churn, define MRR-based formula separately.
On-Time Delivery Rate (Logistics)
- Name: On-Time Delivery Rate
- Purpose: Operational reliability and carrier performance.
- Formula: deliveries with actual_delivery_time <= promised_delivery_time / total deliveries completed
- Window: Daily, reported weekly.
- Granularity: by carrier and region.
- Entities:
- Completed delivery: shipment_id with status = delivered .
- On time: use customer-facing promise at order confirmation; adjust for daylight savings; compare in destination time zone.
- Filters: exclude re-deliveries due to customer not present; exclude test orders.
- Source of truth: logistics.shipments and promises table.
- Notes: Backfills occur within 48h; treat daily numbers as preliminary until T+2.
Net Promoter Score (NPS)
- Name: NPS (Quarterly)
- Purpose: Measure customer loyalty sentiment.
- Formula: (% Promoters [9-10]) - (% Detractors [0-6])
- Window: Calendar quarter; survey responses within quarter.
- Granularity: company-wide; segment by product_line.
- Filters: first response per user per quarter; exclude employees and testers.
- Source of truth: surveys.responses_nps
- Notes: Neutral [7-8] are excluded from numerator and denominator categories but included in total responses when calculating percentages.
How to document KPI definitions
Use this lightweight template in your KPI dictionary:
- Name:
- Purpose:
- Formula (numerator/denominator):
- Primary window and granularity:
- Entities and uniqueness rules:
- Filters (include/exclude):
- Source of truth (table/view/event):
- Time zone and currency:
- Owner and review cadence:
- Variants/aliases and differences:
- Known caveats:
- Last reviewed (date/version):
Exercises
Complete the tasks below. You can compare with the provided solutions after attempting. Note: The quick test is available to everyone; only logged-in users get saved progress.
Exercise 1: Define Active Users precisely (mirrors Exercise ex1)
Draft a KPI definition for Active Users for a mobile app that considers push notifications, app opens, and background fetch events. Specify formula, time window, uniqueness, filters, and source of truth.
- Deliverable: a completed KPI template entry.
- Tip: Decide whether background fetch without user interaction counts as active .
Show solution idea
Name: Active Users (Mobile, 7-day)
Purpose: Track engaged users for product and marketing decisions.
Formula: distinct user_id with at least one qualifying event in window / none (count only).
Window: Trailing 7 days, updated daily.
Uniqueness: distinct user_id.
Qualifying events: app_open and in_app_action; exclude background_fetch and push_received unless opened.
Filters: exclude employees/testers; include iOS/Android; region = global.
Source of truth: events.mobile_app (event_name, user_id, event_time).
Time zone: UTC.
Owner: Product Analytics.
Caveats: Known undercount on iOS < 13 due to tracking changes.
Purpose: Track engaged users for product and marketing decisions.
Formula: distinct user_id with at least one qualifying event in window / none (count only).
Window: Trailing 7 days, updated daily.
Uniqueness: distinct user_id.
Qualifying events: app_open and in_app_action; exclude background_fetch and push_received unless opened.
Filters: exclude employees/testers; include iOS/Android; region = global.
Source of truth: events.mobile_app (event_name, user_id, event_time).
Time zone: UTC.
Owner: Product Analytics.
Caveats: Known undercount on iOS < 13 due to tracking changes.
Exercise 2: Resolve Conversion Rate conflicts (mirrors Exercise ex2)
Marketing uses unique purchasers / unique visitors . Product uses orders / sessions . Propose the canonical definition and document the alternative as a variant. Explain impact on reported values.
Show solution idea
Canonical: Conversion Rate (Sessions) = orders_paid / total_sessions; reason: aligns to funnel steps tracked per session.
Variant: Conversion Rate (Users) = unique_purchasers / unique_visitors.
Impact: Session-based rate is typically lower because denominator (sessions) > users. Document both with labels, ensure dashboards clearly display which is shown. Map aliases: CR (Marketing) -> Conversion Rate (Users) ; default dashboard shows session-based.
Variant: Conversion Rate (Users) = unique_purchasers / unique_visitors.
Impact: Session-based rate is typically lower because denominator (sessions) > users. Document both with labels, ensure dashboards clearly display which is shown. Map aliases: CR (Marketing) -> Conversion Rate (Users) ; default dashboard shows session-based.
Common mistakes and self-check
Common mistakes
- Vague formula (e.g., engagement ) without numerator/denominator.
- Mixing time zones or currencies silently.
- Counting events instead of distinct entities when needed.
- Forgetting exclusions (internal traffic, test data, bots).
- Unstated late-data policy, causing day-to-day jumps.
Self-check before publishing
- Can a new analyst compute it from your definition alone?
- Would two teams get the same number within rounding?
- Is the time window and granularity explicit?
- Are filters and exclusions testable in SQL?
- Is the data source (and refresh) named precisely?
- Have stakeholders signed off (owner listed)?
Practical projects
- Create a one-page KPI dictionary for your team: 10 core KPIs with full definitions and owners.
- Audit an existing dashboard: identify and fix at least 3 ambiguous metrics.
- Implement variant labeling: where two definitions exist, prefix names (e.g., Users vs Sessions ) and add info tooltips.
Learning path
- Start with one domain (e.g., acquisition) and lock 35 core KPIs.
- Add operations/retention KPIs using the same template.
- Run a review session with stakeholders; capture decisions in the dictionary.
- Automate QA checks (e.g., bot filters, time zone standardization) in your ETL/BI layer.
Next steps
- Do the exercises above, then take the quick test below to reinforce concepts.
- Remember: the test is available to everyone; only logged-in users get saved progress.
Mini challenge
In 10 minutes, write a clean definition for Repeat Purchase Rate for your store. Include formula, time window, and how you treat exchanges and refunds. Then identify one likely stakeholder conflict and how youd resolve it.