There are a number of paid services (Wealth Engine, for example) you can employ to help you sift through your donor data. But with a few simple Excel formulas, a pivot table or two, and utilizing basic Google searches, you can get a head start with just an Excel savvy employee (or volunteer) and some dedicated computer time.
Export Your Data to Excel
The first step is to export your data into a spreadsheet application, like Microsoft Excel, so that you can manipulate it. While many more sophisticated donor databases allow you to run reports or exports, few databases allow you to create your own custom queries and fewer individuals tend to know how to do this within the scope of databases that want you to use their prefigured reports.
Length of Giving
A typical planned giving prospect is someone that has made many, small gifts over several decades. Build a search that looks at donors whose first gift was at least 30 years ago, presuming your organization is that old. From there, use the count formula in Excel to total the number of gifts in this grouping per individual. Reverse sort, and start cultivating the donors who have given the most number of gifts over this time. You’re likely to find many of your board members and other close friends and supporters in this grouping, but there’s a good chance there may be some others in the wings that you’ve been missing.
Often times we overlook donors that don’t make large, ‘major’ gifts and miss the small subset of individuals who make multiple gifts in a fiscal year that total a ‘major’ gift. Run a query of the past five fiscal years, then sort by donor ID or the other unique identifier in your database. If you don’t have this, sorting by last name, then first name should get you closer – though you may need to manually cross check for duplicates for common names (John Smith, for example). Then use the subtotal feature in Excel to let it do the math for you. You might be surprised to see there’s a small group of donors that actually give at a major gift level but don’t make individual gifts of that size.
LYBUNT and SYBUNT
Last Year, But Unfortunately Not This (LYBUNT) and Some Years, But Unfortunately Not This (SYBUNT) reports are your best friend as a manager. They help you see where your development team, whether volunteer or employee driven, is lacking. It helps you identify those people that are falling through the cracks. For LYBUNT, simply run a query of all donors for the last fiscal year and a second query for the donors in this fiscal year and use Excel’s conditional formatting to de-duplicate the list. For SYBUNT, run the same query for the past three years, and a second query of this fiscal year, and again use the conditional formatting to de-dup. The remaining are the donors that have fallen through the cracks, decided they no longer want to give to your organization, or perhaps haven’t been asked.
Sign up here to receive the highlights from the Starfish Impact blog in your inbox.