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.