Tools and tips for using Google Analytics with Excel to improve business reporting

I’m excited by the new opportunities to readily access and manipulate your Google Analytics data through the tools provided by Microsoft Excel gives great opportunities for creating custom dashboards or in-depth analysis. This post shows you how and highlights some of the free and paid tools to help.

But many site owners or marketers who aren’t web analytics specialists may have missed the announcement in April when Google Analytics launched an API to enable developers to access Google Analytics data and incorporate it into other web services or software.

This is significant since as Econsultancy recently reported in their Online measurement and strategy report, 80% of surveyed businesses are now using Google Analytics, so there is a great opportunity for many companies to get more from their Google Analytics installation by devising reports and analysis to it to improve and report on their online business.
I’ve been following these developments closely, since I’m the trainer on the new Econsultancy course on Optimising your site using Google Analytics and consult often on improving business reporting through Google Analytics.
By the way, I’m also repeating my breakfast briefing on Google Analytics for Econsultancy which we ran earlier in the Summer which attracted over 100 attendees. It would be good to see you along if you’re in the North or Midlands like me. This time the, details are:

* Getting the most out of Google Analytics briefing
* Date 14th October 2009 (8:30am - 10:15am)
* Venue Peter House, Manchester, United Kingdom
* Price: £75.00

About the Google Analytics API

The API has these features to query your accounts and profiles:

* Define own start and end date - good for standardising monthly or quarterly reporting
* Define dimensions (e.g. ga:pageTitle) and metrics (e.g. ga:bounces)
* Can define sort orders
* Can query using filters similar to profiles or Advanced Segmentation, e.g. restrict results to show keywords related to brand search only
* Offers an extract feed with 10,000 record

View reference for data feed API

View reference for dimensions and metrics

View Google Analytics API sample page
Benefits and disadvantages of the Google Analytics API

The benefits of using the API as I see it are:

* Can analyse larger data volumes (rows - the maximum is 10,000 rows per query - much larger GA reports)
* Can calculate derived or custom metrics - web analytics tools like Omniture or Webtrends enable this, but GA doesn’t by default
* Can produce custom reports and dashboards - there are some capabilities for this with the new custom reports feature within GA but I have personally found it lack flexibility
* Can use to archive select data - Google claims to store data for at least 25 months (may be more) - if you need historic data the API can store it
* When combined with an analysis tool like Excel options for analysis through sorting, filtering and alerts are enhanced

The disadvantages are:

* Security, security, security - risks if account username and passwords are stored in spreadsheets or could be accessed / stored by third-party data providers
* Complexity and staff training - as with all analysis the analyst has to know the right questions to ask

Business reporting and analytics in Excel using Google Analytics

In August, Google Analytics showcased some tools for creating Excel reports with Google Analytics. I’ve been experimenting with them. Here are my recommendations on how you can use them:

1. Simple Excel worksheet to configure from Mikael Thuneberg

This is a simple template you can adapt to your own needs. It has 2 functions:

* getGAauthenticationToken - to verify your account and log-in - NB There are security issues with storing GA login details in a spreadsheet - best that these are entered each time by users into a login dialog
* getGAdata - to pull data you specify

I found it easy to setup - you just enter your login details, profile number and an authentication string. Then you just need to type in the dimension and metrics for what you want to view using this reference. The example below shows how a report can be produced on brand keywords using a filter.