Does anyone know of a way to easily report on first time donors? I'm trying to set up a weekly, automatic report that will show us all of our first time donors.
Thanks so much!
Mark
Hi Mark,
Off the top of my head I'm not sure whether there's a quick way to do this out of the box, but if somebody there is SQL-savvy you can probably manually edit a list to give you patrons who made a first gift in the last week. Then use that list to filter the New Contributions Report, (or the Fund Activity Report, or whichever other report you prefer.)
I just did a quick and dirty manual list edit to try to meet these criteria:
...and came up with this, which seems to work:
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 >= 1
Group By a1.customer_no
Having MIN(a1.cont_dt) BETWEEN DateAdd(dd,-7,Convert(varchar,GetDate(),112)) and DateAdd(dd,-1,Convert(varchar,GetDate(),112)+' 23:59:59')
and COUNT(a1.cont_dt) = 1
) as e ON e.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1
Obviously you'd want to test a lot to see if that works with your setup. I can think of some caveats right off the bat. If somebody who already has an account creates a new account and makes a gift, (through your website, e.g.,) their new account would meet these criteria as a first time donor. If you do credited giving, this would return the creditor rather than the creditee, (or, if the creditor has previously made gifts on behalf of other donors they would be left off entirely even if the creditee is a new donor.) And my list criteria don't take into account which campaign/designation/fund the gift was entered to, though that's something you could certainly tweak in the SQL if you only wanted to see new Annual Fund donors, for example. But, perhaps an approach like this could meet your needs? If you make it a dynamic list so that it generates automatically you could then schedule your report to run automatically for the same date range. I haven't tried it but seems like something that should definitely be achievable.
Hope that helps!
-Matthew
If one did want this list to only include annual fund donations, what would the query look like? (I'm guessing it's some sort of "where fund=xx" but I don't write SQL so I don't want to make it up myself.)
Totally--if you wanted to limit it to certain funds, you could just add one more line to the above query under the 'where' clause:
[...]
and a1.fund_no in(100,101,102)
...replacing 100,101,102 with a comma-separated list of your fund(s) of choice. You can find the fund numbers in the fund setup (look for "ID") or by making a list with "Contribution Fund IN ..." and then clicking "Show Query" to find the fund numbers.