Why a monthly social media report needs a KPI tree
Every social platform will happily export forty columns of numbers for a single month: impressions, reach, views, likes, comments, saves, shares, profile visits, link clicks, follower changes and many more. A client who receives all forty learns nothing. A client who receives three well-chosen numbers and one sentence about why they moved learns what to do next. The difference between the two reports is not design. It is structure, and the simplest structure a reporting analyst can use is a KPI tree.
A KPI tree starts with the one result the client actually pays for and splits it into the drivers that produce it. Each level of the tree can be recalculated from the level below, so nothing in the report is decoration: every number either is the goal or explains the goal. When the goal number moves next month, you walk down the tree and find the branch that moved. That is what turns a table of metrics into commentary.
This free lesson builds such a tree in a spreadsheet for Marlow & Finch Cycles, a bike retailer and a client of the social media agency Fernhollow Social. The agency, its clients, the posts and every number are invented for teaching. The data is the same as in the lab above: twelve posts published in September 2026 on three platforms.
The concept: one goal, three drivers
Marlow & Finch sells bikes and accessories in an online shop. The reason the company invests in social media is traffic to that shop, so the top of the tree is link clicks: the number of times somebody tapped through from a post to the website. Other clients will have a different top node, for example enquiries for a dental practice or video views for a publisher, but the method is identical.
Link clicks can be written as a product of three drivers:
- Posts: how many pieces of content were published. This is the output of the team and the only driver the agency controls completely.
- Impressions per post: how often the average post was shown. This describes distribution: the algorithm, the posting time, the format and any paid support.
- Click-through rate (CTR): link clicks divided by impressions. This describes the pull of the content: does a person who sees the post care enough to visit the site?
Multiply them and the units cancel neatly: posts × (impressions ÷ posts) × (clicks ÷ impressions) = clicks. That is why the tree is exact and not just a nice picture. Beside the tree sits the engagement rate, engagements divided by impressions. It is not a factor of link clicks, but it is a useful quality signal, because content that earns likes, comments, saves and shares tends to be distributed further in the following weeks.
The data: twelve posts in September 2026
Here is the post list exactly as it appears in the lab sheet, columns A to F, rows 4 to 15. Each row is one post with its platform, format and three exported numbers.
| Post | Platform | Format | Impressions | Engagements | Link clicks |
|---|---|---|---|---|---|
| P01 | Reel | 18,400 | 1,012 | 221 | |
| P02 | Carousel | 9,600 | 624 | 134 | |
| P03 | Photo | 6,200 | 298 | 62 | |
| P04 | Reel | 14,900 | 864 | 164 | |
| P05 | Carousel | 8,300 | 506 | 108 | |
| P06 | Article | 4,200 | 147 | 126 | |
| P07 | Photo | 3,100 | 93 | 71 | |
| P08 | Video | 5,400 | 205 | 151 | |
| P09 | Carousel | 3,900 | 148 | 109 | |
| P10 | TikTok | Video | 26,500 | 1,643 | 133 |
| P11 | TikTok | Video | 31,200 | 2,059 | 187 |
| P12 | TikTok | Video | 15,800 | 916 | 79 |
| Total | 147,500 | 8,515 | 1,545 |
The list on its own already invites wrong conclusions. Post P11 has the most impressions (31,200), so it looks like the star of the month. Post P06 has only 4,200 impressions, less than a seventh of P11, yet it produced 126 link clicks against 187 for P11. Whether P06 or P11 is the better post depends entirely on which level of the tree you look at. The summary per platform makes that visible.
Step 1: count the posts per platform with COUNTIFS
The summary table starts in column H with one row per platform: Instagram in H4, LinkedIn in H5, TikTok in H6. The first driver is the number of posts. COUNTIFS counts the rows of a range that meet a criterion: =COUNTIFS($B$4:$B$15,H4) counts the cells in B4:B15 that contain the text in H4. The dollar signs lock the data range so that copying the formula down to I5 and I6 changes only the criterion. The result is 5 posts on Instagram, 4 on LinkedIn and 3 on TikTok.
Step 2: impressions per post with AVERAGEIFS
The second driver is the average number of impressions a post received. =AVERAGEIFS($D$4:$D$15,$B$4:$B$15,H4) averages column D for the rows whose platform equals H4. Note the argument order: the range to average comes first, then pairs of criteria range and criterion. You get the same result with =SUMIFS($D$4:$D$15,$B$4:$B$15,H4)/I4, total impressions divided by the post count from step 1.
Instagram: 57,400 impressions over 5 posts is 11,480 per post. LinkedIn: 16,600 over 4 posts is 4,150. TikTok: 73,500 over 3 posts is 24,500 per post, more than twice the Instagram figure and almost six times LinkedIn.
Step 3: click-through rate as sum divided by sum
The third driver is the click-through rate. For a platform it is all link clicks of that platform divided by all its impressions: =SUMIFS($F$4:$F$15,$B$4:$B$15,H4)/SUMIFS($D$4:$D$15,$B$4:$B$15,H4). Format the cell as a percentage with two decimals. Instagram: 689 ÷ 57,400 = 1.20%. LinkedIn: 457 ÷ 16,600 = 2.75%. TikTok: 399 ÷ 73,500 = 0.54%.
The rule sum divided by sum matters. It is tempting to calculate a CTR for each post and then average those rates. For Instagram the average of the five post rates also rounds to 1.20%, the same as the correct 1.20%, but only because the Instagram posts happen to have similar rates. Across all twelve posts the difference is large: the average of the twelve post rates is 1.54%, while the true account CTR is 1,545 ÷ 147,500 = 1.05%. The average of rates treats the small LinkedIn posts as if they were as big as the TikTok videos. A client who multiplies that rate by next month's planned impressions would overestimate the clicks by almost half.
Step 4: engagement rate per platform
The engagement rate uses the same pattern with column E in the numerator: =SUMIFS($E$4:$E$15,$B$4:$B$15,H4)/SUMIFS($D$4:$D$15,$B$4:$B$15,H4). Instagram earns 3,304 engagements on 57,400 impressions, 5.76%. LinkedIn reaches 3.57% and TikTok 6.28%. This lesson divides by impressions. Later in the course you will see that dividing by reach or by followers gives different rankings, which is why the denominator must always be written next to the number.
Step 5: prove the tree
The last column multiplies the three drivers: =I4*J4*K4. If the tree is built correctly, the product equals the link clicks you could also get directly with a SUMIFS on column F. For Instagram: 5 × 11,480 × 1.2003% = 689. For LinkedIn: 4 × 4,150 × 2.7530% = 457. For TikTok: 3 × 24,500 × 0.5429% = 399. The three results add up to the 1,545 link clicks of the month. This check looks redundant, but it is the habit that protects a report: when a decomposition does not multiply back to the total, one of the definitions is inconsistent.
The finished summary, columns H to M of the lab sheet:
| Platform | Posts | Impressions per post | CTR | Engagement rate | Link clicks from the tree |
|---|---|---|---|---|---|
| 5 | 11,480 | 1.20% | 5.76% | 689 | |
| 4 | 4,150 | 2.75% | 3.57% | 457 | |
| TikTok | 3 | 24,500 | 0.54% | 6.28% | 399 |
| All platforms | 12 | 12,292 | 1.05% | 5.77% | 1,545 |
How to read the result
The summary tells a story that the post list hid. TikTok supplies 73,500 of the 147,500 impressions, almost exactly half, with only three videos. Yet it contributes 399 of the 1,545 link clicks, about a quarter. Its click-through rate of 0.54% is the lowest of the three. LinkedIn is the mirror image: 16,600 impressions, about 11% of the total, but 457 link clicks, about 30%, thanks to a CTR of 2.75%. Instagram sits between the two and delivers the largest number of clicks, 689.
None of this means that one platform is good and another bad. It means they do different jobs in the tree. TikTok is a distribution engine with a strong engagement rate (6.28%): people watch and react, but few leave the app. LinkedIn is a small, intent-rich audience. A sensible comment for the client reads: TikTok built awareness with 73,500 impressions from three videos, while LinkedIn converted attention into visits at more than twice the Instagram click-through rate. That one sentence uses three nodes of the tree and no jargon.
Using the tree for what-if questions
Because the tree is a multiplication, it answers planning questions directly. Suppose the client asks how to get more shop visits from TikTok. One more video at the current averages would add one post: 4 × 24,500 × 0.5429% = 532 clicks instead of 399, a gain of 133. Alternatively, a clearer call to action that lifts the TikTok CTR by 0.2 percentage points, from 0.54% to 0.74%, would give 3 × 24,500 × 0.7429% = 546 clicks, a gain of 147 with no extra production. The tree does not tell you which change is achievable, but it shows what each is worth, and that turns a vague request into a decision.
Step by step in the spreadsheet
- Check the export first. The platform names in column B must be spelled identically in every row and in H4:H6. A trailing space or a different capital letter makes COUNTIFS miss the row. If a count looks too low, compare the text with
=B4=H4. - Click I4 and type
=COUNTIFS($B$4:$B$15,H4). Press F4 (Mac: Cmd + T) while the cursor is in the range to add the dollar signs. Copy the formula down to I6. - Click J4 and type
=AVERAGEIFS($D$4:$D$15,$B$4:$B$15,H4). Copy down to J6. - Click K4 and type
=SUMIFS($F$4:$F$15,$B$4:$B$15,H4)/SUMIFS($D$4:$D$15,$B$4:$B$15,H4). Copy down to K6 and format as a percentage. - Click L4 and type the same formula with column E in the numerator. Copy down to L6.
- Click M4 and type
=I4*J4*K4. Copy down to M6 and compare the three results with the link clicks of each platform. - Add a total row under the summary. Use
=COUNTA(A4:A15)for the posts,=AVERAGE(D4:D15)for impressions per post and=SUM(F4:F15)/SUM(D4:D15)for the CTR. Do not add or average the three platform rates.
Common mistakes and how to spot them
- Averaging rates. The simple average of the three platform CTRs is 1.50%, but the account CTR is 1.05%. If your total rate is higher than you can explain, check whether it was averaged.
- Unlocked ranges.
=COUNTIFS(B4:B15,H4)copied down becomesCOUNTIFS(B5:B16,H5)and silently drops the first post. Lock the data range with dollar signs. - Wrong argument order. SUMIFS and AVERAGEIFS take the range to add or average first. The older SUMIF takes it last. Mixing the two patterns returns 0 or an error.
- Mixed denominators. A CTR on impressions and an engagement rate on reach cannot be compared in one table without a label. Decide on one denominator per report and state it.
- A tree that does not multiply back. If posts × impressions per post × CTR differs from the link clicks, one of the three was calculated on a different set of rows, for example with a filter still active.
- Reading a rate without its volume. A CTR of 2.75% on 16,600 impressions is 457 clicks. A rate always needs its base next to it.
Quick reference: functions in this lesson
| Function | What it does here | Example |
|---|---|---|
COUNTIFS | Posts of one platform | =COUNTIFS($B$4:$B$15,H4) |
AVERAGEIFS | Average impressions per post of one platform | =AVERAGEIFS($D$4:$D$15,$B$4:$B$15,H4) |
SUMIFS | Total clicks, engagements or impressions of one platform | =SUMIFS($F$4:$F$15,$B$4:$B$15,H4) |
| Sum ÷ sum | A rate for a group of posts | =SUM(F4:F15)/SUM(D4:D15) |
$ references | Keep the data range fixed when copying | $B$4:$B$15 |
Where this is used in practice
- The first page of a monthly report. The goal, its three drivers and one sentence per driver fit on a single slide and answer most client questions before they are asked.
- Briefing the content team. If the tree shows that distribution is healthy but pull is weak, the brief is about calls to action and link placement, not about posting more.
- Choosing what to leave out. A metric that is neither the goal, a driver nor a quality signal for a driver does not need space in the report. It can stay in the appendix.
- Comparing clients fairly. An agency can compare the pull of two accounts by CTR even when one has ten times the audience, because the tree separates size from quality.
What you will build in the rest of this course
Lesson 1 gives the report its skeleton. The following nineteen labs fill it with the comparisons and checks a client expects, each on a new fictional data set from the Fernhollow Social client list.
Comparisons over time. You calculate month-on-month changes with a band for what counts as flat, compare seasonal months year on year, and normalise months of different length to per-day and per-post averages so that February is not reported as a decline.
Benchmarks. You look up illustrative platform benchmarks, index each account against them and build a second kind of benchmark from the account's own history with a rolling average, a mean and a standard deviation.
Cross-platform roll-ups. You combine four platforms into one blended engagement rate, harmonise metric names that differ between exports with a small dictionary, and see how the choice of denominator changes a ranking.
Goal pacing. You compare month-to-date results with the expected value on the report date, project the month end, and work out the weekly run rate a quarterly goal still needs.
Insight and quality. You find top posts and content pillars, separate organic from paid results, test whether a change is larger than random noise, write rule-based commentary sentences, reconcile report figures with platform exports and build a what-if grid.
Final project. You assemble a one-page monthly client report for Marlow & Finch Cycles with month-on-month and year-on-year changes, a blended engagement rate, goal attainment, a status column and a chart.
Summary
A KPI tree puts the client's goal at the top and explains it with drivers that multiply back to it. For Marlow & Finch Cycles in September 2026 the goal is 1,545 link clicks = 12 posts × 12,292 impressions per post × 1.05% click-through rate. COUNTIFS counts the posts, AVERAGEIFS gives impressions per post, and two SUMIFS divided by each other give rates per platform. Rates for a group are always sum divided by sum. The summary shows that TikTok supplies half the impressions, LinkedIn has the strongest pull and Instagram delivers the most clicks.
Now scroll up to the lab and build the summary yourself. It takes about a quarter of an hour, the Coach checks every step, and the result is a structure you can apply to your own platform export the same day.
Frequently asked questions
What is a KPI tree in social media reporting?
A KPI tree puts the goal of the account at the top, for example link clicks, and splits it into drivers that multiply or add up to it, such as posts, impressions per post and click-through rate. It shows which driver caused a change in the goal.
How do I calculate click-through rate per platform in Excel?
Divide the platform's total link clicks by its total impressions: =SUMIFS(clicks, platform_range, platform)/SUMIFS(impressions, platform_range, platform). Format the result as a percentage.
Why should I not average the rates of single posts?
An average of rates gives a small post the same weight as a large one. The rate of a group must be calculated from totals, sum of clicks divided by sum of impressions, otherwise it can be far from the real rate of the account.
What is the difference between COUNTIFS, SUMIFS and AVERAGEIFS?
COUNTIFS counts the rows that match the criteria, SUMIFS adds a column for those rows and AVERAGEIFS averages it. SUMIFS and AVERAGEIFS take the column to add or average as the first argument.
Should engagement rate be based on impressions, reach or followers?
All three are used. Impressions measure every display, reach counts people, followers measure the size of the community. They give different values, so choose one for the report, state it next to the number and keep it the same every month.
Which metric should be at the top of the tree?
The result the client cares about and that social media can influence: link clicks for a shop, enquiries for a service business, video views for a publisher. Followers or likes are rarely the goal itself, they are usually drivers or quality signals.
Keep going: Social Media Management & Client Reporting: KPI Trees, Month-on-Month & Year-on-Year, Benchmarks, Cross-Platform Roll-Ups, Goal Pacing & Insight Commentary
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Month-on-Month Change: Absolute Change, Percentage Change, a Flat Band and Direction Labels
- Year-on-Year and Seasonality: Comparing with the Same Month Last Year, the 12-Month Total and a Two-Year Line Chart
- Fair Months: Days in the Month with EOMONTH, Impressions per Day, Per-Day Change and Impressions per Post
- Benchmarks: Looking Up a Platform Benchmark with XLOOKUP, the Benchmark Index, Verdict Bands and a Highlight Rule
- The Account's Own Baseline: Rolling 3-Month Rate, 12-Month Mean, Standard Deviation and the z-Score of the Report Month
- Cross-Platform Roll-Up: Total Impressions, a Blended Engagement Rate, Platform Share and a Stacked Column Chart
- Goal Pacing Within the Month: Expected to Date, Pace, Projected Month End and a Three-Level Status
- Quarterly Goals: Cumulative Actual against a Linear Plan, the Required Weekly Run Rate and the Projected Quarter End
- One Language for Four Exports: A Metric Dictionary with XLOOKUP and a Standardised Roll-Up with SUMIFS
- Engagement Rate by Followers or by Reach: Two Denominators, a Ranking with RANK.EQ, the Best Account and a Bar Chart
- Top Posts and Content Pillars: LARGE for the Top Three, INDEX-MATCH for the Post, Pillar Counts, Pillar Rates and a Bar Chart
- Follower Growth: Net Adds, Monthly Growth Rate, Unfollow Rate, Twelve-Month Growth and a Column Chart
- Trend and Forecast: SLOPE, R Squared, FORECAST.LINEAR for the Next Three Months and the Gap to a Goal
- Organic and Paid in One Report: Paid Share of Impressions, Blended and Organic Engagement Rate, Cost per Paid Engagement and a Highlight Rule
- Noise or Real Change: The Two-Proportion z-Test for an Engagement Rate, p-Values and a Verdict Column
- Commentary Rules: Materiality, Explain-the-Miss Logic, Sentences Built with TEXT and a Count of KPIs That Need Words
- What-If Grid for a Goal: Mixed References, a Heat Map, Required Cadence, Required Engagements per Post and Scenario Counts
- Quality Checks Before Sending: Reconciling Report Figures with Platform Exports, a Tolerance, Check Flags and a Row Highlight
- Final Project: A One-Page Monthly Client Report with Month-on-Month, Year-on-Year, Blended Engagement Rate, Goal Status and a Chart