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.
Patrick Altoft is Director of Search at Leeds based digital & SEO agency Branded3. Patrick also runs Blogstorm.You can get our blog posts delivered for free by email every day - simply add your email address to the box below or alternatively grab the RSS feed.
{ 6 comments… read them below or add one }
Find me on StumbleUpon | Twitter
Excellent tip Patrick. The advanced ones particularly!
More comments from Richard Baxterhow to extract all keyword referral data with excellent analytics? which dimension and metrics should i take?
thanks for any tutorital!
Hey good article, but I can’t seem to figure out which columns etc, to use in Excellent Analytics (maybe making a example Excel doc to download would be useful?)
Figured it out, I used “Keyword” under “Campaign” for the dimension and “visits” under “Visitors” for the Metrics. Then make sure you choose the right website, date range and click execute.
This looks like a great method. Haven’t quite found the time to do it properly yet but I’m sure I will eventually. Offers more detailed comparison than using a quick tool like Wordle.
There seems to be something wrong with the data in the screenshot though. Figures on most rows don’t add up.
{ 2 trackbacks }
Leave a Comment (registration is optional)