It’s safe to say that (not provided) sent a few shockwaves around the SEO world, but it’s even safer to say that it’s not the end of days. If you’re wondering how you’re going to understand your most valuable keywords and how much traffic they’re driving, get yourself a brew, get comfortable and read on.
With the loss of keyword data from Google in Analytics, the next best way to measure the keywords that are driving traffic to your site is via Google Search Console. By looking at the search volume for a keyword and knowing the click through rate you would get if you were to rank number 1, you can easily calculate your SEO opportunity for that particular keyword. There are a number of studies offering average CTR’s ranging from the leaked AOL data to a more recent Slingshot SEO study but all are based on averages and not your specific website. We thought, why not just calculate the specific CTR for each of our clients rather than relying on averages?
When we started this study we thought that the numbers would be pretty similar but we were amazed at the variation. We have some clients getting upwards of 35% CTR for position 1 rankings for competitive commercial keywords and others struggling to hit 15%. It all depends on the landscape for that keyword and the attractiveness of the brand and their search result.
This step-by-step guide will teach you how to make an effective Click-Through Rate (CTR) model in Excel based on Webmaster tools. Some of you might think this is easily done by adding up the average CTR that Google gives but it’s a bit more involved than this and needs some good Excel skills.
Step One
Find the data you want
In “Search Queries” you can see the following data:
Query: What is being searched for in Google
Impressions: How often your website appears in the SERPs for this term
Clicks: How many people have clicked on your website when they’ve seen it in the SERPs
CTR: Click-Through Rate. The percentage of people who have clicked on your site in the SERPs
Avg. Position: Your website’s average ranking position in Google for this term across the specified time period
Depending on the kind of traffic your website gets, you will want to see data in one of two possible ways:
As much data as possible: Good for websites on which visits and visitor intent is not seasonal
Month by month: Good for websites on which the traffic is seasonal
Either way, the date range needs to be changed to see what you want. Webmaster Tools only shows three months’ worth of data at a time.
If your website traffic is seasonal, your CTR model may have to be updated every month. The same instructions still apply, but the process would have to be repeated.
The following example is for a website whose visitor needs are not usually seasonal.
First choose a date range:
Change “traffic” to “Queries with 10+ impressions/clicks”. If you have a way to truly estimate impressions/clicks to anything other than the useless “<10”, please, be my guest…
Change the “location” to the primary location of your visitors. If you have a lot of visitors from many locations, you will have to do this more than once.
Step Two
Download the data
Click “Download this table” and save the document as a CSV.
Step Three
Format in Excel
Open your downloaded document in Excel.
You will get some pretty data that looks like this:
For easier working, turn it into a table.
That’s better.
Filter for any Impressions/Clicks that are still labelled “<10” (those pesky little buggers still find their way through!). Delete them!
OPTIONAL
Add a nice column called “Monthly local search volume” and fill it with the search volumes for that term. Recommended: Builtvisible’s plugin for Excel
If doing this month-by-month, use the search volume for that particular month. If not, an average search volume will do.
The monthly search volume doesn’t play a role in calculating the CTR, but we may be using it for this next part.
OPTIONAL
Step Four
Group your keywords!
Different kinds of keywords will have different CTRs. Create a column called “Keyword Type” and give every Query a label. Here are some that I would consider:
Brand/Non-Brand: If a keyword is related to your brand, it most likely has a higher CTR than a non-brand keyword
Head/Mid/Longtail: Group keywords based on the Monthly Search Volume. It’s up to you how you define these. I have used “≥1,000 searches is Head” and “<100 searches is Longtail”
Core Business Areas: Does your website offer a wide variety of services? Label them as such.
In my spreadsheet, I’ve used a mixture of Brand and Head/Mid/Longtail:
Step Five
Pivot tables
To create your CTR model, you will need to use a pivot table.
Before the pivot table is compiled, we need to add a calculated field – True CTR. It will calculate a CTR based on the sum of clicks and impressions, rather than the sum of CTRs.
To do this, you need to go to:
PivotTable Tools > Options > Fields, Items & Sets > Calculated Field
Name: True CTR
Formula: = Clicks/Impressions
With this in hand, we can make our CTR tables. Ideally, you should have one for each Keyword Type and, if necessary, one for each month.
Report Filter: Keyword Type
Row Labels: Avg. Position
Values: Sum of True CTR
And then you’ll get something like this:
To make this data actually useful, right-click on Row-Labels and select Group
You will want:
Starting At: 1
Ending at: Highest Number
By: 1
Then you have a nice little model for CTR that looks like this:
You may have to apply a filter for different kinds of keywords. For example, Brand keywords will probably have a different CTR to Non-Brand keywords, similar for Head/Mid/Longtail keywords. The Excel spreadsheet attached contains all four kinds.
Step Six
Make these into actual tables and rename some things
The way in which pivot tables work means that you will need to actually paste them as tables and rename them.
For example, 1-2 becomes “1” and 2-3 becomes “2” and so on. Renaming tables and Columns so that it’s easier to read and understand as well.
Step Seven
Identifying missing data
If Google Webmaster tools doesn’t give you a lot of data, you may not have a full CTR table. For example, we didn’t rank in P6 or P7 for any Head terms.
Without any CTR data, any estimated traffic will be zero, so something needs to be done.
We rectify this by creating a Scatter Graph based on the rank and CTR. The more data points available, the more accurate this method will be.
Once this is done, add a trend line (the line used here is a Power line, although that may not be the best fit for every CTR graph). Make sure to check the box that shows the Equation on the chart.
And you have a graph that looks like this:
To estimate the CTR for the remaining positions, use the formula given to you, where x is the position. You can do this for as many positions as you want – I went up to P25 as the graph tapers towards zero after that point.
Then combining the estimated data with the true data, we have a complete CTR model.
Other methods include using Moz’s CTRs – advisable if similar to your own, or halving the CTR for each position lost. This is the method we used for our Brand terms (since we only rank P1 for our brand terms at present, it’s difficult to guess).
The formulae have been left in the CTR Tables tab.
How to use this CTR model
If you have a list of keywords you track rank for, categorise them based on the same categories for the Google Webmaster Tools keywords. Split these into separate tables. You can do this in one table if you want, but it involves some fancy excel formulas.
For each keyword, get the search volume and current rankings. We have an internal tool that checks rank.
Fill in the Estimated Monthly Traffic column by using a VLOOKUP to find the relevant CTR and multiplying this by the Search Vol. I’ve left the formulae in so you can have a look.
And then you get something like this:
Et voila!
To download the Excel file used to calculate this, simply click here.
