KPI definition dictionary

Purpose

Create a single, auditable definition for each requested KPI. The result ties the business question to a formula, grain, source fields, time window, owner, target, and quality test so analysts cannot silently publish competing versions of the same metric.

Scope

Cover KPI requests, existing metric catalogs, dashboard specifications, SQL or spreadsheet formulas, source-table schemas, target plans, and data-quality policies. Include derived ratios, counts, rates, cohort measures, and period comparisons. Excluded: redesigning source systems, changing production queries, setting targets without an approved business source, and certifying data that was not supplied.

Data basis

• Metric request register with metric_id, business_question, requester, decision, cadence, and status.
• Source schemas and record tables containing table_name, column_name, data_type, grain, key, and refresh_at.
• Dashboard or report definitions with metric_name, formula, filter, date_field, dimensions, and visualization.
• Planning or policy documents containing target_value, threshold, owner, review_cadence, and unit conventions.

Result

Produce a KPI dictionary sheet with one row per approved metric and a conflict log for duplicate names, incompatible formulas, missing fields, and untestable assumptions. Each row includes metric_id, canonical_name, purpose, numerator, denominator, formula, source_table, source_columns, grain, date_field, time_window, dimensions, unit, owner, target, thresholds, refresh cadence, status, and citations.

Quality criteria

• Every metric has a unique metric_id and a plain-language decision purpose.
• Ratios identify numerator, denominator, zero-denominator behavior, and rounding rule.
• Source columns, grain, date field, and filters are named exactly as supplied.
• Targets and thresholds are cited to a plan or policy; unknown values remain blank and flagged.
• Duplicate or conflicting definitions appear as separate findings with document or row citations.
• Every dictionary row has a validation rule that can be checked against the stated source data.

Instructions

Prefer an existing approved definition over a dashboard label or informal request. Preserve the requested name in an alias field when it differs from the canonical name. Distinguish event date, posting date, and snapshot date; never infer one from another. Use the source unit and currency as written, and mark conversions as dependencies. For a ratio, report the denominator population and exclude rule alongside the formula. Mark a metric proposed until its owner, source, and acceptance test are evidenced. Do not merge metrics merely because their labels match.

Adapt before use

• Map the company metric register, source schema, dashboard specification, and policy column names to the fields used here.
• Add the approved naming convention, unit and currency rules, and metric lifecycle statuses.
• Identify the roles allowed to approve owners, targets, thresholds, and canonical definitions.
• Add the authoritative planning document or KPI governance policy to the scope.