Friday, August 3, 2012

data viz challenge

A participant from a past workshop recently reached out to me hoping for help visualizing some data she wants to communicate about affiliate community foundations to members of those organizations and her board. Rather than keep all of the fun for myself, I thought I would put this challenge out to you.

Here's the description of the problem faced:

Basically, we serve as a host or parent organization for several smaller, local foundations throughout our service area. What we would like to show is how the money-in (gifts) compare to the total assets and money-out (grants) in a given year, and also allow the audience to compare their foundation to the other affiliates.

The data is contained in several separate tables (attached), and includes not only the values in $ but also the number of gifts, funds and grants. This information is not critical to the story, but if there's a way to include it (possibly by merging a table and graph, as I saw on your site) there are some specific cases where having this information will be useful. I can see where it will be hard to include it though, without being cluttered.

I'm open to whatever suggestions you have. I attached a pencil drawing of what I was picturing, but I'm not dead set on it being a certain way. I thought the format would be good because it allows you to visualize the relative size of the incoming to the outgoing for a given year, but also see how it changes over time, and how it compares to the other organizations, fairly easily. If there's a way to include the data labels in the bars or something, that would be good.

One major obstacle may be that the values vary so much (from something like $28 to almost $5 million). I don't know if this will make it impossible to do visually. It will be fine to drop the cents off the values.

[8/7 update: see comment below for additional context.]

Here's the sketch provided to start to get the creative juices flowing:

You can access the full data here.

Your challenge is this: come up with a straightforward way to visualize this data that will allow for the main desired comparisons outlined above by Wednesday, August 15th. When complete, you can leave a comment with a link to your visual, or email it to me directly ( along with any comments you'd like me to post with it, and I'll put it into Dropbox and create a comment for you with the link.

What will you win? A chance to help a philanthropic organization better communicate with data. Eternal notoriety. Oh, and I'll invite the creator of my favorite to write a guest blog post. Ready... set... GO!


  1. I'm not clear on what story she wants to tell, and therefore whether she needs ALL her data...or additional data.

    I think all too often people create a chart or table and then write their story. I prefer to look at my data, see what story jumps out at me, and then highlight the data.

    Given the differences in the values, I'm thinking averages might be the way to go to drive comparisons. If you want to make better apples-to-apples comparisons, you can now focus on your relative position.

    The problem is I don't know what's "good" or optimal. Do I want a 50-50 split between grants and gifts? 67%-33%? Do I prefer a couple of huge gifts or grants, or contributions from a broad spectrum of donors or foundations? Am I moving in the right direction?

    Going with averages gives you the ability to create a more level playing field for comparisons and tighten your data fields. You might want to add the number of gifts or grants in parentheses OR segment the results by number of gifts or grants (i.e., group the foundations with 1-10, 11-20, 20-50, 51-100 or whatever combination works for you).

    You could also create a five-year trend chart with these percentages. If you make it the % of your assets that come from Gifts over time, you don't need to show the same numbers for Grants.

    The other thought is that maybe you DON'T need a graphic element for your presentation. Perhaps you just want to create a slide with one or two numbers on it and then include your data in an Appendix or separate handout.

    Cole: I'll e-mail you the spreadsheet but did want to share the concept with everyone.

    1. Hi Peter, Thanks for your thoughts and I agree with your points on forming the story first and using that to determine what data to show as well as how helpful it would be to understand what good or optimal is in the situation. I'll post a follow up comment if I get more insight on these things. I've included the link to the spreadsheet you forwarded me with the additional metrics calculated:

  2. Here are some comments and an interactive data viz from @jschwabish:

    A few notes about this viz:
    1. It was a little unclear to me exactly whether they wanted a viz for public consumption or something they could use as a data discovery tool. Hopefully, what I've created sort of blends the two.
    2. I decided to go with an interactive Excel visualization because so many people are familiar with Excel and my guess is that this firm is less familiar with other forms of visualization (e.g., d3, Tableau), but are probably familiar with Excel; thus, I'm guessing that can use this viz in their everyday work to assess different affiliations successes (and failures). (Yes, this could easily be made in Tableau and probably be more involved with d3, but it sounded like this firm would benefit the most from having something they could modify in the future fairly easily, and hence the Excel approach.)

  3. Here is some additional context from the philanthropic organization:

    After chatting with a few colleagues, we feel the “story” in this case is two-fold: Congratulations and Competition.

    Congratulations: for the growth over time that has taken place among all of our affiliates. The general trend has been positive when you look at the growth in all three categories (Gifts, Assets, Grants). It is not just one piece of this (e.g. assets) that is important but all three pieces as a whole.

    Competition: to allow the affiliates whose “bars” aren’t as big to see that other affiliates are having success, and to hopefully encourage them to figure out what they are doing differently.

    Again, I am not bent on the (highly artistic, ha) sketch I provided being the final product, but it is the best way I can think of to accomplish what we are trying to accomplish. A colleague suggested having one set of bars that only pull the most recent year from each affiliate in to one visual for comparison (that could perhaps be the one up on the screen during a presentation), and then another set that includes the year to year comparison for each affiliate separately (for a handout).

    The reasons for this are a board member from Affiliate A can then see what their affiliate has done historically (e.g. Are they roughly in balance gifts to grants? Are they growing all three pieces over time?) but also how this compares to their “competition” (who they really don’t compete with geographically, but a little healthy rivalry among our network could be a good thing.) This could help them to see who is trending in the right direction, and hopefully spark conversation how they’re doing it.

    It’s hard to state the story more specifically than that. It’s going to vary a little bit from one affiliate to the next, because each affiliate’s story is a little different. Some have a large, lump sum gift every year that comes from the state which they get to regrant, so their gift to grant ratio will be roughly in balance. However, if they are content to keep the status quo and not actively working to develop new gifts, their total assets will be stagnant (although it may be bigger than another affiliate who is working hard to grow but doesn’t have the benefit of the state dollars).

    In other cases, certain affiliates received emergency flood relief dollars which were regranted out, so their total assets may be smaller the following year when that money is gone.

    Seeing how the three pieces compare may also be revealing as to how the market is performing. For example, if an affiliate’s gift to grant is pretty equal but their total assets is growing, that would be indicative of good market performance (the opposite could also be true if the total is shrinking, and would help show why it is important to consistently work for new gifts).

  4. Hi Cole, working on an Excel dashboard. I'll finetune few things and send it soon.

    I felt like the challenge isn't too complicated... but later I realized it is not that easy :-) I hope my dashboard solution can help that organization.

    1. Lubos' Excel dashboard can be accessed via the link below. Note that it was created in Excel 2003.

  5. Blog follower Gautham created the interactive Tableau dashboard linked below (you can download Tableau Reader to view it:

  6. Any chance of releasing the raw data for this? Working with aggregates is not as much fun. :)

    1. No, I don't have the raw data here. While I agree there may be some interesting things there, it's the aggregate data they are interested in communicating. Visualizing it can be fun, too!

  7. An entry from Rupert via his blog post here:

  8. Here are two visualizations and some comments from Jeff Shaffer:

    I typically would go with line charts for time series data, but in my concept chart here I followed more along the lines of your original sketch. While I think it works ok, in this particular case my preference for this data by year would probably be an area chart. Also, I read a follow up comment that said the foundation was not only interested in the growth of assets, but also the growth of gifts and grants. For this I would use a line chart. One problem you encounter is that the scales of those 3 are different, especially Assets. In that case I would recommend a small multiple and break them into three. The y-axis would then have the appropriate scale while the x-axis could be combined for a clean presentation.

    Tableau Version (see prior comment to download Tableau Reader):

    Excel Version: