• Replies 5 replies
  • Subscribers 412 subscribers
  • Views 120 views
  • Users 0 members are here
Related

List of donors/members with at least one contribution in 10 of the past 15 years.

Camila Jones
$organization

Hi all!

Can anyone think of a way to pull a list of constituents who have given a contribution in at least 10 out of the last 15 years? Any amount for the contribution is fine, and I do need to exclude one fund, but can do that once I have a basic criteria for the list. 

Thank you in advance for your time and/or suggestions! (Also this is my first time posting on the forums, so please let me know if more information is needed, or if I have overlooked a very obvious solution to this query). 

-Camila

  • $organization

    Hi!

    I actually think I may have worked out a solution to this using Analytics, but wanted to leave this open if anyone had a better suggestion or solution. Thanks again! 

  • $organization in reply to Camila Jones

    Big analytics fan here so that's a great idea and easy to sense test.

    But in the interests of a challenge you could do it in a list - especially if you wanted up to the minute results like during an EOFY campaign or for emails.

    It's tricky in lists because you need to count the number of campaigns and create > 9 

    I cheated by

    1. creating a list with the Campaigns and amount > $1
    2. saving and opening up SHOW QUIERY and editing the SQL (It's not too hard if you have Google by your side)

    Originally it looks a bit like this

    SELECT DISTINCT a.customer_no
FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
       INNER JOIN
       (SELECT a1.customer_no
        FROM   vs_contribution_with_initiator AS a1 WITH (NOLOCK)
        WHERE  a1.campaign_no IN (172, 105, 104, 101, 103, 94, 4)
               AND a1.cont_amt >= 1.00) AS e
       ON e.customer_no = a.customer_no
WHERE  a.inactive = 1

    So to count the Campaigns and set a threshold I added in 

    • LINE 4: count(distinct campaign_no) as Num_Campaigns  so we are counting campaigns but only once in case they gave 2+ in a season
    • LINE 8: GROUP BY customer_no so we are counting campaigns by customer
    • LINE 9: HAVING Count(distinct Campaign_no) > 4  This is the slicer - I've gone with 5+

    SELECT DISTINCT a.customer_no
FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
       INNER JOIN
       (SELECT a1.customer_no, count(distinct campaign_no) as Num_Campaigns
        FROM   vs_contribution_with_initiator AS a1 WITH (NOLOCK)
        WHERE  a1.campaign_no IN (172, 105, 104, 101, 103, 94, 4)
               AND a1.cont_amt >= 1.00
        GROUP BY customer_no
        HAVING Count(distinct Campaign_no) > 4 ) AS e
       ON e.customer_no = a.customer_no
WHERE  a.inactive = 1

    You can have a play with it using an output set or fiddle with the number after the having to check.  A good one is = 10 and check that it's pulling in people who have only only 10 campaigns in your range

    Cheers,
    H

  • $organization in reply to Heath Wilder

    Thanks so much! I have not had a chance to get into SQL yet, but this seems like a great way to start. Much appreciated! 

  • $organization in reply to Camila Jones

    Its' not a bad way of getting a start in SQL. If you need a hand breaking into any of that or breaking it down reach out.