Mike Ginley

Free SEO Content Audit Template Using Screaming Frog + Google Sheets

Share Article!

In This Post

TL;DR

  • UPDATED for new metrics + date to source poor performing content.
  • Use this free SEO content audit template to quickly see pages that are not performing. From there you can further drill down into why and how to optimize.
  • This is not a fully automated one-size fits all audit. You will need to do some actual work after using this!
  • When creating or refreshing your content, make sure to use this free SEO content template.



SEO Content Audit Video Walkthrough

How To Perform A Content Audit?

There is no one way to do a content audit, just like there is no one way to perform SEO. It all depends on the subject matter of the website and the capabilities of the team. In this post, I will cover my way of performing a content audit that has been very successful for me. I will also reference some other great ways in the ‘Helpful Resources’ section at the bottom of this post. This SEO Content Audit Template can be used in either Google Sheets or Microsoft Excel, whatever you prefer.

I will recommend that you come up with a way that works best for you and your team using the thought process I go through here. For this, you will need access to Google Search Console, Google Analytics, Google Sheets and Screaming Frog. Anyone that is concerned about the quality of their website, content or organic presence should already have access to these tools. If you don’t I highly recommend you do that ASAP.

With these tools, we will extract relevant user data to highlight what content may not be performing up to expectations. There are numerous reasons that could be happening, but this audit will help surface those pages so you can dive deeper to determine how to remedy them (Refresh, Remove, Monitor, etc.).

I will caution you though, there is no 100% foolproof automated content audit that will do all the work for you. You will need to use the tool between your ears to really audit your content. If you think you can cut corners with your website, it will show in the results.

See also An Automated Way To Surface SEO Low-Hanging Fruit

SEO Content Audit Walkthrough

Updated SEO Content Audit
SEO Content Audit Spreadsheet

For my SEO Content Audit (real example linked in the above image) I will utilize the Screaming Frog Google Search Console & Google Analytics API to attach crawl data with 12 months of analytics. This will allow me to see how content has been performing on a page-by-page basis. I have also created 7 checkpoints to compare against the website median to help us drill down on poor-performing content. The more checkpoints that have a ‘No’, the worse the content is performing. All you will need to do is enable the Google Search Console & Analytics API in Screaming Frog with the correct website you are crawling, export the data into a spreadsheet and copy and paste it into my excel template. All the data should be updated accordingly.

Screaming Frog Google Search Console API Setup

  1. Under Configuration –> API Access –> Google Search Console.
  2. Click ‘Connect New Account‘ and sign into your Google Search Console account, then click ‘Allow‘.
  3. Now back in Screaming Frog choose your website from the ‘Available Properties‘ dropdown.
  4. Go to the ‘Search Analytics‘ tab and change the time frame to whatever makes sense for you (I use 12 months).
  5. Then under ‘URL Inspection‘ check the ‘Enable URL Inspection‘ box.
  6. Click ‘OK‘.
  7. Do the same for Google Analytics 4 API.
  8. Under ‘Crawl Analysis‘ select ‘Configure‘.
  9. Check the ‘Auto-Analyze‘ box at the bottom of the window.
  10. IF YOU ARE LOOKING TO ADD DATES!
  11. Click ‘OK‘.
  12. Start your crawl!
  13. When the crawl is complete, find the ‘Internal‘ Tab.
    • Filter for just HTML
  14. Click ‘Export‘ right below the website input box.
screaming frog configuration
Screaming Frog Configuration
Screaming Frog Connect Google Search Console
Screaming Frog Connect Google Search Console
content audit analytics
Screaming Frog Search Analytics
Screaming Frog URL Inspection
Screaming Frog Crawl Analysis

Google Sheets Content Audit Setup

  1. Make a copy of the SEO Content Audit Template.
  2. Open up your ‘Internal Screaming Frog Export‘.
  3. Match all of the columns in the sheet and paste it into cell L8.
    1. Add or delete any rows for Columns A-G depending on the number of rows you paste.
    2. Each row you paste in should have corresponding calculations in Columns A-G, delete any excess.
  4. The calculations should all be updated.
  5. Sort ‘Column A‘ by largest to smallest.

This will now show you which pages are not performing well. You can also sort by pages with the least impressions or clicks when you weed out pages that are not in the index.

  • Column G: URL is on Google – Some pages may not be indexed in Google which is a big issue. They could be new, or their quality could be so poor Google decided not to include them.
  • Column F: Days Since Crawled Less Than Median – Has it been a while since Google crawled this page? Not a good sign of quality. Maybe needs more internal links and refreshed content to show Google that it has changed.
  • Column E: Impressions Less Than A Quarter of the Total Median – I use the quarter calculation to help weed out outliers that may skew the median. If your pages are not meeting a quarter of the median impressions, they need some work.
  • Column D: Clicks Less Than A Quarter of the Total Median – Same as above, but it’s easy for impressions to meet the threshold, clicks not so much. Maybe you’re getting a lot of impressions, but not clicks. Time to dive in and see why.

There are 7 metrics, but you can always add more to further drill down on your content quality. Just mimic the structure/logic setup in the calculation columns for new ones to compare against.

Now it is time to review the data and make some decisions. If a page isn’t in Google’s Index that is easily the first thing you should deal with, make some notes as to why or why not you will tackle this. As for the impressions and clicks not being at least a quarter of the total median, now is the time for you to use the tool between your ears. Is the content actually helpful to the user? If so select the ‘Refresh’ option in the Decision dropdown. Is it unrelated to what you do? If so select the ‘Remove’ option in the dropdown. This audit should make it really easy to see what content is not performing in Google, it’s up to you to decide if it is still useful.

What Is Useful Content?

Useful content is tough to slap a metric on or have a tool check for you, again you will need to use your brain with this one. At the end of the day, every page on your website is an employee, and the entire website is the company. Are the employee’s doing their job? If not it is time to figure out why and if that ’employee’ can’t be fixed, it might be time to get rid of them.

Your content should serve a purpose. It should educate your users about your services and/or products. It should break down the Who, What, Why and How clearly enough that they don’t need to research anymore. Content is your website’s greatest asset just like employees are to a company. The more you invest, the better the return will be.

How To Use This Audit?

This audit is a tiny sliver of what can be done to audit a website’s content. Screaming Frog has many other APIs you can connect and create similar thresholds to surface poor-performing content. Google Analytics is one that I use as well but decided it would be easier to illustrate with just Google Search Console here.

Take the logic from this audit and see how it can best be molded for your needs. Create thresholds that your content should meet so you can easily focus on what needs to be fixed instead of looking at a massive list. If you have any suggestions for how I can improve this please let me know!

What Is A Content Audit?

Simply put a content audit is the practice of reviewing all content accessible on your website to ensure it serves a purpose and is helping the user. Google recently announced the Helpful Content Update aimed at devaluing content created strictly for ranking in the search engines. The goal of Google is “to organize the world’s information and make it universally accessible and useful” – Google.

There are many websites out there that are falling short of Google’s and their user’s expectations. Many webmasters, SEO’s and content writers are purely just creating content to check off a box or highlight a ‘win’ in their reporting to stakeholders. The goal should not be to just produce content, it should be to satisfy the user with your content. The way to do this is to ensure every page attached to your brand/website serves a purpose. A content audit is the first step to doing that.

How I Help?

The whole purpose of this website is to help educate those at any stage of SEO, Digital Marketing and Website Analytics. I have learned from so many others that I wanted to do my part to help in the same way they helped me. I enjoy writing these blogs, but if at any point you need freelance help for any area of digital marketing please feel free to reach out or on my social channels.

Who To Follow

Helpful Resources

Recent Articles

Digital Marketing & Chili
Digital Marketing
Mike Ginley

Digital Marketing & Chili

TL;DR Pretty ridiculous title but I think I have a point. I’m always seeing weird recipes on Instagram that use no ingredients or take 30

Read More »

Popular Articles