Have you presented what you consider exciting trend data only to have eyes glaze over and attention drift? Most of us have. While financial folks appreciate the income detail offered by the fine print, many can’t absorb what you’re trying to communicate. Showing trends and results in a summarized, graphic form accompanied by narrative comments can help earn the attention your reports deserve as well as help you communicate strategically by driving focus where you want it.
Sarah: Good afternoon, everyone. My name is Sarah Tron with Allegiance and I will be hosting today’s webinar. Today’s proven practice webinar, Creating Insightful, Easy-to-Understand Reports is presented by Virginia Dambach. Virginia works with public radio and television stations around the country and local and regional nonprofit organizations in the areas of development assessment, grant writing, direct marketing and fundraising planning, implementation, and analysis.
Prior to founding her company, Dambach & more, she served as the director of development for Prairie Public Broadcasting, the statewide network here in North Dakota, and Nevada Public Radio in Las Vegas. Welcome Virginia.
Virginia: Thanks a lot, Sarah. Well, we’re going to get into it, doing insightful, easy to understand reports and the first thing you have to decide is what’s your point? What are you trying to communicate? Are you looking at income trends? Are you trying to just chart your campaign success? Are you doing year to year comparisons or are you trying to answer questions either that someone has asked you or something in your database?
Why is this happening? And you’re starting today again, you want to see why, and then once you have the numbers, then you need to see, because most people, when you show them a sheet of numbers, which I’m enamored of, you may be enamored of, but lots of people who look at a sheet of numbers, their eyes glaze over and they are lost in the ozone.
So one of the reasons you want to do. some charts and graphs is to communicate the data without putting people to sleep.
And the first steps, of course any time you’re going to do this is to review the data, find out what you want to communicate. And play with the various chart forms to see which one does the job better, because sometimes they can tell the story really well, sometimes you may need them to obscure the facts, but you prepare the graph and a narrative context.
So the first thing we’re going to look at is data and charts. And this is data that you need to prove a point. And so, there’s just several scenarios we’ll go through today. This would be: you would like to get wealth engine or another service to help identify major donor prospects that are hiding in your database and to date you haven’t been able to get it in the budget because the boss says, or your colleague says, well, we already know all the wealthy people in the community.
We know who’s rich and who’s not. You’ve picked out those eight people, but you need the data to provide evidence to the contrary. And this is a case, and there’s lots of cases where you may have to use data from outside sources. You heard at a conference that at other stations, they have significant wealth hidden in their own databases.
So you secure the data, What’s the people’s net worth? What percentage of the donors are millionaires? What do you want to communicate?
So there’s two different charts that show you. So you experiment with the charts. Sarah, can we do a brief survey? I just want to find out how many people attending use Excel charting features and feel comfortable doing that.
Sarah: Okay. It’ll have to be an informal survey. So if you could just let me know in the questions box, ask the question again.
Virginia: How many people use the Excel charting tools to chart out data like this?
Sarah: One person said yep, and a few more yeses are coming in, and then a couple of people said that we use it a little bit, but they could be a little bit more comfortable with it.
Virginia: Okay, we’re just going to look at a bunch of stuff and we’ll talk about it, but at the very end for those of you who don’t use Excel a lot, or don’t use charting features. I’ll do a little brief Excel tutorial. But the experience people don’t have to stay for that if you don’t want to. So here’s two different ways to look at this chart, to look at the data and you’ve got a bar chart, or you have a pie chart. And personally, here’s what I liked.
I think the colors are more dynamic and I’ve also made a case here looking at this data. This is what I want to show my boss. We have 10,000 donors. Now we’ve identified 200 major donor prospects in the database that aren’t yet major donors. Other stations like ours have found 15% of the file to be million-dollar plus prospects.
So if we do this screening, we could identify 1500 major donor prospects. And I feel I’ve made my case. If we could convert a hundred of those at the average gift, how much income would that be? Again, just to take that data further and prove that you can make more money than you’re spending with this kind of investment. So there’s one way, using outside data to make your point, whatever that point is. Using data for comparisons, annual donors versus sustainer donor performance. You may need to put more resources into sustainers. You may just want to show what has happened in your data in the last few years between annual donors and sustainer donors. So here we’ve got some performance data. The original gift amount, $13. That’s not significant, but that’s a nice little bit. Last renewal amount. Ooh, that one’s really big. Last upgrade. That’s pretty big too. Current giving level. The only place where sustainers and annual donors are almost equal is last add gift amount. And in that one, annual donors give a little bit more, but $5 more as an add gift, it’s $115 more at their current giving level, so I give up the $5 more in add gift.
On the right, you can see the comparison with the table, but on the left you see the charts. And so pick the way that’s most compelling to tell your story.
When it comes to data and charts, a lot of data can answer questions and it depends on what kind of questions you’re asking yourself or what kind of questions are being asked by your boss or the CEO, or one of the members of the board of directors. So we’re going to look at who responds to telemarketing because there are lots of questions. The people who respond to telemarketing are the people that called in on pledge drive. That’s all just phone responsive people call in by phone, but nothing else. But if you do a report on telemarketing responses by original gift method, what you find out is on-air pledge people are really quite a small percentage. Mail is the largest. Web pledges are close to on-air, and a nice percentage of people who got a telemarketing call last year, respond to a telemarketing call this year. But look at all those mail people. This chart will just demonstrate how all kinds of donors acquired by all kinds of methods, respond to telemarketing and that it’s generating significant revenue.
Here’s another one. Who should get add gift appeals? I myself used to think it was really awful that people who had already given us an add gift, we would ask them again. Or that people who had just renewed, two months later they would get an add gift appeal. That’s greedy.
And actually, that’s good fundraising practice because if you can solicit an add gift and get that second gift within 90 days of the joining date, you are building loyalty, you’re building affinity. And so look at the chart and see if you think about months remaining and membership, the pink is 12 months remaining.
That means that 14% of the people who responded to your add gift appeal actually just joined or just renewed their membership within the last 30 days; 13% joined or renewed in the last 60 days; 10% in the last 90 days. So a good number, not quite 50%, a huge percentage of that pie is people who just joined. The only people to suppress would be the people who are going into the renewal cycle simply because you want your renewals to be an upgrade from their last renewal. And typically the only time you get a higher level in add gifts could be from major donors. But from the rest of the population, your add gift is going to be lower than your renewal amount. So those are the people you would suppress, but looking at suppressing people who had just given, you’d lose a lot of money, if you did that.
And how do you know when to send another renewal notice? How do you know how many to send? How do you know if it’s even worth sending more than one or two? This chart actually illustrates that. If you look at the renewal response by week, how much money how many renewals came in, you can do it either way, during these weeks.
And the interesting thing about this chart is you can see, the first renewal, the green, went out and it came back. But when you send out the second renewal, which is the black, all of a sudden people are pulling that first renewal notice out of their desk and they’re responding. The same thing happens with the third notice.
There is a bump in the first two notices when the third notice goes out, and the fourth notice. And it would go on down the line through 5, 6, 7, and the how you decide that is by looking at your responses to recent expired member mail. And if you’re getting more response from your renewal, if you’re getting still 6% from your renewal mailing, then adding another renewal mailing, and testing that to see when does your renewal response fall below your expired member mailings and emails? And when that happens that’s enough, but until then you can add more.
Oh, and here we’re to the tutorial. So we’re going to take a look at this bit of data. This is the Excel spreadsheet, and it’s just looking at method of gift. What’s the response rate by on air mail, telemarketing call or web pledge. What’s the percentage of response for each of those?
And that’s the numbers. So you have to be on the home tab first, and then you have to click on insert, and then you’ll see a bunch of recommended chart types. And you’ll see vertical and horizontal bar charts, line charts, pie charts.
So here’s the same data shown in a 3D bar chart. Because you can have 2D charts or in a 3d clustered column. I prefer the clustered column because I like different colors to show the different things. And it also works really well this way. When you’re showing things over time, if you’re showing things over a three-year period, all of your on-air for the three years will show in green.
All of your mail for the three years would show in black. You get it. there are other styles annual versus sustaining donor, a stacked chart or a line chart. I think the line charts are really interesting when you have a lot of data. This really isn’t very much data to show. If you did something showing your income by month, over a 12-month period.
That one makes a very interesting line chart because it’ll go up and down based on when your pledge drives are very interesting to look at. And if you keep your pledge drives at the same time each year, your peaks and valleys will reflect each other. If you move your pledge drives around, it’s more sporadic. If you do renewals every month at the same time, your line charts will line up. If you miss mailings and don’t do things, if you skip renewal notices during pledge drives, your income is all over the map. It’s much harder to predict.
And the thing I like to do when I’m doing this, when I’m getting ready, even for a webinar, much less a report for my boss is to look at the same data, using the different charts and see which one tells the story the best.
And you can do that. You select your data and outline it and you pick a chart and it’ll create it. You can select your data and outline it and pick another chart and create it all on the same spreadsheet.
So you can do, three or four, you can find out, which I often do, that your table is in the wrong order. You need to change something. And you can discover that by playing with these and practicing with them. Play with the charts. Show the same data in different styles and see which tells the story. You can experiment with the colors. You can choose a different color palette. When you click on the bars, you can just change the color, just like you can text color when you’re in Word, and then choose the chart that tells the story.
I know this is pretty basic, but there you go. I think it’s easier to work with real data, rather than pretend data, because when you’re looking at your own data, you know what you’re looking at and what’s meaningful and what is not meaningful.
Sarah: So they could just run a series of very simple analysis reports to practice charting in Excel. The number of new members and the acquisition types.
Virginia: Acquisition types or renewals by month to see what months are high performing. And that’s where you get that up and down chart. But you could do every gift type, renewals, add gift, pledge, or you could do total income by month. You can break it down in many ways. Just what do you need to show?
Sarah: Any questions on what to pull into reports, how much to include and then how to get it into Excel or PowerPoint.
Virginia: I’ll relate one anecdote. And this is, I think the deepest we ever went into analysis because our renewals were pretty flat. at probably 63%, which is low, I thought. And I was just sure that it was because we were so aggressive in asking for add gifts. And so I asked for the data. What’s the renewal rate for people who give add gifts?
And we got that. And shockingly enough, the renewal rate for people who give, add gifts is much higher than the renewal rate for people that don’t. I was going well. Okay, but why is the renewal rate flat? So then I said, well, let’s go look and see what’s the renewal rate by method of gift. We looked at that and there wasn’t much different. There’s differences, but it was subtle.
Finally we did a report on method of gift, and giving an add gift, and years of membership and found out that overall we had an 85% renewal rate, but that the people who gave during pledge drives, who did not give an add gift, had an 18% renewal rate. And that was making all, I mean that was just depressing, all of our renewal results. And I think I had been rewriting the renewal letters for a couple years at that point, because I thought it was the letters, but no, it was the segment.
So then we were able to deal with, well, how can we get this segment of people more on board, have more affinity, more reliable. That’s an example of just every time you do a data run and ask a question and put together a chart, you have another question.
Sarah: And when you put it in, I know that you had an example earlier on a slide where it means something to just put the numbers into an Excel and say, well, look at this, but then when you chart it, and it becomes so visual.
So if you’re trying to convince the powers that be that you need to extend the pledge drive by a day or that you need to not cut out an add gift mailing out of your cycle of mailings in a year. Give it that punch of the visual, not just the numbers, but give it that visual punch by using these charts in Excel to really drive your point home.
Virginia: Yep. Because every decision you make in fundraising has an impact, meaning yes, you’ll save X amount of dollars by cutting an add gift, but look how much you’ll lose.
Sarah: So put that in the chart and present it that way. If you need to reach Virginia, there’s her email in the lower right corner of the screen. If you have any questions directly for her. If you need to reach us at Allegiance, we have all kinds of ways you can reach us, and those are just opposite of Virginia’s email address there.
Thanks so much for joining us. Have a fabulous afternoon.