Uncover Your Most Valuable Keywords with Aira’s New Keyword Opportunity Estimation Sheet

Whether speaking to senior management or just trying to figure out what direction our SEO strategy should take next, as SEOs we often find ourselves asking the same question: “but what could I get from this?”

Particularly when we’re prioritizing work across different keywords, it can be hard to know:

1. What rank we can expect to achieve (rather than just assuming position 1 for everything).

2. What we could get from that ranking in terms of traffic/conversions.

3. What that means in terms of additional traffic or revenue

It can really trip us up if any of our predictions are based on wild rankings we could never achieve, or if we accidentally include traffic we’re already getting anyway!

So, what’s the solution to solve all of that, and ensure we focus on getting the biggest bang for our buck? Introducing Aira’s Keyword Opportunity Estimation Sheet.

Get your copy of Aira’s Opportunity Sheet here.

What is Aira’s Keyword Opportunity Estimation Sheet?

Built in Google Sheets, the focus of the sheet is to:

1. Identify the highest probable ranking position for different keywords.

2. Estimate what that could achieve in terms of traffic, conversions, and revenue, etc.

3. Highlight the best opportunities so you can prioritize your efforts. This is done by subtracting current estimated traffic, conversions, and revenue from the estimations if you were to rank in the highest possible position.

The sheet takes a top-20 report from a rank tracking tool and:

• Allows you to enter a list of domains you determine you are unable to outrank.

• Removes any ranking positions for domains you’ve listed as those you are unable to outrank.

• Allows you to toggle on/off a list of commonly hard-to-beat domains so you can quickly cut down the list.

• Removes instances where you might be trying to compete with competitors on their own branded terms.

• Automatically picks out where you’re ranking currently to see how much more traffic/conversions you might be able to get on a given keyword.

Here’s an example output:

Let’s dive into an example

Imagine you’re doing SEO for a new flight site. Let’s call it BrainAir.

You know that you can probably rank for quite a few “flights” terms, unless there’s a comparison site like expedia.com already ranking. So, you add expedia.com as a domain to remove and now the sheet will find the best possible ranking you could get for each keyword except for positions where Expedia is already appearing.

In the example below, skyscanner.net and expedia.co.uk are both listed as domains to remove. In this case, position 2 is the highest potential ranking position, as this sheet only removes the specific ranking positions for the domains listed. This means you can still get a better position if that is available.

When doing keyword analysis, you may also realize there are some terms in your list like “Easyjet iceland flights”. If you don’t think you could beat Easyjet for “Easyjet iceland flights”, you can tick a box and the sheet automatically ignores any time Easyjet is ranking for a search that includes the word “Easyjet”.

Why use this sheet?

This sheet can be used to see:

• How much more traffic you could get from an SEO project in general.

• How much more traffic you could get from specific keywords.

• Where you should prioritize your efforts first.

While no estimate is going to be bang on the money, many other approaches will likely wildly overestimate how much traffic you could get in total, as they usually assume a highest position of 1, which is not always attainable.

On top of that, other approaches tend to not look at current traffic estimates, so don’t factor in additional traffic, instead just focusing on total traffic. This leads to situations where you’re focusing primarily on keywords with the highest search volume, as opposed to focusing on the keywords capable of driving the highest amount of potential traffic to the site.

This can help with developing SEO strategies, such as:

• Prioritizing new page creation/re-optimization based on potential traffic opportunity. For example, if this sheet highlights an opportunity to drive a significant level of additional traffic for specific keywords, you may decide to prioritize building out new landing pages, or re-optimizing your existing content to target those keywords more effectively.

• Prioritizing technical fixes based on potential opportunity. For example, if a tool such as Little Warden highlights technical issues , then this sheet can help you see the highest potential rank for specific keywords, should those issues be fixed.

• Seeing which competitors are having the biggest impact on ranking positions for specific keywords/keyword groups. For example, if you see that the same domain consistently appears as one you need to replace in the rankings, then it will be worth investing time looking at their strategies and approaches.

How to use the sheet

Click this link to get your copy of the Opportunity Sheet.

Stage 1: Copy & Paste in your top 20 report

Import your top 20 report into the tab named [Input] Top 20 Ranking Report, pasting into cell A1. You should delete/override the existing dummy data. The top 20 ranking report can come from any rank tracking tool, for example STAT, Rank Ranger, Accuranker, Data For SEO, etc.

At this stage, the order of the columns does not matter.

Stage 2: Selecting which columns to include

Navigate to [Input] Column Selection.

Here you need to use the dropdowns to select which columns relate to:

• The keyword column

• The search volume column

• The rank column

• The URL column

The table on the right-hand side will automatically update as these different dropdowns are updated with the first six rows.

Please check if the data aligns with the correct columns (or the rest of the outputs are going to look really odd).

Stage 3: Defining which domains you’re unable to compete with

Navigate to [Input] Domain Selection.

The first step is to enter your domain. The example in this case is booking.com

Next, add the domains you feel you’re unable to compete with. These can either contain the protocol or subdomain, or not.

You can find your competing domains by using a tool such as Moz’s Free Domain SEO Analysis Tool. Alternatively you can go to [Output 4] Current Traffic/ SOV Per Domain, which displays all of the domains ordered by total estimated traffic and share of voice.

We would recommend revising this list once you have seen the results, to include any additional domains you hadn’t previously included.

On the right side of the page, you can choose to include pre-defined domains in the list. This includes standard, hard-to-beat domains such as Google and Amazon, or social media domains such as Facebook and Twitter.

Finally, you can decide whether you want to exclude domains that contain target keywords in the domain name. For example, if you don’t think you could beat Easyjet for “Easyjet flights”, tick this box and the sheet automatically ignores any time Easyjet is ranking for a search that includes the word “Easyjet”.

Stage 4: Entering CTR, conversion rates, and average order values

Navigate to [Input] CTR, Conv Rate and AOV.

This section is designed for you to enter an estimated CTR for each position, average conversion rate, and average order value (AOV).

You can access the CTR position data yourself by using Google Search Console. It would make sense to focus on non-branded keywords, as branded keywords would skew these figures.

Advanced Web Ranking also provides an average CTR for each position for different industries based on a sample of sites. This can be found by navigating to the categories tab on this page.

Note: If you leave this column blank, traffic estimates will use the Average Non Branded CTR from Advanced Web Rankings.

The Conversion rate and AOV data can be found using Google Analytics, though it’s worth noting that these figures will vary depending on the type of page. For example, a blog is likely to have a much lower conversion rate than a product page, so it’s worth bearing that in mind during your analysis.

Stage 5: Output 1 — Keyword Breakdown

Navigate to [Output 1] Keyword Breakdown.

Here you can see the top 20 report with just the four columns that were previously selected: Keyword, Search Volume, Rank, and Ranking URL.

There are a number of additional columns:

• Domain: The domain of the ranking URL.

• Can our site outrank this domain?: This column tells you whether you “Can Compete” or “Cannot Compete” with each of the domains for their ranking position, depending on whether they are included in the domain list in [Input] Domain Selection.

• Is it our domain?: This signifies whether it is the domain you have inputted in the [Input] Domain Selection tab.

• Highest Potential Ranking Position for your site?: This column shows whether the column is the highest potential ranking position for that keyword.

• Domain Name Mentioned in the Keyword?: This column tells you whether the domain name is mentioned in the keyword.

Stage 6: Output 2 — Keyword Highest Rank

Navigate to [Output 2] Keyword Highest Rank.

Here you can see a summary for each keyword showing you the highest potential rank, estimated traffic, and conversions/revenue, as well as which domain/URL you could conceivably outrank.

There is also data related to your current rankings and potential increase in traffic, conversions, and revenue should you reach the highest potential ranking position.

These figures are based on your previous inputs, so go back and check what you have entered if you feel that any of the figures are noticeably different to what you would expect.

Stage 7: Output 3 — Keyword Highest Rank

Navigate to [Output 3] Keyword Opportunities.

This output provides the top-level summary focusing on the keyword, search volume, and which domain/URL you could seek to replace. The metrics in this output are focused on potential additional traffic, conversions, and revenue.

These figures are calculated by working out estimated current traffic, conversions and revenue based on current rank, search volume, conversion rate, and average order value and subtracting this from these figures should the domain rank in the highest potential position.

Stage 8: Output 4 — Predicted Traffic/SOV Per Domain

Navigate to [Output 4] Predicted Traffic/SOV Per Domain.

This output provides an overview of the total estimated traffic per domain from the top 20 report, which allows you to see which domains are driving the highest amount of traffic across your keywords.

There is also a Share of Voice column, which pulls in the share of voice for each of these domains. The calculation is total traffic per domain/total traffic across all domains.

On the right side of the page, your own domain’s current estimated traffic will be pulled through, alongside Share of Voice.

You can then enter competitor domains into the boxes below, which will provide total estimated traffic and share of voice with a comparison to your own domain.

How does this Google Sheet work?

This sheet is designed to allow you to import a top 20 rankings report for your priority keywords, select which domains you feel you aren’t able to outrank within your niche, and optionally enter in CTR figures by position, average conversion rate, and Average Order Value (AOV), if you have access to this data.

Then, in the [Output 3] Keyword Opportunities tab you’ll get a list of the best potential rankings you could get for each keyword, ordered by total additional traffic and revenue you could get from on top of what you are currently getting.

In order to make all this magic happen there is a fair amount of Google Sheets spice happening in the background, so if you’re a Google Sheets enthusiast, you may enjoy taking a look under the hood to see how we’ve pulled it together.

The main formula used is the QUERY function in order to pull specific data from one sheet to another, which automatically updates based on the user’s selection on the [Input] Column Selection tab. This logic is used in all the main outputs.

The domain selection uses REGEX in order to combine together a list of different domains which are used in the different outputs to determine whether a site can rank for a specific domain or not.

Whenever working with rows, we use ARRAYFORMULAs in order to ensure that the formulas are applied to the whole column.

In the example below, we are using the regex above in order to determine whether a domain can or cannot compete for different ranking positions.

The estimated traffic, conversions, and revenue positions take the inputs from the [Input] CTR, Conv Rate and AOV tab.In the example below, we are working out the estimated traffic by multiplying the highest potential rank (in column C), looking up the CTR for that position and then multiplying it by the Search Volume (in column B). The same logic applies to the conversions and revenue figures.

Working out the difference in potential vs. current position is done by subtracting the estimated traffic from the current estimated traffic. There is some additional logic in there to catch whether the current traffic is higher than the potential traffic (as we obviously wouldn’t want the potential rank to be lower than the current rank).

These are the fundamentals, but if you are interested further, do make a copy, unhide the hidden cells, and have a good look under the hood.

Final thoughts

Within SEO, it’s critical to focus on impact when delivering results.

When you have a list of keywords, it’s often tricky to know where you could potentially rank, what levels of traffic you can earn, and how this relates to conversions and revenue. Aira’s Keyword Opportunity Estimation Sheet tries to answer these questions.

Please reach out on Twitter to let us know how you get on with it!