How to Create a Media Mix Model for Budget Allocation

# How to Create a Media Mix Model for Budget Allocation

For many Brisbane business owners, deciding where to spend your marketing budget can feel like a guessing game. A Media Mix Model (MMM) takes the guesswork out of the equation by using historical data to quantify how different marketing channels—like Google Ads, Facebook, or local radio—contribute to your bottom line, allowing you to allocate your budget with surgical precision.

Why Media Mix Modelling Matters

In an era where privacy changes (like iOS 14) have made digital tracking less reliable, MMM provides a "big picture" view. It doesn't rely on individual user tracking; instead, it looks at the correlation between your spending and your sales. This helps you understand the true impact of your offline and online efforts combined, ensuring you aren't over-investing in one channel while neglecting another that actually drives growth.

---

Prerequisites: What You’ll Need

Before you begin, gather the following data (ideally covering the last 24 months to account for seasonality):

• Sales Data: Weekly or monthly revenue figures (from Xero, Shopify, or your POS).
• Marketing Spend: Broken down by channel (Google Ads, Meta, LinkedIn, Local Print, etc.).
• External Factors: Data on public holidays (like Ekka Wednesday), local weather patterns, or economic shifts (like RBA interest rate changes).
• Tooling: Microsoft Excel or Google Sheets (Advanced users may use Python or R).

---

Step 1: Define Your Objective and Kpis

Before touching a spreadsheet, decide what "success" looks like. Are you trying to maximise total revenue, or are you focused on lead generation? For most Australian SMEs, the goal is Return on Ad Spend (ROAS).

• What you should see: A clear document stating your primary goal (e.g., "Maximise total sales revenue while keeping CPL under $50").

Step 2: Aggregate Your Data into a Master Sheet

Create a spreadsheet where each row represents a time period (we recommend weekly for accuracy) and each column represents a variable.

• Column A: Week Starting Date.
• Column B: Total Sales (Your dependent variable).
• Column C-F: Spend per channel (Google, Facebook, etc.).
• Column G-I: Non-marketing variables (Average temperature, school holidays).

Step 3: Account for Lag Effects

Marketing doesn't always result in an immediate sale. A customer might see a Facebook ad on Monday but not buy until Thursday. In MMM, this is called the "Adstock effect."

In your spreadsheet, you can create a simple decay formula to account for this.

Step 4: Normalise Your Data

Ensure all your currency is in AUD and that your time increments are consistent. If you have a massive spike in sales due to an EOFY sale, make a note of it in a separate "Promotions" column so the model doesn't mistakenly attribute that growth to regular ad spend.

Step 5: Run a Multiple Linear Regression

This sounds technical, but Excel makes it easy. Go to Data > Data Analysis > Regression.

• Input Y Range: Your Sales/Revenue column.
• Input X Range: All your Marketing Spend and External Factor columns.
• What you should see: A pop-up window asking for your input ranges. Ensure you include the labels in your selection.

Step 6: Analyse the R-Squared Value

Once Excel generates the report, look for the "R-Squared" value. This tells you how much of the variation in your sales is explained by your marketing spend.

• Target: An R-Squared of 0.70 to 0.90 is generally considered good for a small business model.

Step 7: Identify the Coefficients

The regression output will give you "Coefficients" for each channel. This is the gold. A coefficient of 5.0 for Google Ads means that for every $1 spent, you are seeing a $5 return, holding all other factors constant.

Step 8: Calculate the Marginal Return

Not every dollar spent performs the same. As you spend more, you eventually hit "diminishing returns." Use your coefficients to see which channel has the most room to grow before the return starts to drop.

Step 9: Factor in External Australian Context

Australian businesses are heavily influenced by the calendar. Ensure your model accounts for:

• Public Holidays: Easter, Anzac Day, and Christmas.
• The Fiscal Year: Spend often spikes in June (EOFY).
• Seasonality: If you sell air conditioners in Brisbane, your summer spend should be weighted differently than your winter spend.

Step 10: Create an Allocation Scenario

Using your coefficients, create a "What-if" calculator. If you shift $2,000 from Facebook to Google Ads, what does the model predict will happen to your total revenue?

Step 11: Validate with a Small Test

Don't move your entire budget at once. If your model suggests Google Ads is undervalued, increase that budget by 20% for one month and monitor if the actual sales growth matches your model’s prediction.

Step 12: Visualise and Present

Create a simple bar chart showing "Current Spend" vs "Optimised Spend." This helps stakeholders (or your business partner) see the potential uplift clearly.

---

Common Mistakes to Avoid

• Ignoring the Baseline: Some sales will happen even if you spend $0 on marketing (organic word of mouth). Your model must identify this "Baseline" so you don't over-attribute success to ads.
• Data Overload: Don't try to track 50 different variables. Start with your top 3-4 channels.
• Short-Term Thinking: Only using 3 months of data won't account for the quarterly cycles of the Australian market.

Troubleshooting

• "My R-Squared is very low (under 0.5)": This usually means you are missing a major factor. Did a competitor open across the street? Did you have a website outage?
• "One channel has a negative coefficient": This doesn't mean the channel is losing money; it usually means there is "Multicollinearity" (e.g., you always increase Facebook spend at the same time as Google spend, so the model can't tell them apart). Try testing them at different times.

Next Steps

Once you have your first Media Mix Model, the work isn't over. You should update your data monthly to refine the accuracy.

If you find the data analysis overwhelming or you want a professional team to build a custom attribution model for your Brisbane business, we can help. Contact the Local Marketing Group team today for a strategy session on optimising your ad spend.