Our membership manager is wanting to do some analysis on membership trends. She wants to know how many people gave consistently for the past 5 years to each yearly campaign but have not yet given for the current year. We attempted to create an extraction that suppressed anyone who gave in 2020 but now we are trying to create a segment that will give us everyone who gave every year 2015-2019. I have been racking my brain and can't seem to think of a way to do this with the existing criteria.We tried using contribution campaign with the "in" and then provided each years campaign, but that will yield anyone who gave at least once to any of those campaigns, not the people who gave to all 5 campaigns. We tried adding contribution campaign 5 times for each year, but that yields no one because it require a single contribution to have all 5 campaigns which is impossible.
I'm fine writing a custom SQL query for her to use, but if she ever wanted to look at different years or a different time frame, I would always have to edit the script for her. I also tried looking in analytics for a way to do this but haven't had much luck.
And from the way I can tell that lists and extractions build the query, I can't even think of a custom criteria I could build for her that would accomplish this. Anybody had any luck with something like this?
One method of doing this is to use multiple lists. It would work something like this:
List 1 = Anyone who gave in 2015
List 2 = Anyone who gave in 2016
List 3 = Anyone who gave in 2017
List 4 = Anyone who gave in 2018
List 5 = Anyone who gave in 2019
List 6 = Anyone who gave in 2020
And then you bring them all together with a final list (or a segment in an Extraction) where the criteria look like the following. (Note that I am using the criteria element called List.)
List HAS List 1
List HAS List 2
List HAS List 3
List HAS List 4
List HAS List 5
List DOES NOT HAVE List 6
What this is saying is "Create a list of constituents where the constituent is on Lists 1 - 5, but also is not on List 6."
I feel so stupid. I never even considered using the HAS operator. In theory couldn't I just do what I had done before and add campaign 5 times and just use the HAS operator for each one and it would yield the same result?
Yes, if your criteria is strictly campaign, you can certainly do it all in one list! But if you find that the criteria for each year end up getting more complicated (excluding certain funds, gifts but not pledges, etc.) then multiple lists can help you manage them cleanly.
The HAS operator is your friend!