Donors who have given a set number of years, but not necessarily consecutive years

Options
Martha Spiegel
Martha Spiegel ✭
Ninth Anniversary Facilitator 1
in Blackbaud Raiser's Edge NXT®: General Discussion
I need a report on donors who have given for 15 or more years, but not necessarily consecutive years. A consecutive years report will miss people, especially if they gave every year in December but one year didn't send their donation in until January. I need a list of donors who gave in any 15 years.. Suggestions?
Tagged:

Comments

  • John Heizer
    John Heizer ✭
    Ancient Membership Facilitator 3 Photogenic

    Martha Spiegel:

    I need a report on donors who have given for 15 or more years, but not necessarily consecutive years. A consecutive years report will miss people, especially if they gave every year in December but one year didn't send their donation in until January. I need a list of donors who gave in any 15 years.. Suggestions?

    This always gets messy because of how you might define a "year."  As you point out, when a donation varies by as little as even a few days how do you determine which "year bucket" that donation falls into?  So if someone gave one gift every calendar year for 14 years, except for one year they had two gifts fall in a single calendar year because they missed one calendar year by a few days, how do you count that person?  Do you want them included? Different organizations view it different ways.

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    Martha, don't hate me.  It's not an easy, canned process in RE unfortunately.  There are several other issues if you are looking at straight calendar year or to an annual fund.


    This post addresses those who've given this year.
    https://community.blackbaud.com/forums/viewtopic/159/13220?post_id=42053#p42053


    Or see Lauren's post in this thread:
    https://community.blackbaud.com/forums/viewtopic/159/26060


    There were detailed posts on this in the 'old' community but I can't get any of them right now through search.  Essentially you need to do a query of donors.  I personally would do donors who gave during the date range.  I'd take that query to Export function (to Excel).  Then for the output use summary field and set criteria for each year.  I use summary gift amount vs. summary # of gifts  as we enter $0 when person says no.  (You can set gift types/funds and the dates.)  You'll have to select the summary gift info once for every year and set the criteria for each year.  I rename output field (right click > column heading) for that specific year.  It's cumbersome with over 15 years of giving history but you should only have to do it once and than annually add current year.


    When you get the data to excel you can do a formula to count # of columns greater than $0.


     
  • Martha Spiegel
    Martha Spiegel ✭
    Ninth Anniversary Facilitator 1

    John Heizer:

    Martha Spiegel:

    I need a report on donors who have given for 15 or more years, but not necessarily consecutive years. A consecutive years report will miss people, especially if they gave every year in December but one year didn't send their donation in until January. I need a list of donors who gave in any 15 years.. Suggestions?

    This always gets messy because of how you might define a "year."  As you point out, when a donation varies by as little as even a few days how do you determine which "year bucket" that donation falls into?  So if someone gave one gift every calendar year for 14 years, except for one year they had two gifts fall in a single calendar year because they missed one calendar year by a few days, how do you count that person?  Do you want them included? Different organizations view it different ways.

     

    The request I was given was for "donors who have made a gift 15 or more years." I believe I have discretion to include the scenario that you mention. We're looking for donor loyalty to address prospects for a planned giving campaign. 

  • Christine Cooke 2
    Christine Cooke 2 ✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    Martha Spiegel:

    I need a report on donors who have given for 15 or more years, but not necessarily consecutive years. A consecutive years report will miss people, especially if they gave every year in December but one year didn't send their donation in until January. I need a list of donors who gave in any 15 years.. Suggestions?

    I have to do this... and I have a Query - thought we are a small operation and want to do 5 years and above instead of 15:

    Gift Total Number of gifts_1 greater than or equal to 5

       Gift Date betwenn 07/01/1996 AND 06/30/2016

       AND Gift Type one of .....

        AND Fund ID one of ....


    We use it for annual report recognition.

  • Alan French
    Alan French ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic

    Thomas Klimchak:

    Harder, But Free, Way:  Create a query which selects all your donors.  Now create an Export with a Giving total column with each and every year's number of gifts.  This is remarkably tedious, but you'll want to make sure that each field is created with exactly the same giving criteria.  It would be nice if you could copy and paste fields in Export, huh?  Anyway, this will be a big, long export that might take hours to run, depending on your constituents and giving history.  You might have 50 columns or so of giving in Excel (one for each year!) when all is said and done.

    If you're handy with Excel, there might be a way to tweak Thomas' process to make it less tedious. Rather than totalling the gifts by year within RE, export your constituent list and gift data into 2 separate spreadsheets, then use a Countifs or Sumifs function on the constituent sheet to pick up whether they have any gifts on the other sheet within the first date range. Copy that formula to the next column and change the dates to the following year, then copy the column again for the next year etc.


    You could even use a formula to generate the date range, so that the year automatically increments as you drag the formula over to the next column. The column() function could be useful here: it returns the number of the column it's in, so A=1, B=2, Z=26 etc.

  • Joy park
    Joy park ✭
    Tenth Anniversary Facilitator 1 Photogenic
    15 years?!  *WHEW!*  Sounds like you need the Summary fields in Export/Query.  Just set some date ranges to avoid the Dec/Jan. issue you speak of. :)  


    If you set up the Summary fields to show you Total Number of Gifts then it should be what you are looking for.   


    I often run this with Total Number of Appeals alongside - if we have a donor going back 5+ years, and they have received 10 solicitations and no gift, why are we continuing to waste paper on this person?  Let's find another way to get their attention!  ;)  
  • James Andrews
    James Andrews ✭
    Ancient Membership Facilitator 2 Photogenic

    Alan French:

    Thomas Klimchak:

    Harder, But Free, Way:  Create a query which selects all your donors.  Now create an Export with a Giving total column with each and every year's number of gifts.  This is remarkably tedious, but you'll want to make sure that each field is created with exactly the same giving criteria.  It would be nice if you could copy and paste fields in Export, huh?  Anyway, this will be a big, long export that might take hours to run, depending on your constituents and giving history.  You might have 50 columns or so of giving in Excel (one for each year!) when all is said and done.

    If you're handy with Excel, there might be a way to tweak Thomas' process to make it less tedious. Rather than totalling the gifts by year within RE, export your constituent list and gift data into 2 separate spreadsheets, then use a Countifs or Sumifs function on the constituent sheet to pick up whether they have any gifts on the other sheet within the first date range. Copy that formula to the next column and change the dates to the following year, then copy the column again for the next year etc.


    You could even use a formula to generate the date range, so that the year automatically increments as you drag the formula over to the next column. The column() function could be useful here: it returns the number of the column it's in, so A=1, B=2, Z=26 etc.


    I like this one!  Add a year column to the gift list to make your life easier.


    Another idea: do a gift export of the gifts you want to count (i.e. query for the date range, gift types, and filters), and dump the whole thing into a pivot table in Excel. Gift date for column headers, sortkey for row headers, count of gift import ID  in the values field.  


    Right click the column headers and choose "group" and group them by year. That will give you a count of everyone's gifts for each year, which is not super helpful until you copy the pivot table, paste values in a new sheet, and replace all non-zero numbers with 1 (I did this by going to the right of my data and writing a formula that says IF(ISBLANK(B3), 0, 1). Copy and paste that formula for your whole table).


    Now you have an indicator if someone gave in a certain year.  Add up those columns for each person and you're good to go.

Categories