How to Add Analytics Data Into a Content Audit Spreadsheet

An early step in our website development process is to conduct a content audit. In this we take an inventory of all the pages on the site and collect information such as:

  • it’s level in the site structure
  • whether it’s linked from the menu
  • when it was last updated
  • it’s overall content quality (measured against our content standards)
  • if it links to any assets

We then make an assessment of each page, and recommend whether it should be retained, improved or deleted.

This helps us to restructure sites and identify where content improvements are needed.

The volume of information and work that needs doing following the audit can be overwhelming. So we want to help our editors to identify the pages they need to focus on first. One way to do this is show which are the most viewed pages on their websites.

It’s easy to get this data from Google Analytics, but how can we combine that into our audit spreadsheet? It’s not possible to just copy and paste the analytics data as there’s not a value for every page in the audit.

The answer is the VLOOKUP function in Excel (or Google Sheets if you prefer). This will compare the two spreadsheets and fill the values from the analytics data only in the rows of the audit spreadsheet where there’s a match between unique identifiers, in our case URLs.

If you can forgive the cheesy music, this video shows how VLOOKUP works. Take a few minutes to watch and then follow the step by step instructions below (they assume a basic knowledge of Excel).

Preparing the data

You’ve got two spreadsheets. One containing your site audit and the other with your analytics data. I like to begin by copying my analytics data (as there’s usually not too much of it) into a new worksheet in my audit spreadsheet.

Now let’s look for the information that’s common to both. In both the audit and our analytics data there is a column containing URLs. We’ll use this to match up the data.

First of all you need to make sure both sheets use the same URL format – you may need to add or remove http:// for example.

Next you need to make sure the columns containing the URLs are sorted the same way, let’s use A-Z.

Configuring the formula

In your audit sheet select the first empty cell in the column you want to import data into. Go to the Formulas tab and select ‘Insert Function’. Select the category ‘Lookup & Reference’ and choose VLOOKUP from the function list.

A dialogue box will open for you to enter the ‘Function Arguments’. And here’s what you need to enter:

  • Lookup_value – select the first cell containing content in the URL column of your audit worksheet
  • Table_array – select all the cells containing data in your analytics worksheet (you need to add a dollar $ sign before each column and cell, so A1 becomes $A$1, this will lock the reference when you copy the formula later)
  • Col_index_num – enter the column number you want to import data from, A= 1, B = 2 etc)
  • Range_lookup – enter false so that the function looks for an exact match between URLs

Your filled dialogue box should look something like this:

Function Arguments dialogue box for VLOOKUP function in Excel

Click OK to close the dialogue box. You should see that the original cell you highlighted now has a value in it. This could appear as #NA if the first URL in the list isn’t one of your most visited pages.

Copy the formula to all the other cells in the analytics column of your audit sheet (this will only work if you added the $ sign to the table array). Now to view your most visited pages within your audit spreadsheet you just need to sort or filter by this column.

Summary

Obviously, you can use this process for combining any two sets of data in a spreadsheet.

I was so pleased when I discovered how to do this – I love to solve a problem.

I’d be interested to hear from you in the comments if you found this post useful or if you’re using analytics data in any other way in combination with site audits.

Institutional Web Management Workshop 2015

At the end of July I attended my first Institutional Web Management Workshop. It was a great event where I learned a lot from colleagues in similar web roles at other UK universities.

The central themes coming through all talks at the conference were:

  • digital transformation – across the university not just focused on the website
  • user experience – a focus on people, not systems
  • working in an agile way – to enable continual development

Digital strategy: people, process, systems

Those of you who’ve been reading my blog for a while will know that I’m a keen sketchnoter. At the conference I experimented with a new style of sketchnote. Instead of drawing a note covering a whole talk, I isolated single quotes and ideas into separate sketches. This worked well, for me and also other attendees who shared my quick sketches widely on Twitter. I’ve since digitised some of them and made them available via my website.

I’ve written a guest post for the organiser, Brian Kelly. This goes into more detail about my key takeaways from the event. You can read it on the UK Web Focus blog: Reflections on IWMW 2015 by Emma Cragg.

Content strategy – a conversation with Kristina Halvorson

In June GatherContent ran a live Q&A with Kristina Halvorson on the topic of content strategy for redesign projects.

Kristina tackled 20 questions from the audience in 90 minutes. You can listen to the Q&A recording on the GatherContent blog.

Here are my key takeaways.

Content strategy is not copywriting

Not all copywriters are strategic thinkers. Copywriting is one element of content strategy. It also includes:

  • content inventories and audits
  • setting objectives/ goals for content
  • informing the future development and governance of content

How can we make people care about content strategy?

It’s a sad truth that nobody really cares about content strategy (other than content strategists of course). The best way to make people care about anything is to demonstrate why it’s important and what problems it can solve. It’s no different with content strategy. Sell the benefits.

Linking strategies for online and offline content

I deal with a lot of clients who miss the connection between the content they produce for print and the content on their websites. Here are some questions to ask yourself when linking or combining strategies for online and offline content:

  • What are the needs and expectations of each?
  • How are they linked?
  • Can content be reused?
  • How will content be updated/deleted?
  • What are the priorities?
  • What is in place for workflow and governance?

Content and style guidelines need to be linked to training

We need to support our clients who are writing content, not just hand them the guidelines and leave them to get on with it. We should be teaching them why the guidelines are important first and then make sure we’ve made it as easy as possible to follow them.

Managing content around an event

Tips to ensure content created for an event is relevant before, during and after:

  • build a community through a blog, talk about key themes and issues, start the conversation
  • use twitter for real time engagement with your community at the event and those participating remotely
  • share outcomes and resources through your blog and other channels, eg Slideshare

Essential elements of a project postmortem

When the project is done and dusted how are you going to review it? Here’s Kristina’s advice:

  • get all stakeholders in a room with a neutral facilitator
  • review the core outcomes
  • link the outcomes to the project goals
  • review what worked well and what didn’t

Conclusion

So much was covered in the 90 minutes that just reading through my notes to write this post my head is popping. There’s scope for further discussion around all of these takeaways – to keep the conversation going share your thoughts and questions in the comments.

Further reading

In defence of Lorem Ipsum – Karen McGrane

How agile and lean principles can improve content strategy and governance – Josh Tong