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
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