Level 3CapstonesProject, 12 min
Capstone: a KPI dashboard from a raw export
The task
Every Monday the ad platforms dump a raw export onto the Export tab: one row per channel per week. Build the Dashboard tab so it rebuilds itself whenever a new export lands: totals, KPI tiles, a channel table and a weekly trend.
- Totals with BYCOLOn Dashboard, in B2, total all four metric columns of the export in one formula:
=BYCOL(Export!C2:F17, LAMBDA(column, SUM(column))). - KPI tiles with HSTACKIn B5, build the three KPI tiles side by side with
HSTACK: ROAS (revenue / spend), CPA (spend / conversions) and conversion rate (conversions / clicks), all from the totals in row 2. - The channel tableIn A7, summarize spend and revenue by channel, best revenue first:
=QUERY(Export!A1:F17, "select B, sum(C), sum(F) group by B order by sum(F) desc label sum(C) 'Spend', sum(F) 'Revenue'") - ROAS per channelIn D8, divide each channel's revenue by its spend, for all four rows of the table, with
MAP. Return 0 if spend is 0 so a new channel can't break it. - Weekly revenue trendIn A14, total revenue by week, oldest first, ready for a line chart:
=QUERY(Export!A1:F17, "select A, sum(F) group by A order by A label sum(F) 'Revenue'")
The data, 2 tabs
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Spend | Clicks | Conversions | Revenue | |
| 2 | Total | ||||
| 3 | |||||
| 4 | ROAS | CPA | Conv. rate | ||
| 5 | KPIs | ||||
| 6 | |||||
| 7 | ROAS | ||||
| 8 | |||||
| 9 | |||||
| 10 | |||||
| 11 | |||||
| 12 | |||||
| 13 | |||||
| 14 | |||||
| 15 | |||||
| 16 | |||||
| 17 | |||||
| 18 | |||||
| 19 |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Week | Channel | Spend | Clicks | Conversions | Revenue |
| 2 | Sep 7 | Paid Search | $3,200 | 1840 | 92 | $13,800 |
| 3 | Sep 7 | Paid Social | $2,800 | 2650 | 61 | $7,300 |
| 4 | Sep 7 | $400 | 920 | 48 | $5,760 | |
| 5 | Sep 7 | Display | $1,500 | 610 | 9 | $1,080 |
| 6 | Sep 14 | Paid Search | $3,350 | 1910 | 97 | $14,650 |
| 7 | Sep 14 | Paid Social | $2,950 | 2810 | 66 | $7,900 |
| 8 | Sep 14 | $400 | 880 | 45 | $5,400 | |
| 9 | Sep 14 | Display | $1,500 | 580 | 8 | $960 |
| 10 | Sep 21 | Paid Search | $3,100 | 1795 | 88 | $13,200 |
| 11 | Sep 21 | Paid Social | $3,100 | 2990 | 58 | $6,950 |
| 12 | Sep 21 | $450 | 1010 | 55 | $6,600 | |
| 13 | Sep 21 | Display | $1,400 | 545 | 7 | $840 |
| 14 | Sep 28 | Paid Search | $3,500 | 2020 | 104 | $15,900 |
| 15 | Sep 28 | Paid Social | $3,000 | 2870 | 70 | $8,400 |
| 16 | Sep 28 | $400 | 940 | 51 | $6,120 | |
| 17 | Sep 28 | Display | $1,600 | 650 | 11 | $1,320 |