03 January 2024 |

A content audit template

By Tracey Wallace

I’ve been thinking a lot about content audits. For one, I try to do one at least every year –– typically during the holiday season since things are slower. And two, holy heck there has to be a better way to do this. 

Did I spend an insane amount of my time off this holiday season trying to find a content audit tool that wasn’t SEO-focused? Sure did. What did I find? Parse.ly, though it isn’t exactly what I need. 

If you have one you use and love, please send it my way. 

In the meantime, I made my own rudimentary template. In an ideal world, after you followed the steps below, you’d add in categories, themes, word count, page height, etc. This is where it gets tedious. 

But, give yourself about 30 minutes to an hour, and you can create something rudimentary as you dive into the manual bits. 

Here is how it works:

  1. Go to your GA instance > Behavior > Site Content > All Pages and use a filter for your content pages specifically, whatever it is you are responsible for. Blogs? Case studies? Resources? Whatever it is. 
  2. Pull the data for the entire year, and then export it into Google Sheet. 
  3. Do the same under Conversions > Goal URLs. Choose the goals important to your organization, and export those to a Google sheet. 
  4. Combine each sheet, and then map the sheets together to create a single sheet that is an inventory of all your content that includes:
    • Page URL
    • Pageviews
    • Unique pageviews
    • Avg. Time on Page
    • Bounce Rate
    • Goal 1
    • Create a column for goal 1 conversion based on page views 
    • Goal 2
    • Create a column for goal 2 conversion based on page views 
  5. Here is an example sheet for you to use. 
  6. From here, add an average column at the top and average all of the metrics at the top. This calculation is done for you in the template. 
  7. Optional: Use conditional formatting to highlight cells green that are above the average for their column. This is just a nice visualization tool for me…but what you really want:
  8. Create a new tab and add all of the URLs to it.
  9.  Create IF functions on that tab for if a cell for that specific URL is above or below average. And if above, give it a score of 1. 
  10. Add up the scores and create a point system:
    • 1: Assess value
    • 2: Update 
    • 3-4: Opportunity 
    • 5-6: High performer 
    • 7-8: Top performer 
  11. Now, and this is important if you are going to share this with other folks or filter… COPY the numbers from the total sheet (the data sheet in the template)  and create a score column in the sheet with all the URLs and GA data. Paste the value only! 
  12. Then, create a value column to assign a content action based on score. You’ll use VLOOKUP for this: =VLOOKUP(SCORECOLUMN,DATARANGE).

What you’ll end up with a list of all the content for the last year, and its status / what to do next based on how it performed across average traffic metrics, engagement metrics and conversion metrics. 

Be sure to create a copy of the template if you are going to use it, and do noootttt forget to copy and paste the value of the content into a new column the sheet with all of the metrics on it. Then, use that column to VLOOKUP. If you don’t, you’re going to sort one of the sheets, and none of the numbers will be right –– and it will drive you made. Trust me on this if you have trusted me on nothing else! 

Go get ‘em, 2024!