SYBUNT

Kerry Ayres-Smith

for those of you who pull SYBUNT lists for mailings (i.e. we are doing an annual fund mailing, and trying to pull a SYBUNT list for those who gave to Annual Fund 2010, 2011, 2012, 2013, 2014, 2015, and 2016 but not 2017), how do you do it? I am having some difficulties and just would like some new ideas!


Thanks!

Ryan Hyde

I would probably write a query for this.


Essentially, you need a constituent query with eight "summary number of gifts" fields. In the first 7, you're going to say "number of gifts is greater than 0," and you'll include whatever parameters you need to find gifts in a given fiscal year. Once you have the first seven done, you'll then do "number of gifts equal to 0" for the 2017 date range. 

Kerry Ayres-Smith

Ryan Hyde:

I would probably write a query for this.


Essentially, you need a constituent query with eight "summary number of gifts" fields. In the first 7, you're going to say "number of gifts is greater than 0," and you'll include whatever parameters you need to find gifts in a given fiscal year. Once you have the first seven done, you'll then do "number of gifts equal to 0" for the 2017 date range. 

 When I run that in conjunction with my LYBUNT list, I have a lot of overlap. Is there a way to prevent that?

Ryan Hyde

Well yes, there should be perfect overlap between the two. All LYBUNTs should be in this query (assuming 'this' year is 2017 for your purposes) because your query requires that, on top of other things, they also gave last year but not this year. EDIT: Nope, not perfect overlap - wasn't thinking straight. Just some overlap.


I'm assuming you want to mail to your long term SYBUNTs with specific messaging, and then your LYBUNTs who do not have long, continuous giving histories separately, with slighlty different messaging. And I also assume you're asking your LYBUNT report to output a constituent query, which you're probably plugging into an export to create a mail file. 


Assuming yes, you need to merge these queries with the <sub> operator. Essentially you're subtracting the results of one query from the other. 


Query > Merge Queries


Put your LYBUNT result query in the first box, then put your custom SYBUNT query in the second box, and click the <sub> operator. Click Merge and this will create a static query that has all of your LYBUNTs who are NOT in your custom SYBUNT query. Now you have two mailing lists that aren't merged at all.


CAVEAT: In both your custom query and your report, you should consider carefully how you want to handle soft credits. It's possible that one spouse may end up in one query and one spouse may end up in the other, which would result in one household receiving both pieces, which I'm sure you want to avoid. If your database has any househoulds where both members are constituent records, then you'll want to make sure that your custom query and your LYBUNT report are applying soft credits to both records.