Free Tool For Back-Of-The-Napkin Paid Search Forecasting

Forecasting is a delicate process which demands care.

When prospective new clients ask our agency to build 12 month PPC projections, we often shudder. It isn’t that building forecasts is technically or theoretically difficult: stir reasonable monthly estimates of term-level click volumes and click costs into reasonable estimates of site conversion, bake at 350 degrees, and you have a tasty paid search sales and cost forecast.

The problems arise when clients misunderstand the precision of the estimates (suggestion: always provide high and low scenarios to make your uncertainties explicit), or when clients overlook that forecasts are a direct reflection of their planned strategy (suggestion: revise and resubmit forecasts should their advertising strategy change materially during the year), or when slavish devotion to a forecast impedes smart marketing. To that last point: we once had a large client instruct us to turn off their search campaigns two weeks before Christmas, when their conversion and sales and profits were through the roof and their search ads were literally printing money, because their intended annual budget was exhausted. Ouch.

Sometimes, however, marketers need quick back-of-the-napkin projections. The common question goes something like “So if we got really aggressive and doubled our Google spend next year, about how much do think our sales would go up?”

In this situation, you need a quick approximation, not slow, formal, full-blown forecast.

Enter the “Square Root Rule” model. This is a simple mathematical statement which assumes the relationship between sales and advertising follows a square root relationship.

This model is utterly wrong, and we’ll return to that point. But, even wrong, it is still useful. My apologies if I don’t slog through the mathematics in this post. Folks who enjoy recreational calculus can review the equations in a longer blog post. For the rest of us, we’ve wrapped an Excel spreadsheet around this model, and that free tool is available here (XLS file).

The next few paragraphs reference the spreadsheet. To follow along, pop that sheet open now.

The sheet opens loaded with a particular scenario, an example given by Kevin Hillstrom in a MineThatData post last year. Aside: I recommend Kevin’s blog to folks interested in multichannel direct marketing.

In this particular scenario, the retailer is generating $10mil in sales from a $2mil paid search budget, for an ad-to-sales ratio of 20%. For our purposed here, time period doesn’t matter – those could be annual, quarterly, or monthly figures. This hypothetical retailer has 60% cost-of-goods and 13% variable costs: pick-pack-ship, telephone support, credit card discount fees, etc.

The model suggests that increasing advertising spend up to $2.2mil (up 10%) would increase sales to $10.5mil (up 5%), and decrease profit by $70K (down 10%).

With these inputs, the model suggests that reducing ad spend would increase profit. The retailer is spending 20 cents in advertising to generate a dollar in sales, and that dollar only yields 27 cents in effective margin—too much advertising.

On the other hand, suppose the hypothetical retailer was generating that $10mil in sales using only $1.2 mil in search (12% A/S). With these inputs, the model declares that the retailer is under-advertising, and predicts increasing ad spend by $300k would increase sales by $1.25mil and maximize profit.

To use this model for quick estimates, change the gray shaded cells. Put your ad cost for a recent campaign in cell C8 and the corresponding tracked sales in C9. Put your average cost-of-goods sold percentage in C10. Put your other variable selling expenses in C11.

The model offers a base P&L for your campaign. It also estimates the sales and profit impact of +30% and -30% changes in ad spend, and it computes the advertising amount which would maximizes your operating profit.

I mentioned earlier the model is utterly wrong. Yup. In real life, revenues don’t scale smoothly and without limit, but this naïve model thinks they do. The model happily predicts this retailer could generate $1billion in sales from paid search ads—which is ridiculous—but they’d need to pay Google $20 billion to do so—even more ridiculous!

The model is least wrong for small changes, say, plus or minus 20% changes in spend. But before using this tool, I’d recommend you read up on its limitations.

True campaign optimization comes through testing, and true forecasting takes patience and care. But, when you need a back-of-the-napkin guestimate of sales for a given ad spend, the square root rule can be a handy tool.

Alan Rimm-Kaufman leads the Rimm-Kaufman Group, a direct marketing services and consulting firm founded in 2003. The Paid Search column appears Tuesdays at Search Engine Land.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: SEM | Paid Search Column | Search Ads: General


About The Author: sadly passed away in July 2009. His fellow co-founder of the Rimm-Kaufman group, George Michie, took over Alan's post as CEO, and is also a contributor at Search Engine Land.

Connect with the author via: Email


Get all the top search stories emailed daily!  


Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Jon Miller

    The square root rule is very insightful and useful. Our research at Marketo shows that the actual relationship between advertising spending and results varies from x^0.4 to x^0.6, where 0.5 would equal a square root.

    Once you have that data for each advertising medium (or even each keyword), it becomes very useful for allocating spending optimally across options (e.g. portfolio-based optimization) — subject, of course, to all the complexity you point out (i.e. lower and upper limits, discontinuities, etc.).


Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest


Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States


Australia & China

Learn more about: SMX | MarTech

Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!



Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide