Another take on Google’s Motion Charts

Late last year I posted a tutorial on creating Google motion charts with your data. These very cool charts work with your time-series data, stored in Google Docs, to create an animation with the power to convey a lot of information in an easily understandable form.

But what about private data? You may not want to rely on Google’s ability to password-protect your data, or the privacy provisions you work with may prohibit posting data to an outside server.

Here’s another way to take advantage of motion charts. I was put onto this by Trevor Skillen, President and CEO of Metasoft, in Vancouver BC, whose company is working on incorporating motion charts into their well-known FoundationSearch product.

This version uses stored code to manipulate your data locally, rather than pulling it from Google Docs.

The advantages are clear:

• Your data is stored locally and the code is executed locally, in the browser – nothing is sent to Google.
• You gain precise control over the appearance – you can hide options that the user doesn’t need to see.
• The example code provided by Google is fairly easy to modify without requiring programming or scripting skills.

Trevor directed me to Google’s ‘playground’ where one can get a quick feel for the technology without much tech effort.

There is a downside … there is a good deal of manual coding you’ll have to do if you want to put a chart together using your own data. This limits you to fairly simple charts – unless you’re capable of writing the additional code that will allow the chart to get data from a file or table.

Cool motion charts – Part 4

In my previous post in this tutorial, I described how to assemble the data to create your bubble chart. Now comes the relatively painless part: Pasting it into Google Docs and inserting a Google Gadget – the motion chart itself.

To review, the required columns in your spreadsheet should be in this order:

• A column to define the bubbles (in our case, this is Class Decade)
• A column to define the time series (Year, i.e. fiscal year of giving)
• At least two columns of numerical data for the x-axis and y-axis. (You can have more than two columns, to give you more options for charting, but you need at least two. I used Median Gift for the y-axis, and a choice of either Number of Donors or % Participation for the x-axis.)
• You may also have a column for Category, which just labels the circles in the legend (in our example, this is just a duplication of the data in the Class Decade column)

Assuming you already have a Google or Gmail account, navigate to Google Docs and click on ‘Create New’. Choose ‘Spreadsheet’ from the drop-down menu. Copy all the cells of your Excel spreadsheet, and paste them directly into the Google spreadsheet. Give the file a name, and Save.

(I’m going to assume that you have permission to post your institution’s data online. Keep in mind that you can block public access to the data, or limit it to select invitees who have to log in, or make it wide open and available to all. In any case, it would be best to seek approval.)

Select all of the cells in your sheet that contain your chart data, including the column headers. (Don’t select whole columns – click on cell A1, then hold shift down while clicking on the rightmost cell in the very last row of the sheet.)

In the spreadsheet menu, choose Insert. Click on Gadget.

A window of options will open. You might have to scroll down to find Motion Chart. Click the ‘Add to spreadsheet’ button.

The chart settings window will appear on top of your spreadsheet. (If you don’t see it, scroll up!)

The Range field will already be populated, because you had those cells selected before inserting the gadget. You can modify the range if need be.

Enter a title in the Title field. Ignore the other fields for now.

Click Apply and close.

The chart will take a second or two to appear. It won’t look right – we need to tweak it a bit.

It will also be rather small and hard to work with. To move it to its own sheet, clicking on the little down-arrow at the top left of the chart title bar, and select “Move to own sheet …” from the drop-down menu.

(For additional help at this stage, select Help from the More drop-down menu at top right.)

Now let’s choose the correct values for our x-axis and y-axis.

Click on the x-axis name, and choose the desired value from the options that pop up. (We’re using % Participation.)

(Ignore the Lin and Log menus for now. We’ll leave the scale as Linear, rather than Logarithmic.)

Now click on the y-axis name, and choose Median $.

Notice that the bubbles adjust their orientation accordingly.

Other items that you’ll want to tweak are below. All of these are able to be saved as the default state of your chart:

• Colour: This should be set to ‘Category‘
• Size: Set this to ‘Number alumni‘. For fun, you can also set this to ‘Number of donors’ – then the bubbles will change size over time!
• Playback speed: The little triangle to the right of the Play button. I usually set this on the slowest speed.
• Starting year: Push the slider all the way to the left.
• Labels and trails: You can also click on individual bubbles to label them, or display their trails as they move.

If you play around a bit, which I know you will, you’ll notice that it’s very easy to lose all your settings. And if you try to share your chart with someone else, it won’t display in their browser the way you want it to.

The method for saving your default chart state will be covered in Part 5.

Cool motion charts – Part 3

In Part 1 I introduced the concept of using Google Gadgets to visualize our data. In Part 2 I gave an example of an alumni giving history visualization.

Today I will offer some additional notes on how the Giving chart spreadsheet was assembled. As I said before, assembling the data was a lot more work than actually making the chart! It would be helpful for you to have query access to your institution’s database for this, but if you can get your IT people to extract the data, you should be able to prepare that data in Excel.

I started with an MS Access query of our database (Banner) which gave me a row for every single alumnus/na, living and deceased (about 43,000 records), donors and non-donors, with a column for Class Year, and a column for Total Giving for each fiscal year from 1989 to 2009.

(Each fiscal year total was in fact a sub-query – there’s definitely some work involved. Luckily I had all these sub-queries already made, from previous data projects. Gathering all this data by individual may not be the easiest way to go about this. If you can obtain data that is already aggregated in some way, such as by class year of donor, go for it.)

The length of your time series will be limited by the depth of historical giving data available in your database – ours happens to go back 20 years.

Let’s look at the final spreadsheet we need to create. Here are the first few rows of mine:

We need to collapse our tens of thousands of rows of data on individual alumni into something like what you see above. This is a lot easier to do with a statistics software package, but no doubt it can be accomplished in Excel as well.

I use Data Desk for stats. Paste all your individual giving data into a new data file. The first step is to create a derived variable to recode Class Year into decades (1930s, 1940s, etc.) These will be used to populate the first column of our spreadsheet.

The first column defines the ‘bubbles’. The last column (Category) gives the bubbles names. The two columns contain exactly the same data, but the gadget requires this label information to be contained separately.

The second essential column is the time-series data, in this case Year. This isn’t Class Year – this is the fiscal year in which gifts were made. Year is the variable reflected in the time slide-control at the bottom of the chart.

Creating the spreadsheet was a bit tedious from this point on. It involved a lot of cutting and pasting of the desired values from Data Desk into an Excel spreadsheet. You may be able to vastly improve on my methods.

The middle column, Number Alumni, is the count of individuals in each Class Decade. The relative size of each bubble is based on this number, from 719 in the 1930s to more than 10,000 in the 2000s. (I’ll show you how to define bubble size later.)

The other columns provide the data that will form the x-axis and y-axis of the chart.

In my chart, % Participation or Number donors can be on the x-axis – you can choose one or the other. (% Participation = Number donors divided by Number alumni.)

And finally, the data in the column Median $ is used for the y-axis.

As I said, I used Data Desk to help do the calculations and order the data properly before pasting it into Excel and getting it all arranged properly. Here is a Data Desk Summary by Groups table showing giving totals by Class Decade for the year 1995:

This provided me my donor counts as well as my median giving data. Of course I had to do the same thing over and over for each fiscal year, 1989 to 2009.

Give it a shot. Use the tools you’ve got to massage your data until it’s in the form of the spreadsheet I showed above. This really is the time-consuming phase of your project. Once you’re done, the rest is surprisingly easy. I’ll get to that in Part 4.

Cool motion charts – Part 2

In my previous post, Cool motion charts – Part 1, I talked about a Google Gadget which allows you to make a motion chart based on a spreadsheet of data you create in Google Docs. Now I will show you a live example I created. In Part 3, I will go into more detail about how you can create your own.

WordPress does not allow embedding of scripts, and Google Gadgets are scripts, so you’ll have to click on the link below to view the motion chart. The spreadsheet will open in a separate tab or window. Click on the “Motion Chart” tab at the lower left, then wait a moment for the chart to build.

(See note at bottom of this post for some caveats related to this data.)

Motion chart, "20 years of alumni giving". (Click to access.)

When you click the Play button in the lower left-hand corner, the animation runs from fiscal years 1989 through 2009. At any time you may click and drag on the time scale to control the year being displayed. Animation speed control is to the right of the Play button.

Each circle represents a class decade (1930s to 2000s). The size of the circle corresponds to the number of alumni, living and deceased. Click on any circle to highlight that decade. (Click on ‘Trails’ to record how it moved over time.)

The vertical axis is Median Gift for that fiscal year. I used median instead of mean (average) to avoid huge swings due to the influence of large outlier gifts.

The horizontal axis is Percentage Participation for that fiscal year. I just noticed that the gadget has converted all my percentages to decimals, as in 20% = 0.20. (If you click on % Participation, you have the option of changing the view to Number of Donors. Those are the only two views that make any sense.)

It took a lot longer to prepare the data for the spreadsheet than it did to produce the chart. Click on: Part 3 (data preparation) to continue!

[Note on data: Actual donor participation figures we have published are higher than this chart suggests, as multi-alumni households (which are common for our institution) are counted here as a single donor when gifts are not split. As well, giving figures include both annual and campaign gifts, but exclude student giving (via union fees), estate giving, and all other non-alumni giving.]

Cool motion charts – Part 1

I’ve started using some cool things for visualizing data which don’t involve buying and learning new software.

Google Docs has some new features called “gadgets” which allow you to do some interesting things. One of these is a Maps gadget which works with your Docs spreadsheet to whip up maps with pushpins of the addresses you post in your spreadsheet. I’ve been using it to plot addresses for our Planned Giving Officer for trip planning. It could function as an alternative to MapPoint. Bonus: it works great with Canadian addresses, as long as they’re true street addresses.

I’ll post on that another day, because way higher on the fun-o-meter is the Motion Chart gadget, which again works with a Google Docs spreadsheet to create an animated chart of time-series data.

I recently created a motion chart for alumni giving to our university for the past twenty years. It’s a bit hard to describe, but basically when you hit ‘play’ the animation begins running from 1989 to the present, and coloured bubbles representing each class decade from the 1930s to the 2000s start wandering around like planets, according to two values: median giving for the fiscal year (on the vertical axis) and percentage donors for the fiscal year (on the horizontal axis).

Screen shot of motion chart for "20 years of alumni giving", stopped at 1995. (Click for larger version.)

I’ve played it back many times – it’s an interesting way to view a ton of data all at once. In fact, because the underlying spreadsheet has 6 columns and 168 rows of data, the animation allows you to visually absorb the information contained in 1,008 cells! Behavioural differences among class decades become visible, as well as the general effects of campaigns and general economic conditions (when they affect giving).

I offer more detailed instructions in Part Two.

Also have a look at Google’s directions and give it a try.

Thanks to Audrey Geoffroy, Data Analyst at University of Florida Foundation for mentioning to me that this gadget resulted from Google’s acquisition of Gapminder’s Trendanalyzer software, which was made famous in Hans Rosling’s TED talk (view below). Very entertaining!