Why this matters
The advanced marketing analytics level built one warehouse report. At expert level the job is the layer under every report. The guide to designing a marketing data model makes each choice once, in tables: what a row means, which dimensions every fact shares and which keys join them. The guide to loading ad platform data into a warehouse covers the source that breaks most often, because platform figures keep changing after they land in the warehouseA database designed for analysis that stores data loaded from many systems, such as analytics, ad platforms and the CRM, so it can be joined and queried..
The franchise and its spreadsheets
Holvaspen Cleaning is an invented home cleaning franchise. Each branch buys its own search, social and trades directory advertising and sends head office a monthly spreadsheet, from which cost per booking is reported.
Holvaspen Cleaning: sources and spreadsheets
| Branch | Spend recorded as | Campaigns recorded as | "Booking" means |
|---|---|---|---|
| A | Platform spend, net of VAT | Names copied from the platforms | First clean booked |
| B | Invoice totals including VAT | "Google" and "Facebook" only | Any clean, repeats included |
| C | Platform spend in euros | Names, renamed twice this year | First clean completed |
| D | Agency invoice including its management fee | The agency's own labels | First clean booked |
- Eight branches, each with its own Google Ads and Meta accounts; six trade in pounds, two in euros. One Meta account reports in a time zone eight hours behind head office.
- The trades directory charges per lead and invoices each branch monthly.
- A postcode checker routes each website enquiry to a branch; the analytics export lands daily.
- The booking system holds every lead with its branch and campaign value, the first booking and every clean.
- Last October branch E's territory was split, and the new branch G took half its postcodes.
- In March branch C switched its Meta attribution setting to include view-through credit.
- Platform conversions credited to one Monday, by load: next morning 41, three days later 52, a week later 57, two weeks later 59, three weeks later 59.
Facts, dimensions and keys
Start from the processes, not the spreadsheets. Spend, sessions, leads, first bookings and cleans each record events at their own 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. and get their own fact. Shared dimensions make them comparable, keys head office controls keep the joins working through renames, and loading rules decide whether spend can be trusted.
Task
- Design the fact tables, writing each one's grain as a sentence, and define a booking once.
- Design the shared dimensions and the campaign mapping table, and say how a new campaign gets its key.
- Set the keys, and decide how history is kept for renamed campaigns and branch E's split territory.
- Plan how platform data lands: what is loaded, the refresh window, attribution settings, currency and time zones.
- Write the monthly reconciliation against the platforms and against invoices, branch D's fee included.
- Restate branch B's cost per booking on the model's definitions.
Worked answer
Facts. Each grain is written as a sentence in the table's documentation.
| Table | One row per | Source |
|---|---|---|
fct_ad_spend_daily | Day, platform, account and campaign | Google Ads and Meta, by campaign ID |
fct_directory_cost_monthly | Month and branch | Directory invoices |
fct_sessions | Session | Analytics export |
fct_leads | Lead | Booking system |
fct_bookings | First clean booked | Booking system |
fct_cleans | Clean visit | Booking system |
The directory gets its own monthly table, since monthly rows in a daily fact get summed with daily ones. A booking is a first clean booked; repeats live in the visits fact, so branch B's figure becomes a separate measure, not a rival definition.
Dimensions and mapping. dim_date carries head office's reporting weeks and months; dim_channel is fed by one
mapping table from platform, campaign type, source and medium; dim_branch holds franchisee, currency, time zone
and territory. A Naming ConventionAn agreed pattern for naming campaigns, ads, events and tracking parameters so that reports can group them reliably. encoding branch, service and offer is parsed into
dim_campaign, with failures shown as unparsed. map_campaign maps every Google Ads ID, Meta ID, cleaned link value
and booking-system campaign to one campaign key. The mapping row is a launch checklist step, and a weekly unmapped
report lists spend and leads with no key.
Keys and history. Every dimension has a key head office controls, with the source identifier beside it. The platform IDs are the join keysThe shared field, such as date and channel, a campaign ID or a click ID, on which rows from two data sources are matched in a blend or a query., so branch C's renames touch only a names history. Campaign attributes take current values; territory is the exception, with validity dates on the postcode-to-branch rows so last year's leads stay with branch E. Phone numbers and emails stay in the restricted cleaned layer; reporting tables aggregate by day, branch, channel and campaign.
Loading. A managed connector, checked for how far back each sync reloads, loads spend, clicks, conversions by conversion action, currency, time zone and load metadata by campaign ID. Every run deletes and reloads a trailing window set from the snapshot, and reports label those days provisional. Each conversion figure carries its attribution setting, and a settings history dates branch C's change as a break in its series. Platform conversions are never summed across platforms; cost per booking uses bookings from the booking system. Euros are converted once in the cleaned layer at finance's monthly rate, originals kept. The odd Meta account's days are documented as account days, and the launch checklist sets new accounts to head office's zone.
Reconciliation. Monthly, for a closed month older than the window, warehouse spend is compared with each platform's report by account, in the account's currency and time zone. Invoice differences, such as VAT and branch D's fee, are explained in the process the guide to tracking spend against budget sets out, never patched into the loads. The dashboard is written up in the dashboard specification template, and the tutorial on data tests for marketing tables turns each grain and the unmapped report into checks that run with every load.
Holvaspen Cleaning: the window and branch B restated
The Monday's conversions still move after a week and stop by two weeks, so each run reloads the trailing 14 days, labelled provisional. An append-once load would have kept 41 of 59, about 30% short.
Branch B, last month: spreadsheet £2,400 including VAT ÷ 96 cleans = £25 per "booking". Model: £2,000 net spend ÷ 32 first cleans booked = £62.50, comparable with every branch.
Common mistakes
- Monthly directory invoices in the daily spend fact. Mixed grains get summed together.
- Joining on campaign names. Branch C's renames would break every report.
- Overwriting territory history. Branch E's past gets rewritten.
- Appending each day's load once. Recent cost per booking always looks worse.
- Adding VAT and fees to loaded spend. The platforms stop reconciling.
