Estimating SEO Traffic for a Keyword Set

Estimating your organic traffic will allow you better assess your current performance for a set of keywords, but also allows you to directly compare that performance with your competitors.

In this video, I run through how to properly set up your data and run the formula across a set of categorised keywords. This will be the basis of future videos, on how we can better use this data.

The estimated traffic is being worked out by multiplying the search volume of the keywords, with the expected CTR at their current rankings.

The formula I use for this in Excel is;

=IFERROR([@[Search Volume]]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(CTRModel16[Position],[@Rank]),CTRModel16[CTR]),”N/A”)),0)

Which is templated as;

=IFERROR([*CELL FOR SEARCH VOLUME*]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(*CTR MODEL TABLE RANK COLUMN*,*CURRENT RANK*),*CTR MODEL TABLE CTR AT RANK COLUMN*),”N/A”)),0)

Removing the bulk of it, we essentially have the search volume, along with a lookup for the CTR at that rank. The lookup could be simplified with a vlookup if you want to, however, I choose to just use my categorisation formula for this.

This can be simplified to just a vlookup for the rank table, especially if you’re using whole rankings rather than GSC average rankings that have a decimal place.

Estimating SEO Traffic in Google Sheets

You can duplicate a Google sheet below which is 95% the same as the Excel above to get you started.

Let me know if you have any issues, but it should just be plug and play with your keywords, rankings data, and any associated categories.

About The Author

2 thoughts on “Estimating SEO Traffic for a Keyword Set”

  1. Why bother building a custom CTR model when every query has its own CTR? Why not just multiply the monthly search volume for that query by its own CTR%?

    1. Good catch Roger! Each query will indeed have its own CTR.

      However, how would you find that CTR info? You’ll only know the CTR based on your existing ranking for that keyword. If you moved up or down a position, that CTR value would change.

      This estimation is used to analyse competitors where they sit, or on yourself if you want to estimate what you’d get if you moved up or down.

      There could be a way of taking into account your current CTR, and then applying a formula to it that takes into account the current ranking, and then could weigh up % increases/decreases based on position movement. Would need to look into it though.

      Have you had a look into anything here? Would love any further ideas on refining the process!

      Cheers.

Leave a Comment

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

Scroll to Top