The Keyword Analyser

Step 1: Identify The Competition:

You may already know your competition, but often your commercial market competitors aren’t your only organic competitors so it’s good to follow this step just to double check you’re looking at the right people in the space. 

We recommend that you have at least 3 competitors, the ideal number is 5 but this can be increased if you would like to. Start adding your competitors to the ‘Competitors’ tab, in Column A input the name or brand of the website, and in column B the pure domain without any SSL or subdomains. 

If you want to know who is dominating the SERP estate that you are looking to target, simply search a handful of your top head terms and cross-reference the competitors that appear, if domains appear in more than one list – add them to the list! And that is one tab down already!

Step 2: Collect Your Own Data

Understanding the competition is one thing, but you also need to compare this against yourself to really know how to move the needle. You will be collecting all the keyword data that your domain ranks for to see how it stacks up against the competition. 

This is where you will need access to any popular search tool, such as SEMrush or Ahrefs – all tools provide the same metrics (albeit slightly different outputs

sometimes, so just use your favourite). We will be using Ahrefs for this task.

In the tab ‘Client Data’ there are only 3 columns which need input, but it is crucial that you input the right data into the right columns to allow the Analyser to compare & display the right data. Using a search tool, input your domain into the site explorer, head over to organic keywords & export the entire list. 

Once you have the list downloaded, simply pop in the column’s keyword, current position and ranking URL, and you’re done with this sheet! On to the next.

Step 3: Pull Your Competitors Data:

In the sheet ‘Competitor Data’ as you can imagine, this is where we will be putting all of our competitors’ data to be crunched and analysed. Columns B – H are input columns which will need to have data popped into, columns A,J,K, I  (this column is hidden) & L are all automatic and will not need any attention from you. 

It’s really important in this step that you input the right data into the right columns for the Analyser to output the accurate data later on. The Analyser has the correct columns already but just in case you misplace them they should be named as below, if you run into any problems just rename them as below and stick to the column names.

• B – Keyword
• C – SERP Features
• D – Volume
• E – KD
• F – CPC
• G – Current Position
• H – Current URL

Simply take your competitor’s domain from the ‘competitors’ tab and drop it into the site explorer and head over to organic keywords. Once you’re here this will display all the keywords that this domain ranks for. Before we export the data we will clear it up a bit. 

Apply the filter to only rank for keywords that the domain ranks into the top 30 positions for, this makes sure that the keywords we’re exporting are more relevant than the total top 100. 

If the list is still pretty big, or you only want to target the most high volume keywords, feel free to add a volume filter as well to filter out anything that has less than 50 search volume. 

After cleaning up the data with filters you should (hopefully) have a minimum of 500 keywords. Export these keywords and drop in the relevant columns to the ‘Competitor data’ tab in the Analyser, making sure to only enter data in columns B-H. 

Once you have dropped the data in you will start to see columns A, J,K & L update appropriately. Columns J, K & L will determine whether our domain is currently ranking for the competitor keyword & which URL is ranking. Column L will count the amount of times a unique competitor ranks for the keyword, if all your competitors rank for the same term – it’s safe to assume it’s a relevant term that you want to rank for too!

Rinse & repeat this cycle for all your competitors until you have gone through all of them and have a big list of competitor data! 

4 – Be on time (and trend) 

Seasonality plays a huge part in many industries, knowing when your peaks & troughs are going to be throughout the year can ensure you align content, production & targeting all in plenty of time to be where you need to be when you need to be there. Seasonality by keyword & category can help you take your strategy to the next level and plan out your efforts for the next 12 months based on exactly when you need to be at the peak. 

We’re using the Keywords Everywhere tool here, to pull data in bulk, the tool will pull the last 12 full months of search volume data to allow you to map out those peaks & troughs. You’re going to take your keyword list from the competitor data tab and open up the Keywords Everywhere tool like this – and click bulk keywords data. This will open up a text box which you can paste all of your keywords into, there is a maximum keywords you can analyse at one time so you may have to do this part in a couple of steps depending on how many terms you have. 

Once the data has been processed you have to move it across to your ‘Seasonality’ tab in the Analyser. Exporting this data in excel or CSV is the easiest option here as the copy function isn’t the best formatted. 

Once exported this will give you a datasheet of A-Q columns, copy all columns and drop straight into A1 (make sure you paste values only, to maintain the format of the sheet). And that’s another sheet done!

5 – The Analyser

Now for the fun bit, where you can see all of your data and hard work visualised and start to plan that killer strategy which is cemented in data points from the competition. The only cell you need to change on here is C/D2 which is where you enter your keyword that you want analysed. This is case sensitive so if it’s not working with capitals, try without and vice versa.

There are crucial data points here, which display about the keyword you have selected, including search volume, keyword difficulty (how difficult is it to rank), CPC, your own rank for the website you are analysing, the URL that currently ranks & then it’s trending volume.

On the right hand side you have the seasonality of the keyword to guide you through the annual peaks,  if the keyword is trending positively it is more fuel to the fire in terms of ramping up the strategy to make sure you hit these peaks. 

The bottom half of the Analyser is dedicated to the competition , showing the name of the competitor, the position they are ranking and the URL that ranks. This is where you can piece together your strategy and understand where you might be missing the mark. 

You can usually identify the intent of a URL by the terminology and structure used, if it’s consistent across competitors but you’re looking elsewhere then it may be time to change up the targeting. If all competitors are ranking a guide for a keyword and you’re ranking lower with a category page, it’s time to write extra information and provide users with more detail than you are currently. 

This can take your strategy to the next level by understanding what the competitors are doing and how they are achieving it. 

Hopefully this has been useful & you can understand how to either implement this into your current strategy to take it to the next level or use it as a starting block to build your strategy from.