KPI Tracker

---
name: kpi-tracker
description: Use when building a multi-KPI scorecard tracker over time, with sparklines, MoM/YoY, thresholds. Triggers on "KPI tracker", "scorecard", "weekly business review", "monthly KPI dashboard".
---

# KPI Tracker

Build a tracker the team scans in 30 seconds and uses to drive a 30-minute meeting. KPI trackers fail when they're either a wall of numbers (no signal) or a designed dashboard that takes 20 minutes to update (so it doesn't get updated). Optimize for "easy to update, easy to scan."

## Required inputs

1. **The KPIs** — 5-15 metrics. More than 15 is a list, not a tracker.
2. **For each KPI**: name, definition (1 sentence), unit ($, %, count), target, threshold for green/amber/red, owner
3. **Period** — weekly or monthly (don't mix). Weekly for ops, monthly for board.
4. **History window** — last 13/26/52 weeks or 12/24 months
5. **Source** — manual entry, CSV paste, formula off another tab

If the user names 25 KPIs, push back — pick the 10 that actually drive decisions. The rest go to a "secondary" tab.

## Approach

**openpyxl** for the workbook. **Sparklines** in Excel are native (`add_sparkline` in xlsxwriter; openpyxl has limited sparkline support — may need to switch libs depending on need). For Google Sheets, the `SPARKLINE` formula is built in and surprisingly capable (`=SPARKLINE(range, {"charttype","line";"color","#3366cc"})`).

For threshold colors, use **conditional formatting** with formulas referencing the KPI's own threshold values from a config tab — not hardcoded colors per cell.

## Workbook structure

1. **Cover** — period definition, last refresh date, owner, color key for thresholds, instructions for refresh
2. **KPI Config** — one row per KPI: ID, Name, Definition, Unit, Target, Green Threshold, Amber Threshold, Direction (higher-is-better or lower-is-better), Owner, Source
3. **Data** — KPIs down the rows, periods across columns. The historical record. Editable.
4. **Tracker** (the main view) — KPIs down the rows. Columns: Name | Owner | Latest | MoM Δ | YoY Δ | Sparkline (last 13) | Target | Status. Status cell is conditionally colored.
5. **Definitions** — a clean reference of every KPI definition. The "what does this even mean" tab. Critical because someone always asks.
6. **Trend** — small-multiple charts (one per KPI), 2-3 per row. The "deep dive" view for the meeting.

## The Tracker tab — column-by-column

- **Name** — pulled from KPI Config via XLOOKUP
- **Owner** — accountable person, single name
- **Latest value** — pulled from the most recent period in Data
- **MoM Δ** — current vs prior period, % change with sign
- **YoY Δ** — current vs same period last year, % change
- **Sparkline** — last 13 periods. Line for trend KPIs, column for count-based.
- **Target** — from KPI Config
- **Status** — formula returns "GREEN" / "AMBER" / "RED" based on direction + thresholds. Conditional format the cell.

## Formula principles

- **XLOOKUP for the latest value**: `=XLOOKUP(MAX(Data!$1:$1), Data!$1:$1, INDEX(Data, MATCH(KPI_ID, Data[ID], 0), 0))` — gets the latest column dynamically.
- **MoM and YoY as offsets**, not hardcoded column references. Use INDEX/MATCH or OFFSET so adding a new period doesn't break formulas.
- **Status formula respects direction**: `=IF(Direction="higher", IF(Latest>=Target, "GREEN", IF(Latest>=Amber_Threshold, "AMBER", "RED")), [reverse for lower])`.
- **Sparklines reference dynamic ranges** — `OFFSET(Data!B5, 0, COLUMNS(Data)-13, 1, 13)` for last 13 periods.
- **Conditional formatting** on the Status cell using a text-based rule against the cell value (matches "GREEN" / "AMBER" / "RED").

## Common pitfalls

- **Hardcoded thresholds in formulas.** Move to KPI Config so the team can adjust without rewriting formulas.
- **Targets that don't exist for some KPIs.** That's fine — leave Target blank, Status returns "—". Don't force every KPI to have a target.
- **Mixing weekly and monthly KPIs in the same tracker.** Confuses the period column. Pick one cadence per workbook.
- **Status calculated against last period instead of target.** "Better than last week" is not "good." Compare to target.
- **No definition tab.** Two months in, no one remembers if "Active User" means MAU or DAU. Definitions tab is non-negotiable.
- **Sparkline range that doesn't auto-extend.** When Q1 2027 starts and the sparkline still shows Q1 2024–Q4 2026, the team loses trust. Use OFFSET-based dynamic ranges or named-range tables.
- **Color-grading by gut.** "Green if above 90% of target" — fine, but document the rule on Cover. People will challenge a yellow cell.

## Common KPI patterns

For SaaS: ARR, NRR, GRR, new logo MRR, expansion MRR, churn $, gross margin %, CAC payback, magic number, rule of 40, MAU, time-to-value
For e-comm: revenue, orders, AOV, conversion rate, repeat rate, returns rate, gross margin, CAC, contribution margin, inventory turns
For services: utilization %, billable rate, project margin, pipeline coverage, win rate, average deal size

Don't make up KPIs the user didn't ask for, but if they list things that aren't actually KPIs ("send 50 emails" is an activity, not a KPI), gently push back.

## Output

Generate the .xlsx. Print: KPIs included, color-status counts (e.g., "3 green / 4 amber / 2 red"), and the top 1-2 KPIs trending worst by MoM. End with a refresh instruction: "Add a new column to the Data tab each week. Tracker updates automatically. Don't touch column widths or sparkline cells."