Step-By-Step Guide for Creating a Content Gap Analysis

Insights / 11.18.2020
Tyler Trent / Sr. SEO Strategist

1/23/2024 8:09:05 PM Red Door Interactive http://www.reddoor.biz Red Door Interactive

Problem: Your website needs to attract more relevant, qualified organic traffic but doesn’t have a strategic approach or tactical solution to meet that need.

You have questions and aren’t sure where to get answers. What matters to your audience? Where does their consumer journey begin, and with what information? How do they discover information about your industry, products, or services? Who is providing that information? Is it reliable?  There are plenty of questions to consider when making decisions about your website’s organic search strategy.

Solution: Thankfully, the vast swath of data available to today’s marketers can provide actionable answers to most, if not all these questions.

As agency marketers, our clients look to us for concise observations and actionable insights to grow their earned marketing channels. SEO, content marketing, social media, etc. – with any of these approaches, content creation is the foundation upon which success is built. After building a business case, getting approval, and obtaining a budget, what content should be created? Ideation is a challenge and has traditionally been a subjective, creatively driven process. Often, a group of folks are sharing opinions about what should be created and how, but those opinions can clash. Today, we can access the wide world of digital marketing data to provide proof for our content ideas when sharing them with our teams and presenting them to clients. Using this data effectively enables you to build the best content – content that resonates with your audience and drives organic performance for your earned media channels.

Collecting the data is not the first step, though. What data will you collect? In this case, we are going to show you how to see exactly what pages on your competitor’s sites are getting traffic. We’re going to take a proverbial peek into their website analytics and use their insights as our own. How can we get insights from our competitor’s performance? We’re going to perform a Content Gap Analysis (CGA) – a process that uncovers a variety of great insights that strategically inform your content and SEO efforts. 

What’s the Relationship Between Competitor Keyword Analysis and Content Gap Analysis? 

Competitor keyword analysis can be a part of content gap analysis, as understanding the keywords your competitors are targeting helps you identify gaps in your own content. By analyzing the keywords your competitors rank for, you can identify keyword opportunities that you may have missed and include them in your content strategy to enhance your visibility in search results. While keywords are a part of both analyses, the content gap analysis3 takes a broader perspective by considering t3he overall content strategy, topic coverage, and content types. It looks beyond individual keywords and examines the content ecosystem as a whole. The analysis helps you understand not only what keywords competitors are targeting but also what content gaps exist that you can capitalize on to differentiate yourself and provide unique value to your audience. 

Important Content Gap Analysis Tips and Things to Consider


  1. Your Vertical’s Competitive Landscape: Who leads the pack? Are there new challengers to consider?
  2. Estimated Market Opportunity for New Products: Is there a niche that your competitors are (or aren’t) serving that you’ve considered targeting with your products or services?
  3. Content Ideation: What topics have your competitors used to earn traffic more effectively than you? What relevant topics are your competitors not considering where you can seize an opportunity?
  4. Content Types: What types of content (articles, videos, infographics, images, FAQs, etc.) perform well for your vertical? What types of content perform poorly?
  5. Keyword Opportunity: The data collected in a CGA provides valuable keyword research, reducing your overall workload.

At Red Door, we take a data-driven approach to content research and now you can too! Download our content gap analysis template and follow the instructions outlined below to learn how to identify content gaps. And while some might consider this analysis to be one of our SEO Team’s secret weapons, we want to share it with the entire marketing community to repay you for some of the incredible ideas you’ve given us along the way.

Using Content Gap Analysis to Win your Clients’ Hearts (and create content, too)

Our process transforms the Content Gap Analysis workbook (we’ll walk through that later on) into a PowerPoint presentation and highlights just the key insights. This way, we don’t melt our clients’ eyes with the million-plus data points that make up this monster. Here’s a slide from a client presentation showing our client only receiving <5% of the organic traffic that the best competitor is earning for this keyword group.


These insights are used to estimate the organic traffic reward for improving a client’s content. Through this keyword competition analysis, Red Door then is able to make a business case saying, “Competitor X is earning 4x more organic traffic than you are for this niche. If you take your current traffic for this niche and multiply it by 4, that’s the kind of performance lift we can expect from launching this project.”

Now that you understand the significant value of this research, let’s walk through the Content Gap Analysis tutorial.

Content Gap Analysis Walk-Through

Follow these instructions for a step-by-step guide to creating a Content Gap Analysis for your business or client.

The Ingredients

  1. This tutorial (Oh look, you’re here. Easy, right?)
  2. The Red Door 2021 Content Gap Analysis Template
  3. Microsoft Excel
  4. Access to SEMrush, or BrightEdge DataCube; we typically use BrightEdge, but will use SEMrush in our tutorial
  5. Your domain
  6. Your top 3 direct competitor’s domains; we’ll talk about choosing competitors shortly

How to do a Content Gap Analysis:

1. Enter your domain into SEMrush to find competitors.
For this tutorial, we’ll be using Blue Apron as our example client. Disclosure: They are not a Red Door client. First, you’ll need to find the competitors with enough organic visibility to uncover opportunity. Select competitors that are similar enough to your business that you know you want their traffic, so that you can conduct a competitor gap analysis. Sometimes, you’ll already know these competitors but that isn’t always the case. That’s why SEMrush’s competitive research tools are helpful. Click on Organic Research > Competitors. Then, enter your domain, and SEMrush will suggest domains with similar keyword profiles.

For our Content Gap Analysis example, we’ll use these domains:

  • Blueapron.com (faux client domain)
  • HelloFresh.com
  • HomeChef.com
  • Freshly.com
2. Filter out all brand keywords and download non-branded keyword data for each domain.
Click Organic Research > Positions to see all the keywords that SEMrush knows your domain is currently ranking for.


Since you’re not measuring brand popularity vs. competitors, filter out branded terms and product names. Use the “Advanced Filters” function found above the data table.


This is easy to do for BlueApron.com because they don’t have many branded product names. If we were analyzing a brand with thousands of unique product names, those all need to be filtered out. Red Door has done this analysis for a sports & active lifestyle company – we filtered out athlete names, sports teams, product names, and hundreds of other branded keywords.

Once you filter out all the brand keywords, export the data. If there are more than 30,000 keywords in your list, you’ll need to set a minimum search volume threshold, or order a custom report from SEMrush, which requires customer service. Usually 10k-20k keywords per domain is enough. For this example, we set the minimum search volume at 200 searches per month. The number varies depending on the vertical. You’ll need to do this for your domain and all competitor domains using the same search volume threshold for each.

3. Add Keywords from each domain into the Red Door Content Gap Analysis Template within their respective tabs.
To do this simply copy and paste each export from SEMrush into the corresponding tab for each domain in the Excel template.


4. Compile the keywords into one list and clean the list for relevance.
Copy/paste the values in columns A-D from each competitor tab into the A-D columns of the “KW & Volume” tab, creating a super-list of keywords. If two domains are ranking for the same keyword (i.e. “meal delivery”) you’ll have duplicates in the list. You’ll want to remove duplicate keywords from the list. Click Data > Remove Duplicates to do this but be sure to only remove duplicate keywords and not other values like search volume.



5. Filter out any remaining branded/irrelevant keywords that were missed in step 2.
Some terms are hard to filter out in SEMrush. In Excel, you can filter terms by “contains” and delete any irrelevant or unnecessary rows. I prefer to sort alphabetically to group terms together for easier removal.

6. Add a “KW Group” Column to the “KW & Volume” tab to start grouping keywords.
Now that you have cleaned up the data and removed the irrelevant keywords, it’s time to create keyword group columns in the table on the "KW & Volume" tab.  Here, you’ll define relevant categories for grouping the keywords. For example, if we are doing a CGA for a client/competitors that are in the athletic shoe industry, we would probably create categories related to shoe types such as “Kid's Shoes” or “Cleats” as relevant keyword categories.

7. Paste your filtered and de-duped keyword list into column A on the “Gap Analysis” tab.
From the “KW & Volume” tab you’ll need to copy column A and drop it into the A columns of the “Gap Analysis” tab. Do not edit any columns on the gap analysis tab. All the formulas will populate data into the remaining columns.


8. Copy the entire table in the “Gap Analysis” tab EXCEPT for the TOTALS row at the very bottom. Paste as values into the “Gap Analysis – Static” tab.
Once we have copied the entire table (except for the totals row at the bottom) from the "Gap Analysis" tab and pasted it into the "Gap Analysis - Static" tab, we are going to want to clean up the table a little to keep Excel from crashing due to data overload. There are formulas in the totals row of the "Gap Analysis - Static" tab that we need to stay intact. Do not paste over the TOTALS formulas.

9. Add your keyword categories to the “Gap Analysis – Static” tab from the “KW & Volume” Tab.
Remember when we created keyword groupings earlier in the KW & Volume tab? It’s time to incorporate those into the “Gap Analysis – Static” tab. To do this efficiently, we can use an INDEX-MATCH function (or VLOOKUP, if that’s your thing) to make sure all our data stays organized. In the screenshot, we’ve included an IF function to cover any keywords that were left uncategorized.



If you are unfamiliar with these kinds of functions, visit ExcelJet.net’s “Index and Match” tutorial. (If you spend a lot of time in Excel and don’t use it already, it might change your life). The limitations of VLOOKUP make INDEX-MATCH a far superior alternative for our use-case.

Note: Once you’ve added the lookup formula for your keyword groups and it is populating correctly, be sure to copy and paste as values to keep the values and remove the formulas – this will make your spreadsheet work much faster when filtering.


10. Add Data Slicers to help filter your keyword categories.
After you’ve added your keyword groups to the “Gap Analysis – Static” tab you can now insert Data Slicers to help filter your defined keyword categories. In the main Excel ribbon, select Insert > Slicer to bring up the “Insert Slicers” dialog box.


Select only the “Keyword Group” slicer in the dialog box and click OK.


A new “Keyword Group” data slicer should appear.


Once you have the data slicer, copy and paste it into the “Bubble Charts” tab. Now, you can quickly sort by keyword category, and the bubble charts will dynamically update based on the filter you’ve selected. In the bubble chart tab, you can also create a data slicer for “Names” to filter by client and competitors.


12. Analyze for Opportunity
Filtering is important here – it enables you to sort and identify the gaps in the collected data. To see the terms that more than one competitor ranks for, sort by “Coverage” in column C. Then, find keywords where your site’s “BRank” is greater than 30, which should be highlighted red. In theory, if all your competitors are ranking on page 1 for a term, you’ll be able to rank if you try. Columns D, E, and F on the Gap Analysis tab can also help you find new keyword opportunities based on automated flags configured by RDI. Use the cell comments on the headings for Columns D, E, and F to get more background on the data in each column. 

Does this sound like too much work?

If you’d rather have Red Door perform the analysis and create a concise PowerPoint as we mentioned, check in with us and we’ll be happy to give you an estimate. We’re also working on updates to this template that use programmatic filtering to streamline keyword list cleanup, and an automated version that makes use of a variety of APIs that enable monthly updates to the analysis with zero additional effort.

Written by Jared Gardner, SEO Manager, 08.02.2017
Updated by Tyler Trent, Sr. SEO Strategist, 11.18.2020

  • Insights