The holidays are a great time to demonstrate our gratitude to the donors who support our organizations’ missions. Here’s an easy project you can use to identify a short list of your best donors so you can send them a little extra bit of holiday cheer, like a special holiday card signed by your program participants or your board.
Or, you might use this method to identify donors you want to get in touch with to share your organization’s plans for 2018. Not coincidentally, this project will help you uncover some of your best major giving prospects.
We’re going to look for our recent donors who are the most loyal, and who’ve given the most. This is no accident: retailers use Recency, Frequency and Monetary (RFM) scoring to identify their best customers: those who have shopped recently, often, and spent the most.
Doing an RFM project is a fun and relatively easy way to segment your donor pool. But you probably don’t have time for that right now, because you’ve got a million other year-end projects to do. So, we’re going to apply the principles of RFM scoring without doing all the work. That way, your list will include not only your biggest donors from this year, but also those who have been the most loyal and who have given you the most over their lifetimes.
Pull your list
First, use your fundraising database’s reporting tool to pull a list of everyone who has given in either this fiscal year or last fiscal year. Include the following fields in your outputs: donor name, this year’s cumulative giving, last year’s cumulative giving, the year before that, etc., for a total of five years of giving history, each in its own column in your spreadsheet. Also include cumulative lifetime giving.
If your project involves mailing, go ahead and include all the outputs you need for your mail merge, like mailing label name, salutation, and address fields. That way, you’ll have your mailing list ready to go after you are done with your prospect identification.
Open your list in Excel, and add an extra column to the end of your list called “Holiday”. As you identify donors, add an “X” in this column for each donor to be included. Then, use these methods to identify which donors should get the “X” in the holiday column.
Top monetary donors: this year, last year, all time
Think about what a “top” donor means to your organization. Is it people who have given more than $100, $1,000, $10,000 or $100,000 in a single year? What cumulative giving amount would signify a “top” donor for you?
If you don’t know the answer to these questions, that’s okay. For each category, identify the top 3% – 10% of donors based on your criteria. The percentage you select will depend on your donor pool’s size and your time/budget for this project.
Start by sorting your list in descending order (biggest amount first) by this year’s giving total. Flag the top donors based on amount by putting an X in the holiday column.
To make sure you don’t miss any recent large donors, sort your list again in descending order, this time by last year’s total giving. Flag any top donors who you did not already identify in your first sort.
Finally, sort your list by cumulative lifetime giving. Flag any top donors who are not already flagged. These donors may not be your largest donors this year or last, but they are still some of your top supporters of all time.
Large gifts are wonderful. However, don’t forget about your loyal donors: the people who give to you every single year.
Identify anyone who has given in five out of the last five years. If you are only midway through your fiscal year, you may decide to look at giving in the five complete fiscal years prior to this one.
Identifying these loyal donors is fairly easy to do using an Excel function called COUNTIF. COUNTIF counts the number of cells that meet a certain condition.
Add a column to your spreadsheet called “Density”, which is a fancy way of saying, “In what percentage of years did this person give?” Then add your COUNTIF formula to this column.
In this example spreadsheet, we have five years of giving, from 2009 to 2013. Column V contains the Density formula, which in this example would be:
This formula will look in the columns that contain your giving information (H – L in this example; adjust this to fit your spreadsheet), and will count any that are greater than $0. So, if someone has given in five out of five years (that is, they have five gift history cells with a value greater than zero), then they will score a 5.
Anyone who has a 5 is a loyal donor who is a great candidate for your special holiday efforts or other outreach you may plan to top donors.