LYBUNT - How to find constituents who gave less this year than last year

Melissa Sheinman

Each month I run a LYBUNT to find constituents who have given to a set of funds (approximately 25 funds, pulled from 3 campaigns) last year but not this year.


Here is the problem I'm encountering: a constituent who normally gives $1000 in membership dues has not yet paid this year.  However, they have donated $100 to one of the other funds I include in the report.  Since they have given to one of the designated funds this year they are (correctly) not pulling into the report - but the team reviewing the LYBUNT want to see these types of constituents for review.  


Essentially, if the past giving is significantly more than the current giving they want the constituent to be included.  It almost seems like we need a "Gave less this year than last year" report instead of a traditional LBYUNT, but I can't see any way to narrow the criteria by gift amount in the built in LYBUNT that RE offers.  I've tried to think how I can use query\\export but am running into a wall there as well.  


I've also thought about making a LYBUNT for each fund but worry that will put constituents on the list who shouldn't actually be there (maybe they haven't paid their dues yet, but they donated a signification amount to our annual fund).  Since ideally the team should be pulling up each constituent's RE record to review their giving, etc. before reaching out to them maybe this is ok - but obviously nobody wants to waste their time looking through a list where half the results aren't accurate.


My brain is a little bit tied in knots over this - any advice is greatly appreciated!


Melissa

Daniel Bayer

I have a similar situation, where the need is for a "gave less this year than last year" report. My solution has been to use a query of everyone who gave last year as the base for an Export, in which I include two Summary Gift columns - one for the total of last year's funds and one for this year's funds. Then in Excel, I sort by the summary column for this year and do some conditional formatting to color the cells in that column based on how this year's giving compares to last year's. It's not perfect, and it's more manual work on my part than I'd like, but it gets the job done.

Melissa Sheinman

Thanks for the reply, Daniel!  I actually created a similar export using summary columns but was asked to also include the gift from last year which qualified them for the report.  It is hard since we are dealing with so many funds - the export becomes unruly very quickly! 

Elizabeth Johnson

I sometimes use exports as Daniel Bayer‍ for this analysis and sometimes I use the canned report built into RE. Under analytical reports go to comparative reports. On the first tab you can specify your date ranges to use and on the detail option of the format tab you can look at which variance you would like to include and how you want to display that variance. You can use a query or regular filters to narrow this down further.


I hope this helps,

Elizabeth


 

Karen Diener

I've pretty much always done the same as Daniel Bayer.  The canned reports always seem to have something about them that you can't adjust, so they are more of a last resort for me, quite honestly.


I export the total giving for each set of dates, add a column in my Excel sheet, and add a calculated field where I subtract this year's giving from last year's giving, for example.  If they gave more this year than last, it is a positive number.  Same amount is $0 and less than last year will be negative.  If you need color coding, by the newly added column and apply colors.  It honestly take 5-10 minutes after the export is complete.


Karen

Naomi Harding

On the Details section of the Format tab of the Comparative Report (in Analytical Reports category), you are able to indicate that you want only those who Decreased, only those who Increased or Both. The other really helpful component in that area, is that you can define the $ amount or % amount difference threshold that needs to be met to in order for the records to be included. By using the option to get an Output query (from General tab), you can group the qualifying constituents for a follow-up communication.

Melissa Sheinman

Thank you all for your replies!  These are all helpful ideas and I think will be good options for supplementing the LYBUNT I've been pulling.  

Christine Cooke

Use the Comparative Report under Analytical Reports.  You can set the dates you want to compare to specifically compare and the output shows if the gift amount is higher or lower.