• Replies 2 replies
  • Subscribers 412 subscribers
  • Views 70 views
  • Users 0 members are here
Related

Tracking number of gifts over 10 years

Sara Swain
$organization

Hello, All-

Has anyone had experience pulling the number of gifts a donor has contributed over a given time frame? I'm specifically looking to pull a list of donors who have given at least 7 times during the last 10 years, and I haven't been able to come up with a method of doing this that isn't super labor-intensive.

Thanks for any feedback you can offer!

Sara Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

  • $organization

    It is fairly easy if you know how to execute the SQL

    All donations, including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

    All donations, not including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_amt > 0 and cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

     

    Aggregate subquery, if you want to include creditees:

    select customer_no, cont_amt, cont_dt from T_CONTRIBUTION

    union all

    select creditee_no, credit_amt, cont_dt from T_CREDITEE join T_CONTRIBUTION on T_CONTRIBUTION.ref_no = T_CREDITEE.ref_no

     

    Apologies if I got anything wrong but I'm just showing these as examples. Outright I will tell you this is based on Contribution amount and not Rec'd Amount. Also you should probably remove the anon customer 0 which will most likely be included in this list.

    You can run these against your Impresario DB in SQL Server Manager, or remove the count columns and load them into List Manager as a manual query.

    If you have a SQL expert on staff I would suggest hitting them up about this as it's pretty easy to run against the DB. There may also be an easier way to do this for you but I use SQL + Pivot Tables over T-Stats as T-Stats is a bit clunky for me.

  • Former Member
    Former Member $organization

    Hi Sara,

     

    I would download a Fund Activity Report to a CSV file.  Then, I would filter out all the pledge payments and run a pivot table on the pledges and gifts.   In the pivot table you could filter for the fiscal years needed, filter out any funds you don’t want included and then choose count instead of sum for the totals.    You would need some Excel experience, but it would be fairly easy.

     

    Feel free to email me if you need more info.

     

    Mark

     

    Mark Frey  |  Manager Database & Analytics  |  mark.frey@woodruffcenter.org

    1280 Peachtree Street NE  Atlanta, GA  30309  |  P: 404.733.4277  |  F: 404.733.5294  |  Give to the Arts!