Donor Four out of Five Years

Kate Becker · February 2020

I have been asked many times by my team to look at lists of donors who have contributed 4 of 5 years, or 5 of 7 years. Raiser's Edge says there is no easy way to do this, and its for the most part manual. Any ideas/suggestions?

Thanks!

JoAnn Strommen · February 2020

Here's an old thread on the topic. Yes, no easy way.
https://community.blackbaud.com/forums/viewtopic/158/31956?post_id=117552#p117552

and this thread was referenced:
https://community.blackbaud.com/forums/viewtopic/159/26060?post_id=92969

Options are a Crystal report or as referenced in the second link pulling summary fields for each year.

I'm thinking there was another old post that gave step by step directions but didn't pull in my search.

Crystal Bruce · February 2020

Discussion moved to Raiser's Edge Community. Thanks!

Tom Klimchak · February 2020

We do things like this pretty regularly, though we often want "8 years out of 10 years" or something more than just five... Yes, you could build something in Crystal Reports, but for a (relatively) quick and dirty report we have two options:

Option 1: Excel Export with ID, Name and Year1 Giving, Year2 Giving, Year3 Giving... Year10 Giving columns. At the end of those columns create one more column which uses the COUNTIF function. It looks like this: =COUNTIF(C2:K2,">0") That will simply look across and "count" any column that has any number greater than 0. Fill that formula down and you have everyone who gave x out of 10 years (or how many years you exported). You can copy or import those IDs back into RE and you have your list.

Option 2: This requires a plug-in, but if you find yourself doing this sort of thing a lot it might be worth it. It's an Omatic plugin called ScoreOMatic. It allows you to build "scores" like this and drop them into Attributes. We have one set up and we just manually run it every few months. All my donor records have an Attribute called "Years Given in Last Decade" which I can query on very easily...

Let me know if you need more details or want to see a sample Excel file... I can make an upload a quick example....

James Andrews · February 2020

This is pretty easily doable in Excel.

• Export all your gifts for the date range.

• Create a nonduplicated list of donors in a second sheet (use the remove duplicates function or power query).

• Put your years along the top, use SUMIFS>0 with the double unary to add up each person's giving for the years.

• Add across to get each person's total for the range.

I just made a sample set of data to show you what I mean:

2fd1bc8da5b020005753bcc9af9aa379-huge-ye

Kate Becker · February 2020

Tom Klimchak:

We do things like this pretty regularly, though we often want "8 years out of 10 years" or something more than just five... Yes, you could build something in Crystal Reports, but for a (relatively) quick and dirty report we have two options:

Option 1: Excel Export with ID, Name and Year1 Giving, Year2 Giving, Year3 Giving... Year10 Giving columns. At the end of those columns create one more column which uses the COUNTIF function. It looks like this: =COUNTIF(C2:K2,">0") That will simply look across and "count" any column that has any number greater than 0. Fill that formula down and you have everyone who gave x out of 10 years (or how many years you exported). You can copy or import those IDs back into RE and you have your list.

Option 2: This requires a plug-in, but if you find yourself doing this sort of thing a lot it might be worth it. It's an Omatic plugin called ScoreOMatic. It allows you to build "scores" like this and drop them into Attributes. We have one set up and we just manually run it every few months. All my donor records have an Attribute called "Years Given in Last Decade" which I can query on very easily...

Let me know if you need more details or want to see a sample Excel file... I can make an upload a quick example....

Hi Tom,

Thanks so much for your answers. We used to use ImportOMatic so I am somewhat familiar with omatic. Do you happen to know the cost for ScoreOMatic? If you would be able to share a sample excel file for option 1 that would be great.

Thanks!

Chris Nungesser · February 2020

I like what James and Tom have proposed. To piggyback on what they have suggested, I'll share yet another way. It's very similar to what they've suggested, but just a slightly different route. If you're comfortable with basic pivot tables functions in Excel, you might prefer to use this method.

I'd export the gift data into Excel. I'd pull your gifts from the last five years. At a minimum, the fields you will need are Constituent Name, Constituent ID, Gift Date, and then Gift ID and/or Gift Amount. (On the attached sheet you'll see that I also created a field that combined Name and Constituent ID. That was just to make the pivot table easier to read).

Then I pull that data into a pivot table. You'll see the pivot table I created on the second tab of the attached file. The reason I like the pivot table is that it makes it easy to lump constituents together. Putting the Constituent ID in the rows (or the Name/ID combo) will make it that you don't have to worry about duplicates. And then put Gift Date across the Columns and grouping by Year simplifies that step as well. In the Values section, I would Count the number of Gift IDs or Sum the Gift Amount.

I think the easiest next step is to copy and paste the pivot table into a new sheet/tab. Just mind the header row. Then I'd use the same formula that Tom recommended. From there you can filter the table so you can look at the constituents that have given four or five of the last five years.

Again, I think Tom and James both recommended great ideas. Just throwing this out there to give you another option.

Good luck.

Chris

James Andrews · February 2020

Ha, yeah, I started to go the pivot table route but tbh every time I start with pivot tables I end up getting frustrated with the formatting limitations and whatnot and I just end up writing formulas. BTW you can avoid the issue with the name & ID in pivot tables by using Tabular form in report layout.

Tom Klimchak · February 2020

Sure thing, Kate Becker‍! I've attached a sample Excel spreadsheet with the formula in it. I also put in a quick screenshot showing what our export sort of looks like. You can define what you want a year of giving to be (calendar year, fiscal year, hard credit, soft credit, committed, received, etc)... I like using this Export Constituent Totals method because while it's tedious to build the first time it takes up less time than exporting out 400,000 individual gift records each time you want to do this.

As for ScoreOMatic I think it was relatively inexpensive. We were early adopters so we may have received a bit of a discount. If you're a data nerd it's a lot of fun because you can do things like this: Count up the number of years given in a decade, add in a point for every Presidential Action, give two points if there was a recent gift, add in a point if lifetime giving was over $50k, maybe add two points if they played basketball... and then apply that formula to ALL your constituents to give them a "custom score".... We've created several different "scores" for various projects over the years. We've discovered a few "hidden gems" here and there using these scores.

Kate Becker · February 2020

Thank you everyone! All of your input and guidance is so helpful!

Tom Klimchak‍ Do you create the query then use export to get the information out of Raiser's Edge? We always export directly from query. What is the best practice here?

Chris Nungesser‍ I have not used pivot tables in excel before but I am willing to try. Is there a way would suggest reducing down your output list. If I wanted to pull someone who made a gift 7 out of the last 10 years, would I just pull a list of all gifts in the last 10 years?

Tom Klimchak · February 2020

Kate Becker‍ We might use a query to "find" all the donors, but we always, always kick out any sort of "giving totals" from Export because exporting directly from a query is not always accurate. I believe best practice is to always use Export for any sort of totals.

Don't get me wrong: there are definitely times when you have to export from a query but we have found that there can still be strange duplicates (even with suppress duplicates turned on) and other odd calculation errors.

Kate Becker · February 2020

Just wanted to say thank you to everyone who responded on here - what a great community. There were a lot of helpful tips and resources provided and I am very thankful.

Best,

Kate

Donor Four out of Five Years

Comments

Categories