How to build a simple marketing mix model in a spreadsheet

by

TL;DR

You can build a functional marketing mix model using Google Sheets or Microsoft Excel with weekly spend and revenue data, adstock transformations, and a multiple linear regression. The whole process takes three to five hours if your data is already reasonably clean. You need at least 52 weeks of historical data, a spreadsheet with regression capability, and enough formula confidence to build a named range.

What You Need Before You Start

  • Weekly data, minimum 52 rows. Monthly data works in a pinch but gives you too few observations for reliable coefficients. Weekly is the standard.
  • Revenue or conversions as your dependent variable. Pick one metric and stick to it. Do not mix revenue weeks with conversion weeks.
  • Spend by channel. Separate columns for each paid channel: paid search, paid social, display, TV, email, and so on. Organic traffic or brand search can go in as a control variable, not a spend variable.
  • Macro controls. At minimum: a seasonality index (or month dummies), a promotional flag (1/0 for sale weeks), and a price index if your product pricing shifts.
  • Google Sheets (free) with the Analysis ToolPak, or Excel (Microsoft 365, any tier) with the Data Analysis add-in enabled.
  • Optional: Coefficient or Lido for pulling ad platform data directly into your sheet without copy-paste.
  • A basic understanding of what R-squared means. You do not need to understand the math behind ordinary least squares, but you should know that R-squared closer to 1.0 is better fit.

Step 1: Pull Your Raw Data Into One Tab

Create a tab called raw_data. Each row is one week. Your columns should run left to right: week_start, revenue, spend_search, spend_social, spend_display, spend_tv, promo_flag, price_index.

Pull spend from your ad platforms. Google Ads, Meta Ads Manager, and DV360 all let you export weekly spend as CSV. Paste each export into a separate holding tab, then use VLOOKUP or QUERY to join them onto your raw_data tab by week. Never type spend numbers by hand. Copy-paste errors compound across 52 rows in ways you will not catch until the model looks wrong.

=IFERROR(VLOOKUP(A2, meta_spend!$A:$B, 2, FALSE), 0)

Use IFERROR(..., 0) so missing weeks populate as zero rather than breaking your regression later.

You should now see one clean table with no blank cells, no text in numeric columns, and dates formatted consistently (YYYY-MM-DD works everywhere).

Step 2: Set Up Your Modelling Tab

Create a second tab called model_data. Copy your week_start column over. Then reference each column from raw_data rather than pasting values. This keeps the two tabs linked so a data correction in raw_data flows through automatically.

At the top of the tab, create a small parameter block, rows 1 to 5, with labels in column A and values in column B. You will use these for adstock decay rates in the next step.

B1: decay_search   = 0.3
B2: decay_social   = 0.5
B3: decay_display  = 0.4
B4: decay_tv       = 0.7

Name these cells. In Google Sheets: select B1, then Data > Named ranges > call it decay_search. Do the same for each. Named ranges make your adstock formulas readable instead of cryptic cell references.

You should now see a clean modelling tab where all data traces back to raw_data and four named parameter cells sit at the top.

Step 3: Apply Adstock Transformations

Adstock captures the carry-over effect of advertising. A TV spot run in week 3 still influences sales in week 4 and week 5. Without adstock, your regression will underestimate TV’s contribution and misattribute it to channels that ran concurrently.

The geometric adstock formula for row n is:

adstock_n = spend_n + decay * adstock_(n-1)

In Google Sheets, your first row (row 2 in the sheet, week 1 of data) has no prior week, so set it equal to spend directly:

=C2

Every subsequent row uses:

=C3 + decay_search * D2

Where column C is raw search spend and column D is the adstock column for search. Drag this formula down for all 52+ rows. Repeat the process for each channel using its own decay parameter.

Start with the decay values in your parameter block. You will tune them in step 6 after seeing the regression output. For now, 0.3 for digital channels and 0.6 to 0.8 for TV are reasonable starting points. See our guide to choosing adstock decay rates for deeper detail.

You should now see adstock columns that start high in weeks with big spend and tail off gradually in quiet weeks, rather than dropping straight to zero.

Step 4: Add Control Variables

Controls are variables that explain revenue variance but are not spend. Without them, your spend coefficients absorb unexplained variation and become inflated or sign-flipped.

Add these columns to model_data:

  • promo_flag: 1 in promotional weeks, 0 otherwise.
  • price_index: your average selling price that week divided by your base price. If pricing is constant, skip this.
  • trend: a simple counter, 1 through N. This catches long-run organic growth or decline.
  • month dummies or a seasonality index: either 11 binary columns for months (dropping one to avoid the dummy variable trap) or a single index column derived from prior-year sales if you have two years of data.

Keep it simple at first. Four to eight total predictors is fine for a first model. More variables require more data to estimate reliably.

You should now see your model_data tab with adstock columns plus a control section, all numeric, no blanks, ready for regression.

Step 5: Run the Regression

In Excel, go to Data > Data Analysis > Regression. Set your revenue column as the Y range and all adstock plus control columns as the X range. Check the Labels box if you included headers. Output to a new sheet.

In Google Sheets, install the XLMiner Analysis ToolPak add-on (free). Then Extensions > XLMiner Analysis ToolPak > Regression. The interface mirrors Excel’s Data Analysis tool almost exactly.

Select all your X variables together as a contiguous block. If your columns are not contiguous, move them so they are before running. The built-in regression tools do not handle non-contiguous X ranges gracefully.

Y range:  model_data!$B$1:$B$53
X range:  model_data!$D$1:$L$53

Tick “Labels” and “Confidence Level 95%”. Click OK.

You should now see a regression output sheet with a summary table showing R-squared, an ANOVA table, and a coefficients table with one row per predictor.

Step 6: Read and Tune Your Coefficients

Look at the coefficients table first. Each spend channel shows a coefficient that represents the revenue generated per unit of adstock spend. A search coefficient of 4.2 means each dollar of adstock-adjusted search spend is associated with $4.20 in revenue.

Check three things before trusting any coefficient:

  1. Sign. Every spend coefficient should be positive. A negative spend coefficient means something is wrong: multicollinearity, a bad decay rate, or a missing control.
  2. P-value. Coefficients with p-value above 0.1 are not reliably different from zero. Consider dropping that variable or reviewing your data.
  3. R-squared. Aim for 0.75 or higher. Below 0.6 usually means you are missing an important control variable.

Go back to your decay parameters and adjust them if coefficients look wrong. Raising the decay for a channel increases its adstock values, which typically lowers its coefficient. You are looking for the decay value that gives each channel a plausible, positive, statistically significant coefficient. This is iterative. Run the regression, adjust, run again. Three to five rounds is normal.

You should now see a coefficient table where all spend channels show positive values, most p-values sit below 0.05, and R-squared is above 0.70.

Step 7: Build a Channel Contribution Breakdown

Create a tab called contributions. For each week, multiply each channel’s adstock column by its regression coefficient. This gives you the revenue contribution attributable to that channel in that week.

search_contribution = adstock_search * coeff_search
social_contribution = adstock_social * coeff_social

Sum all channel contributions plus the intercept to get your model-fitted revenue. Compare that to actual revenue. The closer they track, the more confident you can be in the model.

Then sum each channel’s contributions across all weeks and divide by total spend in that channel over the same period. That is your channel-level ROI.

ROI_search = SUM(search_contribution) / SUM(spend_search)

This is the number your media team cares about. It tells you how many dollars of revenue each channel drove per dollar spent. For an internal link on interpreting these outputs, see how to read marketing attribution reports.

You should now see a summary row of channel ROIs that you can present without a statistics degree in the room.

Step 8: Build a Budget Simulation Tab

Create a tab called optimizer. Set up a row for each channel with three columns: current spend, proposed spend, and projected contribution.

Projected contribution uses your coefficient and adstock logic to estimate what a change in spend would produce. For simplicity, use a linear approximation: proposed spend multiplied by the channel’s ROI from step 7.

projected_contribution = proposed_spend * ROI_search

Add a total row. As you change proposed spend values, the total projected revenue updates. This is your “what if” tool for budget conversations. It is not a full optimizer but it gives you a defensible starting point for reallocating budget from low-ROI to high-ROI channels.

Lock the total proposed budget cell to your actual total budget so you are redistributing, not expanding.

You should now see a simulator where shifting spend between channels shows projected revenue impact in real time.

Step 9: Sanity-Check With a Holdout Period

Hold back the last four to eight weeks of data when building the model. After fitting on the training period, apply your coefficients to the holdout period’s adstock inputs and compare predicted revenue to actual revenue.

If your predictions land within 10 to 15 percent of actuals, your model is usable. A larger error suggests overfitting or a missing variable. This step is the difference between a model you can present and one that embarrasses you in a quarterly review.

Calculate mean absolute percentage error (MAPE) across the holdout weeks:

=AVERAGE(ABS((actual - predicted) / actual))

Format as a percentage. Below 12 percent is solid for a spreadsheet MMM.

You should now see a MAPE figure that tells you how much to trust your budget simulator.

Common Mistakes To Avoid

  • Using monthly data with fewer than 24 rows. You need degrees of freedom. Monthly data with eight channels and twelve months gives you four degrees of freedom, which is nowhere near enough for reliable estimates.
  • Forgetting adstock on TV and out-of-home. Digital-only adstock models consistently overfit to digital and undervalue brand channels. Even if you have no TV data, flag the weeks competitors ran heavy TV.
  • Including revenue as a predictor. Some people accidentally include lagged revenue as an X variable. That introduces data leakage and inflates R-squared artificially.
  • Interpreting R-squared as accuracy. A high R-squared does not mean your channel splits are right. You can fit revenue well with wrong coefficients if two channels are correlated.
  • Not controlling for price. If your average order value fluctuates, uncontrolled price variation gets absorbed into your spend coefficients. A sale week that drops price by 20 percent will look like a massive social win if you ran social that week.
  • Running the model once and treating it as permanent. Media mix models go stale. Markets shift, competitors enter, creative effectiveness changes. Rebuild or at least revalidate every quarter.

When To Level Up

A spreadsheet MMM works well for one to two years of weekly data, four to eight channels, and a team that runs monthly budget reviews. Once any of those conditions breaks, the spreadsheet becomes a liability.

Specifically, move to a proper tool when you hit these walls:

  • You have more than ten channels and the regression starts giving sign-flipped or near-zero coefficients even after tuning.
  • You want to model diminishing returns properly, which requires non-linear curves that are painful to implement in a sheet.
  • Your stakeholders want scenario planning with confidence intervals, not point estimates.
  • You need to rerun the model weekly with automated data pulls.

At that point, look at purpose-built tools like Robyn (open-source, R-based), Meridian (Google’s open-source Python library), or commercial platforms like Analytic Edge. The data analysis tools category on this site covers several of these in detail, including when the free options are genuinely sufficient.

Frequently Asked Questions

How much data do I actually need to build a marketing mix model in a spreadsheet?
The practical minimum is 52 weeks if you have five or fewer channels. More channels need more rows: add roughly 10 weeks per additional predictor variable. Two years of weekly data is comfortable for most small-to-medium businesses.

Do I need statistical software or can Google Sheets really handle this?
Google Sheets with XLMiner Analysis ToolPak handles ordinary least squares regression fine for datasets under 1,000 rows and 15 predictors. For anything larger or if you want Bayesian estimation, you will need Python or R.

What decay rate should I use for adstock?
Start with 0.3 to 0.5 for digital channels and 0.6 to 0.8 for TV and out-of-home. Then iterate: adjust the decay, rerun the regression, and check whether coefficients improve in sign and significance. There is no universal right answer.

Can I use this model to justify a budget increase to my CFO?
Yes, with appropriate caveats. Present the model MAPE (your holdout accuracy), note the data period it covers, and frame contributions as estimates with a range rather than precise figures. CFOs respond well to “our model suggests search delivers $4 to $5 for every $1 spent, with 11 percent prediction error on holdout data.”

My spend coefficient came out negative. What went wrong?
The most common causes are multicollinearity (two channels that always move together), a missing control variable, or a decay rate that is too high or too low. Try removing one of the correlated channels, adding a trend variable, or halving your decay parameter and re-running.

Bottom Line

Building a spreadsheet marketing mix model is a two-day project, not a two-month one. You pull weekly spend and revenue data, apply geometric adstock to capture carry-over effects, add a handful of control variables for seasonality and promotions, run a multiple linear regression, and read channel ROI from the coefficients. The budget simulator tab turns those coefficients into a practical tool your team can use every quarter without touching the model itself. Accuracy is imperfect by design: this is a directional tool, not a crystal ball. A MAPE below 12 percent on holdout data is a reasonable target for a first version. Once you have run it through one budget cycle and refined your decay rates, you will have something defensible and repeatable. When you outgrow it, the data analysis tools category has reviews of the open-source and commercial options waiting for you.