How to Use SEMRush for Competitor Keyword Research to Find Content Gap Opportunities

SEMRush is a powerful tool in many ways, but one of its greatest offerings is competitor data. This step-by-step guide will show you how to identify “content gaps” in your client’s keyword footprint through analysis of the keywords that their competitors rank for (but your client does not).

Supporting Documentation: Client Competitor Keyword Research Example

Step 1: Accessing Organic Keyword Data in SEMRush for Client Domain

Once logged into SEMRush, enter the client’s core domain into the search field (i.e., SEMRush, enter the client’s core domain into the search field (i.e., goinflow.com).
Then find the “Top Organic Keywords” section in the middle of the page and click “Export” as shown below. The goal here is to get all your client’s organic keyword data out of SEMRush and into a CSV spreadsheet.

Next, import this CSV file (of client keyword data from SEMRush) into a Google sheet in Google Drive. Name the spreadsheet appropriately. Freeze row 1 and column 1. Here is an example: Client Competitor Keyword Research Template: GoInflow.com.

Step 2: Identify Competitors and Export Their Keywords

The next step in the process is to look below the “Top Organic Keywords” section in the middle of the SEMRush.com page and find the “Main Organic Competitors” section. Click on the “View Full Report” button to be taken to a new page, which will list all competitors, ranked by the number of SEMRush’s determination of “Competition Level” (heavily influenced by the number of common keywords but also influenced by the number of keywords that the competitor ranks for).

After clicking the button, you’ll be taken to a new page where you see a section like this:

Here you will want to right click on the competitors that you wish to export keywords for. Open them in new tabs (in your browser) and begin exporting their keywords into separate CSV files, just as you did for your client’s domain.

Pro Tip: Review the domains and ensure they truly are competitors. Just because they have a lot of commonly shared keywords, you may not consider them true competitors. However, even if they are not true competitors, you may want to export their keywords if the site has content about topics that your site should. For example, googlewebmastercentral.blogspot.com is not a competitor of Inflow, however, they have a lot of content about the topics that potential Inflow clients are searching for. Thus, they are likely to be an excellent source of keyword data.

Step 3: Import Competitor Keyword Data Into Your Google Sheet

The goal here is to get all your competitors’ keyword data into a single worksheet along with your client’s data. This will allow you to sort and filter the worksheet by different columns in order to identify content gaps that your client may need to target.

Pro Tip: For competitors that have a very large amount of keywords, you may need to draw a cutoff point, otherwise your Google Sheet will become very sluggish due to the intense resources required to manage tens of thousands of URLs. While Excel may be better at this, the advantage of Google Sheets is that it is collaborative and will enable remote collaboration between you, team members and your client. It’s suggested that you draw this cutoff point before importing into your Google Sheet by deleting all rows with less than a certain amount of searches per month. This is your call on where to draw the cutoff point, but 100 or more monthly searches often works well.

When importing the competitor CSV files into your Google Sheet, here are some things to keep in mind:

• Consider exporting keywords for known competitors that SEMRush didn’t list. This can help ensure that you are targeting as many keywords from the client’s true competition as possible.
  • In this example, we additionally exported keywords for seerinteractive.com and distilled.net, since they are large agencies with excellent content marketing strategies that we can learn from by analyzing their keywords.
• For competitors with many thousands of keywords, filter the first row of their CSV export file and then sort by the “Search Volume” column (descending) in order to find a cutoff point, typically between 50 and 100 searches (depending on the number of keywords). You want to try and keep it to no more than 3,000 – 5,000 keywords per competitor. Too many CSV imports of this size will cause your Google Sheet to become sluggish and difficult to work with.
• Only import the keywords above your cutoff point for search volume in order to keep the Google Sheet from getting too sluggish. It can be more difficult to delete the keywords below your cutoff point in Google Sheets than in Excel.
• The cutoff point for competitor CSV files with many thousands of keywords can be different for each competitor. There are no hard and fast rules. Do what you think is best to get the most useful data set in the end.
• There is no need to cut off any keywords for competitors that only have several hundred to several thousand keywords. Use the cutoff strategy for competitors who have 5,000 or more keywords. This is a rough estimate. Again, it is subjective.
• You will likely need to add another row to the bottom of the Google Sheet each time you import a new CSV file.
• Be patient with larger CSV files that you import. It may take Google Sheets 10-30 seconds to fully process the import.
• Remember to delete the first (header) row of each new CSV file that you import. It’s not needed.
• Wait to sort the worksheet by any of the columns until you’ve completed all your competitor CSV file imports.
• Beyond 10,000 – 15,000 rows of data, the Google Sheet will be noticeably sluggish, so consider this when determining how many competitor CSV files to import.
• When complete, delete any unused columns to help minimize the Google Sheet file and improve performance a bit. Consider also deleting the “Trends” columns at the far right of the worksheet, as this data is not useful in spreadsheet format.

Step 4: Work With Your Data

Now that you have all of your data consolidated into a single worksheet, you can begin having fun! Here’s a breakdown of steps that we recommend you follow.

Pro Tip: Sorting in sequential steps can allow for helpful grouping of keywords with the same metrics in the same column. So, if you sort the keyword column alphabetically first and then sort by search volume, any keywords with the same search volume will be sorted alphabetically amongst the group. You may also sort by multiple columns in a single step using the Data > Sort Range feature in the top menu of Google Sheets.

Step 4a: Filter The First Row

Before you do anything, filter the first row of the worksheet. Note that this needs to be done once all data is added to the worksheet to ensure that all rows are caught in the filter.

Again, be patient when filtering row 1 for worksheets that have 5,000 or more rows. It might take 10-30 seconds for Google Sheets to process.

Step 4b: Sort by Position (ascending)

You will want to put your keywords in order of ranking position before sorting the worksheet by any other columns. This will help you to prioritize the order of sites ranking for the same keyword by ranking position.

Step 4c: Sort by Keyword (ascending)

You will want to put your keywords in alphabetical order before sorting the worksheet by search volume. This will allow you to ensure that the same keywords are grouped together after the worksheet is sorted by search volume.

Step 4d: Sort by Search Volume (descending)

The final step in sorting is to sort the worksheet by search volume (descending). This will put the keywords with the most search volume at the top of the worksheet, allowing you to prioritize your review by the keywords that can potentially send the most traffic.

Step 4e: Add a Column and Count Occurrences of Keywords

You will want to add a new column to the left of “Position” by highlighting the “Position” column, and then selecting Insert > Column Left in the top navigation menu of Google Sheets. This will produce a new column B. Name it “Occurrences” and add this formula into cell B2:

=COUNTIF(A:A,$A2)

Click enter to save the formula in cell B2.

Copy and paste cell B2 all the way down column B by following these steps:

1. Highlight cell B2
2. Click CTRL+C on your keyboard
3. Select all cells in column B, below cell B2, by clicking SHIFT+CTRL+C on your keyboard
4. Paste the formula by clicking CTRL+V on your keyboard
5. Be patient. It may take 10-30 seconds for Google Sheets to process depending on how many rows are in your worksheet.

Step 4f: Add a Column to Use for Tagging Content Gaps and Content Types

Finally, you’ll want to add another two columns, this time to the left of your new “Occurrences” column, and title it “Gap?” This is where you’ll tag rows as content gaps to potentially target.

You’ll also want to add a “Content Type” column, to designate whether the keyword is best targeted via a “Foundational” page (category page, product page, etc.) or “Strategic” page (article, etc.). This will be helpful when moving keywords over to your editorial calendar(s).

Step 4g: Filter to Show Only Client URLs and Change Font Color

Now you’ll want to filter the worksheet to only show your client’s URLs, and change the font color so that you can easily identify them when looking for content gaps.

Change the font color of your client’s rows to your preferred color. Here is what it looks like with red font color (only viewing client URLs).

When you “unfilter” the URL column to show all rows again (not just your client’s rows), you’ll see a mix of red and black font colors as you scroll down the worksheet. If your client does not rank for any of the keywords with the most search volume, you may have to scroll down a while to find them. That’s a clear indicator that there could be a plethora of content gaps to target.

Pro Tip: You will likely find a fair amount of bogus, unrelated keywords in this list. Ignore them.

Step 5: Identify Foundational Content (Keyword) Gaps

Foundational keywords will typically be “head” terms and “chunky middle” terms that are typically targeted on category or service pages. They will often consist of one to three words and have higher search volume. Examples include content analysis, seo training and conversion rate optimization services. However, foundational keywords can also come in the form of long-tail keywords, typically specific product name or service type queries. These keywords are typically later in the buying cycle, as users have identified a solution to their problem (to varying degrees).
You will typically find foundational keywords toward the top half of your worksheet, but they could be anywhere. Here are some tips to find foundational keywords that may have less competition right off the bat.

• If the client is an eCommerce company, filter the “Keyword” column to only show keywords with eCommerce qualifier keywords, such as: sale, discount, free, online, shop, store, reviews, etc.
• If the client is a services company, filter the “Keyword” column to only show keywords with service qualifier keywords, such as services, professional, agency, company, case studies, testimonials, reviews, etc.
• If the client is a local business, filter the “Keyword” column to only show keywords with the location(s) related to your client (i.e., denver, colorado, downtown, uptown, cherry creek, compton, bermuda triangle, never never land, etc.).
• If the client offers international services, filter the “Keyword” column to only show keywords with the location(s) related to your client, such as international, overseas, global, worldwide, multinational, etc.
• …what else is unique or relevant to your client? Filter to find it!

Here’s a screenshot showing some foundational (keyword) content gaps for our client:

Step 6: Identify Strategic Content (Keyword) Gaps

Strategic keywords will typically be long-tail keywords that (usually) have less search volume than foundational keywords and are FAQ style (what, when, where, why, how, etc.), consist of qualifier words (best, most, essential, etc.) or even a structure unique to your client’s industry. These keywords are typically earlier in the buying cycle, or post-conversion, and often represent a customer’s interest in getting more information about a product or service to either help them make a purchase decision, validate an already made purchase or get other sorts of information about a product or service.
You will typically find strategic keywords throughout the middle and bottom of your worksheet. Here are some tips for quickly finding strategic keywords:

• Filter the “Keyword” column to only show keywords with the following FAQ words: what, when, where, why, how.
• Filter the “Keyword” column to only show keywords with qualifier keywords such as: best, most, cheap, etc.
• Filter the “Keyword” column to show related, non-competitive brands that your client either sells or offers services for, such as these for Inflow: google, bing, yahoo, twitter, facebook, copyscape, keyword planner, etc. Note that keywords that are more “inquisitive” in their nature or intent are usually best to be considered “strategic” keywords as opposed to foundational.
• Sort by character count. Add a new column to the right of the “Keywords” column and put the formula =len(A2) into cell B2. Copy this all the way down column B. This will count the characters in each cell of column A. Then sort the spreadsheet by keyword character count and review the longest keywords. Consider filtering to only show keywords with search volume greater than X amount of monthly searches (i.e., 100 monthly searches).

Here’s a screenshot showing some strategic (keyword) content gaps for our client:

Keyword Competition

SEMRush offers the following competition data. Here are their definitions:

• Competition: Competitive density of advertisers using the given term for their ads. One (1) means the highest competition. Use arrows to display results in ascending or descending order.
• Number of Results: The number of URLs displayed in organic search results for the given keyword. Use arrows to display results in ascending or descending order.

You may consider sorting your chosen keyword gaps by these columns in order to find the least competitive keywords, however, this is not necessarily the best way. Alternative approaches consist of running them through Moz’s Keyword Analyzer tool or using the allintitle:”<keyword>” search query to find the number of search results in Google that have the exact keyword phrase in the meta title.

Step 7: Add Keywords/Topics to Your Editorial Calendar

Once you’ve identified enough foundational and strategic keyword topics to keep you and your client busy for the next 6-12 months, add them to your editorial calendar and prioritize by opportunity (search volume, competition, historical optimization opportunities, etc.).

There you have it. Have you found different ways to use SEMRush for competitor keyword research?