Jun 12, 2020

How to perform the ultimate SEO content audit for your blog

19-MINUTE READ | By Pinja Virtanen

SEO Analytics

[ Updated Feb 1, 2023 ]

Let’s say your business blog has existed for three years. During those three years, you’ve published a new post twice a week. That’s a total of 312 blog posts out there in the sewers of the internet.

But unless you’ve been frequently updating your 90+, 300+, or 1,200+ posts, it’s unlikely that they’re all generating a solid stream of organic traffic — and conversions — to your website.

And that’s exactly why we’re here today. We’re here to make your existing content work harder for you.

After reading this post, you’ll be able to run an effective SEO content audit for your blog that will result in an actionable optimization checklist — and eventually, a boatload of organic traffic and conversions.

Ready? Let’s go.

What is an SEO content audit?

An SEO content audit is the process of assessing existing content on your website to determine how you can get more and better quality organic traffic to each page. The goal of the audit is to identify which pages to keep, which ones to optimize, and which ones to scrap.

Today, we’re going to narrow our SEO content audit to the blog simply because a) there are some quirks that only apply to blogs and b) our friends at Moz have pretty much already nailed the process you should follow to audit your entire website

Disclaimer: not all content is search-first, and that’s okay

I know you’re itching to get started, but bear with me for a fun little PSA: not all content is created to rank in Google. 

For example, you might be doing event recaps, interviews with your coworkers for employer branding purposes, company news, thought leadership pieces etc. As you can probably see, organic search was never going to be the primary distribution channel for these kinds of articles. And that’s fine.

So before we get started, I want you to also pay attention to distribution and promotion strategies outside the realm of organic search so you don’t end up getting rid of perfectly good posts with an alternative distribution strategy. Okay? Okay. 

Now, let’s get this show on the road.

How do I audit my blog content?

We’re going to follow a pretty standard process that consists of five steps:

  1. Creating an inventory
  2. Categorizing posts and gathering post-level data
  3. Analyzing the data you’ve collected
  4. Making some difficult decisions
  5. Following through with your plan

Let’s start from the top.

Step 1: Create an inventory

The very first thing you’ll want to do is get some organic traffic data to a spreadsheet like Google Sheets or Excel

You could do this with good old copy/paste or by wrangling a CSV, but I strongly recommend using Supermetrics. Why? Because that will save you approximately an entire lifetime (especially if you’re impatient like me).

You can start your free 14-day trial here for Google Sheets and here for Excel. Go on, I’ll wait.

Once you’re all set up,  launch the Supermetrics sidebar.

Launch Supermetrics sidebar

Next up, you’ll want to run your first query using Google Analytics as your data source. (You can also use HubSpot here if that’s your blogging platform.)

Start from the big picture

I like to ease into my SEO audits by looking at the big picture. In this case, total monthly traffic for the past year and total monthly organic traffic for the past year.

That’s why my very first Supermetrics query looks like this:

  • Data source: Google Analytics
  • Select views: Supermetrics blog
  • Select dates: Past 12 months (I use full months to make my data comparable)
  • Select metrics: Pageviews
  • Split by dimensions: Year & month
  • Options: Try to avoid Google Analytics’ data sampling (this is key, I love GA but the sampling kills me)

You should end up with something like this:

All traffic

(And don’t worry about us if these numbers seem low to you. What you’re seeing here is Supermetrics’ blog traffic data from India only. That’s because I added an extra filter so I don’t have to blur out any business-critical data further down the line. And if you’re wondering “Why India?”, the answer is: why not?)

Next up, I’ll run the same query a few cells down, but this time filter out organic traffic by adding a segment called “Organic traffic” to my previous query.

Once I’ve removed the duplicate header row, added a graph to visualize progress, summed up totals in column N, calculated month-over-month change in organic traffic for each month (for cell C4, July vs. June last year that’s =SUM(C3-B3)/B3), and added conditional formatting to make the table easier to read, my first tab looks like this.

All vs organic traffic to a blog and month over month change

The other thing I want you to do already at this point is to use an SEO tool like Moz, Ahrefs, or Semrush to figure out your domain authority. This is typically shown as a percentage and based on a number of factors including backlinks leading to your domain, referring domains, organic search data etc.

For us, that number is 50% according to Semrush. Now make a note of yours and…

That’s our first task and tab done. ?

Get more granular with post-level data

Now that we know how Supermetrics works, I’ll open a new tab and run a a similar query than my second one but this time with a new dimension — page path (aka post URL): 

  • Data source: Google Analytics
  • Select views: Supermetrics blog
  • Select dates: Past 12 full months
  • Select metrics: Pageviews
  • Split by dimensions: Page path vs. Year & month
  • Segments: Organic traffic
  • Options: Try to avoid Google Analytics’ data sampling
Supermetrics query in Google Sheets

After I’ve clicked on “Get Data to Table” and done some mild housekeeping with my column widths & freezing the first row and first column, my spreadsheet looks like this. (Again, this is India only)

Post-level organic traffic data in Google Sheets

Next, I want to organize the posts from highest organic traffic last year to lowest, so I’ll just do two things:

a) Sum up the annual organic traffic per post in column N. For the first row, that’s =SUM(B2:M2).

Sum up annual organic traffic from monthly data

And copy the corresponding formula to each row.

b) Sort the table based on column N values from highest to lowest.

How to sort data in google sheets

Now, I’ve got an organized table. But to make it easier to read, I’ll add some conditional formatting again.

Conditional formatting to create a traffic heatmap

Here, you’ll want to play around with the minpoint, midpoint, and maxpoint ranges so that they make sense for your data. (You can also just use the automatic values if there isn’t a ton of variation between your posts’ relative performance. But often that’s a no-go because a couple of top posts tend to be total outliers.)

For our posts, it looks like anything above 50 monthly organic page views from India is considered great so I mark that with green, anything above 20 is alright and that’s why it’s yellow, and anything between 1-20 is less than awesome and therefore red.

Now pat yourself on the back and have a slice of cake because you’re done with your second tab! ?

Step 2: Categorize your posts and pull relevant data for each group

I hope some of you were able to call me out on my gross ignorance in the previous step. Because yes, I cut some serious corners by pretending like all my posts can compete for 50+ organic page views from India a month. 

Newsflash: they can’t. The other important thing is that organic traffic shouldn’t be your only goal. You also want those sweet sweet a) conversions (in our case that’s trials and demos), b) blog/newsletter subscribers, webinar signups, and/or content downloads, and c) backlinks, don’t you?

It’s also good to realize that the maximum amount of organic traffic you can get to a single blog post is not only about your ability to rank high on the SERPs but also about search volume for the keywords you’re targeting.

And that’s why it would be foolish to treat all your blog posts as equals.

Depending on your business and content strategy, you may have to make different decisions than I do here. But no matter the case, I want you to think about the most meaningful way to categorize your content into groups based on the goals of the individual posts.

Thinking in terms of Ross Tavendale’s SEO KPI framework can be useful:

SEO kpi framework

In short, your content and SEO KPIs can be split into three categories:

  • Conversational goals (e.g. ranking & backlinks)
  • Informational goals (e.g. newsletter subscribers, webinar signups & content downloads)
  • Transactional goals (e.g. trials, demos & sales)

To simplify this example, let’s say I only care about two goals: 

  1. Maximizing organic traffic and backlinks to my top of the funnel posts
  2. Maximizing organic traffic and trials to my bottom of the funnel posts

That’s why, I’ll split all my 200+ posts between two categories: those that we create for awareness and those that we create for conversions.

Category 1: Top of the funnel / Awareness & backlinks

My first category is dedicated to top of the funnel posts, which typically target a (relatively) high search volume keyword and which we publish so that more people would come to know Supermetrics as a helpful resource for all things marketing data. 

These posts don’t really talk about Supermetrics that much — in fact some of them don’t even mention our products — and therefore they’re unlikely to drive a ton of trials. Instead, they target keywords that are relevant to our audience.

The goal of these posts is to get as many pairs of eyeballs on them as possible and improve our domain authority with high-quality backlinks. That’s why we’ll look at these posts’ and their primary keywords’:

  • keyword ranking
  • search volume
  • organic traffic
  • our share of that traffic
  • number of backlinks
Awareness category data sheet with SERP position, monthly search volume, annual search volume, backlinks

I used Semrush here to pull ranking, global monthly search volume, and backlink data but you can also use Ahrefs or Moz.

Then just calculate the annual search volume. For cell E2 that’s =SUM(D2*12). 

You also might want to calculate the organic traffic divided by search volume. For cell G2 that’s =SUM(=F2/E2) shown as a percentage.

And remember, the data points you’re looking at don’t have to be the same exact ones as mine. Just take whatever you might find useful, and leave the rest — or add more stuff as you see fit. 

For example, keyword difficulty might be a great thing to add, if you haven’t been strategic about choosing your keywords before. Now remember that domain authority score I asked you to find out in the beginning? You can use that as a benchmark to decide which keywords to target. 

In other words, if your domain authority is 50% and a keyword you want to target has a difficulty score of 45%, you should be able to rank for it pretty easily with great content. If the keyword difficulty score is 60% or higher, you might want to either work your ass off to get some backlinks, or choose a slightly less competitive keyword.

Rant over. When you’ve repeated those steps to all your top of the funnel posts, that’s your third tab done! It’s time to move on to the money makers. ?

Category 2: Bottom of the funnel / Trials

The second category is dedicated to posts that actively talk about Supermetrics and guide readers to start a trial. 

For us, typical examples include posts about reporting or dashboard templates, new product/connector announcements, as well as all kinds of tutorials and how-tos — like this one. Pretty meta, right?

To be able to assess the relative performance of these posts, I’ll look at:

  • keyword ranking
  • search volume
  • organic traffic 
  • our share of the traffic
  • trials
  • conversion rate from organic traffic to trials. (That’s trials divided by organic traffic times 100.)
Trial data in Google Sheets

(P.S. I don’t want you to get too excited about “seeing our data”. For reasons beyond my control, I had to use dummy trial data here.)

When you’re done pulling all the data for this second category, make sure that all your posts belong to one of the two categories. If they don’t, you’ll need a third one. Which brings us to…

Make sure to come up with meaningful categories for your business

Like I mentioned, we’re currently not collecting email subscribers from the blog, but if you are, that might be a good thing to include in your top of the funnel analysis or as its own category.

Similarly, if you’re running webinars or putting out a lot of gated content, that might justify a category of its own. Or maybe those are considered bottom of the funnel conversions in your case (e.g. if enterprise sales is at the heart of your business).

What I’m trying to say here is that a) group your content into as many categories as necessary but b) don’t come up with too many categories because the point of this exercise is to get comparable data.

And if you suddenly find relevant data scattered across several tabs, well… That’s going to make the next step more difficult for you.

Step 3: Analyze your data

Now that you have your data neatly organized into two or more categories based on your content goals, it’s time to get to the fun part: analysis.

While there are virtually no limitations to what you can do here, I’m going to run through some examples of things you may want to pay attention to.

Organic traffic decay 

I like to start my analysis from the big picture by asking: how has my organic traffic per post evolved over the past 12 months.

From the very first tab in my spreadsheet, we can see that organic traffic from India has been growing fairly steadily month over month, if you don’t count the COVID-19 drop in March or the seasonal fluctuations around October (and December) last year.

Blog traffic analytics

So that’s good news!

Now that we got that down, we also want to look into organic traffic decay by post. The second tab we created is a good place to start.

All traffic to blog

From here, you should be able to see at a glance if there have been any dramatic changes to your posts’ performance. 

For example, we can quickly see that our DoubleClick post has been steadily deteriorating in organic performance over the past couple of months (which makes sense, because Google recently renamed the tool).

However, since we used a single rule to compare all posts, the heatmap doesn’t always work so well. That’s why you might want to create new formatting rules row by row.

How to do conditional formatting row by row

This way, relative decay compared to the previous month is much easier to spot.

Conditional formatting to spot traffic decay

With these new formatting rules, the lighter the color at the end of the row, the worse the post’s recent organic performance compared to previous months.

If these results look alarming for no apparent reason, I suggest that you run a technical audit (e.g. on Semrush, Moz, or Ahrefs) just to make sure that any technical issues like slow page load times, endless redirect chains and loops etc. aren’t stopping you from getting the organic traffic your content deserves.

And also, note here that you can repeat this same process for trials to see if there’s a correlation between a drop in traffic and your monthly conversions per post. 

Now that you know how to spot organic traffic (and trial) decay quickly without spending hours in Google Analytics, let’s move on to the next order of business: ranking.

SERP position vs. share of traffic

The other analysis that you might want to do relates to the correlation between your position on the SERPs and the amount of organic traffic (and trials) you’re getting compared to the search volume for your primary keyword.

Share of search traffic

From here, we can quickly see that the post that ranks first on Google gets 128.33% of the search traffic for the primary keyword (which is great because it means we’re also ranking for related keywords). The HubSpot reporting post that ranks fifth, on the other hand, only gets 53.33% of the search volume. 

Correlations and comparisons like this can be really useful, because your own data is the best kind of benchmark you’re ever going to get. 

Instead of comparing yourself to competitors who may have a team four times bigger than yours, who may have started doing content marketing 10 years earlier than you, and whose domain authority might be a lot higher as a result, you’ll want to use your own business as a yardstick.

That’s not to say that you should ignore your competition but rather that you should learn to compete in your own weight class if you want to quickly improve your results. 

Keyword difficulty vs. ranking

If you also included keyword difficulty to your analysis, this is a great time to analyze the range of difficulty you should be currently aiming for.

Keyword difficulty vs SERP position

From this view, we can quickly see that we should either work a bit harder to improve our domain authority or target keywords that are in the 50-60% range of difficulty.

Trial conversion rates

The fourth overarching analysis you’ll want to perform relates to your conversion rates from organic traffic to trials/demos/content downloads/webinar signups etc.

Let’s say that our average conversion rate from organic blog traffic to trials for the entire blog is 1.2% and for the bottom of the funnel posts that same number is 3.15%.

By looking at positive outliers, i.e. any and all bottom of the funnel posts that are able to convert more than 3.15% of organic traffic into trials, is there anything we can learn?

Are there more calls to action scattered across these posts? Are the CTAs placed higher up? Are the entire posts built around a certain workflow that you can automate with Supermetrics?

Not only will this help you figure out how you can optimize your existing posts, but it will also help you understand what kind of bottom of the funnel content works best for your audience and business so you can create more of those kinds of posts in the future.

Step 4: Make post-level decisions

Now that you understand what good looks like, it’s time to stop looking at your blog on the aggregate, and start making individual decisions about each blog post.

Simply put, we’re going to come up with a recommendation for each post by using the data we’ve just collected and analyzed. And if you need a client’s or boss’ approval to make those changes, I also recommend that you justify each recommendation briefly.

Your high-level options for each post are to:

  • Keep. This means that you recommend not touching the post at all, which normally means that either the post in question is:
    • performing better than average in its respective category
    • so new that you don’t have enough data yet to make a recommendation
  • Optimize. This can mean anything from:
    • Optimizing the post to target a different/better/more niche keyword
    • On-page optimization, i.e. editing the post to better match search intent by beefing the post up, removing sections, or replacing expired information with new knowledge
    • Conversion optimization, i.e. making the most of your high traffic, low conversion posts
    • Combining two or more existing posts to create a more comprehensive resource on a certain topic
    • Coming up with ways to actively promote, distribute, and get backlinks to posts that aren’t ranking due to high competition
  • Prune aka unpublish & redirect. This is the last resort and saved for posts that are either:
    • So old and/or so outdated that they no longer offer any value to their readers (examples include old event recaps, expired and potentially harmful information about your products, badly outdated industry tips and best practices etc.)
    • Simply not getting any traffic from organic or other channels, the keyword is not worth targeting, there are no other keyword targeting opportunities, and/or there’s no logical business reason to keep the post.

Let’s look at what our recommendations might look like in practice:


Note here that it’s perfectly fine to reassign an existing post to a different category when you notice an opportunity. For example, just because our account based marketing reporting and HubSpot reporting articles aren’t currently converting a whole lot of new trials, doesn’t mean that they can’t do that in the future.

Aaaaanyway, once you’ve rinsed and repeated this process for all your posts, congratulate yourself because your SEO content audit is now done! ?

Step 5: Follow through with your plan

Before I let you close this window and start having nightmares (or sweet daydreams if you’re a content analytics nerd like me) about blog audits, I want to give you a few practical tips to help you follow through with your epic optimization checklist.

  • Start by cutting the fat, i.e. unpublishing all the posts you’ve decided to unpublish — and redirecting those URLs to a relevant page on your website if you deem it necessary. Believe it or not, this is typically the quickest way to increase organic traffic to your blog.
  • Prioritize the remaining optimization checklist, estimate the time and resource commitment, and assign tasks to freelancers and team members where possible. You should end up with a clear plan of when you’ll have the whole project completed, who’ll do what, and how/when you’ll report on the results to your boss or client.
  • Once your plan is ready and approved, it’s time to roll up your sleeves and get to work!

Over to you! ?

To borrow from Coldplay: “nobody said it was easy.”

And the best/worst part is that by the time you’re done putting all your improvement ideas into practice, you’ll probably have to start the whole process over again. 

As a cautionary tale, I audited the Supermetrics blog for the first time in January/February 2020, and I’m planning on repeating the process in July/August — and probably every 6 months from there to eternity.

The truth, though, is that these days, content marketing is as much about content management as it is about creating awesome new content.

So with these words of encouragement, I’m going to leave you to optimize the crap out of your blog. And if you have any questions about any of this stuff, give me a shout on Twitter or LinkedIn, and I’ll do my best to help!

Don’t forget that you can start your free 14-day trial of Supermetrics for Google Sheets or Supermetrics for Excel.

Happy optimizing! ?

Turn your marketing data into opportunity

We streamline your marketing data so you can focus on the insights.

Book Demo