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

February 19, 2015 Victoria Hoffman

Content Audit

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 of improvement for which you should cater your content strategy.

Before I jump into how I 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. (If you are NOT performing your audit on a Hub, skip to Step #4!)

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 the performance of each piece of content within each stream.

To sort URLs by stream:

  1. Add an extra column in your spreadsheet for Stream data (if you don’t have one already)
  2. In your Hub, Go to Hub Metrics > Page Views > Item Performance
  3. Select the appropriate date range
  4. Export your data in a CSV
  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 to pull the data Kapost-style, to keep everything consistent, I would recommend using Screaming Frog in List mode.

  1. Copy & paste the column containing your URLs into an Excel file, keeping them in a single column. Save this document - you’ll need it again for Step #9.
  2. In Screaming Frog, Select Mode > List View.

Screaming Frog mode

c. Click “Select File”. Upload your single-column URL list spreadsheet.

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

e. Match the values using VLOOKUP in your content audit doc.

5.     Pull Google Analytics data

For our audit, I only pulled Unique Pageviews, but you can pretty much pull 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 Pageviews 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):

  1. Open a new tab in your Google spreadsheet
  2. In the top menu, select Add-ons > Google Analytics > Create new report

Google Analytics Report

  1. Fill in the appropriate information in the pop-up that appears on the right. For example, if you want to pull Unique Pageviews, fill out section 3 as demonstrated below.

Google Analytics Report Unique Pageviews

  1. Select “Create Report”.
  2. 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

  1. In the top menu, select Add-ons > Google Analytics > Run reports
  2. 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 2 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.

  1. After creating a free account in SharedCount, go to URL Dashboard and select Bulk Upload.
  2. Remember that single-column Excel file containing all your URLs from Step 4? Import it here.
  3. Once all your social data populates, export it as a CSV.
  4. Using VLOOKUP, match your values appropriately.

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!

Things we learned

Aside from the obvious – most viewed, most shared, etc – we were able to pull a few other interesting insights from our content audit.

In analyzing the number of each type of content versus the number of social shares and unique views generated by each type of content, we distinguished a couple of “sweet spots”. Infographics, templates and webinars had a higher ratio of shares and views to content, confirming that these high quality and very helpful pieces of content have a higher “virality” factor.

Uberflip Content Shares

Similarly, what they say about long-form content is true. Our content with a word count between 1,501-2,500 words is historically more likely to be shared.

Uberflip Content Word Count Shares

Finally, although Wednesday was the day of the week upon which we’ve published the most posts, Monday and Tuesday are the days upon which most of our content is shared. This was especially interesting to me as I’m always digging through our social engagement data looking for consistent patterns. Understanding the sharing activity of our audience on our Hub will help us refine our content distribution strategy.

Uberflip Day of the Week Shares

I’ll admit, when I finally downloaded and cleaned-up the data for each and every single piece of content on our Hub, I felt powerful. So much data! So many learnings! in addition to pulling insights, we’re also using the data from our audit to inform a number of other projects, everything from a Hub revamp (coming soon!) to updating outdated pieces of content.

Let us know if you have any questions about performing a content audit in the comments.

Learn more about how to use data in your content strategy. Check out our eBook. Data-Driven Marketing: How To Create & Optimize A Content Strategy With Data At Its Core.

About the Author

Victoria Hoffman

Victoria is the Content Specialist at Docebo.

Follow on Twitter More Content by Victoria Hoffman
Previous Article
How to Achieve the Ideal White Paper Format
How to Achieve the Ideal White Paper Format

The white paper is a more than a form of content - it's a powerful marketing tool.

Next Article
5 Key Conversations to Have Outside of Your Marketing Team
5 Key Conversations to Have Outside of Your Marketing Team

This blog covers the essential questions to ask from your sales and customer success teams in order to ensu...