Building Your Own CTR Model from Search Console Data

Rather than performing analysis using just the bare average rank, you can bring in a keyword’s search volume and add some weighting to the rank.

To do this, you estimate SEO traffic for a keyword by multiplying its current rank, by the estimated click-through rate at that position.

It’s not meant to be extremely accurate, but it’s meant to be a like-for-like comparison across the keywords, and show that positive movement for a 1,000/search keyword is more valuable than a 50/search keyword.

Now, I have a standard CTR model I use for these analyses.

However, if you want to be a bit more specific, be able to break it out of brand v generic, or be able to view it per category, then you will need to build your own model.

You can also run these CTR models at the landing page level, to analyse landing page performance

How to build an SEO CTR model

It’s actually extremely easy to extract your own CTR model, directly from your GSC data. Here’s how to do it.

1. Extract all your GSC data for the latest month (or longer if you don’t have a lot of data). Run separate exports for queries & URLs, and you can look at the CTRs for both of these.

2. Round the ranking data to the nearest whole number, by adding a new column and using the =ROUND(<rank>,0) formula to round it to the nearest whole number.

3. Create a pivot table from the data, but don’t just use the CTR. If you average the CTR you’ll give single-click keywords, the same weighting as keywords with 500 clicks. You want to create a calculated field, that looks at clicks & impressions from the data.

4. Under ‘values’ of the pivot table, create a calculated field for the CTR that is =Clicks/Impressions. A calculated field is needed, rather than the average of CTR, to weight the keywords. Low volume keywords will swing the CTR model if not weighted.

5. Plot a scatter graph using the pivot table, and throw on a trend line to make it a bit easier to interpret.

And presto! You’ve got a customised CTR model. Now replicate this using a landing page data set.

I prefer to remove some of the lower keywords, and particularly keywords with 0 clicks from the data, to ensure they’re not messing up the data too much as they have significant low volumes. Even with the weighted CTR using clicks/impressions, there can be a lot of junk with 0 clicks so it’s just easier to exclude it all.

It’s recommended you don’t export GSC data using both queries & landing pages at the same time. Both dimensions at once will cause GSC to sample your data, limiting what you get. So unless there is a specific analysis you want to do that requires both together, it’s best to export the data separately.


The data caveat

Unfortunately, GSC doesn’t give us all out query data.

Google explains the missing query data is related to privacy, and/or for long-tail phrases.

They’re basically excluding tonnes of the longer tail keywords, that will have low impressions/clicks each, yet might still drive significant quantities of traffic.

This missing data could skew results if they are indeed super-low individual click count keywords, due to how they may shift the averages.

Even with this missing data, you should be able to make some informed decisions you couldn’t before doing a CTR analysis.

Just know the caveat behind the data though.


What you can use your CTR model for

There are a few uses for your new CTR model.

  • Understanding brand vs generic CTR rates, and having a CTR rate with the brand removed
  • Analysing CTR rates per keyword category, and knowing underperforming areas
  • Analysing CTR rates per landing page, and knowing underperforming landing pages
  • Forecasting SEO traffic
  • Setting baseline CTRs for areas to then make CTR optimisations & monitor going forward

And many more…


Access my sample sheet

You can access my sample sheet with the below link.

You can paste in your data and it should do all the calculations and the graph for you.


How will you use your new CTR model?

Always up for hearing what people will do with this stuff. Whilst you can just roll with the default model, a customised CTR model gives you so many more options.


About The Author

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top