Feb 112007

Little Known Way To Determine How Much To Advertise

Ever ask, "How much more or how much less should I be spending on advertising?" Here's a simple model based on the square root rule to help answer that question, along with an Excel spreadsheet to help with the arithmetic. (To skip equations, click here for the applied section.)

The square root "rule" says sales, S, increases with the square root of advertising, A, like so:


In symbols:


where k is a calibration constant, determined from an actual observation of sales and advertising (S0, A0).

If your effective profit margin (defined as 1 minus cost-of-goods percentage minus variable selling expense percentage) is m, we get this formula for profit P as a function of advertising spend A :




Of course, this toy model is not correct.

The only way to determine the actual relationship between sales and advertising is empirically, through testing.

Nonetheless, assuming the relationship follows these equations provides some insight.

Strengths of this model:

  • Decreasing returns to scale. The slope of the curve get less steep (formally, its second derivative is negative). This means the more you advertise, the more you suffer decreasing returns to scale. Because you buy the best advertising first (or at least try your hardest to do so), the more advertising you buy, the less effective each incremental advertising chunk is at producing sales. Real life works this way, and the model captures that.
  • Self-consistency. The model's predictions are internally consistent for any A_0.
  • Theoretical basis. This equation is a specific case of the well-studied Cobb-Douglas production function from economics.
  • Simplicity. Because of the model's simple form, we can solve for the optimal advertising level which maximizes profit.

Weaknesses of this model:

  • Smoothness. The model says the relationship between sales and advertising is smooth. Not so in real life -- you buy advertising in chunks (channels, media, spots, insertions, campaigns, keywords) with different inherent degrees of "chunkiness" and differing performance. For example, you can't buy 4/5ths of a Superbowl ad.
  • Scope. The model says you can always buy more sales with more advertising, on up to infinity. Not so in real life -- at some point, you've tapped out the market of potential buyers, and additional advertising returns nada.
  • Objective. Optimizing P(A) will maximize operating profits. While most catalogers and direct marketers run their advertising so as to generate maximum profit, many other marketers do not, instead intentionally choosing to "over-advertise" to increase brand awareness or drive top line sales. All valid and good, just be aware this model and spreadsheet adopts the direct marketing perspective and aims to maximize bottom line.

We can maximize profits, P(A), by differentiating and setting the derivitive equal to zero. This gives up the optimal level of advertising, Amax, which coincidentally is also the optimal profit, Pmax.



Under this model, the optimal A/S is


To offer some intuition for this, if your effective profit margin is m cents per sales dollar, you maximize your operating profit (in dollar terms, not percentage terms) by sinking half of your effective profit margin (in percentage terms) in marketing, keeping the other half as contribution towards profit.

Again, this is a highly simplified model for advertising returns to scale. Use this only as starting point.

The only certain way to determine your optimal advertising level is through careful testing of the A vs. S elasticity curve for your business.

Enough Math! How Much Should I Advertise?

We've prepared an Excel spreadsheet that handles all this arithmetic.

To use this model, change the gray shaded cells. Enter your ad cost for a recent campaign in cell C8 and the corresponding tracked sales in C9. Enter your average cost-of-goods sold, expressed as a percentage of net sales, in C10. Enter your other variable selling expenses (credit card discount, shipping subsidy, pick and pack cost, dunnage, etc), again as a percentage of net sales, in C11.

The model will give you a base P&L for your campaign. It will also estimate what would have occurred had your advertising been 30% higher or 30% lower. And it will estimate the advertising level that would have maximized your operating profit.

For an example, I loaded the model with the numbers from Kevin Hillstrom's December post on the square root rule. Kevin gave a (made-up?) example about about a CFO who stormed in on the online marketing team demanding they cut their budget by 10%. With the P&L Kevin provided ($10mil sales, $2mil advertising, 60% COGS, 13% variable), Kevin notes the CFO was likely right -- the last 10% of budget wasn't pulling in enough sales to justify it. Cutting $200k from the ad budget might bump the bottom line up by $60k.

The spreadsheet goes further, suggesting that, in this made-up scenario, profits would be maximized by cutting the budget in half. (Slow down there, cowboy!)

Take some care before slashing (or, alternatively, before heavily increasing) an ad budget by such a large factor based on a simple model. The model could be wrong several ways. The assumptions of the model might be incorrect: your actual sales vs. advertising curve might not be a smooth square root curve. Your advertising team might not be able to identify the worst performing corners of the advertising budget to cut. (If that's the case, you may have other problems.) And your goal might not be maximum online sales: you could be advertising to drive your top line, to build brand, or to increase store traffic.

On the other hand, suppose the online marketing team from Kevin's example was generating the same $10mil in sales (still with 60% COGS and 13% other variable) but was achieving that with a $1.2mil budget (12% A/S), rather than the $2mil budget described (20% A/S). In that case, the model suggests the team is underadvertising to the tune of $300k, and that increasing the ad spend to $1.5mil would increase operating profits by $20k and sales by $1.25 mil. In this case, the CFO could rant that the marketing team was being too conservative.

Before making significant changes to your ad spend, use careful testing to determine your true elasticity between advertising and sales.

This simple model can start that ball rolling by giving you some sense of the direction and scale to test.

Thanks to Kevin Hillstrom for posts on the "square root rule" in December and in January, to Roger Cortesi for his LaTex to image rendering page, and for Peter Newbury and Joost Winne for LaTex cheat sheets.


17 Responses to "Little Known Way To Determine How Much To Advertise"
ray says:
nice blog
Crhiss says:
Sorry for the last comment . Just wanted to say how useful this article is :)
Patrick says:
Nice model. What I'm wondering however is what type of net sales you should include. I believe these should be the sales resulting from the CPC-advertising, right?
Alan says:
You can use this toy model in any channel where you can sales definitively tie sales back to an advertisement or promotion using a tracking code. Here, the context is paid search -- so yes, in that case, only include sales and costs from your PPC campaigns -- but the model is more general. Again, this model is a starting point -- only live tests and/or careful analysis of historic data can really provide the elasticity of advertising vs. sales. Nonetheless, simple and often useful in a back-of-the-napkin sort of way.
Jim says:
I love the statistical basis of your systems and it makes total sense to me. The one piece of the puzzle that is missing for me is how to use limited data to forecast actual values. For example, if I have 2 sales for a keyword, I know that's not enough to be statistically significant. How many sales do I need to determine my sales per click? Until I get those conversions, do I use the sales per click at a higher level (like sales per click for all keywords from that search engine)? What would you recommend as a good source to educate myself on how to forecast these values and apply it to my campaigns?
Hey Jim, Great question. What you're really trying to do is figure out the value of the traffic from a given keyword. The metric for this is usually Sales ($) per Click. If you know what sales per click you'll get on average, you know what you can afford to spend per click. That's tricky with low traffic keywords, but you can't just look for a certain number of orders, you have to keep an eye on clicks too. If the two orders you're talking about came after 2,000 clicks I'd say you have pretty good reason to believe the conversion rate is in the 0.1% range (lousy). If those two conversions came on 8 clicks, you'd have pretty good reason to believe the keyword is going to be tremendously efficient; how efficient is hard to say because there is so little data, but the system needs to recognize that the odds of that 8 click, 2 order keyword being "well above average" are very very good. In general, we'd advocate clustering performance of low traffic terms with their closest cousins to get to statistically significant levels. Broaden the definition of a cousin only as much as you need to to reach that point, and then set bids according to the aggregated performance of the group. Good luck!
Jim says:
Interesting. Looking at it logically, I thought it would something along the lines of what you said (although I didn't have as good of a term as "clustering"). One of the questions that raises is how much you support "lesser value" keywords with higher value keywords if you're doing clustering. For example, if you need to cluster 2 (for simplicity sake) keywords to get a more accurate value, when keyword A has a SPC of $2 and keyword A has a SPC of $1 and they have the same number of clicks and sales, so you get an average SPC for the cluster of $1.50. Do you bring keywords bid down to use the $1.50 or do you keep it up at $2, if you have a pretty good sample set so you know that data is accurate enough? I know I'm throwing up a bunch of curveballs with it and I'm not looking for an answer in the comments but I'd love to see a post about similar topic(s) in the future here. You guys have some great stuff and I love seeing new posts. I also might be a bit biased because I love the city you're based out of - I'm a wahoo alumn.
Hi Jim -- Yes, PPC bidding is a difficult problem. (It is also hard, in the NP hard sense.) We've been collaborating with stats wizards at Yale and Santa Clara (Prof Ediel Pinker and Prof Kirthi Kalyanam, respectively) for a few years now. Of course, much of our company's bidding technology is proprietary. As we've discussed here, a big part of bidding is predicting term performance. Estimating keyword SPC for low-volume long tail terms is a major part of the "secret sauce" and "rocket fuel" behind our bidding platform. SPC estimates need to be done by engine, matchtype, day of week and time of day, destination page, season, etc. As George said, we use a combination of smart clustering and statistical modeling to get reliable SPC predictions. Robust SPC estimates serve as the foundation (but not the entirety) of our bid system. PPC bidding is a distinctly non-trivial statistical optimization problem... Cheers Alan
tony bonn says:
i am impressed with the analysis however qualified and subject to emperical data it may be....which in turn begs the question, are there any studies which explore this idea using empirical data especially in the online marketing arena? the m/2 target is not surprising.... i really enjoyed this article....
Well I'll be blowed, I never realised you could work it out like that, some further correlation from other sources and studies to prove this further would be good.
Rene says:
I am wondering what should be considered "nett sales". Suppose a reseller buys 10 cars for $10.000 each, and his profit is $1000 per car, his cost of goods sold is 10x$10.000 but I can imagine you should consider the 10x$1000 margin as nett sales and his showroom as costs. Another example is a travel agency who only sells tours from 3rd parties and receives a commission of 10% for each tour sold. Which figures should be taken as "sales" and "costs"? Looking forward hearing some opinions.
Rene, If you're working through the spreadsheet in the post, in your first example, the Resulting Net Sales would be $11000 x 10. Cost of Goods as a % would be $10000/$11000 = 90.9% and you would incorporate the showroom costs as Other Variable Expense. So, our definition of Net Sales here is Gross sales – Sales of returns and allowances. The 10 x $1000 figure would be Gross Margin in the sheet, or as some would call it, Gross Profit. I hope this helps. Mark


Check out what others are saying...
[...] http://www.rimmkaufman.com/rkgblog/2007/02/11/how-much-to-advertise/ goes into more depth and gives a nifty spreadsheet to help out with the math. [...]
[...] video mentions the “Square Root Rule”. Here’s a blog post about that model, and here’s a free Excel spreadsheet implementing [...]
[...] to the square root rule, profit is maximized when the ratio of margin generated to advertising spend is 2 to 1. In practice [...]
[...] to the square root rule, profit is maximized when the ratio of margin generated to advertising spend is 2 to 1. In practice [...]
[...] the square root of the fraction of money spent on them. (This relationship has been noted before by Alan Rimm-Kaufman who in turn was quoting others.) Deviations from this rule might be due to budget limits [...]

Leave A Comment