You’ve got a new AdWords account or campaign to launch. The important question looms; will this ad campaign even make money for the business?
You could gamble – throw some keywords and cash at it, and hope for the best.
Unfortunately, this method is all too common.
Or, you could just use my free sheet and avoid the uncertainty.
Using this simple spreadsheet you’ll be able to:
- Avoid wasting your time on something that would never work
- Stop running unprofitable campaigns for the business
- Accurately forecast your Google advertising results
There’s money to be made doing things right…
To put this in to perspective:
Last year Google generated $385 Billion in economic activity, and that’s just in the US.
Google sent over 1.9 Billion phone calls, messages and bookings to businesses – each month!
And a total of 16 Million businesses received a share of that pie.
How big is your share of the pie?
Let me walk you through how the sheet will help you predict the future.
Why not just use the Google Ads forecast tool?
There are two key reasons I don’t use it:
- It does not consider your business metrics or profitability
- It very rarely agrees with the actual real results
When you use actual real world ad spend data in the Google forecast, it very rarely agrees with reality.
So, it’s wrong.
That’s a pretty compelling reason not to use it.
Let’s use the sheet instead.
📼 Video: if you prefer to watch there’s a 15-minute video tutorial at the bottom of this article.
📁 Download: if you want to grab the spreadsheet before reading, download it here.
Copy+paste your keyword research
You’ve done your keyword research.
You’ve downloaded the csv from the Google keyword planner.
We need to get that data in to your sheet.
Head over to the “Input” tab on the spreadsheet and paste in the first 7 columns of data.
Be sure to include only the rows down from the headings, and nothing more.
Lazy tip: you can paste in as many rows as you like, the sheet will automatically filter any rows with missing data so you don’t have to tidy anything up.
If you’re downloading your keyword date from the Google forecast tool, make sure you use the ‘historic’ download option.
We don’t use any forecast stuff because as I said before, it can be miles off. For years I’ve used my own projections spreadsheet and it’s proved it’s value time and time again.
Only change the green bits
The only bits that you change are the green bits.
- Your conversion rate (CVR)
- Your click-through rate (CTR)
- And your remarketing budget as a % of spend
Again, this is something I rarely see on forecasting calculations – some ad budget for remarketing!
What’s a good conversion rate?
If you’re asking, that probably means a) you’ve got a new website or b) you’ve got very little traffic and so, few conversions to judge.
However, if you already have data, use your Organic traffic as the baseline.
I assume for most people that means Google Analytics tracking.
Whatever source you use, just be sure to pick a date-rate long enough to capture a reasonable view of your trading history.
Without your own data? The only way you can really answer this is to have data from a number of your competitors.
There is no tool that will accurately tell you your competitors conversion rates.
I also highly doubt you competitors will tell you, either.
Many people will say the average is 2%.
The average of what exactly? Which market? Which product or service? Which country?
You can see the issue…
Starting from scratch is tough.
You might be selling a high-ticket or luxury item and your conversion rate is half a percent. But that’s okay, because a sale is worth big money.
Conversely, you could be selling inexpensive, golden hot pants (send me a pair). Your conversion rates are 10% because they’re flying out the door!
Well, it is summer!
Thankfully we already have over 3,000 Google Ad accounts connected to AdEvolver. We anonymised the data to calculate a few industry averages:
- All industries – 2.59%
- B2B – 2.25%
- Ecommerce – 2.10%
- Finance – 5.21%
Soooo… the average conversation rate is around 2% then?
The range is so extreme; this isn’t conclusive and should be used as your marker in the sand.
If in doubt, low ball it as your conversion rate will usually improve with time (and ongoing optimisation).
I’m starting to get distracted; we’re meant to be talking about the spreadsheet!
What’s a good Click-through rate?
Seriously, are we doing this?
Look, don’t sweat too much at this stage. In reality, CTR will simply limit how much you can spend.
As with conversion rate, it depends on the industry; consider for many industries 2% is considered low.
4-6% is a more likely average.
Star performers will be in excess of 10%.
Start low, adjust the CTR figure in the sheet to see how it impacts your spend metrics and then set yourself meaningful target.
Moving on, again…
What’s the average cost per click?
Now this one is easy to answer…
Thankfully, I calculate that for you. At the very top of the sheet you will see your blended CPC.
This number is the average cost per click based on an impressions-weighted calculation, for all your keywords with impressions.
It excludes any keywords without impressions and so, their Google estimated bid is not considered.
How big is the search market?
Directly below the input fields is a slider for “impression share”. Divided in to your target impressions, versus the total available for your keywords.
You use the scroll bar in 10% increments to adjust how much of the market you’d like to capture.
This in turn will adjust all the metrics and give you an estimate of your spend and conversions.
You’ll also notice that the blended CPC will rise and fall based on how much impression share you go after.
Pro-tip: in the bottom range of 10 to 20%, cost per click is reduced to simulate where you’re going after a tiny portion of the market. You can be more conservative with your bids.
You bid really low and be more specific with your targeting because you’re not forced to go after the whole market.
And then conversely, when you start getting to 70, 80, 90 and even 100% of impression the blended CPC will rise.
This is to simulate bids increasing at the thick end where you’re at the limits of the available market and bidding is less selective.
It’s a quantity thing.
Start with a impressions share that suits your spend appetite, perhaps somewhere in the middle to stat.
Yo, where’s my month 2 at?
Another thing that makes this sheet different to what you may have seen before, is that I’ve split the performance into month one and three.
You might be wondering why.
Brand new keywords, campaigns and accounts perform very differently in their first few months.
In my experience 90-days in is about the sweet spot for understanding your true performance.
Consider month one your discovery period.
During that time you are data gathering, essentially you’re paying for the data and yes, you may get conversions too.
In fact you might do perfectly fine, but by month three you’re going to be doing better.
That is, if you’re doing things right.
- You’re optimising ads and destination pages
- improving your click-through rates
- pausing off weaker keywords, for now
- adjusting bids and budgets
- tweaking audiences, and more
Good-to-know: by month three your CTR might be up, you might be bidding more aggressively because your conversion rate is now stronger.
That, of course, impacts the spend. I’ve simulated that in the sheet.
Wait, the CPC goes up?
A common myth of pay per click is that the more you optimise, the more your cost per click goes down.
This isn’t necessarily true, particularly at the start!
Over the past 10+ years I’ve seen it can be – and is more often – the opposite.
The more confidence you build in the data means the narrower your focus becomes. You hone what works. You will find that a lot of the commercially biased keywords are more expensive per click.
That does not mean your cost per lead or sale goes up!
On the contrary, paying more per click on something that is converting better and better, will reduce your cost per conversion.
Like I say, your third month will look different.
Month two is just the wiggle in the middle…
Still with me?
Looking at the top right of the sheet you will see you can change the currency.
This is symbolic, it doesn’t convert anything because it doesn’t need to. You will be pasting in your data in your desired currency.
It’s a little visual appeal.
We can also include or exclude the top 10 most expensive keywords from the calculations.
There’s a key reason you might want to do this:
- if you’re in a really competitive market you can potentially save money by not spending on these for now
Note that if the top 10 most expensive keywords are also high impression terms, you might be forced to include them. Particularly if you’re in a low search volume niche.
I’ve also included the top 10 cheapest keywords – just for interest really.
Where the business meets the ad metrics
The whole point of this is to work out – will advertising be profitable?
Thankfully we’ve done most of the work now.
Take a look at your “sales performance” section:
To understand your cost of conversion (or acquisition), you need to input three things:
- Close Rate – your lead to sale conversion rate
- Revenue Per Sale – your average order or lifetime value
- Profit Margin – whadda’ ya make?
For lead generation businesses i.e. if you don’t track ‘transactions’ and revenue on your website:
Close Rate is your lead to sale conversion rate. Your advertising generates a conversion at a cost, you then convert that conversion from being a lead to sale. It might be a phone call or a form submission, for example.
Input your conversion (close rate) here, along with average order value and margin.
For ecommerce things are even easier:
Close Rate for you is a sale minus your returns rate.
If your returns rate is 5%, then your close rate for this purpose is 95%.
Revenue per sale is the same again – just input your average order value.
Ad Spend, conversion metrics and ROAS
Now you have a sheet full of projections.
You’ll be able to answer with confidence:
- What’s my average cost per click?
- What do I need to spend?
- How many sales will I get?
- How much revenue and profit will I make?
- And what will be my return on investment?
If the numbers don’t look favourable, why not? Use the sheet to fine-tune where you might need to improve things.
You can also use the sheet to project your growth potential and how far you can scale your advertising.
As promised, here’s the video tutorial if you’d prefer to watch:
Use the sheet to predict your future performance and potential spend.
A free tool that might just save you… or make you, Millions!
It really is a powerful little tool – be sure to download your copy below.
GET YOUR FREE DOWNLOAD
Make running unprofitable campaigns a thing of the past:
Full-disclosure: when you enter your email, two things happen –
- On confirming your email address you get instant access to download the Excel spreadsheet
- You will also opt-in to receiving an email from me in the future
I hope you agree this is a fair trade.
Of course, you can unsubscribe at any time. 😊