Introducing Analytics Docs 3.0

Archer is releasing an update to a free tool for the web analytics community. The tool will help you create more actionable reports faster and more efficiently. We’ve updated the Google Docs Spreadsheet GA Data Grabber to be compatible with Google’s new API. It’s a Google Docs spreadsheet that automatically pulls GA data from the new API; we call it Analytics Docs 3.0.

We use Analytics Docs 3.0 internally because it saves us 10 to 15 hours per person per week, and because there are certain analyses that just aren’t effective to do with the standard reporting tool. Since it’s a spreadsheet, Excel power-users will find it really familiar. The tool supports all of Google’s charts and data layouts, making visualization or export into another tool (Excel, Tableau, etc.) almost instant. It’s also cross-platform, making it a great tool for GA users everywhere.

For instance, here’s what hourly activity on our website looks like, broken out by day of the week (we used data from April and May to create this chart – in about 15 minutes total).

This new version:

• Will continue working when Google turns off the GA API v2.4 (expected sometime in June 2012)
• Shows you when Google is sending Sampled Data
• Generally runs more reliably than the previous version

You’ll need to register for your own GA API Key to use it (the process is well-documented inside the tool itself).

Our update includes many new few features:

Data Updates:

• Sampled data – the script shows whether or not your data has been sampled (and you can turn this feature on/off for each request)
• Timestamps to ensure fresh data (no caching)
• Refresh button to update all data
• Dates now calculate properly
• Get the list of all the goals set up for all your profiles
• Set the maximum number of rows returned (up to 10,000, which seems to be Google’s max allowance)

Authentication Improvements:

• Uses Google’s OAuth 2.0 Authentication (you don’t have to give it your GA username and password!)
• Requires you to register an application for the Analytics API and get your ClientId and Consumer Secret here

Code/Scripting Improvements:

• Refactored functions with clarified naming
• Private functions based on best practices

We learned a couple of lessons building this, too (developers, we’ve got a few hints for Google Scripts users here!):

OAuth 2.0 and Google Spreadsheets Limitation

OAuth 2.0 requires that the user authorize the request for the web service. We’ve discovered that authorization can’t be completed outside of the Script Editor. Google Documents appears to suppress the Authorize prompt (for security purposes). As a result, the spreadsheet can be clunky the first time, as opening the Script Editor is required to authorize the spreadsheet (complete instructions are included in the document).

Data caching

Google Documents caches fetchurl requests to avoid pushing daily quotas. We added a timestamp to the requests to ensure that the data is always fresh. The onOpen function sets a cell’s value with the current date and time. A refresh button enables “on-demand” data refreshes by the user – without using the script editor.

OAuth 2.0 Scope and Usage notes

When setting up the OAuth code, we discovered a few things. RedirectURI and scope were both unknowns and hard to determine based on currently available resources or documentation that we could find at the time. The RedirectURI should be set to https://docs.google.com, which makes sense since this will be the site that OAuth will be verifying on behalf of. The scope changes based on the Google API service that you are accessing. In the case of Google Analytics, the scope used is https://www.googleapis.com/auth/analytics.readonly. The code below will work for most Google APIs; the scope will change based on the service that you access:

var redirectURI = "https://docs.google.com";
var oAuthConfig = UrlFetchApp.addOAuthService("analytics");
oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://www.googleapis.com/auth/analytics.readonly");
oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
oAuthConfig.setConsumerKey(clientId);
oAuthConfig.setConsumerSecret(consumerSecret);

Get your own copy of the tool, with instructions and privacy policy, here.