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

Marketing Analytics and Reporting › Expert

Capstone: A Finance-Grade Measurement System for a Builders' Merchant

The closing project of the expert marketing analytics level. You write the measurement system an invented builders' merchant's marketing and finance directors will both sign: the warehouse model and platform loads, the tests and who answers a failure, the review and calibration plan for a mix model nobody checked, values for trade account leads, rules for sharing costs, rule-based attribution built in SQL and a register of which figure reports and which decides spend.
Capstone projectExpert levelModule 3: Cost, value and credit finance can follow7 minUpdated

Brief

Belvadrom Building Supplies is an invented builders' merchant selling building materials and tools to trade and retail customers through branches with trade counters and an online shop. It spends on search, social, radio and trade press. Trade customers apply online or at the counter for a credit account, and those accounts carry most of the business.

Reporting runs from spreadsheets that disagree with finance. A mix model bought last year was never reviewed, yet its deck is quoted in budget meetings. Trade account leads are counted but not valued. Agency and tool costs are spread across channels by whoever builds the report. The finance director will not sign another budget until its numbers can be traced. Where the advanced marketing analytics level built one warehouse report, a forecast and a data quality routine, this system is the measurement those rest on.

Inputs and constraints

Illustrative example — not a real result

Belvadrom Building Supplies: last year's figures and sources

SourceWhat it holdsKnown problems
Finance ledgerBranch and trade counter sales £21.6m; online shop sales £3.0m, net of VAT and returnsNot joined to anything marketing holds
Online shop14,200 ordersThe spreadsheet report shows £3.35m, with VAT and cancelled orders included
Google Ads, two accounts£180,000 spend, £26,000 of it billed in euros for the branches across the border; 11,300 purchases claimedExported by hand each month; recent days never updated
Meta£60,000 spend; 5,600 purchases claimedIts attribution setting changed in March; the series runs straight through
Radio and trade press£120,000 and £40,000, from agency invoicesRadio dated by invoice month, not by airtime
Analytics exportLinked 20 months ago; trade account applications are a key eventOnly the analyst who left could query it
CRMTrade account applications, credit decisions and first ordersSource field blank on counter applications

The spreadsheet report adds the Google Ads and Meta purchases together as "paid orders".

Illustrative example — not a real result

Trade accounts, shared costs and the mix model deck

Applications from the year before last, all now trading or declined:

SegmentApplicationsCredit approvedTrading accountsFirst-year contribution per trading account
General builders400300160£3,000
Specialist trades500350150£1,200
Contractors604018£12,000

Shared costs in the ledger last year: search and social agency retainer £36,000; call tracking, reporting and CRM seats £14,400; a marketing team of three £135,000; one photo shoot used in social, trade press and branch posters £18,000. Agency timesheets, tool usage reports, time logs from last quarter and an asset log exist.

The mix model deck: 104 weeks; outcome online shop revenue only; channels search, social, radio and trade press; controls season and holidays, with no trade price list changes or promotions; no data log, holdout or intervals; a fit of 0.91; radio credited with 38% of online revenue. Recommendation: double radio.

Constraints:

  • The team is you, a part-time data engineer, the finance business partner, the search and social agency and the radio agency. The warehouse holds the analytics export and nothing else, and no new tools are funded this year.
  • The radio contract renews in April, and the marketing director wants the deck's recommendation settled first.
  • No test may withhold media from branches during the spring and summer peak.
  • Finance decides the contribution margin and its horizon; lead values are never reported as revenue.
  • State expected effects as a direction with a check; invent no uplifts.

Task

Write one document with these sections, in this order.

  1. Data model and loads. Design the facts at their Table Grain for spend, sessions, applications, credit decisions and orders, with shared dimensions for date, channel, campaign, branch and customer type, and the campaign mapping table, following the guide to designing a marketing data model. Plan the platform loads with refresh windows, the attribution settings stored beside the numbers, the euro account and the monthly reconciliation with invoices, from the guide to loading ad platform data into a warehouse, as in the home cleaning franchise exercise.
  2. Tests and owners. Write the suite with the tutorial on data tests for marketing tables: unique keys, required fields, unmapped rows, freshness, volume and totals against the ledger and platforms, each with a severity, an owner and what the dashboard shows meanwhile. Say which failures escalate to the tracking outage response SOP, and record the roles in a RACI matrix.
  3. The mix model. Score the deck against the guide to evaluating a marketing mix model and give a verdict. Specify the rebuilt dataset with the tutorial on preparing data for a mix model, including the outcome finance will confirm. Then plan calibration with the comparison of mix modelling and incrementality testing: the first channel to test and why, its design checked against the holdout test readiness checklist and written in the experiment hypothesis template, how results feed each refit and what happens to the radio renewal. The rebuilt MMM must see branch sales.
  4. Lead values for trade accounts. Value applications and credit approvals by segment from first-year Contribution Margin and the closed cohort, with the lag for contractors, a refresh rule and the counter applications' missing source, following the guide to valuing a lead by pipeline stage.
  5. Cost sharing. Choose a base for each shared cost with the guide to allocating shared marketing costs, show direct and allocated costs apart, and file the method in the marketing P&L template.
  6. Attribution in SQL. Build rule-based Attribution from the analytics export with the tutorial on building an attribution model in SQL: sessions, conversions for online orders and trade applications, the lookback window, credit rules with weights as parameters, the check that credit sums to conversions and what the model cannot see, branch sales and radio among them. Say which decisions it serves.
  7. Which figure decides. Write a register of every figure, from ledger sales to platform purchases, attributed credit, model estimates, test results, lead values and allocated costs: its source, owner, uncertainty and whether it reports, optimises or decides. Budget moves go through the budget reallocation memo template.
  8. Sign-off page. One page for both directors listing each change, its sequence, its owner and the check that proves it worked.

Evaluation criteria

CriterionWhat a strong answer showsCommon gap
Data model and loadsDesigns facts at their grain with shared dimensions and keys, and lands platform data with refresh windows and currency handledOne wide table rebuilt by hand each month
Data testsWrites tests for duplicates, gaps, unmapped rows, freshness, volume and reconciliation, with an owner for failuresErrors found by the finance director
Mix modelReviews the model's data, specification, fit and story, reads its uncertainty and plans the tests that calibrate itThe model's channel returns adopted as budget shares
Lead valuesValues trade account leads by stage from margin and closed cohorts, by segment, with a refresh ruleOne value for every lead
Cost allocationAllocates shared costs on stated bases, shows direct and allocated costs apart and keeps allocations out of spend decisionsAllocated costs used to cut a channel
AttributionBuilds rule-based attribution in SQL from sessions and touchpoints with stated lookback and credit rules and checksA platform's own attribution reported as the business view

A strong document is mostly a matter of order. Ledger sales come first, because every test, reconciliation and model outcome is measured against them. The deck is judged before April: it models online revenue for a business that sells mostly through branches, so it is rebuilt, and radio is renewed without the doubling until a test outside the peak reads out. Lead values and cost rules are agreed with finance before any report shows them, and the register makes plain that platform purchases optimise campaigns, attributed credit compares digital campaigns, and only calibrated estimates and tests move money between channels.

Check your own arithmetic against these figures.

Illustrative example — not a real result

Reference figures for self-checking

  • Platform claims: 11,300 + 5,600 = 16,900 purchases against 14,200 shop orders, about 1.19 claimed per real order.
  • Online revenue: the spreadsheet's £3.35m is £350,000, about 12%, above the ledger's £3.0m.
  • Application values: builders 160 ÷ 400 = 40% of £3,000, so £1,200; trades 150 ÷ 500 = 30% of £1,200, so £360; contractors 18 ÷ 60 = 30% of £12,000, so £3,600.
  • Credit approval values: builders £1,600; trades about £514; contractors £5,400.
  • One blended application value would be £876,000 ÷ 960 = £912.50, about 2.5 times a trades application's worth and about a quarter of a contractor's.
  • The deck's outcome covers £3.0m of £24.6m in sales, about 12%.

Submission checklist

0/10

When you have finished, compare your model and loads with the home cleaning franchise exercise, your review with the mattress brand exercise and your values and cost rules with the pest control exercise.

Pages this lesson applies

Terms used on this page

Also related

Related