TLC SEO: The Linking Man's Blog

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:

 

scaling content gap analysis

Image via Flickr user dbgg1979

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

Full Report for Organic SEM Rush

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 HUGEIf 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.

A_Weber_SEM_Rush

Step 3.1 Export Keywords

Export Full Keywords from SEMRush

(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

Remove_keyword_duplicates_from_each_data_set

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

remove brand 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.

sort_be_cell_color

Step 9 – Remove Duplicates

At the top of your excel sheet is your overlapping content.

overlapping_content_for_gap_analsysis

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.

content_gap_analysis_pivot_table

Quickly determine which competitor urls are cleaning up in the long tail

Long_tail_content_gap_analysis

 

 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

overlapping_competitor_content

 

If you like this post, you might one to check out: How to organize and manage link prospects

10 comments
spookseo
spookseo

SEMrush provides great analytics but with this guidance it could be a real powerful competitors analysis tool, thanks a lot john!

Al Mukmin
Al Mukmin

Wow John! What a coincidence, I'm actually developing a software that does Step 1-7 with the click of a button. Here's how it'll work.


You enter a URL and the software will do the following:
1-Crawl your website for all your landing pages.

2-Analyze your pages content and come up with a list of currently ranking terms, and keywords you can possibly rank for.
2.1 Assign a Rankability and KEI value to each keyword.
2.2 Actually track the ranking of each keyword in real time(daily).
2.3 Keep generating new keywords from already existing keywords to find ranking opportunities.

3. Besides the Volume data from Keyword Planner, it'll also use the data from Google Analytics and Google Webmaster Tools, correlate and extrapolate to come up with what I'm calling "True Traffic".

4. The value for "True Traffic"(trademarked, lol) will only get accurate over time as it tracks and analyze historical Analytics and GWT data.

5. Would-be-nice feature: Add a tracking pixel so you can track actual conversion and measure actual keyword ROI (again).

Thoughts?

p/s: Is it OK if I add you to my personal list of people that might be interested to give this app a whirl once it's done mid-Jan 2014? It's totally cool if you say no, I'll still come to your blog :)

posizionamento
posizionamento

Nice article definitely helpful for those who have experience in link building

jamesc2
jamesc2

LOVE SEMRUSH! Great Article John, got any writers/services you could suggest?

ArunSingh
ArunSingh

Thanks for this great post. The walkthrough is particularly didactic. 

YuriyYarovoy
YuriyYarovoy

Awesome post. While SEMrush tends to provide bad ranking data in my experience, their other competitive analysis tools are pretty spot on. This is a prime example of how to use these to your advantage. Thanks for writing this up.

JHTScherck
JHTScherck moderator

@Al Mukmin Hey dude, yeah - I will check it out. Let me know when it's finished, just hit me up on Twitter.


Thanks for checking out the blog!

Eugeniya Yershova
Eugeniya Yershova

@YuriyYarovoy Hi Yuriy, thank you for your comment! I'm a customer support specialist at SEMrush. We are working hard to improve our service and the feedback from our users is extremely valuable for us. So, could you let me know some more details on your disappointing experience of using SEMrush? Perhaps, I can clarify some discrepancies you came across using our product. 

JHTScherck
JHTScherck moderator

@YuriyYarovoy Thanks for commenting Yuri - I wouldn't use their rankings for reporting - but it's a nice index of the index ;)