Access your Google Analytics data in Google Docs for custom reporting

We've talked a number of times about the importance of web analytics software for monitoring and optimizing your website. In particular, Google Analytics (GA) provides a nice set of tools for tracking many details about your traffic and site. While GA does have a lot of reporting options, anyone who has worked with analytics data knows that there are always new ways to customize things, and existing software just might not be up to the task. In such cases, you might think about exporting your data, then loading it into a spreadsheet program like Excel or Google Docs for additional analysis. It turns out that some tools exist to make things even easier, by letting you access your analytics data directly from Google Docs, without having to manually export the data.

The ability to import GA data directly to Google Docs comes courtesy of a few Google Docs scripts written by Mikael Thuneberg of AutomateAnalytics.com. I should point out that he also has written similar scripts for Excel, though I haven't tried them out yet. These scripts provide the ability to authenticate with your GA account, and then import data into Google Docs by calling a function inside a spreadsheet cell. As such, once you've set things up, any spreadsheets that you create will always have your up-to-date analytics data available.

If you are interested in using these tools, definitely start at Mikael's site, as he has a nice description of the scripts as well as some examples of how to use them. In this post, I'll just show you how the setup works to get some basic reports working. Before starting, you'll need to have set up Google Analytics on your domain, and signed up for a Google account.

To get started, create a new spreadsheet in Google Docs. You'll then need to add the new scripts by choosing "Script..." from the "Insert" menu. These scripts are user-generated functions that add additional functionality to Google Docs. To find the GA scripts, search for "Analytics" and install "Google Analytics Data Fetch Functions." You'll then be prompted by a warning that the script wasn't written by Google. It's worth pointing out that these functions will have access to all of your analytics data once you start using them, so you want to make sure you really trust the author of the code. In this case, I've glanced over the scripts and didn't notice any red flags, but be sure to keep in mind where a script comes from before using it.

Once you've installed the scripts, you can start using the new functions to access your analytics data. There are three new functions that you'll need to use:

• getGAauthenticationToken(email,password) - This function gets permission from Google to use your analytics data, based on your analytics email and password info. You'll need this token (the output of the function) to use the other functions
• getGAaccountData(token) - This will get information about the different domains that are attached to your Google Analytics account, including the ID which you'll need to get the analytics data.
• getGAdata(token,domain ID,metric,start date,end data,.....) - This is the main function that lets you extract almost all of the data from your analytics. There are a number of options that are worth exploring if you need to, but in it's basic form, you can grab data about any metric (visits, visitors, bounces, etc) in any date range.

The screenshot above shows an example of how to use these functions to get a single piece of data out of your analytics. With this framework in place, you can make a series of calls with slightly different parameters to extract and process whatever metrics you are interested in. Like I said, the three functions have a lot of different options, so be sure to look at the developer's site if you're trying things out. With these tools in place, you can generate customized reports to your heart's content. As a very simple example, I generated a plot of the growth in weekly traffic to LAS over the past couple of months, highlighting how many of the hits are from new visits vs existing users.

Things are never perfect
Sadly, while the scripts worked pretty well overall, I did run into a few headaches where some of the data queries would time out, or where I'd get a message saying I need to authenticate myself with a Captcha. For regular use, these types of problems could prove somewhat annoying, but it's hard to tell how common they'll be. According to the developer, the Excel scripts work a lot better, so I may end up trying them out. Either way, being able to generate customized reports like this directly from a spreadsheet tool is pretty great. As always, let me know in the comments if you have any questions or problems.