Build a Marketing KPI Tree From Scratch with ChatGPT — The Framework I Use With Every New Client
Contents
A B2B SaaS client I onboarded last year had 47 KPIs in their Looker dashboard. The first tab alone had 11 tabs of charts. Load time was 9 seconds. The CEO told me, proudly, that they "measured everything." I asked him to walk me through the dashboard live. He opened 3 charts, got a Slack message, and closed the tab. Three weeks later, the same dashboard still had 47 KPIs. Nobody had touched 40 of them.
The problem was not the data. The problem was the shape of the data. A flat list of 47 numbers is not measurement — it's noise. Measurement is a tree.
A KPI tree is a small directed graph where every metric has a parent and a child. The root is one number you actually care about. The branches decompose it into the few things that move it. The leaves are the operational levers a marketer touches every day. The job of the tree is to make it impossible to stare at a dashboard full of numbers and still have no idea what to do this week.
I build one of these for every new client, in the first two weeks, before I touch a single campaign. The work used to take me two days of whiteboarding. With the right ChatGPT prompt, I can produce a first-draft tree in 90 minutes, then spend a day arguing with the client about whether the branches are right. Here is the exact framework and the exact prompt.
Why your dashboard is a list, not a tree
The reason most marketing teams end up with 47 KPIs is that they add metrics, they don't subtract. A new campaign launches, three new columns appear in the report. An executive asks for "engagement rate," it shows up. A vendor pitches a new attribution tool, six more metrics appear. Two years later, the dashboard is a graveyard of one-time asks.
The fix is not better dashboards. The fix is fewer metrics, arranged in a hierarchy. A tree forces you to make three decisions a list never does:
- What is the one number we would print on a t-shirt? That's the root. If you can't agree on it, the tree is wrong.
- What are the 3–5 things that actually move the root? Those are the driver metrics, level two.
- What do we change on a daily basis to move the drivers? Those are the input metrics, level three. Everything else dies.
A list never asks these questions. A list is just columns. A tree asks you to be honest about what matters.
The 4-level tree I use
I've tried five-level trees. They become impossible to maintain. Two-level trees are too coarse for paid acquisition. Four levels is the sweet spot for a team of 2–10 marketers.
Level 1 — North Star. One number, no exceptions. For e-commerce it's usually net contribution margin per month. For SaaS it is net new MRR (monthly recurring revenue) or net new ARR (annual recurring revenue) for the period. For a marketplace it is take-rate × GMV (gross merchandise volume). The North Star must be a flow number (rate, per period), not a stock number (cumulative), or you'll lie to yourself with seasonality. I have seen a 12-person team debate for 90 minutes whether "total signups" is a North Star. It is not. Total signups is a vanity. Decide on flow.
Level 2 — Driver metrics. The 3–5 numbers that mathematically determine the North Star. For a DTC (direct-to-consumer) e-commerce tree, the three drivers are roughly: paid CAC (Customer Acquisition Cost, 获客成本), organic traffic volume, and AOV (Average Order Value, 客单价). For B2B SaaS the drivers are usually: MQL (Marketing Qualified Lead, 市场认可线索) volume, MQL-to-SQL (Sales Qualified Lead, 销售认可线索) conversion, and ACV (Annual Contract Value, 年度合同价值) of closed deals. Pick the decomposition that actually multiplies back to the North Star. If your drivers don't multiply to your root, you have the wrong tree.
Level 3 — Input metrics. The daily levers. CTR (Click-Through Rate, 点击率), CPM (Cost Per Mille, 千次展示成本), CPC (Cost Per Click, 单次点击成本), email open rate, organic CTR, branded search volume, etc. Each input metric should be linked to one specific driver. CTR on paid social lives under "paid CAC" because it drives CPC, which is part of CAC. If an input metric doesn't link to a driver, it doesn't belong in the tree — it's decoration.
Level 4 — Counter-metrics. This is the level most teams skip and then regret. Counter-metrics are the guardrails: the numbers that, if they go up, mean you broke something while improving the metric above. Refund rate under paid CAC. Time-to-value under MQL-to-SQL. NPS (Net Promoter Score, 净推荐值) under net new MRR. The point of a counter-metric is not to be optimized. The point is to flag the day someone wins on the driver but loses on the business. I have a client who grew paid signups 40% in a quarter, only to find out that 60% of those signups never logged in twice. We would have caught that in week 2 if we'd had a counter-metric for "logins within 7 days of signup" at level 4.
The ChatGPT prompt
Here is the prompt I open a new doc with every time. Paste in the business context, fill in the brackets, send. It is not magic, but it is good enough that 80% of the time the first draft is usable with light editing.
You are a growth strategist. I will give you a business context. Build a 4-level KPI tree for it.
Levels:
- North Star (one number, a flow not a stock)
- 3–5 driver metrics that mathematically determine the North Star
- For each driver, 3–6 input metrics — the daily levers a marketer can move
- For each driver, 1–2 counter-metrics — the guardrails that should NOT move the wrong direction
Format: a markdown table per driver. Columns: Metric | Direction of good | Current rough estimate | Owner | Data source.
Rules:
- Every level 2, 3, and 4 metric must link explicitly to its parent
- Every counter-metric must be measurable today (or you don't ship the tree)
- Reject any metric that is a vanity number with no decision attached
- If you don't have enough context, ask 3 questions before answering
Business context:
- Industry: [e.g. DTC skincare]
- Revenue model: [e.g. subscription, $42 AOV]
- Current monthly ad spend: [e.g. $180k]
- Channels in use: [Meta, Google, email, organic]
- The single number the CEO cares about most: [e.g. contribution margin per month]
- The biggest complaint right now: [e.g. we don't know which channel is actually profitable]
Two details that save you from the most common failure modes.
First, the "every metric links to a parent" rule is what stops the model from producing a list. ChatGPT will, by default, give you a paragraph of good-sounding metrics. Forcing it into a tree with explicit parent-child links reveals which branches are made up. If a counter-metric has no clear parent, drop it.
Second, the "ask 3 questions before answering" line is the part most people skip. With enough context, ChatGPT hallucinates convincingly. With too little, it hallucinates usefully — meaning it gives you a tree that sounds great but doesn't match the business. The questions force the model into the gaps in your data so you find out what's missing before you bake the wrong number into a quarterly plan.
A worked example: DTC skincare, $180k/month spend
I ran the prompt for a fictional but representative DTC skincare brand last week. The model came back with a tree in 12 seconds. I'll show you the relevant slice — the paid CAC branch, which is the part I usually polish hardest.
Level 1: Contribution margin per month (revenue minus COGS minus ad spend)
Level 2 — Paid CAC driver:
- Paid CAC, blended
- Paid CAC by channel (Meta, Google, TikTok)
- New customer share of paid revenue
Level 3 — Inputs to paid CAC:
- CPM by channel
- CTR by channel
- CVR (Conversion Rate, 转化率) on landing page
- LTV (Lifetime Value, 客户终身价值) -to-CAC ratio at 60 days (early read)
- Creative volume shipped per week
Level 4 — Counter-metrics:
- 30-day refund rate on new customers
- Subscription pause rate in month 2
Two things I'd clean up before this is shippable. First, the model put "creative volume shipped per week" in level 3, which is fine for a creative team, but I would rename it to "winning creatives per week" so the metric is a count of creatives that drove signups, not just count of creatives shipped. The former is a lever. The latter is a vanity. Second, I would force a level 4 counter-metric on the organic driver, not just paid — branded search volume should have a counter-metric on "unbranded search growth" to make sure the brand is building, not just harvesting.
Both fixes are 5 minutes of manual work. The prompt got me 80% of the way.
Where the tree lies
A KPI tree is a model, and models lie in three predictable ways.
First, it can lie about attribution. A tree with a "paid CAC" branch and an "organic traffic" branch assumes the boundary between them is clean. It is not. Branded search converts organic interest that paid social created. The tree has to include a "halo" branch, or you'll argue for six months about which channel is profitable.
Second, it can lie about lag. MQL volume is a leading indicator of MRR. LTV is a lagging indicator of CAC. The tree has to declare which metrics are leading and which are lagging, or you'll celebrate a CAC drop in week 1 that won't show up in LTV until month 4.
Third, it can lie about selection. The 3 drivers you pick in level 2 are the only drivers. If you forget one, the tree quietly pretends the others sum to 100%. The fix is a sanity check: do the 3 drivers, with the input metrics, actually reconstruct the North Star? If the math doesn't close to within 10%, one of your drivers is missing. ChatGPT is bad at this check by itself — it will hand you a tree that doesn't multiply. You have to do the multiplication in a spreadsheet.
The single most useful thing this gives you
The point of a KPI tree is not the dashboard. The dashboard is the easy part. The point is the Monday meeting. When a metric moves — when paid CAC jumps 18% week-over-week — the tree tells you which input to investigate first. When LTV drops, the tree tells you which driver was supposed to be holding it up. When an exec asks "how's marketing," the tree gives you a 30-second answer that doesn't require 14 charts.
If your team is staring at 47 numbers every Monday and still can't answer "what changed and why," the problem is not the data. The problem is the shape. Build the tree.