KPI Definition Template — Owner, Formula, Tolerances, Action

KPI name

Short, descriptive label used consistently across dashboards and reports. Example: Customer Retention Rate (30-day).

Description & rationale

One or two sentences explaining what the KPI measures and why it matters. Link to outcomes or business goals.

Business question addressed

Write the precise question this KPI helps answer (e.g., "Are customers we onboarded last quarter still active after 30 days?").

Owner & steward

Name the accountable owner (person or role) and the data steward (who maintains data quality and pipeline definitions). Clarify responsibilities: the owner is accountable for decisions and actions; the steward ensures the metric is computable and reliable.

Numerator & denominator (or event definitions)

Explicitly list the numerator and denominator, including any inclusions, exclusions, and time windows. If the KPI counts events, define event names and conditions.

Calculation (SQL or pseudocode)

Provide runnable calculation logic or clear pseudocode. Include table names and key joins or filters. Example SQL-style pseudocode is helpful for engineers.

Data source(s) & owner of source

List each source system, the table/view, extraction frequency, and who owns that source. Note any transformations that affect the value.

Cadence / reporting frequency

Define how often this KPI is computed and reported (real-time, hourly, daily, weekly, monthly), and specify the reference period (rolling 30 days, month-to-date, etc.).

Acceptable ranges and tolerance bands (decision thresholds)

Give explicit thresholds and names for bands (e.g., Green / Yellow / Red) tied to business impact and the decision playbook. Include numerical tolerances and whether they are absolute or relative changes.

Decision(s) triggered & linked playbooks

For each threshold band, say exactly what action is required, who should act, and where the playbook lives. Example: "If in Red for 3 consecutive days, owner calls a 15-minute triage with Product, Support, and Engineering; playbook link: /playbooks/customer-retention-triage."

Data quality checks

Specify basic automated checks that must pass before the KPI is trusted (e.g., row count sanity, null rates, duplicate keys, source freshness, expected cardinality). Provide sample queries or thresholds for these checks.

Known caveats & edge cases

Document situations that invalidate the KPI (promotions, bulk imports, system migrations, holidays) and how to adjust interpretation.

Revision history

Track version, date, author, and what changed. Example template below.

KPI: Customer Retention Rate (30-day)

Description: Percentage of customers active 30 days after signup. Useful to detect onboarding issues and product-market fit changes.

Business question: Are newly acquired customers still using the product 30 days after signup?

Owner: Head of Customer Success; Steward: Analytics Engineer (data.team@example.com)

Numerator: Count of customers who had at least one qualifying activity between signup_date and signup_date + 30 days.
Denominator: Count of customers with a signup_date in the cohort period.

Calculation (pseudocode / SQL):

-- Cohort start_date and end_date are parameters
WITH cohort AS (
  SELECT customer_id, signup_date
  FROM dim_customers
  WHERE signup_date >= {{start_date}} AND signup_date < {{end_date}}
),
activity AS (
  SELECT DISTINCT customer_id
  FROM fact_events
  WHERE event_date <= signup_date + interval '30 days' -- join logic shown conceptually
)
SELECT
  COUNT(DISTINCT CASE WHEN a.customer_id IS NOT NULL THEN c.customer_id END) AS numerator,
  COUNT(DISTINCT c.customer_id) AS denominator,
  (COUNT(DISTINCT CASE WHEN a.customer_id IS NOT NULL THEN c.customer_id END)::float / COUNT(DISTINCT c.customer_id)) * 100 AS retention_rate_percent
FROM cohort c
LEFT JOIN activity a ON a.customer_id = c.customer_id;
    

Data sources: dim_customers (CRM), fact_events (product event stream). Source owners: CRM Admin, Event Pipeline Team.

Cadence: Monthly cohort report, computed nightly for the current rolling 30-day window.

Tolerances & decision triggers:

• Green: >= 65% — Continue monitoring.
• Yellow: 55%–64% — Owner to investigate possible causes; run onboarding funnel analysis within 3 business days.
• Red: < 55% or drop > 7pt month-over-month — Trigger cross-functional triage (Product + CS + Eng) and launch a retention experiment within 5 business days.

Data quality checks:

• Daily row count of dim_customers comparing to previous week (change > 10% raises alert).
• Null rate on signup_date < 0.5%.
• No duplicate customer_id in primary key space.
• Event pipeline lag < 60 minutes for fact_events.

Known caveats: Includes customers with multiple accounts unless deduplicated; excludes customers who churned due to payment failures unless payment events are joined.

Revision history:

• v1.0 — 2025-03-12 — Created by Analytics Team — initial cohort definition.
• v1.1 — 2025-06-01 — Adjusted denominator to exclude test accounts — cause: noisy promotional campaign.