I want to talk about the spreadsheet that quietly runs most independent hotels’ marketing budgets. You know the one. It has a column for OTA commissions, a column for your metasearch spend, maybe a row where you guessed at what your direct channel “really” costs, and a vibe-based decision at the bottom about where next quarter’s money goes.
I love that spreadsheet. I also think it is lying to you, and I want to show you how to catch it.
The tool for catching it is marketing mix modeling (MMM). It sounds like something a Fortune 500 with a data science team does. It is not. You can run a useful version of it on a laptop, in a spreadsheet, in an afternoon, with data you already have. Let me show you how, why last-click attribution is the thing you are actually fighting, and how to use the result to claw back more direct bookings and a healthier OTA mix.
Why last-click attribution lies to your hotel
Here is the trap. A guest sees you on Instagram, googles your hotel three days later, reads a review, then clicks an OTA listing because that is the tab that happened to be open, and books. Last-click attribution hands 100 percent of the credit to the OTA. Your Instagram, your content, your reputation work, your branded search? Zero. They get nothing in the ledger.
Now multiply that across a year of decisions. Every channel that assists a booking but does not get the final click looks worthless. So you cut it. So your “proven” channels look even more dominant. So you pour more money into the OTA that was only ever finishing a race that other channels ran. This is exactly the dynamic I unpack in how OTAs quietly intercept your search traffic — the OTA is brilliant at being the last click, which is not the same as being the channel that caused the booking.
Last-click attribution does not measure what works. It measures what happens to be standing closest to the cash register. Those are very different things, and the gap between them is usually money you are handing to the OTAs.
MMM steps back from the click entirely. Instead of asking “which touch came last,” it asks: “over the last year, when I spent more on metasearch, did total bookings go up — controlling for season, price, and everything else?” That question is answerable with math you learned in high school.
What a lightweight hotel MMM actually is
Forget the intimidating version. At its core, a hotel MMM is one regression:
Bookings (or direct revenue) = a baseline + (effect of OTA) + (effect of metasearch) + (effect of direct/SEO) + (effect of season) + (effect of your own price) + noise.
You feed it weekly history. It hands back a coefficient for each channel — a number that says “each extra dollar here was associated with roughly X more dollars of bookings.” That is it. You are not predicting the future to two decimal places. You are getting direction: which channels are pulling their weight, which are coasting on credit they did not earn, and roughly where the next marginal dollar does the most good.
The whole point of MMM for a small hotel is not precision — it is escaping the last-click illusion. Even a rough model that tells you “metasearch is worth 30 percent more than your last-click report claims” will change your budget for the better.
Step 1: Gather the data you already have
You need a weekly table. Twelve months minimum, so about 52 rows. Pull these columns — every one of them lives in a system you already pay for:
- Week (just the Monday date)
- Total bookings or direct revenue — pick one as your outcome
- OTA spend — your actual commission paid that week (bookings times your commission rate; OTA commissions typically run ~15-25%)
- Metasearch spend — Google Hotel Ads, Trivago, etc. (the metasearch playbook for independents covers how to even pull this cleanly)
- Direct/SEO investment — what you spent on content, ads, agency fees, prorated weekly
- Your ADR that week (price is a huge driver — leave it out and the model blames your channels for what was really a rate change)
- Season flag — a simple 1 to 4 for low/shoulder/high/peak, or just a 0/1 for “peak month”
Messy is fine. Estimated is fine. The discipline of writing the numbers down weekly is itself worth more than half of what the model will tell you.
Step 2: Build the model in a spreadsheet
You do not need Python. Google Sheets and Excel both ship with a regression function.
In Google Sheets, LINEST does multiple regression. Lay your outcome (bookings) in one column and your driver columns (OTA spend, metasearch spend, direct spend, ADR, season flag) in adjacent columns, then:
=LINEST(bookings_range, drivers_range, TRUE, TRUE)
It returns a block of numbers. The top row is your coefficients — one per channel, read right-to-left relative to your columns (this ordering trips everyone up the first time, so label carefully). In Excel, the Data Analysis ToolPak gives you the same thing with a friendlier output, including a number called R-squared.
Here is a deliberately illustrative, made-up example of what the output might look like once you tidy it up:
| Channel | Weekly spend (avg) | Model coefficient | Implied return | Last-click said |
|---|---|---|---|---|
| OTA commission | $4,000 | 1.4x | Solid, but lower than it looked | ”Our #1 channel!” |
| Metasearch | $1,200 | 3.1x | Underfunded | ”Meh, few last clicks” |
| Direct / SEO | $2,000 | 2.6x | Quietly excellent | ”Hard to credit” |
| Baseline (brand) | — | large | People who’d book anyway | Stolen by OTA last-click |
Those numbers are invented to show the shape of the insight, not a promise about your property. But the shape is the story I see again and again: the OTA looks less heroic once you stop letting it take credit for demand other channels created, and metasearch plus direct look better than the last-click report ever admitted.
Step 3: Read it without fooling yourself
Three sanity checks before you act on anything:
- Look at R-squared. It tells you how much of your booking variation the model explains. Above ~0.6 is genuinely useful for a small dataset. Below ~0.3, you are probably missing a big driver (usually price or a seasonal event) — add it and rerun. Do not bet the budget on a model that explains a quarter of reality.
- Sniff-test the coefficients. If the model says OTA spend has a negative effect, that usually does not mean OTAs hurt you — it means OTA spend rises in your slow weeks (you lean on them when direct demand is soft), and the model is picking up the correlation backwards. This is the single most common rookie misread. Context matters more than the raw number.
- Watch for multicollinearity. If two channels always move together, the model cannot tell them apart and the coefficients get unstable. Vary your spend deliberately over time and this problem shrinks.
A model you do not understand is more dangerous than no model. If a coefficient surprises you, your first job is to explain why in plain English before you move a single dollar. Half the value of MMM is the questions it forces you to ask about your own business.
Step 4: Turn the model into budget moves
Here is where it pays for itself. The coefficients give you an implied return per channel. You shift money from channels with low marginal return to channels with high marginal return — gradually, not all at once.
In my made-up table above, metasearch is screaming to be funded and the OTA is over-credited. So the move is not “fire the OTAs” — you cannot, and you should not want to. They are real distribution and real demand. The move is to reduce your over-dependence: trim a little OTA-driven discounting, redirect that margin into metasearch and your direct channel, and watch what happens to total bookings over the next quarter. If direct climbs without total bookings falling, you just clawed back commission margin. That is the whole game — a healthier mix, not a fantasy of going OTA-free.
This is exactly the logic behind the book-direct commission math: every booking you move from a 18 percent OTA channel to your own site at a fraction of that cost is pure recovered margin. MMM just tells you which lever to pull to make that shift happen instead of guessing.
And critically — measure again next quarter. MMM is not a one-shot oracle. It is a feedback loop. You make a budget move, you collect twelve more weeks, you rerun, you see if reality agreed with the model. Over a year you build genuine intuition that no attribution dashboard can give you.
A realistic timeline (no magic, no guarantees)
Let me set expectations like a grown-up, because I am not going to promise you a number-one ranking or a revenue jump on a schedule. Nobody honest can.
- Week 1: Build the spreadsheet, pull 12 months of history, run your first
LINEST. You will already spot one obviously mis-credited channel. - Weeks 2-12: Make one or two budget shifts. Keep logging weekly. Resist the urge to change everything at once — you need clean before/after to learn anything.
- Quarter 2: Rerun with fresh data. Now you are comparing the model’s prediction to what actually happened. This is where trust (or healthy skepticism) gets built.
- Ongoing: A quarterly rerun becomes a 30-minute ritual that keeps your budget honest.
The channels MMM points you toward — metasearch, direct, SEO, reputation — are the same ones that compound. They get better with age, unlike a one-off OTA-funded discount. If you want the foundation under the direct-channel side of that, the 2026 hotel SEO starter guide is where I would point you, and the book-direct CRO work is what turns the traffic MMM helps you justify into actual reservations.
Where this fits in the bigger picture
MMM is a measurement discipline. It tells you where to point money. It does not, by itself, build the direct channel it keeps telling you to fund. That is the work — winning your own branded search back from the OTAs, getting your Google Business Profile dialed in, making sure ChatGPT and the AI engines recommend you when travelers ask. (AEO alone gets 27,100 US searches a month now; this stuff is not niche anymore.) MMM is the dashboard. The hotel SEO and AI visibility work is the engine.
But you cannot fund the engine intelligently if your dashboard is last-click. So start with the spreadsheet. Catch the lie. Then move the money toward the channels you actually own.
If you want a second pair of eyes on your channel mix — or you would rather hand the whole modeling-and-reallocation loop to someone who does it for hotels all day — book a free intro call and bring your messy spreadsheet. Messy is exactly where the good insights hide.