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.

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


  • http://www.excellentanalytics.com/ Mark Red

    We’d also like to encourage web analytics-minded developers to contribute to the free, open source, plug-in Excellent Analytics: http://www.excellentanalytics.com

    We can improve it together!

    • http://www.davechaffey.com Dave Chaffey

      Oh yes – meant to include that but I think Excellent Analytics only works with Excel 2007 and I like much of the corporate world am only on Excel 2003. Is that right?

  • http://spanishgringo.blogspot.com spanishgringo

    Depending on the type of reporting that you need to do, my web app (built w/ AppEngine + GA API jQuery + Flot) could be of use to many users, especially those that need to do regular reporting or dumping of data into excel or want to execute quick comparisons across multiple profiles and/or segments.

    http://gaevolution.appspot.com

    • Dave Chaffey

      Hi Thanks for sharing this. I don’t think it was available when this post was originally written.

      I have used your app when there are multiple country profiles and it’s very useful – recommend it! It’s curious that Google Analytics doesn’t have this functionality yet!

      Dave

  • http://www.nextanalytics.com Mike Sullivan

    Although this is an old article, people may land here still. Next Analytics is a new comer on the scene and worth a long look — many features well beyond these old-timers. They are currently giving away over 40 Excel dashboards that are ready-to-run and easily customized, and continue to give away more each week. New release includes social media queries as well!

  • Matt

    Hi there,

    I would like to ask you. I want to get data from source=facebook reduced by medium=cpc. So I want to get traffic clearly from facebook but not from facebook/cpc
    I tried this
    =getgadata(SUM!$V$6;example.com!$J$1;”visits”;example.com!O225-6;example.com!O225;”source=~facebook”)-getgadata(SUM!$V$6;example.com!$J$1;”visits”;example.com!O225-6;example.com!O225;”medium=~cpc”) but the result was negative -552

    Thanks

  • http://www.exceladvise.com mariah carey

    Hi Thanks for sharing this. this post is originally written and it’s very useful for me I recommend it The result is really impressive,

  • Nabendu Varma

    Hi.
    I am using Excel sheet by Mikael Thuneberg. When I am using multiple metrics in the query, separating them by ‘&’, the output is only with respect to the first metric. How to get all the results for multiple metrics. Any suggestions?

    Regards.

Get FREE marketing planning templates

Start your Digital Marketing Plan today with our free Basic membership.

  • FREE fast start guides to review your approach
  • FREE digital marketing plan templates
  • FREE alerts on the latest developments
Summer Sale 2014

Get FREE marketing planning templates

Start your Digital Marketing Plan today with our free Basic membership.

  • FREE fast start guides to review your approach
  • FREE digital marketing plan templates
  • FREE alerts on the latest developments
Feedback Form
Feedback Form