By 5 years ago in Analytics

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.

Analytics traffic increase

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.

Traffic loss analysis

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 Feb 2010

Patrick is the Director of Strategy at Branded3 and has spent the last 11 years working on the SEO strategies of some of the UK's largest brands. Patrick’s SEO knowledge and experience is highly regarded by many, and he’s regularly invited to speak at the world’s biggest search conferences and events. Follow Patrick Altoft on Twitter.

comments

  • http://seogadget.co.uk/ Richard Baxter

    Excellent tip Patrick. The advanced ones particularly!

  • Justin

    how to extract all keyword referral data with excellent analytics? which dimension and metrics should i take?

    thanks for any tutorital! :)

  • http://www.affiliatethree.com James

    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?)

  • http://www.affiliatethree.com James

    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.

  • http://www.adelleda-seo.co.uk/ Richard

    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.

  • Pingback: » Der SEO Kopfgeldjäger | seoFM - der erste deutsche PodCast für SEOs und Online-Marketer

  • http://www.internetmarketingblog.net Necati @ Internet Marketing Blog

    There seems to be something wrong with the data in the screenshot though. Figures on most rows don’t add up.

  • Pingback: February 10 — Roundup | Barry Hand

  • http://seoconsultantx.co.uk Trevor

    Great tip Patrick. One I will be certain to use in the future.

  • Luis

    Very good post.

    For custom report automation on Google Analytics data, I use an application that it’s saving us a lot of time and money. It’s is really worth checking it out:

    http://www.reportingsuite.com