• Replies 4 replies
  • Subscribers 413 subscribers
  • Views 39 views
  • Users 0 members are here
Related

How to track Anniversary Donors

Christopher
$organization

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! 

Parents
  • Former Member
    Former Member $organization

    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_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
    JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no
    LEFT JOIN t_campaign c (Nolock) ON e.campaign_no = c.campaign_no
    Where  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!

Reply
  • Former Member
    Former Member $organization

    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_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
    JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no
    LEFT JOIN t_campaign c (Nolock) ON e.campaign_no = c.campaign_no
    Where  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!

Children
No Data