x

How to Perform Keyword and Content Research with SEMrush and Excel

A look at how to find the keywords that your competitors are ranking for with their content with SEMrush and Excel.

When it comes to competitor research, have you ever wanted to go beyond the top keywords that they were targeting? Do you want to know the content that helps them rank for all of the popular keywords that are driving traffic to a website? If so, then you can find the answers using SEMrush and Excel. In this post, we’re going to look at a simple way to use SEMrush Pro as a competitive keyword and content research tool.

Finding Your Competitors

If you are not sure who to call competitor in your industry on the web, one way to start is by taking the top keywords you want to rank for and seeing who ranks above your website for them in Google search. Let’s say that you’re targeting the phrase “event tracking analytics” – search for it using SEMrush, then scroll down the page for the Organic Results section. This will usually help you bypass your personalized search results.

You should get a list of twenty websites ranking for the keyword phrase you entered. You can take a tour of these websites to determine if they are competing companies with blogs, competing companies without blogs, content links, or high authority sites. In this case, the breakdown is:

  • 6 competitors with blog content.
  • 5 competitors without blog content.
  • 5 content links.
  • 4 authority sites.

Creating Your Spreadsheet

First, you will want to fire up an Excel spreadsheet (or a Google Docs spreadsheet) to put your information together. You’ll want to have the following columns in this order to match with the export of SEMrush data.

  • Keyword
  • Position
  • Previous Position
  • Search Volume
  • CPC
  • URL
  • Traffic %
  • Traffic Cost %
  • Competition
  • Number of Results

You can start with a little analysis of the content links listed in the top 20 results for your keyword by manually adding it to this spreadsheet. Then the fun part begins.

Compiling SEMrush Data

To get the best content URLs and stats from your competitors, you will want to click on the link for each of your competitor’s with blogs listed in the top 20 results for your keyword. For each competitor, scroll down to the Organic keywords section and sort by the Pos (position) column.

To export just blog content, click on the Filters button. Set the drop downs to Include > URL > Containing and enter blog in the text box. If the site doesn’t have blog in the URLs for their blog posts, you may have to use another identifier such as the year in the timestamp.

Also remember that you might find good content topics on other areas of the site like the help section, knowledge base, forums, etc. Keep this in mind while filtering your data.

If you receive a message about too many results, then you can always filter out the blog URLs in the CSV instead using Excel’s filter option.

Once you’re done filtering, use the Export CSV option at the top right of the list to export the data. Then sort the Search Volume column in Descending order. This will put the keywords with the highest search volume that your competitor ranks for at the top. If you were not able to filter blog links out in SEMrush, go ahead and do this using the filter on the URL column.

Now, copy all of the data from the Keyword to the Number of Results column for keywords with a decent search volume from your exported CSV to the main spreadsheet you created. Then follow this process for all of your competitors with blog content.

Once you are finished, you can apply filters to your main spreadsheet, sort it by the Search Volume column, and voila. You now have links to the content that is helping your competitor rank for the top keyword driving traffic to their site from search. You’ll also get a good idea which competitors have the best blog content – you should probably subscribe to them via RSS to keep an eye on future topics they cover as they might be good ones for your audience as well.

Individual Content Analysis

As you analyze individual pieces of content from your competitor’s blogs, you will want to note the following.

  • Keyword usage in the title and throughout the content.
  • Length of each piece of content.
  • Popularity of content (social shares and comments).
  • Content writers (employees, guest bloggers, freelancers, etc.).

These things can help you determine how to optimize your own content, what social networks to promote your content upon, and who you should get to write for your blog.

Social and SEO Analysis

Want to go further into analyzing why some content ranks well and how popular each piece of content is on social media? You may want to also add some of the following to your analysis.

  • Domain Authority
  • Page Authority
  • Linking Root Domains
  • Links
  • Tweets
  • Facebook Likes
  • Google +1′s

Depending on how long your list is, you can grab these stats manually using SEO Site Tools and Open Site Explorer. For larger amounts of data, you may want to try the Bulk Social Metrics GDocs Tool from Distilled, the SEOmoz API for Google Docs (for Pro members of SEOmoz only), or the Excel tools mentioned in the latter SEOmoz post.

For example, if you take the URLs from your main spreadsheet and paste them into the Bulk Social Metrics GDoc, you can then sort it by social popularity.

There you have it! What other ways can you think of to use the SEMrush for keyword and content research? Please share in the comments!