Marketing Mix Model in a Spreadsheet: 4 Channels, Adstock Decay, ChatGPT-Written Formulas

It is 9:47 on a Tuesday morning. A DTC (Direct-to-Consumer, 直接面向消费者) brand I'm working with is losing $14K a month and cannot see it. The CMO thinks paid search is their best channel. The platform dashboards agree. I had pulled 26 weeks of weekly spend and revenue for paid search, paid social, display, and email the night before, pasted them into a Google Sheet, and asked ChatGPT to write the adstock formulas. By 12:55 PM we had a model that said: paid social is doing the work, display is decaying almost immediately, and they are over-spending on branded search by 18%. The CMO stared at the screen. "How is this not what Google Ads shows me?" Because Google Ads cannot see email, paid social, or display — it can only see Google Ads.

This is the whole point of a Marketing Mix Model (MMM): a single, channel-agnostic view of what is actually driving revenue, built from your own data, in a tool you already have. The vendors charge $40K/year for the same answer. The math has not changed in 30 years. ChatGPT writes 80% of the formulas for you. You need a Google account and three hours.

This post walks through the build, the formulas ChatGPT wrote for me, and the sensitivity test that exposed the $14K/month. No vendor, no agency, no PhD required.

What MMM actually does (and what it does not)

An MMM is a regression model that estimates how much each marketing channel contributes to revenue, after accounting for the time it takes for an ad's effect to fade. The two ideas that separate a real MMM from a naive "what was the last click" model:

1. Adstock (广告残留). A paid social impression you saw on Monday can still be nudging you toward a purchase on Sunday. The effect decays gradually. The standard formula is a geometric decay: Adstock(t) = Media(t) + λ × Adstock(t-1). Lambda is the retention rate — 0.3 means each week's effect is 30% of the prior week's.
2. Diminishing returns. Doubling your paid search spend does not double the revenue. Log-transform your spend: revenue ~ log(spend) is a textbook proxy for saturation that fits in one column.

That is the whole intellectual payload. Everything else is plumbing.

What you need before opening the sheet

• 26 weeks of weekly data, minimum. Anything under 12 weeks and the regression will be unstable. Pull from each platform's API: weekly spend per channel and weekly revenue (or conversions, or AOV × order count — pick one KPI and stick to it).
• One KPI. I use weekly new-customer revenue, not blended revenue. New-customer revenue is what the channels are actually creating. Blended revenue is contaminated by repeat purchases the channels did not cause.
• Four channels. Paid search, paid social, display, email. Stop there. The math degrades fast past six.
• One Google Sheet, one ChatGPT tab, three hours.

For the DTC client we used: 26 weeks of weekly spend from each platform's export, weekly new-customer revenue from Shopify. Total build time: 3 hours 8 minutes, including one wrong turn and one coffee refill.

The build, step by step

Step 1 — Lay out the data

Five columns: week, paid_search_spend, paid_social_spend, display_spend, email_spend, revenue. One row per week, twenty-six rows. No formulas yet.

Step 2 — Ask ChatGPT to write the adstock formula

Open ChatGPT (or Claude, or Gemini — the model does not matter much for this). Paste:

"I have weekly marketing spend in column B, starting at B2. Write a Google Sheets formula for column G that calculates adstock with a retention rate (lambda) of 0.4, where the formula references the cell in G above it, starting with the value in B2 for the first row."

ChatGPT will return something like:

=IF(ROW()=2, B2, B2 + 0.4 * G1)

Copy it down 26 rows. That is the paid search adstock. Repeat for the other three channels with different lambdas.

Step 3 — Pick your lambdas

This is the only place judgment matters. A starting set that works for most ecommerce brands:

Channel	Lambda	Half-life
Paid search	0.2	~0.4 weeks (effect fades in 3-4 days)
Paid social	0.3	~0.6 weeks
Display	0.1	~0.1 weeks (off after 1 week)
Email	0.4	~0.8 weeks (a good subject line pulls for 5-6 days)

The "right" lambdas are an industry debate you can lose yourself in. For a first pass, these are close enough. You will sensitivity-test them in Step 6.

Step 4 — Log-transform the adstocked spend

Add four columns: =LN(G2), =LN(H2), =LN(I2), =LN(J2). This is the diminishing-returns trick. A linear regression on log(spend) implies a curve that flattens as spend grows.

Step 5 — Run the regression

Google Sheets has a built-in LINEST function. In a separate tab:

=LINEST(revenue_range, log_adstock_range, TRUE, TRUE)

The TRUE, TRUE flags give you the intercept, the coefficients for each channel, and the standard errors.

The output for our client:

Channel	Coefficient	Interpretation
Paid search	$1.42	Each $1 of adstocked search adds ~$1.42 of revenue
Paid social	$2.87	Each $1 of adstocked social adds ~$2.87 of revenue
Display	$0.31	Each $1 of adstocked display adds ~$0.31
Email	$4.15	Each $1 of adstocked email adds ~$4.15

A coefficient above $1.0 means positive ROI. Below $1.0 means you are losing money on the marginal dollar. Display, in this case, is destroying money. That was the $14K/month waste — display had a 1.2x ROAS (Return on Ad Spend, 广告支出回报率) according to the platform, but only 0.31x in the MMM. The difference: display was getting credit for any conversion that happened within 24 hours of an impression, regardless of what else was actually closing the sale. The MMM stripped that out.

Step 6 — Sensitivity test ±20%

This is the part most teams skip, and the part that matters most. The lambdas are guesses. A coefficient of $2.87 paid social is meaningless if it swings to $0.40 when you nudge lambda from 0.3 to 0.36.

I ask ChatGPT to build a small sensitivity table:

"Build a Google Sheet table that shows the paid social coefficient under 5 lambda values: 0.1, 0.2, 0.3, 0.4, 0.5. Use LINEST on the recalculated adstock for each."

For the client, paid social stayed above $2.20, email above $3.50, display below $0.60 across all five lambdas. The conclusion is robust to the guesses. A channel that flips from "great" to "terrible" when you nudge a parameter is overfit. A channel that holds its sign and order of magnitude across the ±20% is telling you something you can act on.

The four things the spreadsheet MMM will not tell you

• It cannot separate creative from channel. A 2x lift in paid social might be a single viral creative. The model attributes the lift to the channel.
• It is correlation, not incrementality. A coefficient of $2.87 does not prove paid social caused $2.87. It proves correlation after the adstock transform. The gold standard for causation is a holdout test (关停测试): pause the channel for 2 weeks and measure the delta.
• It assumes stable lambdas. Adstock decay shifts with seasonality, creative fatigue, and competitor activity. A monthly refresh keeps the model honest.
• It collapses to weekly granularity. Daily data is more powerful; you would need BigQuery or Python. For a first pass, weekly is fine.

The version I would build today

1. Pull 26 weeks of weekly spend from each platform's export. One sheet, one KPI column.
2. Ask ChatGPT to write the adstock and LINEST formulas. Paste them in. Refine if needed.
3. Run the regression. Read the coefficients in plain English.
4. Sensitivity test ±20% on each lambda. Drop any channel that flips sign.
5. Run a 2-week holdout on the weakest channel (display, in the client case). If revenue holds, kill the spend. If revenue drops, the model was right — go scale the strong channels.
6. Refresh monthly. New weeks in, new coefficients out.

The full build for our client was 3 hours. The decision that came out of it: kill display ($14K/month saved), reallocate to paid social and email (combined coefficient $7.02, paid search $1.42). Projected 90-day lift: 22% revenue at flat spend. The CMO's reaction: "Why did I not have this 12 months ago?" Because Google Ads will never tell you to spend less on Google Ads. A spreadsheet will.

The contrarian ending: an MMM in a Google Sheet is not a "lite" version of a vendor MMM. For a brand under $50M/year in revenue, it is the better version. It is faster, auditable, and will tell you to cut the channel that pays your vendor's bill. Build it. Sensitivity test it. Then act on it.