Keyword research & competitor analysis

Are you struggling to grow your online traffic and attract the right audience? If so, you are not alone. One of the biggest mistakes business owners often make is the failure to identify their target market. One of the best ways to identify your audience’s needs is through keyword research and competitor analysis.

We’re going to show you how to create a robust, keyword list, quickly and effortlessly which, when used in conjunction with a content marketing strategy delivers sustainable and measurable results.

We have combined several methodologies to make it easy to harvest an extensive keyword list and get to know your target audience. You will learn how to identify the best keyword opportunities based on clicks, buyer intent, website metrics and opportunity score (opportunity score is a Visme formula).

Building your parent keyword list

One of the first things we need to do is to formulate a parent keyword list. Parent keywords are high-level keywords which categorised each niche service or products within your business and are typically the highest-traffic and most difficult keywords to rank for as they are the most desired.

A tool we use a lot is Ahrefs, as it provides the most extensive range of SEO tools available. There are other free and paid options available, we have tried most of them, and you are welcome to explore other options for yourself.

So as a brief example, imagine you are the owner of a sporting outlet, the parent keywords might be clothing, gym equipment and shoes amongst others. In this circumstance, the parent topic does not define the type of clothing, gym equipment or footwear they are.

If you are new to keyword research or realise your competitors have a better grip on the market place than you do, the best place to expand on your ideas would be to look at how your competitors have grouped their products and services.

Large eCommerce websites with thousands of products can be a challenge. The bigger the industry and the range of products and services you provide determine how many parent topics you may need to specify.

As a more detailed example, I’ve created a parent keyword list for a digital agency. Remember our goal here is to identify the primary keywords which are representative of the niche products or services you provide.

Take care not to make your parent keywords too broad or overly narrow.

Parent keyword lists mirror the top-level buzz surrounding the key products and services you provide.

Building keyword lists through keyword research

Now you have formulated your Parent Keyword List; it’s time to start our keyword research and build individual Keyword Lists based on parent topic.

1. Enter the first parent keyword on your list into the Ahrefs Keywords Explorer tool and search for results.  Limit your search region to the country you do business.
2. Select All Keywords Ideas (often thousands of results are returned).
3. Click on More Filters and set Clicks to a minimum of 20; this ensures your target keywords have traffic potential.
4. Click on More Filters and set the CPC range from 0.30 to 100.00 (CPC is a better indicator of buyer intent than Volume).  Your keyword results will be significantly lower.
5. Remove any irrelevant keywords by adding them to Exclude separated by a comma.  You don’t have to be too fussy here as you will have another opportunity to remove irrelevant keywords later in the spreadsheet.
6. Export List.
7. Import into Google Spreadsheet or Microsoft Excel.
8. Remove Columns A, Country, CPS, Return Rate, Parent Keyword, Last Update, SERP Features.
9. Rename Sheet to the Parent Keyword.
10. Create a new Sheet and name it All Keywords List.
11. Copy all keyword rows from the Parent Keyword List into the All Keywords List. You can quickly highlight all rows in Google Sheets by clicking on the first row and pressing Shift + Command + Down Arrow, then Command + C (Copy) and Command V to paste the results in your All Keywords Sheet.
12. Repeat steps 1-11 until all Parent Keyword Lists are completed and imported into individual sheets within your spreadsheet – copying the results of each Parent Keyword List into the All Keywords List.

Your list will no doubt have many duplicates. We will show you an effective way to remove duplicates shortly.

Competitor analysis

Part of the keyword research process is getting to know your competitors.  Competitor analysis is a great way of harvesting some great keyword opportunities you may have missed using the simple parent keyword list strategy above – the key is to identify the right competitors.

Our strategy is to target industry or niche competitors who are leaders in your field.  If you don’t already know who your competitors are, identifying them is easy enough. Your industry leaders will appear at the top of the search results for the majority of the parent keywords you search for on Google.

I’m going to show you how to get to know who are the top five competitors in each niche which you can use to build out your keyword list as well as track your progress. I suggest jotting them down as you will use them to gauge the success of your content strategy.

Identifying competitors

The simplest way to identify competitors is by entering the parent keyword into Google and analyse the top 10-20 results. Another approach which provides more insights is by entering the parent keyword into Ahrefs Keyword Explorer and filtering By domains (located in the left-hand column). This method reveals not only the top results but also the amount of traffic and percentage share each domain receives.

Harvesting your competitors’ keywords

Once you have identified your competitors, you can harvest their best performing keywords based on click-throughs and CPC. These two metrics, in particular, reveal a lot about the audience engagement and buyer intent which each keyword provides.

1. In Ahrefs enter a competitor domain into Site Explorer and press Search.
2. In the left column, click on the Organic keywords link.
3. Click on CPC filter and enter range from 0.30 to 100.00.
4. As filtering by Clicks is not possible when filtering by domain, we will use Volume instead. As we are targeting opportunities with a minimum of 20 Clicks, we are going to use a simple equation Min Volume = (Min Clicks x 5), so we set our Min Volume to 100.
5. Export the list.
6. Create a new Sheet in your Spreadsheets Doc and call it Competitor Name Keywords.
7. Import your competitor keywords into this sheet.
8. The columns of interest are Keyword, Volume, Difficulty and CPC, delete all other columns.
9. Arrange the columns in the same order as your Parent Keyword Lists and All Keywords List, i.e. Keyword, Difficulty, Volume, CPC.Note: Your report does not have a Clicks column, we will add this a little later using the same formula we used to filter the Volume previously.
10. Repeat steps 1-9 until you have analysed between 3-5 of your top competitors using the competitor name to identify each list.

When your Competitor Research is complete, you will have an extensive list of Keywords by Parent Topic, an All Keywords List and individual Competitor Keyword Lists. Your spreadsheet will look something like this;

Now the time has come to do a little cleaning, but do not despair; this process is going to be effortless and quick.

Removing duplicates from your keyword lists

As our primary focus is to create an All Keywords List to draw opportunities from; we are going to focus on cleaning the Competitor Keyword Lists and the All Keywords List of all duplicates.

Why are their duplicates? Well for two reasons;

1. When running search queries in Ahrefs as with any keyword software, there is always keyword overlap and how much will depend on the search query.
2. No software is perfect – you may end up with duplicates on the search query list.

Fortunately, we found an excellent add-on you can use within Google Sheets, which makes it easy to remove duplicate keywords called – ‘Remove Duplicates‘, and it’s available from G Suite MarketPlace.

Grab a copy from within Sheets by clicking Add-ons > Get Add-ons and by searching for ‘Remove Duplicates’. Available as a free 30-day trial, yearly subscription or one-time payment starting at just $19.00. Well worth the investment.

Once you have installed, the next step is to find and delete all duplicates on your Competitor Keyword Lists and All Keywords List.

Note: you do not have to clean your Parent Keyword Lists.

To remove duplicates;

1. With your sheet selected, click on; Add-ons > Remove Duplicates > Find Duplicates or Unique Rows.
2. Select the Keywords column and enter the row range and click Next.
3. Select Duplicates (Find duplicates, exclude the first instance) and click Next.
4. Check the Keywords column and uncheck all other columns and click Next.
   
5. Check the Delete rows with selection and click on Finish and Close.

Your keyword list is now duplicate free.  Repeat this step for each of your Competitor Keyword Lists and your All Keywords List.

Next, we compare our Competitor Keyword Lists with our All Keywords List to remove any duplicates.

Comparing competitor lists with all keywords list

Removing duplicates across sheets is effortless; however, it is important the order in which you do it to ensure you minimise your workload and don’t corrupt your All Keywords List.

1. Highlight your first Competitor sheet in Google Spreadsheets, then click on; Add-ons >Remove Duplicates > Compare Columns or Sheets
2. Select the first Competitor Sheet and under Select your column or table, specify all rows and columns (including header) and click Next.
3. Highlight the All Keywords List and under Select your column or table, specify all rows and columns to D (CPC) as shown (including header) and click Next.
4. Select Find Duplicate values and click Next
5. Ensure both column headers and skip empty cells along with your keyword column boxes are checked and click Next.
6. Select Delete rows within section and click Next.
7. Duplicate values which have been found and deleted are shown; Click Close.
   
8. Repeat the steps comparing all your Competitor Lists with the All Keywords List.

Calculating click potential on competitor lists

Now that we’ve cleaned up duplicates from our Competitor Lists present on our All Keywords List, it’s time to add our Clicks column to our Competitor list.

The above is a compromise, I know, but a small one which affects limited data and allows us to calculate our Opportunity Score later.

1. In your Competitor Lists add a Clicks column to the right of the CPC column.
2. To calculate approximate clicks we are going to divide Volume by five, so in the first cell in the Clicks column enter the value =C2/5 and press enter. Then apply that formula to all rows in the Competitor List.
3. If any decimal values are shown in your Clicks column, you can round those numbers by Selecting Format > Number > More Formats > Custom Number Format and select the rounded sample at the top of the list and Apply.

Your competitor spreadsheet should now look similar to the one below.



4. Copy over all rows in your Competitor List (except the header) to the end of your All Keywords List.

5. Repeat step 1-4 for all your Competitor Lists.

Cleaning your all keywords list

Now that we have moved all our competitor’s keywords to our All Keywords List, it’s time to start cleaning things up.  We need to run the duplicate check one last time as keyword overlap may have occurred between our Competitor Lists.

With your All Keywords List selected, navigate to;

1. Add-ons > Remove Duplicates > Find Duplicates or Unique Rows and step through the duplicate process we did earlier one last time for the All Keywords List only.
2. Sort the All Keywords List by Keyword. Click on the top, left-hand box to the left of the first column – this will highlight your spreadsheet, then right-click on any cell and select Sort Range. Check the box Data has a header row, select Keyword from the dropdown and sort in descending order.
3. Remove any irrelevant keywords rows such as brand, product names or keyword misspellings – this process should only take about 5-10 mins depending on the size of your keyword list. Check over your list a couple of times to ensure all irrelevant terms are removed.

Sorting by Keyword makes it easier to spot typos, duplicates and even questions, you will notice all the how’s, why’s and what opportunities all bundled together.

Calculating keyword opportunity score

As mentioned briefly, the opportunity score is a simple formula which the guys from Visme came up with and is intended to help decide which keyword opportunities to pursue first based on your websites domain authority.

1. On your All Keywords List to the right of the Clicks column, name the cell Opportunity.
2. In the second row of the Opportunity column (F2) add the formula =(1/B2)*E2*(D2+1) and press enter.
3. Apply the formula to all rows of your Opportunity column.
4. If your Opportunity column has decimal places – apply rounding to the entire column.
5. Next, we need to sort our spreadsheet by Difficulty. Click on the top, left-hand box to the left of the first column – this will highlight your spreadsheet, then right-click on any cell and select Sort Range. Check the box Data has a header row, select Difficulty from the dropdown and sort in descending order.
6. Check the end of your All Keywords List. If any cells under the Difficulty column has a zero-value check the row data as low Volume may indicate an irrelevant term may have been missed. If however, you believe the keyword has merit, replace the zero with 20; this will enable the Opportunity score to be calculated.
7. Now that we have a clean All Keywords List with Opportunity Scores, we need to sort that list by Difficulty.

Creating your opportunity list

The last step is to create an Opportunity List, and this step requires you to know your website Domain Authority.

Domain Authority is a score calculated by Moz.com. If you do not know your website Domain Authority, you can subscribe to Moz from $129.00 or try out their 30-day free trial.

Let’s assume you have a Moz subscription or free trial account, and you find your current Domain Authority is 30. By knowing your Domain Authority and Opportunity score will help you determine the best possible keyword opportunities.

We are going to match Keyword Difficulty to DA; this means that if your domain authority is 30, we are going to pursue Keyword Opportunities with a Difficulty score of 30 or less.

Follow these steps;

1. Create a new sheet and name it Opportunity List and copy the header from the All Keywords list.
2. Scroll down your All Keywords List to the first occurrence of a Difficulty score of 30 and copy all rows from 30 and below.
3. Paste those rows into your Opportunities spreadsheet.
4. Now, sort your spreadsheet by Opportunity Score in descending order.

Opportunities with the highest score are the keywords terms you should pursue first in your content strategy as they represent your best opportunity to rank in the SERPs.

Enjoy. You now have a list of targeted keyword terms to drive your content strategy.

Pro tip: The easiest way to build on existing strength is to review your current organic keyword positions. Focus on leveraging pages already in the top 20-30 SERPs results.  Work on generating buzz and building backlinks to move them to page one.

Trending topics

One last bit of advice before you start compiling any content is to check how your target keyword is trending. Be mindful some terms may have a short shelf life, and Keyword Difficulty and Domain Rating scores do change frequently.

Targeting a downward trending topic may have limited future potential, whereas an up-trending keyword can provide a steady stream of traffic for many years to come.

Final words

As you can see, keyword research is not an overly complicated process; it does, however, require patience and a significant investment in time to investigate all the opportunities in your industry thoroughly. Take time to formulate, review and grow your keyword lists.

Focus on answering the needs of your community by crafting meaningful content that serves your audience. Never stop learning and keep looking for new ways to engage your audience, and earn their trust and your perseverance will be rewarded.