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

Marketing Analytics and Reporting › Intermediate

Capstone: A Quarterly Marketing Performance Review for a Gym Chain

The capstone of the intermediate marketing analytics level asks you to prepare the quarterly marketing review for an invented chain of gyms: metric definitions everyone signs up to, a channel summary built in SQL, the measurement approach behind every claim, a marketing P&L view and budget against actual with recommendations. It is judged against explicit criteria.
Capstone projectIntermediate levelModule 3: Finance-facing reporting6 minUpdated

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

Illustrative example — not a real result

Quarrystone's third quarter: spend, joins and finance assumptions

Budget and ledger actual, after the month-end reconciliations:

LineBudgetLedger 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:

SourceJoinsNotes
Membership system, all new members2,9001,750 online, 1,150 at a club desk
Ad platforms, self-reported1,150Paid search 610, paid social 540
Analytics, data-driven model, consenting visitors1,450Paid search 520, paid social 310, organic and direct 620
Referral codes redeemed260Online and at the desk
Door-drop promotion codes redeemed70Online 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) and channel_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

  1. 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 KPI sits in a KPI tree from contribution down to channel drivers.
  2. 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.
  3. 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 (MMM) is viable yet, and design the test you would run instead, on the experiment hypothesis template, drawing on the budget increase incrementality test.
  4. 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 (ROI) and the basis it rests on, and set channel returns beside it as the comparison of ROI and ROAS recommends.
  5. 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.
  6. 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.
Illustrative example — not a real result

One strong metric definition, for comparison

FieldEntry
Name and keyCost per join, all marketing; cost_per_join_all
Formulamarketing_cost_ledger ÷ new_members
GrainMonth by club; rolls up to quarter and chain by recomputing from sums
SourcesLedger extract for marketing cost centres; members
Known differencesHigher 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.

CriterionWhat a strong answer showsCommon gap
Metric definitionsEvery metric has formula, grain, sources, filters, time rule, owner and known differences; one name per meaningTwo figures still sharing the name "cost per join"
Dashboard governanceOne certified dashboard, the other moved to team tier or deprecated with a sunset dateBoth dashboards kept "for now"
Query correctnessEach side aggregated before the join; totals tie to the ledger and membership system; unsourced joins kept and labelledDesk joins dropped because they have no channel
Count reconciliationPlatform, analytics and membership counts explained by overlap, consent and desk joinsPlatform joins summed and quoted as the total
Measurement approachEach claim carries its method and caveat; radio treated as a hypothesis, with a test designedThe radio town comparison presented as proof
Marketing P&LBuilt from ledger cost and agreed contribution; the fee waiver shown as a revenue reduction; the basis for credited joins statedEvery join credited to marketing, or the waiver added to marketing cost
Return figuresReturn on investment and channel returns shown together, with the bridge between themChannel returns presented to finance as if they were profit
Budget against actualEvery material variance explained, typed and carried into the next quarter's forecastVariances explained in campaign language
RecommendationsFew, each tied to evidence and a decision, with the size of the money involvedA list of activities with no decision attached

Submission checklist

0/13

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.

Pages this lesson applies

Terms used on this page

Also related

Related