Content Audits are usually done with the sole goal of analyzing SEO/Traffic metrics and determine what pages you should remove/update/maintain to increase organic traffic.

That’s ok, but for me, this is only the surface of the potential of a content audit.

You also must see what’s impacting your company’s growth.

So today, I’m gonna explain my easy-to-follow process to perform a content audit, analyze it and find insights to inform your next actions and strategy.

I hope it really helps you

And, of course, I include all the templates I use along the process.

NOTE: Due to the subreddit limits, I couldn't add the images to explain each step from the original post so I adjusted the content a bit. Let me know if something doesn't make sense.

First things first – Info and resources you need

I’m writing this guide using one of my side-projects in Spain as an example (LegalTech business). It has around 200-250k of traffic per month and around 1,000 URLs of content.

That said, you’ll need the following tools/resources:

• Ahrefs
• Screaming Frog Spider
• Google Analytics
• Google Search Console

The content audit template is not intended to be used with SemRush but it can be easily adapted. Leave a comment below if you need help with this and I’ll help you.

How to perform an SEO Content Audit: Step by Step

Step 1 – Data: what you need and how to collect it

You are right.

To perform an analysis, we need first to collect all the data we need.

Let’s get our hands dirty.

1.1 Configure Screaming Frog Spider

The first thing is to connect and set up all APIs:

Google Analytics:

Configuration -> API Access -> Google Analytics 

• Account, property, and view: Select the account and view of your website
• Segment: Organic Traffic
• Date Range: I usually select the last 12 months, but you can adjust this to your needs
• Metrics: Leave as is but add: Site Speed -> PageLoadTime

NOTE: If you want to get conversion data, you must select the specific goal id from the “Goals” dropdown within the Metrics tab.

For this website, I selected Goal 16 Completions and Goal 16 Conversion Rate.

That goal id is our Intercom Conversations. Every time an intercom chat is opened, we track the event in GA.

Although it’s optional, this is needed if you want to make an in-depth analysis not only for your SEO/Traffic performance but also to see how your content is impacting your key growth levers.

Google Search Console:

Configuration -> API Access -> Google Search Console 

• Account: Select the website’s property
• Date Range: Last 12 months

Ahrefs:

Configuration -> API Access -> Ahrefs 

• Account: Follow on-screen instructions to get your API key
• Metrics: URL (Exact URL) -> Backlinks, RefDomains, URL Rating, Keywords, Keywords Top 10, Traffic

1.2 Crawl your website

Now it’s time to crawl your website, type in your website address and click on “Start”.

Once it’s finished (it can take a bit for websites with more than 10,000 URLs), use the dropdown filter at the top.

And export all your data into a CSV file. Please put it in a safe place

Now, you have to make another crawl of your website, but this time disconnect all APIs, except Google Analytics, so it doesn’t take that long to complete.

Select “All Users” within GA configuration.

Click on “Start” again.

Wait for it to complete, filter again by Internal -> HTML, and export the data.

1.3 Get Ahrefs Data

Log into Ahrefs.

Type in your website address within the Site Explorer tool.

Make a full export of the “organic keywords” report.

And now it’s time to set up the google sheet to do the content audit.

Step 2 – Get your data altogether: Set Up your Content Audit Template

2.1 Import your data

First, make a copy of this google sheet.

Now import all the CSVs files you have exported from Screaming Frog and Ahrefs into the Google Sheet.

Paste your data to the relevant template sheets

(1) Screaming Frog Data Dump (Organic Traffic) -> Your crawling data ('Organic Traffic' segment). 

Once it’s ready, create a filter for all columns and sort it out by URL (A to Z)

(2) Screaming Frog Data Dump (All Traffic) -> Your crawling data ('All Users' segment) 

Once it’s ready, create a filter for both columns and sort it out by URL (A to Z) (just like the step above)

For this second worksheet, keep in mind that you only need to paste 2 columns:

• URLs
• GA Sessions

​

(3) Ahrefs Dump -> Copy-paste your ahrefs data. 

Ok, that’s it. Easy, right?

By now, the “Master” tab should be populated with all your data in a fancy, nicely formatted way.

Regarding goals/conversion data, you can paste the data directly on the ‘Master’ tab –columns from AH to AL– (marked up in Orange).

You can add more columns if you need to. Just remember to include those new columns in your sheet filter.

2.2 Understand the Content Audit Google Sheet

Before moving into the analysis, I’d love to clarify some columns from the template.

Most of them are self-explanatory but here we go:

• Category – The type of content or the content topic for a given URL. E.g., “Templates” (content type) or “Google Analytics” (content topic)
• Page Score – Metric calculated with a custom formula. This is calculated based on how optimized the page is. It looks at things like sessions, backlinks, time for the page to load, page title length, how many keywords it ranks for, word count, etc. The lower the score, the lower the page performs and the less optimized it is.
• Suggested action – Automated actions based on total sessions, organic traffic, links. It takes into consideration the thresholds we will set in the “Start Here” tab.
• Priority Number – It’s like a “ranking”. The lower the number, the more important is that URL. This is useful to prioritize what to update/optimize first.

Please Note: when you see “Delete” as the suggested action it doesn’t mean you should remove that content right away. All pages should be reviewed before you make the final decision of removing them.

2.3 Tweak the “Start Here” tab

Here is where you can tweak things a bit to fit it to your needs.

Conditions Table

A summary of the averages for key content and SEO metrics. Pretty self-explanatory.

You don’t need to change anything here.

Inputs Table (Scoring Thresholds)

I usually leave this, as you can see in the image. I modify the ‘Minimum Organic Traffic (for page suggestion)’ and ‘Minimum Total Sessions’.

And the values I set depends on the averages that you can see from the Conditions Table above.

Again, this depends on your needs, your strategy, and the goals you’re pursuing. But I’m keeping it simple for this guide.

Priority Tweaking Table

This affects the “Priority” column from the ‘Master’ tab.

If you set a high multiplier, you're saying that metric X weights more when prioritizing that page against the others.

That’s why Word Count and CTR metrics are set in minus if one of your goals is to improve already-existing content.

2.4 Categorize all your website URLs

The first thing I recommend doing is tag each of your URLs with an actual category.

Like I said above, a “Category” could be a content topic or a content type/section of your website.

Content Type/Section examples:

• Templates pages
• Product/Service Pages
• Partners directory
• Integrations pages
• Authors pages

Got it? Ok!

Content topic examples:

• Content Marketing
• SEO
• Link Building
• Google Analytics
• Business Analytics

To do so, you can filter column A from the ‘Master’ tab out by using the ‘Text Contains’ formula and entering some of your keywords, examples:

Text Contains…

• content-marketing
• analytics
• seo
• customer retention

TIP for big websites: If you want to filter in/out larger amounts of URLs, you can use the following formula:

To filter out:

=not(regexmatch(A:A, "(?i)/template|/partner|/integration|/product|case-study") 

To filter in:

=regexmatch(A:A, "(?i)/template|/partner|/integration|/product|case-study" 

The strings between each vertical bar ( | ) are the keywords/text you want to filter in/out. The vertical bar acts like an – OR – logic.

You can find more info about this function here.

There are times that you don’t know how to categorize a specific URL (articles from the blog most of the time).

When in doubt, analyze that URL in Ahrefs/Semrush and check for what keywords it’s ranking for and see if you find a clear pattern of what content topic is the blog post about.

If still in doubt, review the current page and go through it.

And, if still in doubt, tag the URL as “Blog Content”

2.5 The Pivot Table

Did you see it already?

No?

Go to the Metrics by Website Sections and/or Content Topics worksheet.

Very nice, uh?

With this pivot table, you’re segmenting your website by topics/sections, so it’s far easier to spot opportunities for improvement by checking your metrics in bulk.

Ok, the boring part is done!

Now let’s move into the funny analysis.

Step 3 – Analysis: what to do with all this data?

3.1 Create a list of the potential pages to be ‘Deleted’

We’ll get back to the pivot table super quick but first…

On the ‘Master’ tab, filter out by:

• <30% Page Score – Column T
• 0 Traffic (GA Sessions) – Column G
• Suggested Action of “Delete” – Column V

All these URLs must still be reviewed, but they’re more likely to be deleted than others. As these don’t have any traffic and their page score are low.

The next step is to create another google sheet. Let’s call this file “Content Action Plan”.

This new google sheet is where you will paste groups of URLs from the Content Audit file so you can easily review them and also, it’s where you will outline actions for each URL.

So, copy all the URLs you have filtered out from the audit file and paste them into a new sheet in your Content Action Plan file.

I called this group of URLs: 0 Traffic <30% Page Score - Delete / Update

When pasting, make sure you only paste values and then the format. This way, you don’t carry over all formulas.

You can repeat this with other groups of URLs, some examples:

• Traffic>1 & <30 Page Score - Delete / Update
• <30 Page Score - Redirect / Update
• <30 Page Score - Delete

Sky is the limit.

Play with filters to find groups of URLs you want to review.

For now, that’s it. I’ll explain later what to do with those groups of URLs. (Point 3.3)

Now it’s time to see the real magic.

3.2 Let’s play with the Pivot Table

Getting back to the pivot table (Content Audit File -> -Metrics by…)

The template is already pre-populated with some key metrics. But you can add/remove as you wish, depending on your needs.

I sorted it out by “Intercom Conversations” because that’s where I want to focus on besides the SEO metrics.

I won’t get into the details of how to use a pivot table in this post, but, here’s a handy article from Zapier’s Blog that explains it in detail.

That said, the opportunities here are endless. I’ll give you an example of how I have analyzed the pivot table:

As you can see, a section of my website, “Content - IP” (where “IP” is a content topic), is the group of pages driving the higher number of intercom conversations.

But I also see that:

• Word Count can be improved – Compared to the rest of the website, it’s below the average. Let’s review and see if we can add more content to make our pages more relevant, which is tied to the next insight:
• AVG Position could be above 10 – We are ranking MANY keywords for this topic (almost 2k in the top 10). Let’s see if we can update the pages of this group with more targeted keywords to improve positions?
• Chat Conversations conversion rate is ‘terrible’: 0.14% – Maybe we can improve and/or add more CTAs on these pages?

Are you already seeing how powerful this is?

If with a 0.14% conversion rate, this topic is the top 1, imagine what would happen if you manage to take that 0.14% to 0.5% or even 1% while you also improve the content to rank more and better.

BOOM!

From here, I’d want to create a list of this group of URLs into the “Content Action Plan” file so I can make a detailed analysis of each of these URLs.

You need to repeat this step with every category or group of URLs you want to do a detailed review of its pages based on the metrics and insights you find on the pivot table.

3.3 Analyzing Website Pages from the “Content Action Plan”

After creating a list of URLs, the first thing you have to do is add another column called “Actions” right to the “Category” one.

This column is super useful in outlining the individual actions you and your team should take on that specific page.

The second thing is to sort the entire worksheet out by “Priority Number” from A to Z.

And now the fun part starts:

• Start filtering out by pages with a “Delete” suggested action, review the current page, its metrics, and decide if you could update them to make it better. If not, leave them as “Delete”.
• Now filter out all pages with a <50% page score. Review them one by one.

Outline your next steps/actions for each URL in the column “Actions”.

Again, you can play with the filters the way you want. The goal here is that all URLs must have a detailed action.

To decide what are the next actions for a specific URL, you should:

• Check Traffic, Content, and SEO metrics from the sheet (including the number of keywords ranking, top 10 keywords, avg position, SERPs CTRs, word count, etc.)
• Check conversion data (if you’ve added it)
• Check if similar pages are talking about the same thing? Could we merge those pages?
• Review the actual page
• Involve feedback from other teams if you think it might be useful
• If available, use third-party tools like SurferSEO, MarketMuse, Clearscope, Ahrefs, or Semrush to get more context to help you decide what to do.

Step 4 – Document: outline all your findings/insights to inform your strategy and subsequent actions

The next step is to document all the insights you’ve found so far.

If you’ve done it right, you’ll find yourself with tons of opportunities to work on.

You can’t improve everything simultaneously, so this document will help you prioritize and give your team a clear focus on the next steps for your content strategy.

You can use a template I’ve prepared for this purpose:

Here’s the link.

This a living document, and it’s intended to be shared with your team.

For that reason, it includes some context about the analytical process, clarification notes about the Content Audit and Content Action Plan files.

The important part of this document is the second point:

2. Strategy: Insights & Action Plan

This is where you’ll explain the insights you’ve found and the actions to take.

You can organize this part of the analysis the way you want. The template I created is to help you get started. But you can use any other tool for this: Notion, Coda, Airtable, whatever.

The thing here is that you should clearly document the next steps for your team so everyone’s on the same page and they know where the strategy is headed.

Wrapping up

If you went through the entire process and learned a thing or two, please let me know in the comments. I really want to know if I have helped you to audit your content assets in some way!

I know I might have missed some points, so please let me know too in the comments to keep improving the process.

I’m here to assist you if you need any help.

If you want to read the full process with the actual images for each step and free templates, you can check it out here.