How to Mine Your Blog for More Keyword Rankings and Traffic with SEMrush

As content marketers, we are constantly asking ourselves what we did well in each successful post. Once a piece of content goes live, it’s easy (and usual) to just start working on the next one without checking up too deeply on the analytics.

When this is the case, we might miss the fact that the older posts are still performing well, but could be updated to work even better. Using fresh keyword research as an ongoing process is a great way to keep your old content relevant and contributing to your traffic numbers.

Honestly, I myself have been guilty of not looking back on my older posts that are still performing and could use a rewrite to help generate more traffic for newer, broader terms. So, the question is,

How do we find such content and how can we modify it to do even better?

And yes, here we’re going to fire up our trusted SEO workhorse SEMrush (we at E2M love this tool), which reveals a lot of data that Google Analytics doesn’t. For the rest of this post, I’m assuming you have a SEMrush account. If you don’t, get one NOW!

Thinking like experts, our goal is to find older content that’s still ranking for a solid number of keywords, but may be out of date or not performing as well as it could be. These posts may still be driving traffic, but we may be overlooking some simple tweaks that could magnify the effect.

Let’s dive in.

Step 1: Get the SEMrush Engine Going

Go to the SEMrush homepage, log in to your account, and enter your domain name. We’re going to use semrush.com itself as an example.

When you click on the search button, you will get all sorts of data that is of use for the various analysis.

Our purpose is to get older posts with a wide range of keywords. Therefore, I’m going to click on “ORGANIC SEARCH” keywords that the domain is ranking on, in Google, for the top 100 results.

The report shows positions that the queried domain is getting in Google’s top 100 organic search results and the keywords it is ranking on. The domain can get multiple positions with the same keyword.

Read also – The 25 Best Alternatives to the Google AdWords Keyword Tool

We are looking for top performing posts that are ranking on Google’s first page. To get this data, use the advanced filters:

We want to keep keywords in the list, so set the first tab as “Include” the second set as “Pos” (this means we want to see the position in a Google SERP) and the third as “Less than.” We will enter 11 to see results ranking on the first 10 positions.

Click Apply:

Now, you will only see data points that appear on the first page of a Google search.

So, how does this data help us better understand our content?

First, you’ll need to export the data.

You will see three options: Export All, Export Selected, and Export First.

Check the format like Excel, set the button to Export All, and save the data. We choose to export all because we’re going to want the whole, beautiful mess for this job. What we get back is a file with every single keyword and the following columns:

• Keyword
• Position
• Previous Position
• Search Volume
• Keyword Difficulty Index
• CPC
• URL
• Traffic (%)
• Traffic Cost (%)
• Competition
• Number of Results
• Trends
• Timestamp

Step 2: Take the Game to Google Sheets

I’m going to do the next few steps in Google Sheet. First, we’ll place the data in the tab All Data. For a better view, you can bold the first row.

For further analysis, I’ll keep only the most important columns. We’ll want to filter for only what the ones we’re interested in.

• Keyword
• Position
• Search Volume
• Url
• Traffic
• Competition
• Number of Results

In the Data menu, select Filter. You’ll see green arrows appear next to each column header. Click on the one next to URL (the D column). I’m going to use “Filter by Condition” with “Text Contains” and isolate all ranking URLs with “/blog/” in them:

In our example, this leaves me with a couple of thousand keyword/URL pairings. Copy and paste the filtered data to a new worksheet – because working with filtered data tends to be a bit unpredictable. So, now I’ve got a separate worksheet (named “Filtered data”) with just the keywords where the SEMrush blog ranks.

Step 3: Create a Pivot table

A pivot table helps us to group data and calculate statistics on those groups, very quickly. In this case, we will find for out how many keywords each unique blog post is ranking.

After selecting all of the data on that new Filtered Data tab, click the Data menu again, and then Pivot Tables at the bottom. This opens up a new sheet with a blank table. On the right, are some slightly cryptic options. Under Rows, I’m going to add “URL.” This tells Google Sheets that each row in the pivot table should be a unique (grouped) URL.

Next, I’ll select Values: The COUNTA (a function that just tells Google Sheets to return the total count for each URL.) As a bonus, I’ve selected the SUM of max volume. This will total up the volume for all of the ranking keywords in our data set, for each URL. Then, the SUM of max competition, traffic, and a number of results.

To make it easy, copy and paste the data (as values only) into a new sheet called “Conclusion“.

Step 4: Dig Up the Treasure

When I group the URLs, I’m left with 353 unique blog posts in this particular data set. Delete the last Grand Total row. I can easily sort by; post ranking for the most keywords, or posts with the most potential search volume, along with other data (under “Data” / “Sort Range”). I’m going to stick to raw keyword count. Here’s a sample:

Obviously, there are many datasets to analyze. However, I noticed that two of the posts in the first two positions seemed to have some connection to tools. This stood out because it’s not a topic they typically write about.

As it turns out, the second one is a Keyword Research Tool from August 2016, so not a great candidate for an update. The first, however (highlighted), is a tools post from early 2015. When you look at the volume and traffic for the first post, you’ll notice it’s quite high compared to the second post – while the competition is high for the second post. This means there’s less competition and more search volume. It’s a nice little ratio to see which keyword-based post we should be targeting.

Also, this post was surprisingly popular, and given how many new tools have come out in the past 2 years, it is a perfect post to be rewritten.

Here’s a link to the full Google Sheet. Feel free to make a copy and play around.

Bonus Step 5: Back to SEMrush

Copy the URL and plug back to SEMrush, the data will then show 1.1K ranking keywords, when you click on organic search.

I sorted it by volume (descending) here; just to get a sense of some of the more interesting keywords. You can, of course, repeat Step 5 with any of the URLs to narrow down best prospective keywords.

Step 6: Rewrite Posts

If you’re going to rewrite the posts you find, make sure that you’re targeting two sets of keywords:

• the important keywords that you currently rank highly on (don’t want to lose that precious traffic)
• higher volume keywords that you have the potential to rank on (for example, where you currently rank in the top 20)

Personally, I would rewrite a post considering the “what is sitemap” to leverage the benefits of featured snippets, along with some other keywords. The goal is to create a better, newer, and more useful post and incorporate highly relevant keywords when you don’t have new ideas to work.

In addition to the body content of your posts, you might consider using the new keywords you discover, to update your posts in the following ways:

Anchors

When you are updating the internal links of a blog post or trying to build additional external links to your updated posts, old, outdated anchors can actually be devaluing your page. Given your research, be sure you are using the freshest anchors in your update.

Create New Sub Sections

As you pepper in new content to your existing posts, you may very well need to add new subsections. The point of this is to find certain terms and phrases that the search engines find relevant to queries related to your content.

Add New External Backlinks

If you want to freshen up your old content in the eyes of Google, one of the best ways to do so is by adding newer links to external pages. For example, if you use stats or data in your older posts, try to find updated stats to swap. The same goes for research and industry insights.

Link to High Performing Internal Pages

Using SEMrush, see which pages on your website have the highest authority. Then, link to those pages from your posts. This is by far one of the easiest ways to pick up links – for obvious reasons.

Title Tags (if needed)

Title tags hold a great deal of pull when it comes to SEO rankings. Based on your keyword research, tweaking the title tags may be a game-changer in making your posts more “clickable.”

Before you make any changes, look on Google Search Console to see which keywords are giving you the most impressions. The last thing you want to do is sabotage your current click-through rates.

Over to You

The importance of evergreen content in your marketing strategy cannot be overstated. It works to deliver traffic over a vast timeframe, deliver leads, get social shares, and show the world time and again that you are an expert in your field.

However, from an SEO standpoint, just because the topic is not dependent on the times, you still need to make proper tweaks to ensure it resonates in accordance with keyword trends. Just choose your favorite keyword tool and see how it can help you do that!