Explore our Google Analytics Toolkit

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

Author's avatar By Dave Chaffey 03 Sep, 2009
Essential Essential topic

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.

ga-excel-plugin

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.

tatvic

3. Shufflepoint query selector

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

shufflepoint

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


Author's avatar

By Dave Chaffey

Digital strategist Dr Dave Chaffey is co-founder and Content Director of online marketing training platform and publisher Smart Insights. 'Dr Dave' is known for his strategic, but practical, data-driven advice. He has trained and consulted with many business of all sizes in most sectors. These include large international B2B and B2C brands including 3M, BP, Barclaycard, Dell, Confused.com, HSBC, Mercedes-Benz, Microsoft, M&G Investment, Rentokil Initial, O2, Royal Canin (Mars Group) plus many smaller businesses. Dave is editor of the templates, guides and courses in our digital marketing resource library used by our Business members to plan, manage and optimize their marketing. Free members can access our free sample templates here. Dave is also keynote speaker, trainer and consultant who is author of 5 bestselling books on digital marketing including Digital Marketing Excellence and Digital Marketing: Strategy, Implementation and Practice. In 2004 he was recognised by the Chartered Institute of Marketing as one of 50 marketing ‘gurus’ worldwide who have helped shape the future of marketing. My personal site, DaveChaffey.com, lists my latest Digital marketing and E-commerce books and support materials including a digital marketing glossary. Please connect on LinkedIn to receive updates or ask me a question.

Recommended Blog Posts