Analysing traffic increases & decreases using Excel

With the huge amounts of long tail traffic websites get these days it’s very hard to analyse where sudden traffic increases and decreases come from. If you get 100,000 visitors one month and 80,000 the next month somebody is going to need a report detailing where that traffic went.

Google Analytics offers a fairly comparison of one months data over another and is also not too bad for comparing how traffic has risen (or fallen) for your top keywords.

This data is useful but it doesn’t show us what we really want to know – the keywords with the largest gains and losses from one month to another. To do this you need to export all of your keyword referral data (use Excellent Analytics to get around the 500 row limit in Google Analytics) for the two months that you want to compare and put them in an Excel file with one sheet for each month.

Next you need to add 2 blank columns next to your current months data, one column for last months figures and one for the gain/loss. Once you have done this it’s time to use the VLOOKUP function which allows you to query your previous months data on a keyword by keyword basis.

The formula you need is something like this, where January is the name of last months data sheet and A2 to B501 is the data range.

=VLOOKUP(A2,’January’!A$2:B$501,2,FALSE)

The end result is the nice spreadsheet below which can be ordered to show traffic losses and gains as you wish.

Advanced stuff

To take this to the next level format the data as a table and run a filter on keywords to include certain product names to see if you can identify a particular keyword silo that’s causing the issues.

If you are really advanced then you can download landing page data with the keywords and run reports to see which landing pages are doing better or worse. Combine this with keyword filters to narrow down exactly which keywords and landing pages are being affected and you will get a pretty full picture of your search campaigns.

In fast moving industries you probably want to save this as a template and run the reports each month.

BY Patrick Altoft AT 7:18pm ON Thursday, 18 February 2010

Patrick Altoft is Director of Search at Branded3 and has worked in the SEO industry for over 10 years. With experience across some of the worlds largest brands as well as startup businesses Patrick is well known in the industry and speaks regularly at the major SEO conferences and events. Follow Patrick on Twitter or Google+