Hello,
At Pacific Symphony, we are getting ready to celebrate Music Director Carl St.Clair's 25th anniversary. One of the projects we are currently working on is pulling a list of donors who have donated each year for 25 years. I have attempted an assortment of List and Extraction Manager acrobatics and nothing seems to be working.
Has anyone out there attempted a similar project? Any pointers would be greatly appreciated!
I recently used an Excel Pivot Table to determine number of years a patron has donated, not necessarily consecutively.
Shelley Salinas
Box Office Manager
COCA
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Christopher AdrianceSent: Thursday, July 24, 2014 3:06 PMTo: Shelley SalinasSubject: [Tessitura Development Forum] How to track Anniversary Donors
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
I've done blunt-force, recursive List Manager lists to get data like this, something like:
LIST 1Contribution Campaign IN First Year Campaign.
LIST 2Contribution Campaign IN Second Year Campaign.List IN LIST 1
LIST 3Contribution Campaign IN Third Year CampaignList IN LIST 2
And do that until you get to the 25th year. But there are more elegant SQL ways to do it beyond my grasp.
I’ve done quick and dirty something like this in the manual query pane in list manager:
Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
JOIN VSB_CONTRIBUTION e (Nolock) ON e.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1
AND EXISTS (
select 1 from vsb_contribution e (Nolock)
where a.customer_no = e.customer_no
and e.cont_amt >= 1.00 ***NOTE weeds out write-offs***
and e.fund_no in (1,2,3) ***NOTE all our annual fund numbers went here- yours would be different!***
having count (distinct e.campaign_no) >=25
)
Issues with this method
1) False positives: someone could give to multiple annual fund campaigns in the same years if you are set up that way, e.g. Individual AF, Corporate AF, Foundation AF in our case—any time you have multiple annual funds in the same year. They could have twenty five distinct campaigns in only 15 years or whatever if they hit more than one campaign per year. Still hefty donors, but not 25 years running possibly.
2) Could miss people who give sometimes as an individual, sometimes through their foundation and other times through their company, assuming each entity has its own constituent number
3) False positives who give every other year for fifty years or every third year for seventy-five years
4) Takes a long time to complete for 25 if you have a lot of constituents, but then if you don’t have very many constituents, you can probably already eyeball the 25ers. I usually use this sort of query for numbers more like 3,4,5 years and limit the campaigns instead of funds, so that I’m looking for 3 -4-5 years out of last 5 and so on.
John Trimble Data and Prospect ManagerDallas Symphony Orchestra Morton H. Meyerson Symphony CenterSchlegel Administrative Suites2301 Flora Street Dallas, Texas 75201-2413 214-871-4041 - phone214-981-2988 - faxj.trimble@DalSym.comwww.MyDSO.com
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Christopher AdrianceSent: Thursday, July 24, 2014 3:12 PMTo: John TrimbleSubject: [Tessitura Development Forum] How to track Anniversary Donors
If you have one campaign per year, you should be able to do it this way:
Contribution Campaign IN (your FY14 campaigns)Contribution Campaign HAS (your FY13 campaigns)Contribution Campaign HAS (your FY12 campaigns)...and so on.
I've never done that for more than maybe 4-5 years but it should work so long as you carefully mind "In" vs "Has". If you (or somebody else there) knows a bit of SQL you can also get at it a few different ways by manually editing the query. I opened a new list and put "Contribution Campaign IN (every annual fund campaign)" and then manually edited to include the Campaign Fiscal Year. From there I restricted to only people who had given for a certain number of distinct fiscal years. Here's what mine looks like, (my additions in bold.)
Select Distinct a.customer_noFrom V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_noLEFT JOIN t_campaign c (Nolock) ON e.campaign_no = c.campaign_noWhere IsNull(a.inactive, 1) = 1 AND e.campaign_no in ([your campaign #'s])GROUP BY a.customer_no HAVING COUNT(DISTINCT c.fyear) = 25
I haven't done much testing on this (so use with much caution,) but I've pulled similar lists before that have worked. There are a couple of issues with that approach. It won't capture any credited gifts. (We have a custom view that includes credited gifts; not sure how you deal with them.) It could also include people if they had a contribution zeroed out in any given year, since zeroing a gift out doesn't delete the record.
Hope that helps some. Good luck!