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
Belvadrom Building Supplies: last year's figures and sources
| Source | What it holds | Known problems |
|---|---|---|
| Finance ledger | Branch and trade counter sales £21.6m; online shop sales £3.0m, net of VAT and returns | Not joined to anything marketing holds |
| Online shop | 14,200 orders | The 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 claimed | Exported by hand each month; recent days never updated |
| Meta | £60,000 spend; 5,600 purchases claimed | Its attribution setting changed in March; the series runs straight through |
| Radio and trade press | £120,000 and £40,000, from agency invoices | Radio dated by invoice month, not by airtime |
| Analytics export | Linked 20 months ago; trade account applications are a key event | Only the analyst who left could query it |
| CRM | Trade account applications, credit decisions and first orders | Source field blank on counter applications |
The spreadsheet report adds the Google Ads and Meta purchases together as "paid orders".
Trade accounts, shared costs and the mix model deck
Applications from the year before last, all now trading or declined:
| Segment | Applications | Credit approved | Trading accounts | First-year contribution per trading account |
|---|---|---|---|---|
| General builders | 400 | 300 | 160 | £3,000 |
| Specialist trades | 500 | 350 | 150 | £1,200 |
| Contractors | 60 | 40 | 18 | £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.
- Data model and loads. Design the facts at their Table GrainWhat one row of a table represents, such as one order, one order line, one session or one customer per day; queries and metric definitions must match it. 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.
- 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.
- 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 MMMStatistical model of channel impact. must see branch sales.
- Lead values for trade accounts. Value applications and credit approvals by segment from first-year Contribution MarginRevenue left after every cost that varies with each sale. 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.
- 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.
- Attribution in SQL. Build rule-based AttributionAssigning conversion credit to touchpoints. 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.
- 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.
- Sign-off page. One page for both directors listing each change, its sequence, its owner and the check that proves it worked.
Evaluation criteria
| Criterion | What a strong answer shows | Common gap |
|---|---|---|
| Data model and loads | Designs facts at their grain with shared dimensions and keys, and lands platform data with refresh windows and currency handled | One wide table rebuilt by hand each month |
| Data tests | Writes tests for duplicates, gaps, unmapped rows, freshness, volume and reconciliation, with an owner for failures | Errors found by the finance director |
| Mix model | Reviews the model's data, specification, fit and story, reads its uncertainty and plans the tests that calibrate it | The model's channel returns adopted as budget shares |
| Lead values | Values trade account leads by stage from margin and closed cohorts, by segment, with a refresh rule | One value for every lead |
| Cost allocation | Allocates shared costs on stated bases, shows direct and allocated costs apart and keeps allocations out of spend decisions | Allocated costs used to cut a channel |
| Attribution | Builds rule-based attribution in SQL from sessions and touchpoints with stated lookback and credit rules and checks | A 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.
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
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.
