Define dashboard KPIs so everyone calculates them the same way

You are an analytics lead building a metrics dictionary for a dashboard.

Business and team: [e.g. "B2B SaaS, customer success team"]
Decisions this dashboard should support: [e.g. "which accounts need attention this week", "quarterly churn review"]
KPIs we want (names as people use them today): [e.g. "active users, churn, NRR, time to first value"]
Data sources available: [e.g. "product events in BigQuery, Stripe subscriptions, HubSpot CRM"]
Known disagreements: [e.g. "sales and finance report different churn numbers"]

For each KPI, produce a dictionary entry with:
- Name and one-sentence business meaning
- Exact formula (numerator, denominator, time window, unit)
- Inclusions and exclusions (test accounts, free plans, refunds, internal users)
- Data source and table/field names, or "to confirm" if you do not know them
- Grain and refresh frequency (daily, weekly; by account, by user)
- Owner role (who decides if the definition changes)
- Target or healthy range, if one can be justified, otherwise "to set"
- Caveats and common misreadings

Then:
1. Flag KPIs that overlap or could be confused, and suggest which to keep.
2. Suggest 1-2 leading indicators for each lagging KPI.
3. List questions the team must answer before the definitions are final.

Do not invent company-specific numbers or field names; mark assumptions clearly.

Example (excerpt):
KPI: Gross revenue churn (monthly)
Meaning: share of recurring revenue lost from cancellations and downgrades in a month.
Formula: (MRR lost to cancellations + MRR lost to downgrades during month) / MRR at start of month. Unit: %.
Excludes: expansion revenue, one-off fees, internal and test accounts.
Source: Stripe subscriptions (field names to confirm).
Owner: Head of Finance.
Caveat: not the same as logo churn (accounts lost); label both clearly.
Overlap: "churn" in the sales report counts accounts, not revenue. Rename it "logo churn".

When to use it

Use this prompt when you are building a new dashboard, when two reports show different numbers for the same metric, or when a team keeps arguing about what “active” or “churn” means. It is useful for analysts, operations leads, founders and anyone who owns reporting. The output is a draft metrics dictionary that your team reviews and approves.

How to customize it

• [BUSINESS AND TEAM] shapes the definitions: “active user” means something different for a banking app and a game.
• [DECISIONS] keeps the dashboard focused. A metric that supports no decision probably does not belong on it.
• [KPIs WE WANT] should use the names people already say in meetings, so the dictionary resolves real confusion.
• [DATA SOURCES] helps the model point each metric to a system, even if exact field names must be confirmed.
• [KNOWN DISAGREEMENTS] directs attention to the definitions that cause the most trouble.

Why it works

Most dashboard disputes come from metrics that share a name but not a formula. Forcing every KPI into the same template, with numerator, denominator, time window and exclusions, makes hidden differences visible. Owners and refresh schedules turn a document into a process: someone is responsible when a definition needs to change.

Asking the model to flag overlaps and to mark unknown field names as “to confirm” keeps the draft honest instead of filling it with confident guesses. Leading indicators add practical value, because lagging metrics like churn arrive too late to act on. Once definitions are agreed, our explainer on how AI data analysis tools work shows how text-to-SQL can help turn each formula into a query.

Tips and variations

• Review the draft in one meeting with the people who use each metric; approval matters more than wording.
• Store the final dictionary next to the dashboard and link to it from each chart.
• Ask for SQL or spreadsheet formulas for each KPI once the definitions are approved.
• Limit a dashboard to the handful of KPIs that support its decisions; put the rest in a separate view.
• Revisit definitions when pricing, products or data pipelines change.