Shredded Wheat or Cap’n Crunch: Making Tough Decisions In Allocating Online Ad Spend

Marketing Budget


When it comes to deciding where to spend your online ad budget these days, do you ever feel overwhelmed with choices? Just take a look at the latest LUMAscapes for Social, Search, or Display. It’s like you’re walking down the cereal aisle of a supermarket — from healthy bran flakes to sugary, frosted, fruity loops, there’s such a vast and varied array of options, it can make your head spin.

So Many Choices, So Little Time

Sure, there are your obvious indulgences like Cap’n Crunch (aka Facebook or Twitter), but somehow you know you’ll find yourself binge eating three boxes later that night with nothing but a belly full of calories to show for it. There’s always the healthy stuff like Shredded Wheat (aka Google), which you know is good for you, but how much fiber can one man eat, really?

Then there are the new kids on the block — some new-fangled oat-cluster-marshmallow-celebrity-endorsed concoction that catches your eye, but you know won’t be there in a year’s time (remember Mr. T Cereal? Or Ask Jeeves?). Or maybe you just go with the cheaper stuff and buy store brand cereal (aka Bing) — half the price and pretty much the same thing. But, am I really going to sit down with a bowl of “Honey Nut Scooters”? Who does that?

The fact is, deciding how to allocate your online ad budget is a difficult and confusing exercise. With so many channels and vendors, it can be nearly impossible to spend effectively or efficiently. While every marketer understands the basic premise that you should invest more where there is a higher return, just exactly how much do you invest relative to everything else? If you’re faced with two programs that achieve, say, a 50% ROI and a 30% ROI, respectively, how much do you invest in each?

Creating An Analytical Allocation Model

daily cost efficiency

A good way to tackle this problem is by mapping out daily cost efficiency and performing some basic regression analysis for each online advertising channel you are considering. Now, for something that appeals to those who love statistics, let me walk you through the process.

1. Gather some data points:

  • Cost by day by channel
  • Conversion Metric (e.g., lead volume) by day by channel

2. Create a scatter plot for each channel, plotting Cost by your Conversion Metric.

So one plot may be Cost vs. Lead Volume for Google Search, and another may be the same plot but for Google Content. You may have dozens of these plots, depending on how many channels you’re invested in.

3. Fit a logarithmic trendline to each plot.

4. Determine the equation of each trendline, as well as the R-squared value.

  • Note: The R-squared value is a measure of how closely your trendline fits the data. R-squared values will always be between 0 and 1, with higher values indicating a more reliable trendline.

5. Once you have a trendline equation for each advertising channel, you can leverage Excel’s Solver tool to find the optimal budget allocation in order to maximize your Conversion Metric.

  • Create a table with two columns: Cost and Conversion Metric.
  • Each row will represent an advertising channel.
  • In the Conversion Metric column, insert your trendline equations. Cost is your “x” value, and Conversion Metric is your “y” value.
  • At the bottom of the table, create a “Total” row that sums the rest of your rows.
  • Open up the Solver Add-In and use it to solve for the empty Cost cells for each row. The options you will choose will be to maximize the sum of your Conversion Metric and limit Cost by your total budget.

solver add in 1 solver add in 2

Essentially, this is finding the point on each spending curve where the marginal return of incremental investment is the same for each channel (in other words, the slopes of the curves are the same). For those of you comfortable with mathematics, this means you can also solve this yourself by taking the derivative of each equation and finding the spending level that sets them all equal.

The Model Is Just The Beginning

The Cost values that Solver spits out are essentially your optimal daily spend by advertising channel. These spend values will, theoretically, maximize your overall Conversion Metric. You are now primed to allocate your online ad budget most efficiently, and as you continue to gather more data, your model will become more and more accurate. How awesome is that?

While the model I just outlined isn’t perfect — it doesn’t factor in inter-channel relationships or other more qualitative aspects like brand value — it’s a great starting point and is rooted deeply in data. The next time you’re feeling overwhelmed by the myriad channels of online advertising, you can look to your model, which operates based on the facts, rather than simple intuition. Now you can truly understand how much to allocate to a program that achieves 50% ROI versus another that achieves 30%, which only helps define your overall marketing analytics.

Let us know if this model works for you, or if you’ve got other ways of effectively allocating your online ad budget. Hopefully this model helps you to budget more efficiently, and at the end of the day, you can devote some of your savings to more worthwhile endeavors … like breakfast for your team? Sounds grrrreat!