This post will teach you how to model data and predict ROI for PPC advertising. Links to resources and a downloadable calculator are included for free at the end of the post.
Leverage Your Data
What I love most about internet marketing is that it is a data-driven industry. We can use this data to proactively capitalize on opportunities, avoid pitfalls, and model future expectations. Stealth Lion has created a formula to help you model whether or not pay-per-click advertising on Google AdWords is worth trying before you even spend a penny! At the end of this post is a resources section which will give you links to tools that will help you use the PPC formula yourself. One of these tools is Stealth Lion’s PPC Formula Excel spreadsheet which you can use as a calculator!
The PPC Formula: What is a Lead Worth?
To figure out if PPC is worth it, first you need to figure out what a lead is worth to your company. Find out how many leads you have had contact with and then see how many of those leads have become customers. Next, find out what the average value of each of these customers is. I tend to be a bit more conservative with my predictions and focus on the immediate value but you should also look at the lifetime value of a customer if you have historic data to reference.
Here’s an example: In 1 month, Bob closed 4 of 10 leads he received. The sales were worth $2,500, $1000, $1,250, and $250 ($5,000 total). $5,000 divided by 10 leads means that each lead was worth $500 to break even.
Mathematically, the formula looks like this: V = ( R / L )
In the formula above, let L equal the total number of leads, let R equal the revenue generated from the closed leads, and let V equal the value of each lead.
If we use the information from the Bob example, the PPC formula looks like this: 500 = ( 5000 / 10 )
The PPC Formula: Modeling Your ROI
Now that we know what a lead is worth, we can look at the rest of the PPC formula. Let’s say Bob is going to spend an average of $5 per click and $700 per month on AdWords. He also will pay a $300 management fee and tracking/reporting fee for a total of $1,000. This budget will allow him to get 140 clicks per month ($700 divided by 5). To break even on his investment, Bob will need to generate at least 2 leads per month because he will be spending $1,000 per month, his leads are each worth $500, and 2 leads are worth $1,000 ($500 multiplied by 2 leads). To profit, Bob will need more than 2 leads.
The next part of the PPC formula looks like this: ( A / C ) * X = E
In the formula above, let A equal the total Google AdWords budget (do not include the management fees), let C equal the average cost-per-click, let X equal the percentage of clicks that become leads (expressed as a decimal), and let E equal the number of leads generated. Some of the information required for this part of the PPC formula needs to be gathered using the tools in the resources section at the bottom of this post.
Let’s plug in Bob’s data and see what the PPC formula looks like if Bob just wants to break even: ( 700 / 5 ) * X = 2
Simplified: 140 * X = 2
Solved: X = 0.0143 which is a 1.43% conversion rate.
How to Use the PPC Formula
You can also use the PPC formula to set goals for conversion rates and see what the outcome could be. For instance, let’s say you knew that realistically Bob would not get more than a 0.5% conversion rate for whatever reason. You can plug that in to calculate the number of leads Bob could expect from his budget.
Using the PPC formula this way would look like this: ( 700 / 5 ) * 0.005 = E
Solved: 0.7 = E which, based on the value of a lead formula, is only worth $350.
You can manipulate any variable in the formula to model different scenarios or look at the possibilities from different perspectives. For instance, we know that a high quality score in AdWords means a decreased cost-per-click (CPC). Let’s see what happens if Bob’s PPC management team can get him the same clicks for only $4 per click using the same data from the break even example from earlier.
Bob’s break even formula with a $4 CPC: ( 700 / 4 ) * X = 2
Simplified: 175 * X = 2
Solved: X = 0.0114 which is a 1.14% conversion rate.
Using this formula you can also play with changing the total AdWords spend and the management fee but I think we’ve had enough examples so I’ll spare you of any more.
The PPC Formula Excel Calculator & Resources
- Google Keyword Planner This tool will allow you to research the average CPC of the keywords that your AdWords account will target. You can set geography restrictions for more accurate data if your ads are targeting specific locations.
- Open Office and Google Docs Spreadsheets are good, free alternatives to Microsoft Excel if you want to use the spreadsheet calculator. I haven’t tries opening or using the spreadsheet in either program so they may or may not be compatible, please leave a comment if you find out!
- PPC Formula Excel Spreadsheet This sheet is setup so you can play with different models by typing in values instead of doing the calculations manually. Play with it using the examples discussed in this post to get a feel for how it works.