A while back I posted about how to automatically import Google Analytics data into Google Docs using a third party script. One of the coolest features of Google Spreadsheets is the motion chart which helps visualization of multi-dimensional data. As you might imagine, the two also make a great team, and in this post I discuss a Google Spreadsheet that you can use to make motion charts from your Google Analytics data.
Google Analytics in Google Docs
As a brief introduction, the previous post gave a rundown of how to use a Google Docs script written by Mikael Thuneberg of AutomateAnalytics.com to import Google Analytics data directly into a Google spreadsheet. Once there, you can use a series of calls of the script to set up your data for various analysis, including tailored charts and visualizations. The great thing about the whole setup is that it will all update automatically, just by reloading the spreadsheet.
One of my favorite Google Spreadsheet tools is the "motion chart" which provides some really nice visualization of multi-dimensional data. In particular, the charts are perfect for visualizing time-courses in which a single set of categories have a number of numeric values associated with them. For example, I first saw motion charts in the context of a TED talk about economic and health data (numeric values) for a number of different countries (the categories) over the past century or so (time). While this setup certainly isn't perfect for every multi-dimensional data set, it's pretty great for analytics data which has a natural time component, as well as a number of categories you might be interested in (e.g. browser, location, or source). If you've never seen or used a motion chart before, you should definitely check them out independent of any analytics ambition. Here's a decent place to get started, or you can add one to any Google spreadsheet through Insert->Chart->Charts->Trend. For now, I'll show you an example using Google Analytics data.
Putting it together
To get started with motion charts and your Google Analytics data, start with this Google spreadsheet that I created. When you follow that link, it should prompt you to make a copy (which will only be visible to you). If it doesn't do that automatically, just use File->Make a Copy... to create your own version. The spreadsheet has three sheets: login, data, and chart. The only thing you'll need to do to get started is to fill in your the email and password for your Google Analytics account on the "login" sheet.
After filling out those fields, the spreadsheet should automatically refresh to fill in the token and domain info. You'll next want to click on the "data" sheet. After doing so, you'll see that the sheet is propagated with data from your analytics account for each month of 2010. For now, it categorizes things by web browser, but if you poke around the sheet a bit, you should see how to customize that. Once the data is all imported (it'll take a few seconds), you can click on the chart tab. Now it's time to play with your data.
If you've never used a motion chart before, it can take a bit of getting used to, but once you get the hang of it, it's a great way to visualize data like this. The default chart shows data in 6 dimensions. Each category is it's own circle (one dimension, D1), and it's x and y placement show two numeric values (D2 and D3) which can be selected using the dropdown boxes (showing "visits" and "newVists" above). The color (D4) and size (D5) of each circle correspond to two more selectable values (pageviews and bounces in the above example), and finally the slider at the bottom lets you move through time (D6). You can also select and deselect categories either by clicking on them in the chart or using the checkboxes in the right-hand panel. The "Trails" checkbox toggles whether the trace through time should be shown, or only the current time.
In addition to that view, the three tabs at the top let you instead show a bar or linegraph with the same data.
Anyway, there's ton more to do and see, but it's best achieved by poking around. If you have any questions, feel free to let me know in the comments.
Sign up to receive updates in your inbox