Level-up your PPC forecasting with free modeling tools for Excel

Have you ever found yourself in the room with an executive — whether your client or your boss or even your client’s boss — recommending a strategy that you genuinely believe to be the best marketing decision for the company, only to be hit with unexpected resistance?

Or perhaps you came seeking additional budget for a channel that is already crushing goals, thinking it would be an easy and obvious sell, but are now facing a barrage of questions. 

The “what can we expect from this?” discussion can be intimidating, no matter when or where it comes. 

At SMX Advanced and SMX Next last year, I shared two resources to equip digital marketers for those kinds of challenging conversations:

• Forecasting Tool with Uncertainty Variables
• Forecasting Tool for New Channels and Organic Growth

You can download a copy of each tool here.

Reliable forecasting is essential but difficult

The great and terrible thing is that forecasting is all about people.

If behavioral economics has taught us anything, it’s that people aren’t exactly rational. We are self-interested, yes, but we also make decisions counter to what should be expected if that were the only variable at play. 

Marketing is also subject to the environment in which our prospective customers are living, working, shopping, and purchasing. As their priorities and preferences change, so must our tactics to inform and invite. 

No one needs to be lectured these days about potential shocks to our delicate ecosystem: beyond the global pandemic we’re all still enduring, the last few years have brought devastating natural disasters, new competitors and disruptors, regulatory shifts (e.g., GDPR), political and social unrest, and more.

Still, marketing is a part of business. And successful businesses rely on sound decision-making, which necessitates effective use of high-quality data. Clients and executives want to be able to say “YES!” when presented with innovative and exciting ideas, but they must also address and mitigate associated risks. 

That means we marketers must be prepared to discuss not only what we believe will happen, based on the best data available, but also what might happen should circumstances suddenly change. 

Forecasts are not predictions. But data and statistics can help us better anticipate and prepare for the future. 

How to use the Forecasting Tool with uncertainty variables

The first tool I created is designed to help everyday marketers utilize random variables and sensitivity analyses to strengthen their forecasts. The model incorporates and mitigates uncertainty by examining how expected outputs are changed if one of the key inputs behaves differently than predicted. 

To use this tool, you should first be familiar with the concept of statistical probability. 

Probability distributions

If you are unfamiliar with the statistical concept of probability distributions, or if it’s been a few years and you’re a little rusty, that’s okay. You don’t have to understand all the details or derivations to use them with the provided models.

For our purposes, you can think of a discrete probability distribution as one in which a list of possible scenarios each has a distinct likelihood of occurrence.

For example, I may not know exactly what my CPCs will be tomorrow. But I might assume that there’s a 50% chance they will be the same as my CPCs today, with a 40% chance that they are higher than today and a 10% chance they are lower. When I wake up tomorrow, the actual CPC will be in one of those three distinct states (equal, higher, or lower).

A normal distribution is more of a sliding scale that covers possible outcomes. If I know, historically, my CPCs have been between $1 and $5, but I observe they are generally between $2.50 and $3.50, then a normal probability distribution would allow me to calculate the likelihood of tomorrow’s CPCs being less than my $4 target. 

The decision of which distribution to use in your model is totally up to you. There’s no right or wrong answer.

I typically prefer a less precise, discrete probability model when I don’t have reliable historical data or low confidence that the future state will likely look like the past. On the other hand, I will opt for a more precise, normal probability distribution if I have good historical data and am confident that future performance will likely align with past performance and trends. 

The first tab of the Uncertainty Forecasting Tool, labeled “How to Use,” provides an overview that you can reference at any time. Each of the other tabs follows the same five-step process to generate an output: 

1. Build the model: Input known or estimated values into a static model with a single output KPI (e.g., CPA or Conversion Volume). Verify that changing one or more input cell values causes the output cell value to change. 
2. Define uncertainty: Add your desired probability parameters to the provided table according to the type of distribution you have selected. 
3. Insert uncertainty: If you have made no changes to the model in Step 1, no action is needed for this step. If you revised the model in Step 1, ensure the dynamic model reflects the same relationships as your static model. 
4. Iterate with a data table: Follow the instruction provided to create a data table using the provided 1000 lines. If the output doesn’t show up, verify the settings by going to the Formulas ribbon, then “Calculation Options,” and selecting “Automatic.”
5. Evaluate outputs: Review the generated statistics of maximum, minimum, median, mean, and standard deviation for your output cell, based on the data table iterations. 

To see an example of this tool in action, watch this video walkthrough: 

How to use the Forecasting Tool for new channels and organic growth

The second tool I constructed is designed to overcome a different kind of uncertainty associated with new, untested channels or organic growth within an existing channel. 

Metrics to understand

The two variations in this tool rely on the metrics of Frequency and Saturation and benchmarks of CPC or CPM. If these are new concepts to you, review the brief explanations below. 

• Frequency: the average number of times a unique individual is exposed to an ad within a specific campaign during a defined time period (in this tool, one month).
• Saturation: the average percentage of a defined audience segment exposed to an ad within a specific campaign at the desired frequency.
• Benchmark CPC: the average cost an advertiser can expect to pay for each click on a platform, based on aggregated data from many advertisers in the recent past. 
• Benchmark CPM: the average cost an advertiser can expect to pay for 1,000 impressions on a platform, based on aggregated data from many advertisers in the recent past. 

Benchmark data for popular PPC platforms can be found via most search engines or assumed based on available benchmarks or platforms of a similar size, medium, and age. 

The first tab of the New Channel Forecasting Tool, labeled “How to Use,” provides instruction for the use of each variation. You will be asked to input your campaign information and desired parameters into the yellow cells for both tools. The cells highlighted in blue and gray provide the output as labeled. 

To see an example of this tool in action, watch the video walkthrough here: 

Templated tools are just a starting point

As you begin to level up your forecasting skills using the files I’ve provided, I hope you will find opportunities to customize the models to fit your needs better. In doing so, two precautions should be considered:

From master statistician George Box: “Essentially all models are wrong, but some are useful.”

Use these and other forecasting tools as a guide to help your decision-making rather than a source of perfect truth. Adding secondary and tertiary uncertainty variables into the model, for example, may provide value and increase your confidence in some areas. However, it could also create false security and overlook interactions between variables that may skew your forecasts. Do not sacrifice usefulness in pursuit of completeness or complexity. 

Walk, don’t run, toward greatness.

Anytime you build or customize a model, be sure to double- and triple-check your formulas to ensure the relationship between variables is correctly defined. Avoid making quick assumptions or accepting startling results without question. These tools should help quantify unknowns in real-world scenarios, and the outputs should fall into a generally expected range.