A simple KPI tracking template, the KPIs worth tracking, and alerts

A KPI tracking template needs one row per KPI and about ten columns: the name, the exact definition, one owner, the data source, how often it updates, a target, what it is compared with, the latest value and trend, a status, and the alert threshold with the first action to take. Keep it to the five to ten numbers that would change a decision this month. Once the template is stable, the same rules can run on their own and send alerts, so no one has to remember to open a spreadsheet.

What goes in a KPI tracking template

Most templates I see are a grid of numbers by month with a traffic light at the end. That shows what happened, but not who should react or what counts as bad. The version below adds the columns that turn a list of numbers into tracking. It works in Excel, in Google Sheets or as a table in Power BI; the structure matters more than the tool.

1. KPI name. In plain words, the way people say it in meetings.
2. Definition. The exact calculation, including what is left out. For example: gross margin % equals net revenue minus cost of goods sold (including inbound freight), divided by net revenue. Writing this down settles half the arguments before they start.
3. Owner. One person, by name. A team is not an owner.
4. Source. The system and report the number comes from, so anyone can recompute it.
5. Frequency. Daily, weekly or monthly, matched to how quickly you could act on a change.
6. Target. The level you are aiming for.
7. Comparison. Against target, last period, the same period last year, or a rolling average. Pick one per KPI.
8. Current value and trend. The latest figure plus the last few periods, so you can tell a single bad week from a slide.
9. Status. Green, amber or red, calculated from the thresholds and never set by hand.
10. Alert threshold and first action. The level at which the owner hears about it, and the first thing they check.

The last column is the one most templates skip, and it decides whether anyone reacts. If the owner can't write down what they would do when a KPI turns red, the number probably belongs on a dashboard for context and not in the tracker.

A worked example for a small wholesaler

To show the template filled in, take a hypothetical business: a 40-person wholesaler of kitchen equipment that sells to restaurants and retailers, with its numbers in an accounting package and a webshop. Its first tracker might hold these six rows. The thresholds are illustrative, not benchmarks.

• Cash on hand. Owned by the finance lead and checked weekly against a floor of six weeks of fixed costs. Alert when cash drops below the floor, or is forecast to within the next four weeks. If you have no forecast yet, a 13-week cash flow template is the simplest way to get one.
• Receivables over 60 days. The credit controller's row, weekly. Alert when they pass 15% of total receivables, or when a single customer passes an agreed amount.
• Gross margin %. Owned by the commercial director. Monthly, with a weekly early read against target. Alert when month to date is two points below target.
• Orders per day. The sales manager's row, checked daily and compared with the same weekday over the last eight weeks. Alert when a day falls far outside its usual range.
• On-time shipments. Owned by the operations manager, weekly, with a target of 95% and an alert below 90%.
• Top 20 customers with no order in 30 days. Split across the account managers by customer, weekly, with an alert on every new name on the list.

Your own thresholds should come from your history and your margins. For the margin row in particular, it helps to know your break-even point first. The break-even calculator shows how many units and how much revenue you need before fixed costs are covered, which tells you how much room a two-point margin drop leaves before the month turns into a loss.

Which KPIs are worth tracking?

A KPI earns a row in the tracker if it passes three tests. A change in it would make someone do something different this week or month. One named person is responsible for responding. And it can be computed from a system without anyone typing it in.

So does revenue qualify? It usually passes the first two tests but is a lagging number: by the time monthly revenue is down, the cause happened weeks earlier. So pair each lagging KPI with one or two leading ones that move sooner. For revenue that might be pipeline value, quotes sent or orders per day. For cash it is overdue receivables and large payments coming up. For churn in a subscription business it could be product usage, or support tickets from accounts close to renewal.

Some starting points by function, to adapt to your business:

• Finance teams might track cash against a floor, overdue receivables, gross margin %, operating costs against budget.
• Sales teams: pipeline coverage against target, win rate, average deal size, days since the last order for large accounts.
• Operations: on-time delivery, stock-outs, order backlog, returns.
• SaaS: monthly recurring revenue movements, net revenue retention, accounts at risk. The SaaS revenue and retention example shows how an accounts-at-risk list can sit behind an alert.

Five to ten KPIs is a sensible ceiling for one tracker. Past that, people stop reading the status column.

KPI tracking in Excel, a dashboard or software?

Excel or Google Sheets is fine for a first version, and I'd recommend it there, because it forces you to write definitions and owners before anyone builds anything. It gets painful when the numbers come from several systems and someone has to paste them in every week; the tracker is then only as current as the last person who updated it. I compared where spreadsheets stop working in more detail in Excel vs Power BI for financial reporting.

A Power BI report solves the refresh problem. Data is pulled on a schedule, and the definitions live in measures instead of cell formulas that someone can overwrite. Its weak point is that dashboards wait to be opened, and plenty of companies have a good one that showed a problem for three weeks before anyone looked.

KPI tracking software, the subscription tools with ready-made connectors, suits teams whose numbers are standard and whose systems the tool supports. It fits less well when your margin definition is particular to your business, when per-user pricing adds up, or when the data sits in a database the tool can't reach.

Whichever you choose, the template stays the same. It is the specification, and the tool only runs it.

From KPI tracking to KPI alerts

Alerts are the step from a tracker people have to check to one that checks itself. Power BI has a basic version built in. According to Microsoft's guide to data alerts, you can set an alert on a dashboard tile in the Power BI service and get a notification, plus an optional email, when the value goes above or below a limit. The limits are worth knowing before you rely on it: alerts only work on card, KPI and gauge tiles, only the person who set an alert can see it, and alerts are only checked when the data refreshes. A model that refreshes once a night gives you, at best, a once-a-night alert.

That suits one person watching one figure. For a tracker like the one above you usually need more, such as a comparison with last year instead of a fixed number, a message to the KPI owner in Teams or Slack, two conditions combined, or a short note on what moved. That is where Power Automate, Fabric Activator or a small scheduled job that reads the same data comes in.

A useful alert message contains the KPI, the current value, the threshold it crossed, the recent trend and a link to the view where the owner can see the detail. If AI is involved, its job is a one-line summary of what changed (which customers or product groups drove a margin drop, for instance), drafted from the numbers in the message and labelled as AI-written.

Data anomaly detection without a data scientist

Fixed thresholds miss problems that look normal in absolute terms. Two hundred orders is a fine Tuesday and a bad Monday if Mondays usually bring four hundred. Simple anomaly rules catch most of this, and anyone can understand why one fired:

• Compare with the same weekday over the last six to eight weeks, and flag values far outside that range.
• Compare month to date with the same point last month and last year, so you never set half a month against a full one.
• Flag a cost line that jumps well above its average for the last six months.
• Flag a zero where zero is unlikely. It usually means a broken feed, not a bad day.

Before switching any rule on, replay it against the past year of data and count how often it would have fired. A rule that would have fired forty times is too loose, and one that never would have may be too tight, or may be watching something that never goes wrong. Statistical models have their place once the simple rules keep missing real problems, but I rarely begin with them.

Watch the data itself as well. A separate set of checks (did the refresh run, is today's row count normal, do totals still match the ledger) should go to whoever maintains the data. An alert built on a stale number is worse than no alert, because silence then looks like good news.

Why KPI tracking fails

The causes are dull, and they repeat from company to company:

• Too many alerts, sent to everyone. Within a few weeks the channel gets muted and the real warning drowns with the rest. Cooldowns, grouped messages and a daily digest for low-priority signals help.
• No owner. An alert addressed to a team gets read by everyone and handled by no one.
• Definitions that differ between the tracker and the monthly report, so the first meeting about an alert turns into an argument about which number is right.
• Targets set once and never revisited, so a KPI stays red for months and people learn to ignore red.
• Manual inputs. Any KPI that depends on someone typing a number will eventually be late.

If the third point sounds familiar, settle the definitions before automating anything. Automating two versions of gross margin only spreads the disagreement faster.

Getting your KPI tracking template running

An order that works: fill in the template for five KPIs in a spreadsheet, argue about the definitions until they're agreed, then run it by hand for a month. You'll find out which thresholds are wrong and which KPIs get ignored. After that month you know what to automate, and the automation is a small job because the specification already exists.

If you'd like help with that last step, I set up KPI tracking and alerts on your own data, delivered in Teams, Slack or email and including the data health checks. Alerts are often added to an automated monthly report as well, so the figures in the alert and the figures in the pack come from the same place.

Frequently asked questions