How to track Anniversary Donors

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 Manager
Dallas Symphony Orchestra
Morton H. Meyerson Symphony Center
Schlegel Administrative Suites
2301 Flora Street
Dallas, Texas 75201-2413

214-871-4041 - phone
214-981-2988 - fax

j.trimble@DalSym.com
www.MyDSO.com

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Christopher Adriance
Sent: Thursday, July 24, 2014 3:12 PM
To: John Trimble
Subject: [Tessitura Development Forum] How to track Anniversary Donors

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! 

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 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 Manager
Dallas Symphony Orchestra
Morton H. Meyerson Symphony Center
Schlegel Administrative Suites
2301 Flora Street
Dallas, Texas 75201-2413

214-871-4041 - phone
214-981-2988 - fax

j.trimble@DalSym.com
www.MyDSO.com

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Christopher Adriance
Sent: Thursday, July 24, 2014 3:12 PM
To: John Trimble
Subject: [Tessitura Development Forum] How to track Anniversary Donors

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! 

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!