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.

You can see the tools available to link to Excel in the Google Analytics Application Gallery.

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.

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.

You can amend the functions - I would recommend adding a login dialog a button to activate a new API query - it can get unresponsive when tabbing between fields. I will do this when I get a moment.

2. Tatvic Excel plugin

This is the next level of sophisitication provided by an Indian GAAC authorised consultants. Currently free, it makes it easy to select dimensions and metrics onto different worksheets and then update or refresh these when required. I found it retrieved 1,000s of records quickly to enable detailed analysis of landing pages or keywords.

3. Shufflepoint query selector

This is a similar approach to defining queries - slightly more polished than Tatvic, but a paid service.

Other systems for enhancing Google Analytics reporting

Of course, Excel isn't the only method of improved Google Analytics reporting and several other software or services that have been released.

Update - other tools

• BIME for Google Analytics