Brief
Biscuitburrow Pet Supplies is an invented online shop selling food, bedding, toys and grooming products for dogs and cats. It is run by its owner with a part-time marketer. Customers find it through paid search, paid social and the shop's own email list, and many reorder food on a regular cycle.
Until now the owner has judged marketing by looking at the shop platform's sales screen and whichever ad platform she opened last. Each platform claims credit for more sales than the shop recorded, and she cannot tell whether last month went well. She has written targets for the first time and wants a monthly report that tells her how the month went against them, why, and what to do next.
Paid media exports for Biscuitburrow's own accounts are being connected but are not ready. The marketer has suggested building and testing the Looker Studio page on the sample campaign dataset, which has the same columns, and pointing it at the real export once the fields pass their tests. The report itself uses the October figures below.
Inputs and constraints
Biscuitburrow Pet Supplies: October inputs
| Source | Metric | October | September | October target |
|---|---|---|---|---|
| Shop platform | Orders | 1,840 | 1,720 | 1,850 |
| Shop platform | Revenue, net of refunds | £64,400 | £58,480 | £66,000 |
| Shop platform | Average order value | £35.00 | £34.00 | £35.70 |
| Shop platform | New customers | 610 | 590 | 650 |
| Analytics | Sessions | 82,000 | 76,000 | — |
| Analytics | Purchases recorded | 1,690 | 1,650 | — |
| Google Ads | Spend; conversions; attributed revenue | £4,800; 410; £14,350 | £4,500; 470; £16,450 | ROAS ≥ 3.5 |
| Meta ads | Spend; conversions; attributed revenue | £3,200; 150; £5,100 | £3,000; 120; £4,080 | CPA ≤ £24 |
| Email platform | Cost; attributed revenue | £300; £13,500 | £300; £11,900 | — |
Annotations log: free-delivery threshold raised on 1 October; checkout update on 9 October, after which a new wallet payment option did not fire the analytics purchase event until it was fixed on 13 October; new cat food range launched on 15 October.
Constraints: the owner reads the report on the fifth working day and has a few minutes for it. There is no budget for paid tools, so the stack is the shop platform, the ad platforms, analytics, a spreadsheet and Looker Studio. The marketer will maintain whatever you build, so it must be simple enough for one person. Every number in the report must be traceable to a source and a definition.
Task
Produce one document with the sections below, in order, and share the Looker Studio page alongside it.
- KPI tree. Following the guide to KPI trees, decompose net revenue into orders and average order value, orders into sessions and conversion rate, and sessions into channels. Split customers into new and returning. Write the formula at each node, check it balances with October's figures, name the owner of each leaf, and add at least two guardrails.
- Definitions. Use the KPI dictionary template to write one definition per metric in the tree: what is counted, when, from which system. Decide which system is the source of truth for orders and revenue, and which figures are channel diagnostics only.
- Looker Studio page. Build a data source from the sample dataset as the data sources and connectors guide describes, with field types checked. Create calculated fields for cost per acquisition (CPACost of each conversion.), return on ad spend (ROASRevenue generated per unit of ad spend.), click-through rate and a channel grouping, following the calculated fields tutorial. Test each against a hand calculation and the ROAS calculator or the CPA calculator. Lay the page out outcome first, using the dashboard design guide.
- Reconciliation. Following the monthly reporting pack procedure, compare analytics purchases with shop orders and the ad platforms' claimed conversions with the shop's total. Explain each gap with evidence from the annotations log, and run the checks in the data quality guide.
- The report. Use the monthly marketing report template: the summary, the decisions, the results table with outcomes first, one line per channel judged on its role, risks and issues, next month's priorities and where the definitions live.
- Agreed actions. List the actions for November with an owner and a date each, separating what is decided from what the owner is asked to decide.
The kind of reasoning the reconciliation needs
Analytics recorded 1,690 purchases against 1,840 shop orders, a gap of 150 or about 8%, wider than September's 70 or about 4%. Google Ads conversions fell from 470 to 410 while shop orders rose. A strong submission does not read this as paid search failing. It points to the wallet payment option missing its purchase event from 9 to 13 October, shows paid search with and without those days, and reports the channel's ROAS of 2.99 (£14,350 ÷ £4,800) with a clear note that the figure is understated, rather than as a miss against the 3.5 target to be acted on.
Evaluation criteria
Judge your submission against this table, or ask a colleague to. A strong answer meets every row.
| Criterion | What a strong answer shows | Common gap |
|---|---|---|
| Tree balances | Every node has a formula that checks out with October's figures | A list of metrics with no arithmetic between them |
| Leaves have owners | Each driver names the person who would move it | Leaves that are still outputs, such as "revenue by channel" |
| Guardrails present | Refund rate and the cost of a new customer watched beside the tree | Growth reported with no constraint |
| One source of truth | Orders and revenue from the shop platform; platform conversions treated as channel diagnostics | Ad platform totals added together as if they were sales |
| Definitions complete | Counting moment, source and period for every metric | Definitions that name the metric but not the system |
| Fields correct | Ratios written as sums divided by sums, denominators guarded, an ELSE in the grouping | Row-level ratios averaged by the chart |
| Fields tested | A hand check recorded for each field before it reaches a scorecard | Trusting a formula because it compiles |
| Gaps explained | The tracking fault identified from the log and its effect shown | A tracking gap reported as a fall in performance |
| Report leads with decisions | A summary written last and placed first, with the reason for the result | A summary that restates the table |
| Channels judged on role | Search on ROAS, social on CPA, email on revenue at low cost | One metric applied to every channel |
| Actions are owned | Each action has an owner and a date, split into made and requested | "Keep monitoring" with no owner |
Submission checklist
Tick each line before calling the capstone done.
If any row of the evaluation table is weak, the earlier lessons on the beginner marketing analytics level cover each part: the car wash metrics exercise for the tree and definitions, the dental group report exercise for the report, and the outdoor gear calculated fields exercise for the Looker Studio page.
