How to calculate your true Click Through Rate with Google Webmaster Tools

  • 0
  • November 7, 2013

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 Webmaster tools. 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 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?

Click here to download the Excel file used to calculate this.

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

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:

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 two download the data

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.

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!

Filter below 10


Add a nice column called “Monthly local search volume” and fill it with the search volumes for that term. Recommended: SEO Gadget’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.

Search volumes

The monthly search volume doesn’t play a role in calculating the CTR, but we may be using it for this next part.


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:

mixture of brand

Step Five

Pivot tables

To create your CTR model, you will need to use a pivot table.

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

Pivot table tools

Name: True CTR

Formula: = Clicks/Impressions

True CTR

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

Pivot Table field list

And then you’ll get something like this:

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:

Model for CTR

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.

Rename them

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.

Identify missing data

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.

Scatter chart

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.

Trendline Options

And you have a graph that looks like this:

CTR Best Fit

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 Slingshot SEO’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:

Longtail terms

Et voila!

To download the Excel file used to calculate this, simply click here.

Get involved with our Brunch with Branded3 Google+ Hangout on Monday 18th November to ask your questions on the above to our expert team, simply follow us on Google+ and join the event here.

Emma Barnes

About Emma Barnes

With a strong mathematical background and a passion for all things data-related, Emma is an essential member of our Insights team. Even outside of work, Emma doesn’t stray far from the numbers and formulas and loves logic-based gaming.

  • Tom Fitton

    Some interesting methodology here I’ll be sure to check out.

    “If you have any way to truly estimate impressions/clicks to anything than the “<10" , please be my guest…"

    Paid vs organic report from Adwords provides accuracy below <10 doesn't it? Also gives you the data without it being rounded to the nearest 10/100/1000.

    I've not had chance to check if you can create an Adwords account and just spend a nominal amount (or better yet spend nothing) to unlock this data yet though.

    • Emma Barnes

      Thanks for the tip! To be honest, I’ve not used Adwords a lot (I know…) but if this info is here, it’ll probably be worth checking out. Will liaise with the PPC guys!

      • auskeo

        Paid & organic (It’s and) will give you really accurate data. I’ve used it a few times. You need to get it set up and i’ve it a few weeks to get a good set of data

  • Bernt Johansson

    Thanks for a great post. Some insightful stuff here.

    One of the most important aspects (for me at least) of being able to track visits on a keyword level is to differentiate between branded and non-branded keyword traffic. Since traffic on branded terms can variate a lot due to factors outside of SEO (tv commercials and other marketing etc.) it is important to be able to report on a visit metric (preferrably with year on year comparison to take seasonality into account) that, in the best way possible, represents how the SEO efforts have impacted traffic growth and in some cases also revenue growth.

    The (not provided) issue has effectively made this more or less impossible. Your way of using GWT data is one of the methods I have been analyzing to determine how well it would work as a replacement for branded and non-branded traffic split from within Google Analytics.

    But I have one big issue with using the GWT Query data, and that is because it is based on the top 1 000 queries for each day. And for sites with impressions and clicks on more then 1 000 unique search phrases per day this will not be good enough. Mostly because the keywords outside of the top 1 000 are in the tail, and in the tail, the ratio of non-brand vs brand keywords is higher then in the head.

    What is your take on this?

    • Emma Barnes

      Yeah, obviously GWMT data isn’t perfect (BOO!) and so it is pretty frustrating for larger sites – analytics was much the same because of how it sampled data. I mean, you could get the majority of your queries, but sometimes visit numbers were quite off.

      With regards to tracking other keywords – if you have the resources, get a massive pool of keywords that your site does/should rank for. Either from back-data from analytics or using SearchMetrics/similar. Grab search volumes and rankings. Use your CTR model to estimate incoming traffic.

      The massive issue is guess-timating traffic from really long-tail stuff (those fun <10 queries) but I would say that if you rank in the top 3 for those kinds of keywords, you are probably be getting SOME traffic (as helpful as that is).

      Not sure how much this helped!


        Hi Emma, I have a large client looking to report on Brand/Non-Brand split of their traffic. Will this help? Thanks in advance!

        • Emma Barnes

          It should help! We do a lot of brand/non-brand reporting via WMT.

  • Ed

    Is this tutorial half complete? I can’t see where it tells you how to convert Power Trendline chart formulas into functioning excel formulas to fill in the missing gaps…after searching online – this seems to be a very tough process and half the battle of completing the CTR model described above

    • Emma Barnes

      Hi Ed – which part are you having trouble with? I suggest downloading the Excel example I did (linked at the bottom) and having a look – I didn’t put the exact formulae in the post as different people lay their spreadsheets out differently. If you’re still having trouble, feel free to contact me.

Like what you see? Talk to an Expert