Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics and Reporting › Beginner

Capstone: A First Marketing Report for a Pet Supplies Store

The project that closes the beginner marketing analytics level. For an invented online pet supplies store you build the KPI tree and its definitions, prototype a Looker Studio page with tested calculated fields, reconcile the month's figures, and write the first monthly report with commentary and the actions agreed for next month.
Capstone projectBeginner levelModule 3: Working with data in Looker Studio6 minUpdated

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

Illustrative example — not a real result

Biscuitburrow Pet Supplies: October inputs

SourceMetricOctoberSeptemberOctober target
Shop platformOrders1,8401,7201,850
Shop platformRevenue, net of refunds£64,400£58,480£66,000
Shop platformAverage order value£35.00£34.00£35.70
Shop platformNew customers610590650
AnalyticsSessions82,00076,000—
AnalyticsPurchases recorded1,6901,650—
Google AdsSpend; conversions; attributed revenue£4,800; 410; £14,350£4,500; 470; £16,450ROAS ≥ 3.5
Meta adsSpend; conversions; attributed revenue£3,200; 150; £5,100£3,000; 120; £4,080CPA ≤ £24
Email platformCost; 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.

  1. 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.
  2. 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.
  3. 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 (CPA), return on ad spend (ROAS), 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.
  4. 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.
  5. 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.
  6. 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.
Illustrative example — not a real result

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.

CriterionWhat a strong answer showsCommon gap
Tree balancesEvery node has a formula that checks out with October's figuresA list of metrics with no arithmetic between them
Leaves have ownersEach driver names the person who would move itLeaves that are still outputs, such as "revenue by channel"
Guardrails presentRefund rate and the cost of a new customer watched beside the treeGrowth reported with no constraint
One source of truthOrders and revenue from the shop platform; platform conversions treated as channel diagnosticsAd platform totals added together as if they were sales
Definitions completeCounting moment, source and period for every metricDefinitions that name the metric but not the system
Fields correctRatios written as sums divided by sums, denominators guarded, an ELSE in the groupingRow-level ratios averaged by the chart
Fields testedA hand check recorded for each field before it reaches a scorecardTrusting a formula because it compiles
Gaps explainedThe tracking fault identified from the log and its effect shownA tracking gap reported as a fall in performance
Report leads with decisionsA summary written last and placed first, with the reason for the resultA summary that restates the table
Channels judged on roleSearch on ROAS, social on CPA, email on revenue at low costOne metric applied to every channel
Actions are ownedEach 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.

0/14

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.

Pages this lesson applies

Terms used on this page

Also related

Related