How to Predict SERP Traffic Using Microsoft Excel

How do you know if a search phrase is worth ranking for?
You would use the keyword traffic estimator, right? Well, yes, but that is only part of the story.
Unfortunately, ranking for a phrase does not give you all the traffic for that search result.
In fact, the first result gets the lion’s share, with big reductions as you go down the page.

So you need to be able to calculate how much traffic you will expect to see and work from there.
How do you do that? You guessed, we can work it out using an Excel Spreadsheet!

The Spreadsheet

Download out spreadsheet from here.

As you can see there is a space for you to enter your search term and a big “Calculate Traffic” button.
Unfortunately due to the traffic estimation tool utilizing a capcha image we could not scrape
the monthly estimated traffic so you will need to type that in until we can find an alternative source for that data.

When you hit the button the spreadsheet will go away and get the search results then calculate what share of the traffic each link is likely to receive. I estimated the traffic by multiplying the monthly traffic with the percent of traffic each serp gets. Those estimates came from this article.

The article is a little old, but the data is still valid according to what I see in my webmaster central account for our domain.

Summary

Is a search phrase worth attempting to rank for if you can only expect to rank in 3rd or 4th place? Now you can work out the answer in seconds using this handy spreadsheet!

About the author

Yoav Ezer co-authors Codswallop, a technology and productivity blog. He is also the CEO of a software company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter or leave a comment in the comment section below.