A couple of months back I wrote a post on how to use Microsoft Excel when calculating search engine visibility percentage (SEV%). This is useful for visualising how you rank for your keywords compared to your competitors. The problem with this method of presenting data is that, while it may make perfect sense to SEO practitioners, it doesn’t translate well into stakeholder/manager speak. It’s possible to end up with dozens of line charts that don’t really communicate what good rankings mean for a website. In addition, a basic SEV% chart does not take the search volume associated with different keywords into account. For example, two clients could have the same SEV% however, one could rank for extremely competitive, high search volume keywords and the other may rank for less competitive, low search volume keywords.
Wouldn’t it be nice to see how often your competitors are getting seen for each of the keywords you monitor? That sort of information would surely be useful in convincing the stakeholders that optimising their website, or areas of their website, is a worthwhile undertaking that can result in traffic increases. Well, unfortunately we don’t all have access to our competitors’ analytics accounts, so we’re stuck with calculating estimates, and one method for doing that is using search volume, current rankings and eye tracking data for the Google Search Engine Results pages to calculate Impressions.
Things you’ll need to calculate the estimated number of impressions for a given keyword set:
- Microsoft Excel (or equivalent)
- Google Traffic Estimator
- Google SERP eye tracking data (What is the eye tracking visibility based on ranking position in the search results)
Before we move on, a small disclaimer, if I may…
We are not about to calculate click through rates and potential traffic increases here. You can use these calculations as a basis for estimating ROI of SEO projects. There are some assumptions that I have made that you may not care for, but you can adjust the numbers to suit your needs. OK, so let’s dive in.
Step 1: Estimate Eye tracking Visibility Based on Ranking Position
This is the table I use most often for general impression calculations. It assumes that if you do not rank for a keyword, you get seen zero percent of the time. If you’re ranking in position 1, 2 or 3 for a keyword you get seen 100% of the time as you’re above the fold. If you’re factoring in mobile devices with smaller screens, adjust the visibility data accordingly. Then, as you can see, the lower your ranking, the smaller the percentage of times you might be seen in the SERPs.
|Position in SERPs||% of time a user will see the search results at this position|
While this may not be the most accurate eye tracking data ever, we will be applying the same percentages to the competitor rankings, so it’s relative. Adjust them as you see fit.
Step 2: Keyword traffic data
|Monthly Searches (Based on exact match)|
|games for free||18,100||880|
|free online games||4,090,000||110,000|
|free games online||368,000||12,100|
|online games free||74,000||3,600|
|online free games||90,500||1,900|
Now check yours and your competitors’ rankings for the keywords, I’ve used some pretty competitive keywords for this example, so the numbers will be large. I’ve backdated with 3 months of data so it’s easier to work with and create charts from:
Now we need to add the traffic estimator data and eye tracking estimates to the mix, I’ve left some room for reach to be added in between (click to enlarge):
Step 3: Use vlookup to calculate
OK, the next step is the hardest part of the task, we need to combine a couple of vlookups to do this:
a) Find the ranking for the keyword
b) Find the estimated impression percentage for that ranking
c) Then we multiply a) by b)
The formula will look like this:
And in this example, it would be placed in cell F3.
What’s happening here step by step is this:
- Identifying the contents of cell B3 – “game”
Looking at the columns I, J and K, finding the word “game” in column I and then looking for the value in column K. 3 is the column index number: I is column 1, J column 2 and K column 3. The value we get here is 90,500.
- Multiply by:
- The second vlookup finds the ranking in C3 – “10”
- Looks in columns L and M to find what percentage is allocated to a ranking of 10, which is “20%” or 0.2
- Now combine both parts and the calculation looks like this: 90,500 * 0.2
Number of Impressions for that particular keyword is 18,100. So for a ranking of 10 for the keyword “game” you’d expect to get around 18,100 impressions per month (Note: This is just the number of impressions or times that we estimate that your website’s results with be viewed in the search results it does not factor in click through rate – CTR).
Now we can drag down the formula to apply it to all keywords and sum by month to get total reach. Repeat the process for the competitors. I’ve added a workbook below to save you the trouble though. Once you have sums per month (pro-tip: use a pivot table to do this, it’ll save you tons of time), you can chart it up.
While these numbers are somewhat limited by the size of the keyword set that you are analysing, they do provide a more insightful comparison of a client’s SEO performance against the ranking and business competitors.
Please leave any questions you have in the comments.
Picture source, CC by Dey via Flickr