For the Destination Local Action Research in Audience Analytics project, I've presented two online video tutorials to help hyperlocal publishers make the most of and be more strategic with their website content. 

The first is on SEO, or search engine optimisation, and the second is on doing a website content audit. This tutorial is a how-to guide to enable hyperlocal publishers to get a better understanding of how the content on their websites perform. It covers useful cost-effective and time-effective techniques and tools, in order to understand what content you should be publishing more of, what content you could be publishing less of, what content your audience engages most with, and the opportunities for improving audience engagement via the content on your website.

If you manage a website of any size and have never carried out a content audit before then I strongly recommend it. In a relatively short time it can tell you a lot about your site - backing up some hunches you may have had, and revealing things you’d never have guessed.

In the video tutorial, I demonstrated this process using Created in Birmingham, a long-running blog featuring posts about Birmingham’s creative communities that I used to run, and below are some written instructions and a link to my content audit spreadsheet.

As you’ll have seen, I want to pull together a list of all the posts on the site, put them in a spreadsheet, and then bring in some extra data (Google Analytics and social media shares) to add context. I can then see what that reveals.

Here are some instructions for a content audit. If you’d like to follow along then you can see my spreadsheet here: http://bit.ly/nestadlcib

Step 1. Getting a list of articles

To start with, you’ll need the title and URL of every article on your site. There are various ways of going about this. You could:

• Use a tool like Screaming Frog (the free version will search your site and list the first 500 URLs it finds)
• Use Google Analytics to provide a list of all the pages that people have visited. This should be good enough but remember it won’t show pages people aren’t visiting, which might be useful.
• If you use Wordpress then you can export the database. There are some plugins for exporting to Excel but I’ve not used them.
• Manually copy each one from your site into a spreadsheet. Not recommended unless you have a very small site.

I used Screaming Frog, which gave me a list of URLs with lots of additional information including:

• Page title
• Meta description
• Word count (estimate)

I exported that into Google Sheets and tidied it up, removing columns I wasn’t interested in and rows featuring pages that weren’t posts (such as tags, categories, authors).

Step 2. Adding analytics data

Next, we want to add some information from our analytics. If you’re using Google Analytics then this is fairly straightforward.

1. Log in and go to Behaviour - Site Content - All Pages
2. Choose the period of time that you want to look at. In the example, I’ve looked at the past six months.
3. At the bottom of the page, you’ll see how many different pages have been picked up in your analytics. Use the dropdown to show as many as possible.
4. As you scroll down the list you may see some URLs in there. It’s not a problem, but you can filter these out if you like using the filter back at the top of the page. I excluded anything pages that contained /tag/, /author/, /page/, /wp-content/ or a question mark.  

That gave me a more manageable list of URLs, which I exported as a CSV and added to a new tab in my spreadsheet.

Step 3. Adding social share data

There’s a great service called SharedCount. Sign up for an account, go to the URL dashboard and click ‘Bulk upload’. Copy and paste your list of URLs from Step 1 and you’ll get back the number of times that URL has been shared across most of the top social media platforms.

Export this information and add it to a new tab on your spreadsheet.

By the way, I say 'most of the top social media platforms' because unfortunately Twitter recently removed access to this information. Also, bear in mind that this is far from an exact science - the share counts might not be 100% accurate, but they should be enough to give a feel for how things are going.

Step 4. Bringing it all together

The next stage is a little complicated, but you can look at the fourth tab of my spreadsheet to see how it works.

1. I copied the data from tab 1
2. I then added the column headings from tabs 2 and 3
3. I’ve then fetched the data for the new columns from their respective tabs using a function called VLOOKUP. Follow along in the example spreadsheet or look up instructions if you’re not sure how to do this.

With all that done, we can move on to the interesting bit…

Step 5. Analysis

Looking over the spreadsheet, we can see things we might never have noticed before. You’ll see I’ve added conditional formatting to some of the columns to make the information easier to understand at a glance.

Just from scanning the document I’m already asking several questions. For instance:

• I suspect a lot of posts are being read from the homepage, but page views for individual posts seem low. Could they be promoted better?
• Some older posts still seem to be popular. Why is that, and do they need updating? For instance, the 2013 post rounding-up the local Christmas markets has been popular, but people finding it will be disappointed to see the information’s out of date. 
• A high bounce rate seems typical, but some articles are more likely to keep people on the site? Why is that? 
• How come nothing’s being pinned on Pinterest? It’s an art and design blog, after all.
• Who knew content was being shared on Google+ and LinkedIn?!

…and that’s just from a cursory glance. Of course, you might want to add additional information to try and make more sense of your data. For example:

• if you add categories then you could see if, for example, articles about crime do better than sports
• if you add the article’s author then you could see who produces content that is more likely to be shared on social media
• if you included the type of content (text, image gallery, video, audio, quiz) then you could see how stats differ for each

I’m sure you get the idea. Try it with your own website and see what you discover.

You can find me on Twitter at @ChrisUnitt.