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 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.

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.

Free Keyword Research Tool - Keyword Opportunity

This is a screenshot of the ‘Opportunity’ 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.

Keyword Research Tool With Revenue Opportunity

**None of the data in the spreadsheet is real

How to Use the Keyword Opportunity Tool

1. Open up the Keyword Opportunity Tool and make your own copy.

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.

Select Ecommerce View

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.


Share This