The other week I wrote about SERP Click Through Rates. This isn’t really useful on it’s own though. To give any meaningful insight, it needs to be combined with keyword volume (usually from the Google KWR Tool) and your current rank for the keyword.
I know that rankings are a bad measurement as they are so variable – depending on device, personalization, location, Google testing new algorithms, etc. That said, you can’t just not do anything, you have to work with what you’ve got.
I’ve created a keyword research and opportunity tool. It will show you the keyword opportunity based on your current rank (and associated estimated traffic) subtracted from the estimated traffic associated with a number one spot ranking.
Related: Learn how to master keyword research
As you can see in the screen shot below, the ‘Opportunity’ tab shows your current rank, the monthly search volume and how much traffic you can gain by grabbing a number one spot. The estimated traffic is based on the Chitika model – Traffic estimates based on all available models are available in a different tab.
To make this more useful (and persuasive), I created a second version of the tool. This one requires a little more work on your end but will show you an estimated revenue opportunity in addition to the amount of traffic you can gain.
How to Use the Keyword Opportunity Tool
1. Open up the Keyword Opportunity Tool and make your own copy.
2. Grab your rankings from your rank checking tool – If you don’t have one yet, I recommend Authority Labs; it’s what we use at Distilled.
3. Then go and get the [exact match] keyword volumes associated with the keywords you’re tracking (or keywords you want to rank for).
**If you’re going to use ecom data from GA, you’ll need to remove the [brackets] from the keywords exported from the Google Adwords Keyword Tool. The easiest way to do this is to open up the csv in Excel and do a find and replace for “[” and “]”, replacing them with nothing.
4. Now open up the Keyword Opportunity Tool, and open the ‘Data: Adwords KW and Rank’ Tab. Paste the data from the Google Adwords Tool into cell A3. Then paste the associated ranking data into cell F3.
If you don’t have ecommerce data, skip to step 6.
5. To add in the ecom data, first make sure that you are using the proper version of the Keyword Opportunity Tool (w/ Revenue). Now go to GA, and go to the organic keyword report. Then click on ecommerce in the explorer tab.
Now export all the data.
Copy the data into the ‘Data: Analytics Ecom’ tab, starting in cell A3.
6. Now click back over to the ‘Opportunity’ tab. This will display how many visits a month you stand to gain based on the Chitika Model. If you’ve entered ecommerce data, it will also show how much revenue you can gain by attaining a number one ranking.
To view traffic opportunity based on other models, click to the ‘Est. Traffic – All Models’
Below are the links to the spreadsheets:
Keyword Opportunity Tool
Keyword Opportunity Tool (w/ Revenue)
**All sample data used in the input tabs is fictitious
In order to keep stakeholders engaged with the performance and bought in for subsequent projects, you should keep them updated on the performance of the initiative that they signed off on. I have created a Google Data Studio SEO Dashboard Template that you can customize to show the performance of your project.
.
Hey Geoff,
Thanks for sharing. This is a very cool spin on a similar process that I talk about a bit in this post: http://www.seonick.net/100000-visitors-per-month/
Big major difference between your model is that this is the revenue side of things only and doesn’t discount opportunities for say time and costs associated, i.e. average costs for content, links, etc. associated with gaining new rankings.
I really like using actual rankings versus assumptions, i.e. I use a blanket CTR for positions 1-5 ~12% vs. 6-10 ~5% and so on.
Smart stuff.
Hey Nick,
I read that post a while back, it was really cool. Thanks for sharing. When you discounted for cost was it based on KW difficulty score or was it more indepth, considering actual time/cost to develop and rank content?
Glad you like it
Thanks for the compliment Geoff :) Discounts were done at an aggregate level based on SERP, so based on average scoring metrics for each page 1 SERP (avg DA/PA/# links?LRD’s/etc.) we created discount rates for each metric and then rolled a formula that looked something like (MSV*CTR)((DA*DR)(PA*DR)*LRD’s) to get approximate expected traffic within a set amount of time (say 90 days) then took average link+content development costs to come up with a total traffic projection and total cost estimate.
From there multiplied traffic by average conversion rate, and that by average conversion value, and then dividing revenue by cost to compute the mean efficiency rate (MER) which is the metric that drove all decisions around keyword opportunities. I’m writing about it now, hopefully will be finished with the post soon :) Really good stuff here though!
Thanks Geoff for the Keyword Opportunity Tool. Small world. We have the same last names. I’ve not looked at the spreadsheets yet, but I’m sure I will benefit and learn from using the two Excel spreadsheets.
Joaquin
Hi Geoff,
Great tool, really useful while choosing the right keywords to rank.
I’m wondering why did you chose Chitika model, in your opinion is the most similar to google/bing ?
Hi Simon,
I ended up using the Chitika one as it was in the middle of the other models I had available. If I get better data, or one model proves more accurate, I’ll revise
The add keyword data tab has directions at the top that conflict with your post. The post is correct in saying paste to A3 the actual tab says post data to G3 – I know I am a Consistency Nazi, not tryin to call accidents out, just helpin! Hope your days great and Thanks for the tool!
Good catch – it was initially formatted differently :)
Hi Geoff,
Great stuff. I only have a question. Maybe I am not familiar with the models included, but for the keyword “type of wine” , does this make sense?
Rank 6 returns an estimated traffic of 1582 and $2055 Rank 8 returns an estimated traffic of 1668 with $2166 A lower ranking 8 vs 6 returns more traffic and more money? It doesn’t make sense.
Sorry if it’s an obvious question :)
Thanks for your insights.
Regards from Spain.
Hi Diego,
The example from the spreadsheet that you pointed out doesn’t make sense because the data is fictitious. All of the data inputs (keywords, ranking data, and GA eCommerce data) are from different sources. If you put your own data in, it will look more reasonable.
The estimated revenue opportunity is based on the GA ‘Per Visit Value’. Take a look at the data. If you ignore the keyword and look at how it’s calculated, it will make more sense.
Also – for ‘types of wine’, there were only two visits (according to the GA, which is for a different data set). You’ll probably want to raise this so that you are getting more valid data.