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:
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.
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.