Analyze Google Analytics Data Without a Web Browser (Use Excel Instead)



If you’re a Google Analytics user I recommend that you download Excellent Analytics (full disclosure: I was the one who originally pushed for the creation of it), a free, open source, plug-in for Microsoft Excel that will enable you to pull Google Analytics data right into your spreadsheets.

To get started, download and install Excellent Analytics. This will add the Excellent Analytics menu to Excel. When you click on it you get three options to choose from: Account, New Query and Update Query.

Account is simply where you enter your Google Analytics e-mail address and password.

New Query is where you define the data you wish to pull into Excel. If you click this option and haven’t entered your login information the Account dialog box will open.

Update Query is only activated once you have run a previous query and marked its heading.

Example

Let’s say you suspect that your website does not display properly in some web browsers, potentially causing visitors to leave. Well, let’s use Excellent Analytics to find out if your hunch is correct!

This is what you do:

1. Click New Query (and enter your credentials if you haven’t already)

2. Select the date range and Google Analytics profile

3. Select the browser dimension by clicking Dimensions, expanding the Visitor level and ticking the browser box.

4. Select the bounces and visits metrics by clicking Metrics, expanding the Visitor level and ticking the bounces and visits boxes.

5. Execute the query

6. In Excel, use formulas to calculate KPIs.

In this example it seems like Safari users are less likely to bounce than Internet Explorer or Firefox users.

VoilĂ ! Using Excellent Analytics doesn’t have to be more complicated than that.

Please note that there are limitations to which metrics and dimensions that you can combine.

If you’re not an Excel wizard, or simply looking for inspiration on how to take your Excel reporting to the next level, check the numerous websites dedicated to Excel dashboarding (webimagesvideos).

Benefits of reporting and analyzing Google Analytics data in Microsoft Excel:

  • Get one less tool to keep track of
  • Use a familiar interface
  • Combine data from multiple data sources
  • Use Excel formulas, charts, and pivot tables
  • Define and calculate customized KPIs
  • Build dashboards just the way you like them
  • Share workbooks with other Excel users
Excellent Analytics was released in June and has been updated a couple of times since then. The project is still in its infancy though, and your help in improving it would be appreciated. The project was initiated by Mark Red, but has been made available at no cost and is being run as an open source project. Developers on four continents have joined as volunteers. If you would like to contribute as well, please send an e-mail to Christoffer at Mark Red.

If you’re not a programmer/developer and would like to contribute you can always post your comments about improvements that you’d like to see made.

Author: Lars Johansson

Subscribe by Email

Comments

  1. says

    Great tool, if you like Excel.

    We created a similar tool that runs in the browser. It lets you:
    - query metrics and dimensions from the Google Analytics API (no limitation to number of rows)
    - generate a table with real-time data, which you can sort and export to Excel, CSV or XML.
    - visualize the query result in an interactive pivot chart that shows the selected metric(s) grouped by the selected dimension(s).
    - add the pivot chart to your iGoogle, so that you have instant access to it at any time

    Try it here: http://www.youcalc.com/apps/1249652239535

    We also created a light edition of the tool, for low-tech users who just wants to pivot live Google Analytics data in a chart, without building queries: http://www.youcalc.com/apps/1249987579059

    Any feed-back would be welcomed!

  2. Peter van den Berg says

    friends of excel and excellent analytics,

    I love excel. terrific tool.
    However, by using excellent analytics I'm not able to retrieve the data:

    of course I'm logged in, get the dimensions etc. and working in excel 2007.

    Had anyone of you in the beginning problems to retrieve data and solved the problem by 'doing something'.

    Please react: Peter@Pifworld.com

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>