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

Marketing Analytics › Attribution

Preparing Data for a Marketing Mix Model

Assemble the dataset a marketing mix model needs: weekly spend by channel, outcomes from the system of record, controls, checks for gaps, and a data log.
Tutorialadvanced11 min readUpdated

What the model will need

The basics guide describes three kinds of input. In table form, the finished dataset looks like this, one row per week.

Column groupContentsSource of truthTypical problems
WeekThe start date of the week, on one agreed definitionThe data logMixed week definitions between sources
OutcomeRevenue, orders, sign-ups or volume sold, in totalThe order system, finance ledger or sell-out dataTaken from an ad platform; returns treated inconsistently
MediaSpend, and where useful a delivery measure, per channelPlatform reports for digital; agency invoices and schedules for offlineOffline spend dated by invoice; channels defined differently over time
Price and promotionAverage price or a price index; promotion flags and depthPricing and trading teamsPromotions known only to the people who ran them
CalendarHolidays, trading events, season markersThe trading calendarMovable holidays falling in different weeks each year
Business eventsLaunches, distribution changes, stock-outs, outages, known shocksProduct, sales and operations teamsNever written down anywhere

Every column must cover the whole history at the same grain, and every value must mean the same thing in every week. That is the whole specification; the steps below are how to meet it. The academy exercise on checking the dataset behind a mattress brand's mix model practises reading an agency's weekly data against this specification before any of the model's results are trusted.

Before you start

  • A decision the model will inform. Annual channel budgets, a question about offline media, the split between brand and performance spend. The decision fixes the outcome, the channel definitions and the history needed.
  • Access to the outcome's system of record and to someone in finance who can confirm that the weekly totals tie to reported figures.
  • Spend records for every channel over the whole history: platform exports for digital media, and for offline media the agency's invoices, booking schedules and the media owners' confirmations.
  • People who remember the history. Trading, pricing, product and sales colleagues who know when promotions ran, when distribution changed and what went wrong in which month.
  • A place to build the table, such as a warehouse or a well-controlled spreadsheet, and enough SQL to roll daily data up to weeks; the SQL for marketers guide covers the patterns.
  • A data log started on day one: a document or sheet where every source, definition, gap and fix is recorded as it is found.
  1. Agree the outcome and its source

    Name the one outcome the model will explain and the system it comes from. For most businesses it is revenue or orders from the order system or the finance ledger, in total across every route to purchase, because the model's value is in seeing effects that never pass through a website. Do not use an ad platform's conversions, which are credited by that platform's own rules, or an analytics tool's key events, which miss offline sales and consent-declined visitors. Decide the details explicitly: gross or net of returns, with or without tax, whether trade or wholesale sales are included, how cancelled orders are treated. If revenue is the outcome, the model can later express each channel's contribution as a return on spend (ROAS); if volume is the outcome, price must be a control. Write the decision in the data log and have finance confirm that a few sample weeks tie to their figures.

  2. Set the grain and the history

    Use weekly data, as the basics guide describes, on one week definition, such as weeks starting on a Monday, used for every column. Weekly smooths day-of-week noise while keeping enough rows to estimate carry-over. Then fix the history: long enough to contain more than one full seasonal cycle, so that the model can separate Seasonality from media, and no longer than the period over which the business, its channels and its records are comparable. A history that crosses a change in what the business sells, a merger or a switch of order system may be better cut at the change than stitched across it. Record the start and end weeks and the reason for each.

  3. Collect spend for every channel

    Gather spend for every paid channel across the whole history, not only the ones that are easy to export. Digital platforms usually provide daily spend by campaign; pull it at that grain so it can be re-mapped later. Offline media is harder: television, radio, print, outdoor and podcasts are usually held in agency invoices, which are dated when billed rather than when the media ran. Rebuild their weekly spend from the booking schedule, the spot logs or insertion dates, and the confirmed costs, so that each week carries the media that actually appeared in it. Decide whether figures are net or gross of agency commission and production costs, and use one basis for every channel. Include sponsorships, affiliate commissions and any channel paid for media, and leave out costs that buy no media, such as tools and salaries, unless the decision needs them.

  4. Map spend to the channel definitions

    Agree the channel list the model will use, and map every campaign or invoice line to exactly one channel. The basics guide warns that the definitions are the first consequential decision: too coarse and the model cannot separate prospecting from retargeting, too fine and the series become sparse and move together. A common compromise is to separate media that work differently, such as brand and non-brand search, or prospecting and retargeting on social, and to group small channels whose spend is too thin to estimate on their own. Build the mapping as a table keyed on platform, account and campaign identifier, so that renamed campaigns keep their channel. Check that mapped spend sums back to the source totals for each channel and month, and list every line that could not be mapped.

  5. Align dates, currency and time zone

    Every series must be on the same weeks, in the same currency and on the same clock. Platforms report in the account's time zone and other systems often in UTC, so roll daily data up to weeks only after converting each source to one agreed time zone; otherwise every week boundary is slightly wrong in every source. Convert currencies at one documented convention, such as a monthly average rate or the budget rate, and apply it to every channel and to the outcome. Where spend is only available monthly, spread it across weeks by the days the media ran rather than evenly by calendar, and flag those series in the log as lower resolution.

  6. Build the controls calendar

    List everything other than media that moved the outcome, week by week. Price and promotions come first, because the basics guide shows that an omitted control lends its effect to whichever media series moved with it: an average price or a price index, plus a flag and depth for each promotion. Then the calendar: public holidays, trading events and season markers, placed in the weeks they actually fell in each year. Then business events: product launches, changes in distribution or store count, stock-outs that capped sales, site outages, and known external shocks. Interview the people who were there, check trading reports and old plans, and record the source of every entry. Leave out anything that media itself causes, such as branded search volume, which belongs on the outcome side if it is used at all.

  7. Check gaps, flat series and channels that move together

    Run three checks before anyone fits a model. Gaps: count missing weeks per column and separate real zeros, when the media did not run, from missing records; rebuild or flag every missing record. Flat series: look for channels whose spend barely changed over the history; a channel with no variation cannot be estimated, and the basics guide notes that its contribution will then reflect the modeller's assumptions rather than the data. Channels that move together: compute the correlation between every pair of weekly spend series and plot the pairs that move in step. Pairs that always rise and fall together cannot be separated, whatever the model, so decide now whether to combine them, to accept a combined estimate for the history, or to plan variation that separates them in future. Then look for step changes and outliers, which usually mean a definition changed or a record was duplicated, using the routine in the data quality guide.

    SELECT
      CORR(paid_search_spend, paid_social_spend) AS search_social,
      CORR(paid_search_spend, online_video_spend) AS search_video,
      CORR(paid_social_spend, online_video_spend) AS social_video
    FROM mmm_weekly
    WHERE week_start BETWEEN @history_start AND @history_end
    
  8. Write the data log

    The data log is the dataset's documentation, and the model's reviewer will read it before the results. For each column record the source system, the owner, the extraction date, the definition, every transformation applied and every known issue. For each gap or fix record what was wrong, what was done, what evidence supported it and who agreed. Record the decisions made in earlier steps: the outcome definition, the week definition, the time zone, the currency convention, the channel mapping version and the controls included and excluded with reasons. A model whose data log is missing cannot be reviewed, and a model that cannot be reviewed should not move a budget.

  9. Plan the refresh

    A mix model is refreshed as channels change, so the dataset has to be rebuilt the same way each time. Turn the steps above into a repeatable process: saved queries for the platform data, a standing request to the agency for offline schedules and invoices, an owner for the controls calendar who adds entries as they happen rather than from memory a year later, and a check that new weeks tie to finance before they are appended. Decide the refresh cadence with the people who use the model, and version the dataset so that each model run can be traced to the exact table it was fitted on.

A dataset, assembled (illustrative)

The example below follows the steps for one business and shows the kind of entries a data log should contain.

Illustrative example — not a real result

Juniperwick Pet Food assembles three years of weekly data

Juniperwick Pet Food, an invented pet food brand selling online and through retailers, assembles 156 weeks of data for a model that will inform next year's channel budgets. The outcome is net revenue across its own site and retailer sell-out, confirmed against finance for twelve sample weeks. Spend covers seven channels: television, radio, paid search, paid social, online video, retail media and print. The gap check finds that radio spend is zero for nine weeks in the second year although the media plan shows a campaign; the radio invoices for those two months were filed under a different supplier name. The correlation check finds paid search and paid social weekly spend correlated at 0.94 across the whole history, because both were always raised for the same promotions. Four data log entries record the fixes.

Log entryIssueFixEvidence
D-07Radio spend missing for nine weeks in the second yearRebuilt weekly from the station confirmations, £186,000 in totalBooking schedule and confirmations from the agency
D-11Paid search and paid social move together, correlation 0.94Kept as separate columns; the model may only estimate their combined effect for the historyWeekly spend plot and correlation table
D-12Retail media reported monthlySpread across weeks by flight dates; flagged as lower resolutionRetailer campaign reports
D-15Two stock-outs of the best-selling dry foodAdded as weekly flags to the controls calendarOperations stock reports

The team also agrees with the channel owners that next year search and social budgets will be pulsed in alternating fortnights for two quarters, so that the next refresh can separate them.

Planning variation for the next model

The most useful thing a data preparation exercise produces is often a list of what the history cannot tell you. Channels that never varied and pairs that always moved together will stay unestimable until the plan changes, and the cheapest fix is deliberate variation: pulses and pauses in a channel's spend, different weights in different regions, staggered timing between channels that were always raised together. Each must be large enough and long enough to show up in weekly data, which only your own history can tell you, and each should be agreed with the channel owners in advance and recorded in the controls calendar when it runs.

Variation also needs a range. Response curves are estimated well only within the spend levels the history contains, so a channel that has always run at one level tells the model nothing about Diminishing Returns above or below it. Planned tests are the strongest form of variation, because a controlled test gives a measured point on the curve that the model can be calibrated to; the comparison of mix modelling and incrementality testing explains how test results feed the model, and an Incrementality test on a channel that moves with another is often the only way to separate them. The marginal returns and forecasting guide shows how the resulting curves become spend scenarios, and the comparison of mix modelling and multi-touch attribution explains why this aggregate dataset answers questions that path data cannot. When a model is built from the dataset, the guide to evaluating a marketing mix model sets out what a reviewer should check, starting with the data log. The attribution hub places the model beside attribution and experiments.

Common mistakes

  • Using platform conversions as the outcome. The model then explains the platforms' own credit, not sales.
  • Dating offline spend by invoice. The media appears in the wrong weeks and its carry-over is misread.
  • Mixing net and gross spend. Channels on different bases cannot be compared.
  • Rolling up to weeks before aligning time zones. Every week boundary drifts.
  • Leaving out price and promotions. Their effect is credited to whichever media moved with them.
  • Filling missing weeks with averages. The gap disappears from view and the model learns from invented data.
  • Ignoring channels that move together. No specification can separate what the plan never separated.
  • Skipping the data log. Nobody can review the model, and the next refresh cannot repeat the dataset.

Frequently asked questions

Should I use spend or impressions as the media input?

Spend is the usual starting point because it exists for every channel, offline included, and because the budget decision the model informs is made in money. A delivery measure such as impressions can be better for channels whose prices changed a lot over the history, since the same spend bought very different amounts of media in different years. Many datasets carry both, with the choice recorded in the data log. Whichever is used, it must be measured the same way across the whole history of each channel.

What should I do with weeks where spend data is missing?

Find out whether the media ran. If it did not, the week is a genuine zero and stays one. If it did and the record is missing, rebuild the figure from the best available evidence, such as the agency's booking schedule, the media owner's confirmations or the invoice spread across the weeks the media ran, and record in the data log what was rebuilt and how. Never leave a missing week blank for the modeller to guess, and never fill it with an average that hides the fact that it was missing.

Can I build the dataset from ad platform reports alone?

Only for the media inputs from those platforms, and even then with care, because platform reports carry the platform's own conversion figures, which a mix model should not use as its outcome. The outcome comes from the system of record, such as the order system or finance ledger, and the offline media, promotions, prices and other controls come from sources no ad platform holds. A dataset built from platform reports alone misses the parts of the history that make a mix model worth building.

Terms used on this page

Frameworks

Where this page is used

Part of these learning paths

Also related

Related