Finding donors that have given X out of Y years

Joshua Bekerman bCLO bCRE · January 2016

After years of reading many forum questions about how to create a query, export or report to find constituents that have given X out of Y years I've finally started developing a custom Crystal Reports report that will help do this. The report will calculate how many years each constituent has given ("X") within a certain time frame ("Y"). There would still be some additional steps in Excel and RE but the end result would be a query that includes constituents that have given X out of Y years (using "ConsID is one of" criteria). Even though there are additional steps I'm hoping that this report will save time and help everyone, especially those that are not very familiar with query and Excel.

Since I don't think I've ever actually been asked to pull this information at any of the organizations I've worked for, I need your help. For those that need to query or report on X out of Y donors, I'm wondering what criteria is usually attached to such requests. For example, is it usually based on total giving amount or any gift amount (i.e. pull all donors that have given at least $1,000 per year for 7 out of the past 10 years? or pull all donors that have given at least one gift per year for 7 out of the past 10 years?)? Calendar or fiscal year? If fiscal year, what are the start and end months? Any other criteria?

Thanks for your help! As part of my mission to help nonprofits work smarter (more on this in my rant found here: http://bekerman.com/blog/bigfoot-blackbaud-forest), once the report is finished I'd be glad to share it. I'd like to make the report meet the needs of as many organizations (with as many different scenarios and criteria) as possible so the more feedback the better. Also, let me know if you'd like to help with testing the report.

Thank you!

Josh

Joshua Bekerman

Information and Technology Services Manager

American Indian College Fund

Joanne Felci 2 · January 2016

Hi Josh,

This sounds pretty cool - and quite an undertaking.

At my current job we have a group of donors that we track how many consecutive fiscal years they have given, regardless of dollar amount, excluding certain gift subtypes (events, in kind, foundations, etc.)

The date range would be from July 1 - June 30

We would also need some type of criteria to exclude certain gift subtypes (but that could probably be done via the gift export from RE - deleting those rows prior to tying it to the Crystal Report)

I'd love to stay in touch as you are putting this together.

Good luck!

Joanne

Joshua Bekerman bCLO bCRE · January 2016

Hi Joanne, thanks for the info! Just to clarify (and possibly even help) my report would calculate how many years they gave within the time frame, whether or not they are consecutive. There is a built in report in RE called Consecutive Years Report that might help or you might already be using.

You're absolutely correct when it comes to excluding (or including) based on gift type, that can be done in the export used to create the MDB data file, and also in a query that the export is based on.

I'll keep in touch, thanks!

Josh

JoAnn Strommen · January 2016

I've been asked for it a couple of time. The last time I was asked for donors who have given x of y (4 out of last 5 years) a total of $X per year or more to certain funds/campaigns. Our fiscal year is calendar year. List was used to to help create a prospect list for endovwment fund. Have been asked for similar lists of donors given at certain level for fund/campaign to help slot them for asks for upcoming campaign.

John Heizer · January 2016

Sounds like a great project! I'd be happy to help you test it.

Marie Stark · January 2016

Great idea, Josh. We do a report of donors over age 50 who have give in 7 of the past 10 years. I currently do this through a cumbersome export to Excel where I do a formula to count the numbers of years.

Sharon Ann Gilgenast · May 2016

Hi Josh, I have only been using RE for a little over a year. I am very interested in your results. We are trying to find donors who have given any amount in 8 of the past 10 years and I cannot figure out the best way to get to those results! Thanks!

John Cioni · May 2016

I just did something similar for our employee giving campaign to recognize employee's who have given consecutively for the past 5 years and 7-8 years. The only stock report I was able to find in RE would only allow me to pull a years worth of data which I found odd. I created a query to pull all employees, gift date with my range, gift type equals all. I exported the query and did text to column on the date so I was just left with the year for each gift.

I then created a pivot table with the constituent name and gift date as row labels. Depending on how many you have you can just count up how many years you have for each constituent or do a formula to do the count for you. There's probably a better way to do this with reporting but I needed it sooner than later.

Lauren Schler CFRE bCREPro · May 2016

Hi Josh and Sharon,

I have easily been able to do this through a Constituent query
using Summary Information. Create a summary field for each of the
last 10 years that returns the cumulative amount given for that
year. Then use a Pivot table (you can do this inside of RE or
outside of RE) – there are several tricks you’ll need to use to
manipulate the data to get it in the right format.

I’ve attached an Excel file with pivoted data to show you that the
number of years given for each donor (column . This was all done
through pivot, I didn’t have to do any of this calculation
manually. You can see the raw data (completely made up) on the Raw
Data tab. You’ll see that I created a “Name with ID” field so I
could easily see both name and ID for each constituent and
included that in Row Labels (this can later be parsed in separate
fields if I needed Constituent ID to import data back into RE).
Then in Values, I included Gift Year being summarized and set the
Value Field Setting to “Count of” rather than Sum.

Let me if you have any questions on this!

Best,

Lauren Schler (Raiser’s Edge Consultant)

Lauren Schler Consulting

Judy Hudson · May 2016

Hi Lauren ~

I saw you post in which you discuss the use of
pivot tables and mentioned the attachment of an excel file w/made
up raw data. Would it be possible for you to forward me a
copy of the Excel file to which you are referring in your post so I
can see the format. It’s possible this would be helpful for
use within my organization. Thanks so much!!!

Judy Hudson| Database Administrator |

The Family Place | PO Box 7999 | Dallas, TX
75209 |

O: 214.443.7765 | F: 214.443.7744 | E:
jlhudson@familyplace.org
|

Where family violence stops

www.familyplace.org

24-Hour Crisis Hotline 214.941.1991

Sharon Ann Gilgenast · May 2016

Hi Lauren, Thank you so much for your
reply! I am rushing out to a meeting this afternoon but am
excited to try this on Monday; however, I did not see that
attachment. Can you forward it to me? Thank you again!
And I look forward to following up!

(Ann)Sharon Gilgenast

Administrator

Mission Advancement Department

Sandy Cove Ministries

Est. 1946 – Celebrating 70 Years!

60 Sandy Cove Road

North East, Maryland

443-674.9452

Facebook | Twitter | SandyCove.org | Instagram

Lauren Schler CFRE bCREPro · May 2016

Hi Sharon,

Yes, I’ve reattached it though it was attached last time. It’s
possible that the system is removing the attachment. If you don’t
get it, please send me your email address and I will send it to you
privately.

Best,

Lauren

Sharon Ann Gilgenast · May 2016

Thank you Lauren. I think it is removing it. My email is
a.gilgenast@sandycove.org.

Judy Hudson · May 2016

Hi Lauren ~

Would you please forward the attachment to my
email address: JLhudson@familyplace.org

Thanks so much!

Judy Hudson| Database Administrator |

The Family Place | PO Box 7999 | Dallas, TX
75209 |

O: 214.443.7765 | F: 214.443.7744 | E:
jlhudson@familyplace.org
|

Where family violence stops

www.familyplace.org

24-Hour Crisis Hotline 214.941.1991

Lauren Schler CFRE bCREPro · May 2016

Hello all,

I’ve received a number of requests for the pivot table attachment I
created which unfortunately cannot be included as an attachment on
this listserv.

Please email me privately if you would like a copy and I will
forward it to you!

Thanks,

Lauren Schler (Raiser’s Edge Consultant)

Lauren Schler Consultting

Email: Lauren@staupell.com

Finding donors that have given X out of Y years

Comments

Categories