Before we get started, grab your copy of my content analysis tool here.
Note: This example only contains 500 terms for each company due to the limitations of Google Spreadsheets. This means the data may not be entirely accurate, but the principles still stand.
Let’s Get Started
First, let me start by explaining the purpose of my Content Gap Analysis tool. It has many uses, but in regard to content I use it to find terms my competitors are ranking for that I’m ranking poorly for or not at all.
It helps me identify gaps in my own content so that I can close up those holes in my content strategies and take traffic from my competition.
This tool doesn’t spit out the headlines for you so you’ll need to use your creative juices to come up with ideas, but it does provide a lot of guidance.
Once I build one of these out I share it with others on my team so that they too can use it to generate new content ideas. Two+ heads are better than one right?
To get a copy of a content gap analysis I recently built where I was playing the role of Taco Bell, visit this Drive link to view and make a copy of it.
Note: I recommend downloading this tool as an Excel file before customizing it. Google Spreadsheets is fairly limited when it comes to processing power.
How do I use this thing?
In this scenario we’re playing the role of Taco Bell and our goal is to find terms that our competitors are outranking us for so that we can generate content to fill in those gaps.
The key is, people are already searching for these terms and finding either our website or our competitor’s site. The problem is that some of the terms are of high relevance to us it’s just that we’re not providing answers or meeting our customer’s needs. Let’s fix that.
Explaining the Columns
So, in the example content analysis tool I shared above you can see in columns A and B a list of keywords and their monthly search volume.
In columns C – G you can see Taco Bell’s and each competitor’s rankings for those terms. I left columns F & G blank for this demo, but I typically find at least 4 competitors to compare.
Some of you may have noticed a column is missing because you remember your ABCs. Column H is hidden because it’s used to host a simple formula I’ll explain later.
In columns I – M you can see the URLs that rank for the terms in column A, this is extremely useful because we’ll be able to see the types of content our competitors are using to outrank us for specific terms.
Finding Content Gaps with an Example
If you scroll down to row 503 you’ll begin to see where our competitors are outranking us.
As you begin scrolling through the terms you’ll start to pick up on trends. Del Taco and Pink Tacos seem to beat us when users search for “Taco Tuesday Specials”. Check out rows 522, 526, 588, 624, 631 and 733.
Not only is this bad for our organic rankings, but in some cases we don’t even show up for terms related to Taco Tuesdays one of the most popular taco buying days of the week.
Due to this discovery, our new goal is to come up with content and special pricing to help us persuade customers to visit one of our locations on Tuesday instead of our competitor’s.
As you browse through the keyword listings you’ll start to come up with additional content and even product ideas to help win over customers.
How do I make my own analysis?
If you look at the bottom of the sample analysis you’ll see a few additional tabs. The first one being the Gap Analysis (Raw) tab which shows the raw formulas I used to create it.
Next you’ll see tabs for Taco Bell and our competitors. This is where you’ll paste in the raw ranking data for each company. I like to use SEMRush to pull this data but in theory any tool that exports its data should work.
Now I’ll breakdown each step I took to create my analysis tool.
Step 1: Import Your Data
Create tabs for your company and each competitor.
Take your keyword rankings export from SEMRush and paste the entire thing into the corresponding tab.
Step 2: Create the Analysis Front End
I already did this for you if you downloaded my example, but essentially this step requires you to begin labeling your columns and organizing your analysis tool sheet.
After the columns are labeled copy over the keywords from each company’s data tab, I just do a quick copy & paste. Paste each set of terms under each other, in the example I took the top 500 words for each company which turned into 1,500 rows of keywords.
Next, I copy over the Keyword Volume for each term similar to how I copied over the terms themselves. Make sure when you paste over the Keyword Volumes that the numbers correspond with the correct Keyword.
Step 3: Insert Your VLookup Formulas
Now the fun part.
Highlight cell C3 in the Gap Analysis (Raw) tab to view the vlookup formula I used to find the rankings of each term.
That formula pulls in the ranking each company’s data for each corresponding keyword.
Here’s what it looks like:
Let’s break it down.
The orange A3 tells the formula to look for the term in cell A3, as you drag the formula down it will changed to A4, A5, etc.
The purple ‘Taco Bell’!A$1:N$500 range tells the formula to look in Taco Bell tab from cell A1 to cell N500. The $ in front of the numbers are required as they tell the formula to stick to that range permanently even if the formula is dragged down to other rows.
The blue 2 tells the formula to look in column 2 for the ranking data that corresponds to the keyword in cell A3.
The ‘false’ is included at the end of the formula to make sure it looks for an EXACT match of the keyword found in cell A3, we don’t want the formula to pull in ranking data for terms similar to the original keywords.
Step 4: Fix the #N/A Errors
Once you replicate and drag down the formula for each competitor you’ll start to see how they rank against each other. You’ll also notice a lot of broken formula errors, #N/A. Don’t worry about those we’re about to fix them.
Highlight all of your rankings data, in my analysis example that range would be from cell C3 to cell E1502. Once highlighted copy all of the data and right click in cell C3. After you right click select Paste Special > Paste Values Only.
Now, to fix the #N/A errors do a Find a Replace. On a PC hit Ctrl + F and a mini window will appear. Click the vertical ellipse to expand it. In the expanded window type #N/A in the top input and 100 in the second input. Then Search drop down box select “Specific Range” and select the range for all of your keyword rankings, C3 to E1502 in the example.
I use 100 because most tracking tools give up tracking a term after position 100, that’s the 10th page of Google. You can use whatever value you want as long as it’s above 100 otherwise you’ll muddy your data with fake rankings.
Step 5: Find the Highest Ranking Competitor
In column H, now that you’ve pasted the ranking position values as numbers and not formulas, you can use a formula to find the best ranking company for each term.
I use a simple Min formula to do so:
This formula finds the lowest number in that range and is the first step in how I make the winning position turn green.
Now let’s find the winner. Using conditional formatting we’ll turn the best rank green.
Highlight the entire C column to insert conditional formatting for Taco Bell. Next, once you open up the conditional formatting dialogue,
in the top menu go to Format > Conditional Formatting, we’ll select our entire range of rankings (for Taco Bell) which is C3:C1501.
Now that the proper range is set we’ll select “Custom Formula” at the bottom of the drop down input just below the range input box.
Now, insert =C3=H3 into the custom formula box.
That formula says, if the ranking number in cell C3 equals the number in cell H3, highlight cell C3 green.
Now, repeat this for each competitor.
You’ll soon be provided with a list of highlighted terms you’re company is either winning or losing. You can now begin to sort through the data to come up with content ideas to beat out the competition.
Step 6: Finishing Things Up
To get the URLs that rank for the corresponding terms in column A you’ll follow the same steps we did in order to find the ranking positions.
You’ll even us the same Vlookup formula, except you’ll replace the ‘2’ in original formula with the number ‘7’ which corresponds with the URL column in our company data tabs.
Copy the URLs and paste them as values like we did with the rankings and then use the Find and Replace tool to replace #N/A with “No Ranking URLs” to help clean up your data.
You can also use conditional formatting to change the color of the “No Ranking URLs” font to make it slightly hidden in order to make it easier to view the URLs themselves.
Finding More Uses for the Tool
Coming up with content ideas is great, but this tool shows you exactly what people are searching for when they arrive to your competitor’s website.
It could be used to uncover popular features of their products, entirely new product lines they offer (and you don’t) and promotions that seem to get a lot of attention are just a few ways you can use this tool to your advantage.
There is always room for improvement, so grab your copy of my content analysis tool and start tweaking it to meet your needs.