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:
- Stream (optional)
- Content Type
- Publish Date
- Buyer Persona
- Title Length
- Word Count
- Facebook Likes
- Facebook Shares
- Facebook Comments
- Facebook Total Engagements
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:
- 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
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
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.
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:
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!