Data Visualization: Wielding Your Secret Weapons
In the first article in this series, Let’s Use Florence Nightingale’s Secret Weapons, we saw how nonprofit practitioners have two “secret” weapons: the data packed away in databases and spreadsheets and the visual superpowers of the organization’s staff and board members, funders, clients, participants, visitors . . . indeed any human being involved in the organization. Data visualization (aka data viz) capitalizes on our visual superpowers and puts our data to work.
Now that you know what your secret weapons are, how best to use them? In this installment I’ll help you get started with data viz. As you become more committed to visualizing your data, you will not only better understand what your organization is doing and how you could do it better, but also how to collect more useful data in the future.
1. Consider how to deploy data viz
Nonprofits can use data viz to advance program evaluation and planning, fundraising, or financial management.
As discussed in the first article in this series, many nonprofits have a strategic plan. The plan might be depicted in the form of a logic model (aka theory of change), which shows how inputs are supposed to lead to outputs. Data visualizations can help you to see the amount of inputs and outputs over time and which inputs appear to actually lead to which outputs. In other words, you can see if your program model, as planned, really works.
Visualization also can help you to understand which subgroups are driving positive changes and which groups are lagging.
For example, if your visualization shows that children aged 9-11 are doing particularly well in your tutoring program, you might want to understand why. If it’s because fourth and fifth grade teachers are sharing their curriculum with you and encouraging students to participate in tutoring, perhaps those teachers can serve as ambassadors for your program in presentations to other teachers.
Current and potential funders share our visual superpowers and thus appreciate visualized data to understand an organization’s effectiveness, efficiency, and distinctiveness. A simple bar chart showing how our organizations compares to others can go a long way in answering funders’ questions and motivating their support.
Similarly, you can use our own visual superpowers to easily grasp your organization’s financial situation: where our income and expenses are in relation to our goals overall and by subcategory. We also can see, at any point in time, how income relates to expenses.
2. Ask some initial questions
Think of your organization’s data as a mirror, a really powerful one that can show you not only how you look but how you are doing.
As discussed last week, spreadsheets are not engaging data mirrors. Most of us gloss over at the sight of them. That’s because we are wired to process images much more quickly and easily than the numbers and letters crammed into spreadsheets. The best data vizes, the ones that provided the clearest reflection in your data mirror, are those that help you answer your basic question: how are we doing?
To answer that, you need to compare your work to something else: some type of standard or goal, other organizations in your field, or your past performance. A simple line chart showing change over time on a given measure will help you to compare your current performance to the past. A reference line showing a goal or standard will help you to compare your actual performance to your aspirations. And, if you can get data from other organizations, you can plot their trends alongside yours.
You also should closely consider—and vary—what you mean by “we” and “doing.” “We” can be your participants, visitors, funders, etc. But you should also look at subgroups of these groups, for example those in certain age groups or those who have been in the program for different lengths of time. “Doing” can be a measure of any input (e.g., funding or other resources, training, etc.) or any interim outcome (e.g., attendance or survey scores) or long-term outcome (e.g., employment rates, college attendance, housing provided, etc.)
Answering the question “how are we doing?” from a number of different angles will give you a clear picture of your organization and will help you to focus on where to stay the course and where change is needed.
3. Look at your data and clean it
Many nonprofits have entry-level staff or multiple staff entering data into management information systems or spreadsheets. The result can be “dirty” data — data with a troubling level of inaccuracy because it has not been entered correctly or consistently. If, for example, Michael Smith is entered twice, once with a middle initial and once without, then tracking his progress through your program will be difficult. To make sure data is accurate and thus of any value at all, make sure you regularly clean it.
Two simple procedures for cleaning data include:
- Removing Duplicate Rows or Entries. Use the conditional formatting function in Excel to highlight duplicate entries in a spreadsheet or simply sort data and then scan rows to find duplicates. Excel and other types of spreadsheets (e.g., Google Sheets ) allow you to look at all of the unique values in a column using data filters. This is also a good way to find duplicate entries. If Gwendolyn Tvorefsky and Gwendolyn J. Tvoresfsky are both listed as unique values, you’ve probably found a duplicate record. Some databases, such as Salesforce, have checks that prevent duplicate records from being added to a database.
- Correcting Inconsistent Data Entry. Looking at unique values also helps to identify inconsistencies in data entry. If you find “St. Louis” and “Saint Louis” as unique values in a column for city, then any analysis or visualization will consider these as different cities. You also can use a spell checker to find values that are not used consistently, such as a program name. Finally, a data dictionary, which specifies how each data element is defined and how each should be entered into a database or spreadsheet, can go a long way to making your data more consistent.
4. Choose the right type of viz
When you think of visualizing data, your mind probably goes to bar graphs or maybe pie charts. However, there are many more species of visualizations. Ever heard of a waterfall chart or a circular area chart? Your first decision when visualizing data is what type of chart or graph to choose and that depends on what you want to show and what type of data you have.
I highly recommend Andrew Abela’s simple decision tree called Chart Suggestions—A Thought-Starter (there is a link to download it as a PDF in this blog post ). It’s based on Gene Zelazny's classic work, Say It with Charts: The Executive's Guide to Visual Communication. The decision tree starts with the basic question “What would you like to show?” and provides four options:
- Comparison. You have two or more groups of things or people and you want to see which group is largest or smallest (or somewhere in between) on some measure. You may also want to see how these groups compare on the measure overtime.
- Distribution. You have a bunch of data points (e.g. the ages of participants in a program or test scores of students in a class) and you want to know how spread out or bunched up they are. Are most of the ages, test scores (whatever) near the average? Or is there a wide range? Are there some extreme outliers?
- Composition. You want to understand who or what makes up a larger group such as how many of the participants in a program are in different age brackets or how many have been in the program for different lengths of time.
- Relationship. You want to know if one thing is related to another, either at one point in time or overtime. Do participants in a mental health program report less distress over time? Do those with lower incomes have higher heart rates?
Once you decide what you want to show, the decision tree helps you to choose a type of chart based on the type of data you have.
Abela’s chart chooser includes the types of charts you are most likely to select. But there are more rare species out there. To learn more about the wide array of ways to visualize data, check out the Data Visualisation Catalog.
5. Draft some initial vizes
With your questions and preferred chart types in mind, now draft some initial vizes.
There are plenty of software programs out there to help you visualize your data. Excel, which you may already have, is perhaps the simplest to use.
Other programs such as Tableau and Qlik Sense allow you to create interactive visuals and “drill down” into your data. If, for example, you see an overall downward trend in program participation, you might want to see if the trend holds for subgroups of participants such as those in certain age groups. Free versions of Tableau and Qlik Sense are available as long as you store your data and visuals on the companies’ servers (you can make your data and charts invisible to anyone without the URL).
Look for trends and patterns. Do lines on a line graph slope upward or downward? If they sometimes slope upward and other times slope downward, is there a pattern over time? Are there clusters of data points on a scatterplot? Are there also outliers, far away from any other data points? What are the high points or values? What are the low points or values?
7. Ask more questions
Ask yourself and your colleagues what might be causing the trends and patterns you see. Has anything changed at your organization that might have contributed to the trends, such as changes in staffing, program design, funding? Has anything changed outside of your organization that may be affecting trends, such as the weather, the economy, or laws and policies affecting your clientele?
The next step is to consider what additional data you might need to better address your initial questions and to understand possible contributors to the trends and patterns you see. If, for example, you feel that weather is affecting program participation, you can add the average temperature to your program participation line graph to see if participation rises and falls with average temperatures.
Now it’s time to turn your good data viz into a great one, one that staff, board, members, donors, and other can quickly comprehend. And that’s the subject of my upcoming installment. Stay tuned . . .