Consecutive Years Giving - with gift criteria

Options
Steph Williams
Steph Williams
Ancient Membership 100 Comments 25 Likes Name Dropper
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi all,


We're starting up a new Loyalty Society and I've been asked to work on a list of donors with 5+ years of consecutive giving. We don't care the amount, BUT there are some criteria around the types of gifts that are being included (namely - we want to exclude some of our event purchases, raffles, etc.) Any thoughts on how to accomplish this?


A few other notes:

We're including only cash/pay-cash, not pledges

The way we currently include/exclude is based off appeal package category

We can't just exclude event appeals completely, because there are donations at many. (coded appropriately via the package category)

I don't need to know how much someone gave each year, just that there was at least one qualifying gift

Output needed is constituent name (and ID of course) and total number of consecutive years


I was hoping to find a report where I could query on gifts that meet the criteria, but the CYG reports only allow for constituent queries.

I've also tried just running a constituent query and using the summary field for CYG (with appropriate gift criteria), but it seems off? I may need to dig deeper to see if it's a data entry thing vs a RE thing though. Anyone have suggestions or experience with issues on the CYG field in query?


Thanks!
Tagged:

Comments

  • Joshua Bekerman bCLO bCRE
    Joshua Bekerman bCLO bCRE
    Tenth Anniversary 500 Likes 500 Comments Name Dropper
    Hi Steph, it hurts my head every time I start to think about how you might do this in RE, mostly because you need package categories.  Not sure if it would help or be any easier, but I just started working on a report that finds donors that have given X out of Y years.  It wasn't designed to find consecutive giving but you might be able to use it anyway.  How far back are you going to find your 5+ years of consecutive giving donors?


    Here's a Community post about the report that I posted just last week:
    https://community.blackbaud.com/forums/viewtopic/159/26060


    Josh

     
  • Steph Williams
    Steph Williams
    Ancient Membership 100 Comments 25 Likes Name Dropper

    Joshua Bekerman bCRE:

    Hi Steph, it hurts my head every time I start to think about how you might do this in RE, mostly because you need package categories.  Not sure if it would help or be any easier, but I just started working on a report that finds donors that have given X out of Y years.  It wasn't designed to find consecutive giving but you might be able to use it anyway.  How far back are you going to find your 5+ years of consecutive giving donors?


    Here's a Community post about the report that I posted just last week:
    https://community.blackbaud.com/forums/viewtopic/159/26060


    Josh

     

    You're telling me. I probably could encourage alternate criteria (my original goal was just to look at receipt value, which would exclude all those gifts anyway...)


    We want to know whatever they've given. We have gifts in RE back to 1982 (imported from whatever system they were using then.) We did get everyone to agree that anything prior to 2010 counted toward CYG regardless of gift type.

  • Jen Claudy
    Jen Claudy
    Tenth Anniversary 1,500 Likes 1000 Comments Name Dropper
    Wow, that is complex!  I've always disliked working with consecutive year concepts because inevitably a major donor will have some sort of timing exception that will cause one gift to fall such that it is a year "early" or a year "late".  Particularly pledge payments.


    If this were me, I think I would dump a query of every gift in RE to an MS Access table.  Then I would use Access to find all ConstIDs with less than 5 gifts, because they obviously won't be included.  And from there just sort thru removing chunks of data/gifts that do not qualify.  Then you start looking at timing of Gift Dates and do futher exclusions.  Once done, you'll have your list of ConstIDs that currently qualify for your list, which you can tag with a Const Attribute using Global Add or Import (which also allows you to include a date as to when each donor qualified for this status).  Now you just need to design an audit query in RE that will find new qualifiers moving forward.


    Simplified for reasons of typing this in an online forum, and no easy or quick task to undertake, but for me, this would be the best way to get to the end result without missing anyone.


    Do you happen to also use Campaign Categories or Fund Categories?  I use them to easily exclude certain types of Gifts, and I would think it would be easier than Package Categories, since it's not buried quite as deep.  Love Packages, but sometimes that data is more difficult to use than it's worth.

Categories