FlowProofsA KPI tree template saves you from the blank page, which is where most KPI trees die. This post gives you one as a CSV you can open in Excel or Google Sheets, then shows how to build your own tree in five steps, with three filled-in examples.
Download the KPI tree template (CSV). It has the three example trees from this post plus empty rows for your own.
If you want the background on why trees beat dashboards for explaining numbers, read what a metric tree is first. This post is the practical version.
What goes in a KPI tree
Every node in a KPI tree has the same handful of attributes. The template has one column for each:
| Column | What it holds |
|---|---|
node_id | A short unique name, like ecom-orders |
parent_id | The node this one feeds into. Blank for the root |
name | What people call it in meetings |
operator | How it combines with its siblings: multiply, add or subtract |
formula | How the node is calculated from its children, in words |
value | This period's number |
unit | USD, count, rate or hours |
owner | The person who can change it |
source | Where the number comes from |
Owner and source are the two columns people leave out, and they are the two that make a tree useful in practice. More on that below.
Step 1: pick one output metric
Start with the number your meetings argue about. Not the five numbers on your current dashboard. One.
For most companies it is revenue or gross profit. For a subscription business, monthly recurring revenue. For an operations team, it might be on-time delivery or cost per order. If you cannot choose, pick the one that appears first on the slide the CEO looks at.
Step 2: write the identity
Write the output as an equation of two to four inputs that produce it exactly. Exactly is the important word.
Revenue = Orders * Average order valueThat is an identity: orders times AOV is revenue, by definition. Compare "Revenue = Marketing spend × Brand strength". That is a belief about how the business works. It may be true, but it can't be checked with arithmetic, so it does not belong in the tree.
A good test: could you compute the output from the inputs in a spreadsheet, and get the same number as your finance system? If yes, it is an identity.
Step 3: split until someone owns it
Take each input and ask the same question: what is this made of? Orders split into sessions times conversion rate. AOV splits into items per order times price per item.
Stop splitting when you reach a number that one named person can act on. Conversion rate is owned by whoever owns the checkout. Items per order belongs to merchandising. There is no point splitting further than the level where someone could be asked "what are you doing about this?"
In practice, that is two or three levels for most businesses. A tree deeper than four levels is usually a map of the org chart, and nobody reads it.
Step 4: fill in the numbers and reconcile
Put this period's value against every node, then check that each parent equals its children combined with the operator. In a spreadsheet, a check column per parent does it: =Sessions*Conversion should equal Orders.
If a parent doesn't reconcile, the usual causes are:
- Two sources counting the same thing differently (orders from the store, revenue from the payment processor, refunds in only one of them)
- Mismatched periods, like a weekly rate multiplied by monthly volume
- Rounding, if you typed rates as 3.2% instead of linking to the underlying counts
Fix every mismatch before you show the tree to anyone. A tree that almost adds up is worse than no tree, because the first person to check it will stop trusting the rest.
Step 5: add owners and sources
Write a name next to every leaf, and the system each number comes from. This does two things. It turns the tree into an accountability chart, and it makes the inevitable "where does this number come from?" question a ten-second answer.
Example 1: a SaaS company
Output: monthly recurring revenue.
- MRR = Paying customers × ARPA: 420 × $200 = $84,000
- Paying customers = Starting + New − Churned: 400 + 32 − 12 = 420
- New customers = Trials started × Trial-to-paid rate: 400 × 8% = 32
The interesting branch is usually customers, since ARPA moves slowly unless you change pricing. When MRR growth stalls, this tree immediately tells you whether trials dried up, conversion from trial fell, or churn rose. Those are three different conversations with three different people.
If you report to a board, the 8 metrics a seed-stage board actually looks at builds on this same tree.
Example 2: an online store
Output: revenue.
- Revenue = Orders × AOV: 3,120 × $54 = $168,480
- Orders = Sessions × Conversion rate: 97,500 × 3.2% = 3,120
- AOV = Items per order × Price per item: 2.4 × $22.50 = $54
This tree separates marketing (sessions), product and checkout (conversion) and merchandising (items and price). When revenue drops, the first step is to find which of the four leaves moved. Often it is conversion, and the conversion funnel analysis post shows how to dig into that one branch.
Example 3: a services firm
Output: revenue.
- Revenue = Billable hours × Average hourly rate: 1,920 × $95 = $182,400
- Billable hours = Consultants × Available hours each × Utilisation: 15 × 160 × 80% = 1,920
Services firms often watch revenue and headcount and miss utilisation, which is where most of the month-to-month movement lives. A drop from 80% to 72% utilisation costs this firm 192 billable hours, or $18,240 a month, without anyone leaving.
Using the template
Open the CSV in Excel or Google Sheets. Each row is one node, and the tree column groups the three examples so you can filter to one.
To start your own, filter to your-tree, rename the root, and add rows for each input with parent_id set to the node it feeds. Then add a check formula next to each parent. In Google Sheets, =PRODUCT(FILTER(G:G, C:C="root")) multiplies the values of every node whose parent is root, which is a quick way to test a multiplicative branch.
Two notes on the file. Rates are stored as decimals (0.032, not 3.2%), so formulas work without conversion. And operators are written as words rather than symbols, because a cell starting with a plus or minus sign gets read as a formula when the file opens.
Reviewing the tree each month
A tree pays off when it becomes the agenda. The routine I recommend takes about fifteen minutes of preparation:
- Update every leaf with the new period's value, and keep last period's value beside it.
- Mark the leaves that moved by more than a threshold you agree on, say 5%. Only those get discussed.
- Run the one-at-a-time attribution on the root, so you know how much of the change each moved leaf explains. The method is in what a metric tree is.
- Ask the owner of each moved leaf for one sentence before the meeting: what happened, and what they are doing about it.
In the meeting, start at the root, go straight to the leaves that moved, and read the owners' sentences. A review run this way rarely needs more than twenty minutes, because nobody is arguing about what the numbers are. The time goes on what to do about them.
Mistakes to avoid
Mixing identities and hunches. Keep the tree to arithmetic. If you believe brand campaigns drive conversion, write that next to the tree as a hypothesis, not inside it.
One tree per stakeholder. If sales and marketing each have their own tree with a different definition of "lead", you have two trees and an argument. Agree on one.
Building it once. A tree that is filled in for one quarterly review and then abandoned is a slide. The value comes from updating it every period, so people learn to read it.
That last point is the hard one, and it is honestly why I built FlowProofs. A KPI tree on a canvas reads straight from your data, so updating it is a refresh instead of an afternoon, and anyone can click a node to see its source. If you run an ops team, the operations managers page shows a version built around orders and on-time rate. But the CSV works without any of that.
Download the template, delete two of the three examples, and fill in the third with last month's numbers. If every parent reconciles, take it to your next review instead of the dashboard and see which question gets asked first.