Brief
Quarrystone Fitness Clubs is an invented chain of gyms in towns across one region. Members join online or at a club desk and pay monthly. Marketing is a manager and an analyst, with an agency running paid search and paid social; the chain also buys local radio in some towns, door drops of leaflets and a referral scheme that rewards members with a free month.
The finance director has asked for a quarterly review that the leadership team can rely on. Her complaint is specific: the two marketing dashboards both call themselves certified, both report cost per join, and they disagree. She wants one set of numbers, a clear statement of what marketing can and cannot claim, and a view of marketing that reconciles with the accounts. The operations director wants to know whether radio should be extended to more towns.
You are the analyst. The review covers the third quarter and goes to the leadership meeting in the second week of October.
Inputs and constraints
Quarrystone's third quarter: spend, joins and finance assumptions
Budget and ledger actual, after the month-end reconciliations:
| Line | Budget | Ledger actual |
|---|---|---|
| Paid search | £26,000 | £28,000 |
| Paid social | £24,000 | £22,000 |
| Local radio (five of fourteen club towns) | £18,000 | £18,000 |
| Door drops | £9,000 | £9,000 |
| Referral rewards | £7,000 | £6,000 |
| Agency fee | £7,500 | £7,500 |
| Production | £4,500 | £5,300 |
| Total | £96,000 | £95,800 |
Joins in the quarter:
| Source | Joins | Notes |
|---|---|---|
| Membership system, all new members | 2,900 | 1,750 online, 1,150 at a club desk |
| Ad platforms, self-reported | 1,150 | Paid search 610, paid social 540 |
| Analytics, data-driven model, consenting visitors | 1,450 | Paid search 520, paid social 310, organic and direct 620 |
| Referral codes redeemed | 260 | Online and at the desk |
| Door-drop promotion codes redeemed | 70 | Online and at the desk |
The two dashboards: the agency's acquisition dashboard computes cost per join as paid media spend over platform joins; the clubs dashboard computes it as all marketing cost over membership-system joins.
Finance assumptions, agreed with the finance director: an average monthly fee of £30 after discounts; a contribution margin of 60% on membership fees; an expected average membership of eight months, giving a contribution per join of £30 × 60% × 8 = £144. In August the chain waived joining fees, which reduced revenue by £5,800 and is booked as a reduction of revenue, not as marketing cost.
Club towns with radio averaged 230 joins per club in the quarter; towns without averaged 195. Three of the five radio towns have clubs opened within the last year. Weekly data by club exists for two years; radio started this year.
Constraints:
- The warehouse holds
media_costs(day, channel, campaign, cost),members(one row per member, with join date, club and a join source from tracking links online or the desk's "how did you hear" field) andchannel_map. - Desk joins rarely carry a reliable source, and a share of online visitors decline analytics consent.
- The review must reconcile to the ledger and use the membership system as the record of joins.
- Leadership reads one page of findings; the detail sits in an appendix.
Task
- Metric dictionary. Define cost per join, join rate and marketing contribution using the field list in the guide to metrics layers and definitions: formula, grain, source tables, baked-in filters, time rule, null rule, owner and known differences. Resolve the two-dashboard conflict with named metrics, decide which dashboard stays certified, and set out how the other is moved or retired under the dashboard governance guide. Show where each KPIA metric tied directly to an objective. sits in a KPI tree from contribution down to channel drivers.
- Channel summary. Write the query that produces joins, cost and cost per join by month and channel from the three tables, aggregating each side before joining and keeping unsourced desk joins as their own row, as in the channel summary query exercise. State how the query's totals tie to the ledger and the membership system.
- Measurement approach. For every claim the review makes, name the method behind it and its caveat, using the decision-first rule in the guide to choosing an attribution approach. Explain why the platform, analytics and membership counts differ. Say what the radio comparison can and cannot show, whether a mix model (MMMStatistical model of channel impact.) is viable yet, and design the test you would run instead, on the experiment hypothesis template, drawing on the budget increase incrementality test.
- Marketing P&L view. Build marketing's own view as the marketing P&L guide describes: joins credited to marketing on a stated basis, contribution at the agreed figure, all marketing cost from the ledger, and the joining fee waiver shown where finance books it. State the return on investment (ROIProfit relative to cost.) and the basis it rests on, and set channel returns beside it as the comparison of ROI and ROAS recommends.
- Budget against actual. Present each line as budget, actual and variance, label each variance timing or permanent, and give the effect on the next quarter, following the guide to tracking spend against budget. Use the marketing budget calculator for any reallocation you propose.
- Findings and recommendations. Write the one-page summary in the shape of the monthly marketing report template, adapted to a quarter: what happened, why, what it is worth, and up to three recommendations, each with the evidence behind it and the decision it asks for.
One strong metric definition, for comparison
| Field | Entry |
|---|---|
| Name and key | Cost per join, all marketing; cost_per_join_all |
| Formula | marketing_cost_ledger ÷ new_members |
| Grain | Month by club; rolls up to quarter and chain by recomputing from sums |
| Sources | Ledger extract for marketing cost centres; members |
| Known differences | Higher than the agency's figure, which uses media spend over platform joins; that metric is renamed "platform cost per join (media only)" |
For the quarter: £95,800 ÷ 2,900 = £33.03 per join.
Evaluation criteria
Assess your review against each row, or ask a colleague to. Most first attempts miss two or three.
| Criterion | What a strong answer shows | Common gap |
|---|---|---|
| Metric definitions | Every metric has formula, grain, sources, filters, time rule, owner and known differences; one name per meaning | Two figures still sharing the name "cost per join" |
| Dashboard governance | One certified dashboard, the other moved to team tier or deprecated with a sunset date | Both dashboards kept "for now" |
| Query correctness | Each side aggregated before the join; totals tie to the ledger and membership system; unsourced joins kept and labelled | Desk joins dropped because they have no channel |
| Count reconciliation | Platform, analytics and membership counts explained by overlap, consent and desk joins | Platform joins summed and quoted as the total |
| Measurement approach | Each claim carries its method and caveat; radio treated as a hypothesis, with a test designed | The radio town comparison presented as proof |
| Marketing P&L | Built from ledger cost and agreed contribution; the fee waiver shown as a revenue reduction; the basis for credited joins stated | Every join credited to marketing, or the waiver added to marketing cost |
| Return figures | Return on investment and channel returns shown together, with the bridge between them | Channel returns presented to finance as if they were profit |
| Budget against actual | Every material variance explained, typed and carried into the next quarter's forecast | Variances explained in campaign language |
| Recommendations | Few, each tied to evidence and a decision, with the size of the money involved | A list of activities with no decision attached |
Submission checklist
When you have finished, compare your P&L view with the worked example in the marketing P&L guide and return to the intermediate marketing analytics level to mark the level complete.
