• Replies 1 reply
  • Subscribers 412 subscribers
  • Views 70 views
  • Users 0 members are here
Related

Average gift

Lisa McColgan
$organization

Hi all -

Am pulling a ginormous list for wealth screening, and am hitting a wall with one particular segment.

They are looking for constituents with an average gift of $1,500 (all years, all campaigns).  I was able to find folks with a largest gift of that amount (LV_LARGEST_GIFTS), but can't seem to get at average.

Any tips?  Thanks!

-Lisa

Parents
  • Former Member
    Former Member $organization

    Hi Lisa,

    I don't think there's a report that that will do averages. I believe this SQL will get you the list you want. (I took out $0 gifts)

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
    JOIN (Select a1.customer_no From vs_contribution_with_initiator a1 WITH (NOLOCK) Where a1.cont_amt >0 Group By a1.customer_no Having AVG(a1.cont_amt) >= 1500) as e ON e.customer_no = a.customer_no
    Where IsNull(a.inactive, 1) = 1

    My first thought was an excel pivot table. You can run the New Contribution report to get a list of all the gifts given, or gifts from those people. Insert a Pivot Tabel, put customer_no in Row Labels, and cont_amt in values and change to the field settings to Average instead of Sum. You may havet to watch out for people who give some personaly and some though a third party like family foundation or work matching campaign.

    Hope that helps!

    Ann



    [edited by: Ann True at 1:06 PM (GMT -6) on 20 Jul 2017]
Reply
  • Former Member
    Former Member $organization

    Hi Lisa,

    I don't think there's a report that that will do averages. I believe this SQL will get you the list you want. (I took out $0 gifts)

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
    JOIN (Select a1.customer_no From vs_contribution_with_initiator a1 WITH (NOLOCK) Where a1.cont_amt >0 Group By a1.customer_no Having AVG(a1.cont_amt) >= 1500) as e ON e.customer_no = a.customer_no
    Where IsNull(a.inactive, 1) = 1

    My first thought was an excel pivot table. You can run the New Contribution report to get a list of all the gifts given, or gifts from those people. Insert a Pivot Tabel, put customer_no in Row Labels, and cont_amt in values and change to the field settings to Average instead of Sum. You may havet to watch out for people who give some personaly and some though a third party like family foundation or work matching campaign.

    Hope that helps!

    Ann



    [edited by: Ann True at 1:06 PM (GMT -6) on 20 Jul 2017]
Children
No Data