Reporting on First Time Donors

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:

• Anybody who has exactly one contribution of $1 or more
• Contribution date was between 7 days ago and yesterday (as of the time of the list run)

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

Thanks Y'all!  This is some good information.  And from it I found the "First Contribution" criteria in the contributions folder in listbuilder.  I used that and chose >= day run -7 and made it a dynamic list.  I used that in a new contributions report and it worked, though we'll still have to watch out for a few things.  Of the several records in the report, one was a new contribution on the HH, but the individual record had all the history (they must have been recently married or something) - it always gives me a gigantic headache when there are gifts on both Ind and HH!  And we will have to watch out for duplicate records!  It's not perfect, but at least it's something to start/work with.

Mark

Interesting! We don't have a "First Contribution" criteria so maybe that's custom for you guys?

I feel you on the Ind/HH confusion. As a matter of policy, we always do "Move Transactions to Household" when we put existing individuals into a household, but we still miss people sometimes. If you want to make sure you always get the household, you can change your list options to "Replace individual constituents with their primary household, if one exists" just to be safe.

I'm not sure if it's a custom criteria or not.  I found it in the Contributions folder.  One thing on my to do list is to request a list of all potential criteria for both list builder and output builder from our IT dept.  I'd like to see what we don't currently have access to. 

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:

[...]

Where a1.cont_amt >= 1

   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.