Keyword -Driven Content Audit:
Content Marketing – Content is an essential aspect of any digital marketing campaign. The Content Marketing Institute offers some of the best advice around in terms of how the content can help your brand. From all other industry trends to best practices, their posts mostly offer helpful advice on how to create the best strategies for your business and how your content marketing should play a role in the “bigger picture.”
Marketing Land provides lots of quality content on different topics and industry news. From updates in content management systems, to new features on social media platforms, their posts are informative and relevant.
Let’s think about SEOs. One approach is to find the older content that’s still ranking for a solid number of keywords. This is content that’s still driving traffic.
Even for my small blog, I’ve got a healthy list of keywords here. This is all well and good when you have an exact page in mind, but how do you audit an entire site or blog when you don’t know what’s performing for you?
Step 1 – Get all rankings
Let’s say I want to find some buried content treasure. I’m going to select “See all ranking keywords”. In this case, I get back a table of more than 53,000 keywords that currently ranks for. Not too shabby. These are not just keywords I actively track, but all of the keywords ranks for in Keyword Explorer’s “universe” of roughly 40 million keywords.
Step 2 – Export keywords
So, how does a keyword list help us to better understand our content? I’m going to choose the export – we’re going to want the whole, beautiful mess for this job. What I’ll get back is a file with every keyword and the following columns:
• Keyword
• Minimum Volume
• Maximum Volume
• Keyword Difficulty
• Top Rank
• Top Ranking URL
That last column is the important one. The export contains the top ranking URL for each of the keywords. This is where we can start forging the content connection.
Step 3 – Filter pages
I ended up with 30K keyword/URL pairings in the CSV. So that the viewers at home can follow along, I’m going to do the next few steps in Google Sheets. The first thing I want to do is filter out just what I’m interested in. In the “Data” menu, select “Filter”. You’ll see green arrows appear next to each column header. Click on the one next to “Top Ranking URL” (the last column). I’m going to use “Filter by condition” with “Text contains” and isolate all ranking URLs with “/blog/” in them: This leaves me with 13,266 keyword/URL pairings. Personally, I like to copy and paste the filtered data to a new worksheet, just because working with filtered data tends to be a bit unpredictable. So, now I’ve got a separate worksheet (named “Filtered”) with just the keywords where the blog ranks.
Step 4 – Pivot pages
If you haven’t used pivot tables, I’d strongly encourage you to check them out. I’ll walk you through a couple of basics in Google Sheets. Generally, you use pivot tables when you want to group data and calculate statistics on those groups very quickly. In this case, what I want to do is group all of the matching URLs in my data set and get the counts. In other words, how many keywords is each unique blog post ranking on?
After selecting all of the data on that new “Filtered” 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 “Top Ranking URL”. This tells Google Sheets that each row in the pivot table should be a unique (grouped) URL from the top ranking URLs. Next, I’ll select the “Values”:
Pivot table results can be a bit hard to work with (in both Excel and Google Sheets), so I’m going to copy and paste the data (as values only) into a new sheet called “Audit”.
Step 5 – Find candidates
Let’s get to the good stuff. When I group the URLs, I’m left with 1,604 unique blog posts in this particular data set. I can easily sort by posts ranking for the most keywords or posts with the most potential search volume (under “Data” / “Sort range”). I’m going to stick to raw keyword count.
Step 6 – Back to Step 0
Remember that “Exact URL” option I talked about at the beginning of this post? Well, now I’ve got a URL to plug back into that feature and learn more about. Our data dump showed 170 ranking keywords, but when I target that. Of course, you can also feed any of these keywords back into Keyword Explorer for more suggestions. Ideally, you’re looking for a handful of solid keyword concepts to target. The goal isn’t to stuff every variation into your rewritten post. The goal is to create a better, newer, more useful post that also happens to intelligently incorporate highly relevant keywords.