Let’s continue talking about doing a content audit on our site. This is where we go back into the archives of our blogs and bring all that content up to speed (and likely delete a lot of it, too.)

Some of our goals for doing this are:

• Find ways to improve our SEO
• Find the blog posts that have performed well from a marketing perspective (in terms of traffic and email conversion)
• Find out what topics your market really prefers
• Find gaps in the material you cover on your blog, both from a topical perspective, but also in terms of marketing)
• Find posts which should be consolidated because they cover the same thing
• Find underperforming content and optimize it
• Get ideas for new content
• Get rid of old, low quality content or content which no longer reflects your business.

So, that’s the idea. And as I mentioned in the last issue, this is a large project I’m working on here at the Blog Marketing Academy. I had posts in there as old as 2006… and I had never done a content audit. Shame on me! 🙂

In the last issue, I talked about simply going back to “Page 1” in your blog archives, back to your first post, and just working your way forward. But, it is also useful to have some DATA about all these posts as you go.

So, let’s begin talking about that data gathering…

Getting A Spreadsheet Of Your Archives

It is nice to have all of your content sitting on one screen so you can get the big picture. Plus, you can collect certain data that is useful as you evaluate things.

Typically, this involves the use of spreadsheets. And one of the first things I did myself was to get a spreadsheet of every blog post on my site. Here’s how you can do it, too…

There are third-party programs that can help do some of this. For instance, some people like to use Screamingfrog to crawl the site and get a bunch of data. That program is free for up to 500 URLs. I had more than that, so I found another solution which was simpler, although it didn’t provide as much data…

You can also create your own spreadsheets. One quick little tool I found is the Export Post Info plug-in. This simple plug-in will simply export a CSV file of all your blog posts. This plugin exports posts Publish Date, Title, URL, Word Count and Categories to a CSV file that can be imported into Excel. Big shortcut to get the initial data dump.

One thing I appreciated about the Export Post Info plug-in was that it also exported the word count for each post. This allows you to quickly scan your archives to see which content is pretty short and which is longer. Why does that matter?

Most analysis of blog post content which performs well in search show that somewhere in the 1500-2000 word range is a nice sweet spot. You can also go longer, as I often do as I execute the redwood strategy, however if you do much shorter than 1500 words, your post might not perform as well.

Having that word count right there in the data dump allows me to quickly find the stupid old posts I used to write which were 300-500 words long.

Getting A Traffic Data Dump For Your Blog

OK, one more spreadsheet here. This one is for traffic.

Now, I will warn you that COMBINING this spreadsheet with the one we made up above is pretty difficult. At least we found it to be. So, in the end, we just didn’t bother. We simply used both spreadsheets independently as data references. Anyway…

To get our traffic data, we will, of course, defer to Google Analytics.

1. Go into your Google Analytics account for your site.
2. Go to Behavior > Site Content > All Pages
3. Adjust your data range for the report to get the last 90 days of traffic.
4. In the list of pages table, find the “Show rows” option at the bottom and choose a number big enough to capture every page of your site.
5. If you want a spreadsheet of this data, then at the top of the screen, hit the Export button. Select your preferred format (likely Google Sheets, Excel or a CSV file).
6. Download it and you’ve got yourself a spreadsheet. 🙂 When you first open it, you might be confused that it didn’t seem to export much of anything. But, just switch over to the “Dataset1” sheet and the entire export will be there.

Now, that data dump is going to include your page URLs, but those URLs won’t include your full domain name. If that bothers you, then here’s what you do…

1. Insert a new column in your spreadsheet right next to the URL column (column A) as exported out of Analytics. Let’s make your new column Column B.
2. In cell B2, enter the formula =CONCATENATE(“http://www.blogmarketingacademy.com“,A2) . Of course, replace my domain with your’s. And this assumed that cell A2 has the first URL. What we’re doing here is adding the domain name to the URL slug as it came out of Analytics.
3. Cell B2 should now have a full URL. Now, to do the same for all other cells in column B, simply drag that formula down to copy the formula down to all other cells.

And  if you want to make yourself a CLICKABLE URL, we can do one last step…

1. Insert a new column C.
2. In the first cell, use the formula =HYPERLINK(B2) . This will give you the same URL you have in cell B2, but this time you can click on it to see the page itself on your site.
3. As you did before, drag that formula down to all remaining cells in column C to do the same.

Alright, I know that got a little geeky. 🙂 But, what you would have, then, is two spreadsheets you can use for reference as you go through your blog archives. One spreadsheet straight out of Wordpress that also includes your word counts, and the other one which contains your basic traffic metrics for the last 90 days.

Keep in mind that Analytics is only going to export pages which actually got traffic. In other words, the page has to have gotten at last ONE page view in the last 90 days otherwise it won’t be on the report. For this reason, if you find any pages on your Wordpress data dump that are missing from the traffic data dump, it is because that page got no traffic at all.

OK, my friends.

We’ll continue on the next issue. 🙂

– David