Recurring Donors

Lauren Fardella

I am not sure why/how I am struggling but I need to get a total number of donors that are repeat donors that gave this year (so far). It doesn't matter if they gave last year or or 5 yrs ago...as long as they gave again this year. I set up a query of Last Gift Date is Greater Than/Equal to 1/1/17 AND Total Amount of Gifts is Greater Than/Equal to 2, and was gonna plug that into a Gift Detail report.... Is this the best way to go about getting that number?

JoAnn Strommen

In theory yes but if you have monthly/quarterly  donors or anyone else who gave more than once this year, your results will not be accurate. 

There are several ways you could filter it out. You could do two queries and merge.

How about last gift greater  than 1/1/17 like you have AND First Gift > Gift Date less than 1/1/17? 

Or you could do two queries and merge.

Be sure to consider gift type also - do you want pledges or only paid gifts?

Lauren Fardella

JoAnn Strommen:

In theory yes but if you have monthly/quarterly  donors or anyone else who gave more than once this year, your results will not be accurate. 

There are several ways you could filter it out. You could do two queries and merge.

How about last gift greater  than 1/1/17 like you have AND First Gift > Gift Date less than 1/1/17? 

Or you could do two queries and merge.

Be sure to consider gift type also - do you want pledges or only paid gifts?

Paid gifts.    If I do that merge shall I plug into the report afterwards?   I just want the most accurate number.  

Alan French

The trouble with using First Gift and Last Gift is that it will do exactly that - there's no way to tell query to look at the first gift from a particular list of types. I would do two summary criteria: total number of gifts prior to 1/1/17 > 0, and total number of gifts on or after 1/1/17 > 0, making sure to also select the applicable gift types for each summary.

Lauren Fardella

Alan French:

The trouble with using First Gift and Last Gift is that it will do exactly that - there's no way to tell query to look at the first gift from a particular list of types. I would do two summary criteria: total number of gifts prior to 1/1/17 > 0, and total number of gifts on or after 1/1/17 > 0, making sure to also select the applicable gift types for each summary.

I just tried that....is this correct?  Cause when I hit results I had over 100,000 records  

JoAnn Strommen

Yep, thought about summary also.


With constit query, First Gift category does have a criteria field of Gift Type but it may not filter like expected.   ?

Alan French

You've got "greater than or equal to 0"; it should be "greater than 0" otherwise you'll get everyone regardless of whether they're given a gift or not


JoAnn, no the first gift type filter doesn't work the same way in query as it does in export - if someone's first gift is not one of the selected types, they won't show up in the results at all. Not like in export where it keeps looking until it finds the first gift of the relevant type.

Joanne Felci 2

You should not have to, but you might want to try it with (  ) around the criteria (for each summary of gift)


I'd also suggest, running just the number of gifts since 1/1/17 to see how many your total is, then the total of people that have also given prior should be less than that number so at least you know if you are heading in the right direction

Joanne Felci 2

great eyes Alan!


Did not see the 'or equal to' - of course that is why the number is so high 

Lauren Fardella

Alan French:

You've got "greater than or equal to 0"; it should be "greater than 0" otherwise you'll get everyone regardless of whether they're given a gift or not


JoAnn, no the first gift type filter doesn't work the same way in query as it does in export - if someone's first gift is not one of the selected types, they won't show up in the results at all. Not like in export where it keeps looking until it finds the first gift of the relevant type.

ah yes more realistic number now.    Thank you!

Cathleen Mai

There is a canned report- under analytical reports - it is called SYBUNT Report

The SYBUNT Report (some years but unfortunately not this) provides a list of constituents who donated at some time in the history of your organization but did not donate during the time period you specify. The last gift donated and the date of the gift appear with the constituent name.

Is this what you are looking for? - Never mind, just really read the  posting and I see you are looking for the other way around.

 

Denise Marino

Lauren Fardella:

Alan French:

The trouble with using First Gift and Last Gift is that it will do exactly that - there's no way to tell query to look at the first gift from a particular list of types. I would do two summary criteria: total number of gifts prior to 1/1/17 > 0, and total number of gifts on or after 1/1/17 > 0, making sure to also select the applicable gift types for each summary.

I just tried that....is this correct?  Cause when I hit results I had over 100,000 records  

I don't think you want > or equal to  0 - wouldn't you just want > 0?