UPDATE: Now also available for Google Docs, see more information here
One of the most popular solutions for fetching Google Analytics data into Excel has been my set of VBA functions that can be used on an Excel worksheet just like any of Excel’s built-in functions. Shortly after the Analytics API was released three years ago, Google mentioned it as the #1 way for accessing the API from Excel. There are several other tools for fetching Analytics data into Excel (such as our GA Data Grabber and FasterMetrics tools), but nothing can match the flexibility of the VBA functions.
Now after three years, due to changes Google has made to the Analytics API, the original functions have become nearly unusable, and will break completely when the ClientLogin authentication method is removed. As many companies rely on the functions for their reporting, we have decided to publish a new version that works with the new Analytics API.
The new version of the Google Analytics VBA functions for Excel can be downloaded here. From a user perspective, we have kept the new version as close to possible to the old one, to make it easy to migrate existing reports. The functions have the same names (getGAdata, getGAaccountData), and accept the same parameters. This means you can simply copy the new VBA code from the file linked above into your old report files.
There are some changes though, the biggest being that instead of the getGAauthenticationToken function, you need to visit analyticsvba.com to get an authentication token. These tokens will not expire like the old ones, so you only need to do this once. With this new OAuth authentication method, you don’t need to enter your Google account password to the Excel sheet, which makes this much better from a security point of view. The token you place on the sheet only gives access to Google Analytics data, not other parts of your Google Account, and it can be revoked at any time from your Google dashboard.
There are some other improvements as well:
- You can fetch data for multiple profiles with a single query, just combine the profiles IDs with an ampersand (&). This is much faster than the old way of typing a separate getGAdata query for each profile.
- You can fetch an unlimited number for metrics in a single query
- You can fetch more than 10000 results with a single query, using the new maxResults parameter
- You can specify that the dimension values are not shown in the results using the new includeDimensionColumns parameter (this is handy, for example, if you want to fetch data on monthly level for multiple segments side by side, and don’t want each query to return the month numbers)
- The authentication token will not expire, unless you choose to revoke it (with the old version, the tokens expired after 30 days)
To justify the development and maintenance work required for this new version, it comes with a small annual price of € 99. You get a free trial at first, and if you want to continue using the functions, you can make the payment at analyticsvba.com. What you get for this price is a reliable solution that we will guarantee to keep working despite changes Google makes to the API, and support through our help forum.
If you are new to the functions, to get started take a look at this tutorial. You can find a reference for all the function parameters here. The functions work in Windows Excel 2003 and later. For Mac users, we offer GA Data Grabber and FasterMetrics.
Let us know if you have any feedback of the new version!