How to create a KPI dictionary for consistent reporting
A KPI dictionary should define each metric's business purpose, exact formula, numerator and denominator, entity grain, inclusion and exclusion rules, time basis, source fields, refresh cadence, owner, quality checks, known limitations, and version history. Link the definition to actual report calculations and require governed review when a definition changes.
Where the difficulty begins
Revenue, active customer, conversion, churn, and pipeline can each have several reasonable definitions. Reports disagree when teams embed those choices in formulas without a shared contract.
From the field · HR and finance reporting team
Resolve conflicting headcount, active-customer, and revenue definitions
HR counts employees on leave, finance excludes unpaid leave, and operations counts anyone active during the month. Customer and revenue metrics have similar conflicts, so reports disagree even when every formula is working.
Columns used in the example
metric_name · business_definition · formula · grain · population · exclusions · owner · effective_date
Follow it with your own file
- Collect every current definition and the decision it supports.
- Specify entity, time grain, population, exclusions, units, and source fields.
- Name a business owner and a technical steward.
- Version changes with an effective date and restate history only when explicitly approved.
Check: Recalculate representative periods from the definition, compare downstream reports, and require unresolved differences to be documented rather than averaged away.
Check the source before you publish
- Purpose stated
- Formula exact
- Grain explicit
- Scope documented
- Owners assigned
- Change history retained
What may be distorting the answer
- Meetings debate numbers instead of decisions.
- The same KPI differs between finance and product.
- A dashboard changes after an undocumented query edit.
- Historical trends move when a definition changes.
Build the result step by step
- Inventory decision-critical metrics: Start with metrics used for external, board, financial, customer, and operational decisions.
- Write the complete contract: Document formula, components, grain, scope, timing, source, owner, and examples.
- Validate against calculations: Reconcile the definition with current queries and representative records; add automated tests.
- Govern changes: Record reason, approver, effective date, affected reports, and restatement impact.
Define edge cases, not just the happy path
Does active mean any login, a completed action, or a paid account? Are test, employee, refunded, and merged accounts excluded? Which timezone closes the month? Edge cases often explain most disagreements.
Include small worked examples so users can test their interpretation.
Treat definitions as versioned assets
A definition may need to change, but the change should be visible. Record the old and new contract, effective period, reason, approver, and quantified impact.
Reports should reference the metric version used so historical decisions remain reproducible.
Minimum KPI dictionary fields
| Field | Question answered | Example |
|---|---|---|
| Formula and components | How is it calculated? | Retained ARR / opening ARR |
| Grain and scope | Who or what counts? | Paid account-month |
| Time basis | When does it count? | UTC calendar month |
| Owner and version | Who approves change? | Finance, v2.1 |
The decisions that matter
- A label is not a definition.
- Specify denominator, grain, and time basis explicitly.
- Assign both business and technical owners.
- Version changes and quantify their historical impact.
Doing these steps in Prepyr
Upload a CSV working copy in Prepyr. Describe the result you need: Create a KPI dictionary with formulas, grain, owners, exclusions, and effective dates. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Saved analysis · Report templates · Workflow reuse · Evidence lineage
Reports that reconcile across teams because their metrics are governed as reusable definitions rather than copied labels and formulas.
Questions readers usually ask next
Who should own a KPI definition?
Assign a business owner for meaning and a technical owner for implementation and quality.
Where should the dictionary live?
In a searchable governed location linked from reports and calculations, not only in a slide deck.
How detailed should it be?
Detailed enough for another analyst to reproduce the metric and classify edge cases consistently.