Scaling Content Gap Analysis with SEMrush
Content Gap Analysis can be an intricate and exhaustive process. But with a little excel work and a few SEMrush exports, you can do it in a matter of minutes. It’s almost summer, so let’s use grilling blogs as the example:
For this post, I’m performing content gap analysis on BBQ Addicts (They sell bacon explosions, they have my unwavering loyalty), and we’ll use the following competitors:
Step 1 – Open up SEMrush and search for bbqaddicts.com
Step 2 – Go to the Full Report for Organic Keywords
Step 3 – Export all organic keywords… if there aren’t too many.
We’re going to be man handling some data in excel, and SEMrush exports can be HUGE. If you are analyzing competitor sites that you compete with in the SERPs for content, but don’t compete with on a product level – use the filters in SEMrush to narrow down your results. For example, Weber Grills is a huge site, so to narrow down the results – you can remove the bulk of their ecommerce catalog.
Step 3.1 Export Keywords
(Just my preference, but I like to export as a csv then copy over into a .xls file)
Step 4 – Follow the same process for all competitors
Note: do not combine exports yet
Step 5 – Remove keyword duplicates from all individual exports
Step 6 – Compare SEMrush
Add the SEMrush data from first competitor (Kevin’s Backyard – the blog on Weber Grills) to the same excel sheet as the BBQ Addicts data. For this process, its best to find where content lives on the competitor’s site, and then use landing page urls in the report to suss out directories and subdomains that are unnecessary and irrelevant to your gap analysis.
After a quick scan of the urls you can figure out where most of the content lives and what category pages lead to what parts of the site:
- /backyard/
- /recipes/
- /grillout/
- webnation.weber.com
This excludes the following product, brand and ecommerce oriented areas
- /grills/
- /accessories/
- store.weber.com
Step 7 – Remove all branded terms
Step 8 – Identify Content Overlap
Compare your data set to one competitor at a time. Use conditional formatting to highlight duplicates, then sort the keyword column by cell color.
Step 9 – Remove Duplicates
At the top of your excel sheet is your overlapping content.
Save these for later, you can use this data to report on current content overlap.
Step 10 – Do it All Over Again
Copy your remaining data set, but make sure to leave behind your competitor’s data. Paste in a new excel sheet, repeat steps 6-9 until you’ve gone through all competitors.
Step 11 – Combine Data
Combine all competitor data that doesn’t overlap with your sites content into one excel sheet.
Step 12 – Pivot Table Time
Now turn all this data into a pivot table.
Quickly determine which competitor urls are cleaning up in the long tail
Download the Excel Doc
In case you want to see how this excel doc/pivot table is set up, you can download it here.
Also, if you haven’t used SEMrush before, you can poke around in the tool for free, it has a ton of other uses (especially for PPC) you just can’t export anything in the free version and there is a limit of calls per day.
Bonus: Optional Step 13 – Find duplicate keywords in Raw Data
If you want to see how your competitors are approaching content differently, use conditional formatting to identify two or more competitors with overlapping content
If you like this post, you might one to check out: How to organize and manage link prospects