New Years Eve is to (most) nonprofit fundraisers what election night is to political campaigners. A nail biter. A victory party. Utterly exhausting. D) all of the above! But unlike electoral campaigns (where there’s a winner and a loser), fundraising campaigns can all win! There are more than enough resources to go around (unless you’re a cultural org in NYC running a capital campaign). Our scrappy social movement orgs can make it happen! And year after year… we do!
Think about what happens after election season… tabloids, think pieces, and plenty of armchair analysis. Everybody and their cousin has an opinion about why the election turned out the way it did. If we treated our donor data with an ounce of that curiosity, I think we’d learn a TON about our donor base, our fundraising strategies, and our movements!
It’s 2019… do you know who your donors are?
You have new data about your donors… and a LOT of it. But how do you take a list of hundreds or even thousands of donations and turn them into helpful statistics and meaningful insights?
Quick and dirty
When I receive a bunch of fundraising data (and lots of TDAA readers DO send me their fundraising data when they need help — keep it comin!) there’s a few quick calculations that I *always* do to get me started.
This “to-do” list is designed to think about the full extent of the fundraising data, and less to do with data by month, by campaign source, or other types of segments and metrics.
- Average donation – I use the Average function in Excel or Google Sheets (I might divide this into this year versus last year)
- Average donation per person/household (it’s helpful to see all of those monthly gifts combined!) – There are a couple of ways to do this (topic for another blog post, perhaps!), but my go-to is to use a pivot table for this calculation
- Same as above, but remove the top ~10% of donations & grants/bequests (to see the grassroots giving ranges), or any amount over a $X amount (range depends on the scale of the organization)
- Number of donors – the quickest way to do this is to copy the donor email addresses or unique IDs to a new tab and de-dupe (to remove repeat donors) and then see how many there are!
- Number of donations – in your donations spreadsheet, see how many rows there are!
TYBNLY / LYBNTY
What’s the difference between December 31 and January 1?
On 12/31, you’re looking at a list of people who donated Last Year But Not This Year. This list is key for retaining donors and meeting our budget goals!
On 1/1, you’ll want to look at a list of people who donated This Year But Not Last Year. Why? You want to see how many people are new or returning donors, and figure out what made you “appealing” to them #PunIntended.
VLOOKUPs are an amazingly efficient way to compare two lists of people (People Who Donated in 2018) and (People Who Donated in 2017). You can use VLOOKUPs to find out:
- Who donated in 2017 AND 2018?
- Who donated in just 2017, but not 2018? Why? And how much money does that represent?
- Who donated in 2018, but not 2017? Are they new donors or returning donors? What made them give? And how much money does that represent?
This is such a juicy subject that I might want to create a post dedicated to JUST this tutorial!
Increase or decrease
The last thing I’ll do is create a Household Donations pivot table. In the columns, I want to summarize the last couple of years (maybe 2016, 2017, 2018). In the rows, I want to summarize all of the donations combined for each person or household. This will, incidentally, reveal new and lapsed donors! But more importantly (for this exercise, anyway), it will show us the net change in donation amount. Usually, I’ll create a pivot table like this and then I’ll copy the data, once it’s been re-arranged and summarized, into a new tab (outside of the pivot table format) so that I can do some math and analysis, like:
- Percent change (did the donor give more or less than the previous year)
- Net change (whats the difference, plus or minus, between this year’s giving amount and last year’s giving amount)
These calculations can help faithful fundraisers (like you?) better understand their donors, make more accurate projections for next year’s budget, and offer insights into the relationship that the donor has with the organization. Really, it’s a win/win! And it only takes one click and a few drags to make a Pivot Table where allllll is revealed!
The gift that keeps on giving
Running some data analytics (if that word makes your heart beat increase, take a deep breath, you can TOTALLY do this!) can be informative, strategic, and even FUN!
One time, I was working with a client who’s eyes grew wide and sparkly as she burst out, “NO WAY!!!!!” when she made her first pivot table.
Getting a handle on your donor statistics can even help you bring in more donations next year! It’s not a silver bullet, by any means. Your organization’s social justice work speaks for itself, and your values and integrity are what endear your donors to your mission. But good data practices behind the scenes are helpful. Trust me on this one. When have I ever led you astray?
Happy New Year, TDAA fam. I’m excited for a spreadsheetful year of growth, learning, humility, and curiosity… and fighting like hell for a better world. xo