How to Conduct a Content Audit for More Data-Driven Content Marketing

Completing a content audit is a daunting but extremely rewarding task—especially when you have over 450 pieces of content sitting in your blog or resource center.

If you’re already measuring your content marketing efforts, then you probably have a good idea as to what’s working and not working with your audience. A content audit that evaluates the performance of all of your content with a full set of data will help you see the bigger picture—especially the gaps and areas for improvement for which you should cater your content strategy.

Before I jump into how we performed a content audit on Uberflip’s Content Hub, I’ll offer a couple of tips that might be helpful to know before entering the depths of Spreadsheetlandia:

  • If you have a lot of content, you’re going to have a lot of data. I’d recommend using a Google Sheet (instead of Excel) to keep things running quickly (plus, autosave).

  • If you’re not a seasoned spreadsheet user, brush up on how to use the VLOOKUP function. The data from the URLs isn’t gathered in the same order from every tool, so this is super helpful for keeping the audit accurate and will save you tons of time.

  • Confirm the date range from which you’ll be pulling your data. This will likely be “all-time,” but confirm and ensure that your date ranges are set correctly when acquiring your data.

1. Set up Your Spreadsheet

The columns of your content audit should be labeled with the type of data you’re about to collect:

  • Title
  • Stream (optional)
  • Content Type
  • URL
  • Publish Date
  • Weekday
  • Buyer Persona
  • Views
  • Title Length
  • Word Count
  • Facebook Likes
  • Facebook Shares
  • Facebook Comments
  • Facebook Total Engagements
  • Tweets
  • +1s
  • Pins
  • LinkedIn
  • StumbleUpon

The rows will contain the data from each individual piece of content.

2. Download All Your Content URLs

There are a number of tools that allow you to do this simply by entering your blog’s domain (or, you can pull this directly from Google Analytics). I would recommend using Kapost’s Content Auditor because it allows you to pull the following information:

  • Title
  • URL
  • Publication Date
  • Content Type
  • Buyer Persona

If any of the above is missing when you pull your data (most likely Content Type and Buyer Persona), you can tag it in the Kapost auditor tool.

Once all your content is aggregated in Kapost, export it in a CSV. Then, implement it into your audit doc. This information will become the core data for your audit—especially the content titles and URLs.

3. Sort URLs by Stream

Note: If you are NOT performing your audit on a Hub, skip this step.

One of the nifty things about Content Hubs is that you can create Marketing Streams to group your content according to your target’s interests. As such, the same piece of content can live in multiple Streams (for example, our Content Discoverability ebook lives in our Blogging & Copywriting Stream and our Content Marketing Stream).

For our audit, I broke down the data for each piece of content by Stream to gain insight on their performance.

To sort URLs by Stream:

Step 1: Add an extra column in your spreadsheet for Stream data (if you don’t have one already)

Step 2: In your Hub, go to Hub Metrics > Page Views > Item Performance

Step 3: Select the appropriate date range

Step 4: Export your data in a CSV

Step 5: Using VLOOKUP, match the Stream name and Page Views accordingly in your audit doc

Note: You can also keep the Page View data from these steps and add it to your audit.

4. Pull SEO Data

Using free software called Screaming Frog SEO Spider, I was able to pull the following data:

  • Title Word Count

  • Content Word Count

  • Meta Description Word Count

Although you can enter your blog domain in Screaming Frog and allow it to pull the data Kapost-style, to keep everything consistent, I would recommend using Screaming Frog in List mode.

Please note: Screaming Frog is a third-party tool. As with any reference to a third-party application, we do not officially support the use of this tool. These are general guidelines and we offer them only to be helpful.

Step 1: Copy and paste the column containing your URLs into an Excel file, keeping them in a single column. Save this document—you’ll need it again later

Step 2: In Screaming Frog, select Mode > List View

Screaming Frog mode

Step 3: Click Select File. Upload your single-column URL list spreadsheet

Step 4: Screaming Frog will pull the data for the URLs contained in your document. Export this information as a CSV

Step 5: Match the values using VLOOKUP in your content audit doc

5. Pull Google Analytics Data

For our audit, I only pulled unique page views, but you can pull almost any other GA data that can be broken down by page (sessions, time on site, bounce rate, etc.). Note: If you’re using a Hub, the page views from Hub Metrics that you pulled earlier are not unique.

You can export this data from Google Analytics and match the values in your audit, or use this fun trick to pull it directly into another tab in your audit (if it’s in a Google sheet):

Step 1: Open a new tab in your Google spreadsheet

Step 2: In the top menu, select Add-ons > Google Analytics > Create new report

Google Analytics Report

Step 3: Fill in the appropriate information in the pop-up that appears on the right. For example, if you want to pull unique page views, fill out section 3 as demonstrated below.

Google Analytics Report Unique Pageviews

Step 4: Select Create Report

Step 5: Columns A and B will populate on your sheet. IMPORTANT: Enter the appropriate number of days from which you’d like to pull your data in Column B, Row 7:

Google Analytics Report

Step 6: In the top menu, select Add-ons > Google Analytics > Run reports

Step 7: Your data will appear in a new tab in your Google spreadsheet. Use VLOOKUP to match the values in your audit accordingly

*NOTE: If your GA data only includes the end part of the URL, you can add the domain by using the CONCATENATE function to merge the two parts of the URL.

6. Pull Social Data

You may have kept the social data that was included when you pulled your URLs from Kapost, but I would recommend replacing this with data from SharedCount, which provides a bit more information.

Step 1: After creating a free account in SharedCount, go to URL Dashboard and select Bulk Upload

Step 2: Remember that single-column Excel file containing all your URLs from earlier? Import it here

Step 3: Once all your social data populates, export it as a CSV

Step 4: Using VLOOKUP, match your values appropriately

Please note: Sharedcount is a third-party tool. As with any reference to a third-party application, we do not officially support the use of this tool. These are general guidelines and we offer them only to be helpful.

7. Double-Check Everything

Phew, you’re done… almost!

This step requires the least explanation, but will likely be the most time-consuming (it was for me, anyway). After pulling all the data, I took a huge amount of time going through each piece of content and ensuring all the data was accurate. If something didn’t look right, I’d double-check. Granted, I didn’t find too many errors, but it’s a complicated process and things could easily go wrong.

8. Generate Insights

And there you have it—a fresh batch of data just waiting to be sliced, diced, and turned into insight!

About the Author

Victoria Hoffman

Victoria is the Content Specialist at Docebo.

More Content by Victoria Hoffman
Previous Article
How to Use Customer Reviews to Inspire High-Converting Content
How to Use Customer Reviews to Inspire High-Converting Content

The secret to high-converting content is hiding in plain sight.

Next Article
How to Measure the Success of Your Lead Nurturing Campaign
How to Measure the Success of Your Lead Nurturing Campaign

How do you know your demand generation marketing efforts are paying off? This blog post explores what makes...

×

Want more marketing tips, trends, and insights?

Subscribe to our email list!

First Name
By registering, you confirm that you agree to the storing and processing of your personal data by Uberflip as described in the Privacy Policy. By registering I confirm that I have read and agree to the Privacy Policy.
You did it! ;)
Error - something went wrong!