Warning: A non-numeric value encountered in /home/customer/www/seoinexcel.com/public_html/wp-content/themes/Divi/functions.php on line 5837
Select Page

# Estimating SEO Traffic

Estimating your organic traffic will allow you better asses your current performance for a set of keywords, but also allow 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 expect CTR at their current rankings.

The formula I use for this 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.

# 1 Comment

1. Hi the formula isn’t working for me. Have you named the ctr model table CTRmodel16. In older versions of excel I don’t think the @ symbol works either. Is it possible to share a download link to your worksheet.

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