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.