SYBUNT 6+ years ago by giving level

Pam Gealy

Hi,  I'm doing more segmentation.  I need to identify people that did not give in the past 5 years but gave 6+ years ago.  I think I can do that pretty easily with a summary of gifts by date on a query. 


However, I need to further break that down by giving level.  If someone gave $1000+ any year beyond 6+ years ago would be one segment, someone who gave $500 to $999.99 any year beyond 6+ years ago would be another segment...and so on ....for a total of 5 giving levels within that 6+ years ago group.


If it were only a few years, I would just create a summary for each year.  But, since it is 6+ years ago, I can't really do that.


What am I missing (is this possible)?

thanks!

Jen Claudy

It depends a bit on how many records you're looking at.  In my database, I'd probably stick a temporary Attribute on the records found in your first paragraph.  Then pull a Gift Query of Constituents with that Attribute.  Export that data (in my case, I'd choose MS Access, but if you're not familiar with that program, Excel would probably do) and outside of RE, you can get a better look.


When you add your Attribute, either use a Table for the Description, or use a Date of 9/30/2016.  Outside of RE, sort by Gift Amount, and look at those with a gift of $1k+...update the RE Attribute (you can do a Global Change, using ConstID one of in a new Query) by changing the Description or Date (to 10/1/2016 for the first segment, 10/2/2016 for the second segment, etc.).  In the Constituent Query where you're pasting the ConstIDs of those you've determined are in a particular segment, you include criteria of Attribute Description (or Date) equals ___ (9/30/2016 in my example).  This means when you move to your second segment, and someone in the first segment appears again, RE will exclude them from the query due to the Attribute criteria.


You'd need to know whether someone who gave $500 6 years ago and $1k 7 years ago falls in the $1k+ bucket or the $500-999 bucket.  (My plan assumes that you would consider the largest gift, not the most recent.  You can include a Summary Field of Number of Gifts 6+ years ago, so you'll know how many times each constituent appears in your data, in case you need to find and examine other gifts.)


A little tedious, but for me, this makes sense, and would get the job done.  Do be aware that those Gift Summary Fields often need to include Gift Type one of criteria or they will automatically "think for you" and that's often not what I'm looking for.


If I'm not making sense, let me know and I'll do up some screen shots to send you.

 

Christine Cooke 2

Pam Gealy:

Hi,  I'm doing more segmentation.  I need to identify people that did not give in the past 5 years but gave 6+ years ago.  I think I can do that pretty easily with a summary of gifts by date on a query. 


However, I need to further break that down by giving level.  If someone gave $1000+ any year beyond 6+ years ago would be one segment, someone who gave $500 to $999.99 any year beyond 6+ years ago would be another segment...and so on ....for a total of 5 giving levels within that 6+ years ago group.


If it were only a few years, I would just create a summary for each year.  But, since it is 6+ years ago, I can't really do that.


What am I missing (is this possible)?

thanks!

Yes it is possible.  There are going to be a few steps.  I see two ways to go.

Either you build a series of queries (that can then be used over and over throughout time) or you can utilize a couple of canned reports to get your results.


Personally, I would build the queries because they are good for lots of different reporting.  I always keep them as a staple in the pantry of queries. 

Make a query for each giving level window (don't put dates just amounts).  And a query for your SYBUNTS.  Use the merge tool to cull out each giving level.  The reason I suggest this is because you can pull it anytime.


The other way is to use the canned SYBUNT report to pull the info, choose have it doing a query output.  Take that query and pull it through a Donor Category Report and you will get the different levels as you have them set up in the tables. 


Depending on what you are using the segmenting for -- may also determine which path you choose for pulling your info.  Try both and see which one works for your needs.