Finding 1st time $1000+ donors

Tina Wright

Hello!

I need to pull a list of donors that have given (single gift or cumulatively) $1,000 or more this current fiscal year that have not given $1,000 (single gift or cumulatively) any prior fiscal year. What is the best way to pull this? I can't seem to think beyond merging a ton of queries to get that information.

Thank you very much for any input!

 

JoAnn Strommen

Tina, There's probably a way to do this that's a bit simpler but here's one way. 



Create constit query, using summary info for total amount of gifts greater than or equal to $1000.  Gift date this year, gift type - do you want pledges or just $ received-select accordingly. 



Go to reports - Analytical Reports > First/last....  For records use the query you created.  Leave gift dates as all dates for the report.  I unchecked to include last and biggest columns.  Report will generate and show date for first gift.  I'd take that report to excel and remove everyone with first gift date prior to this year. And then would have my list.



As I said there may be an easier way but right now this is what came to my headachey brain.

Keri Eadie

I would create a constituent query, summary information total amount greater than or equal to $1000, gift date betweeen (your fiscal year).

In the output include either the constituent ID OR the Import ID, Gift Date Gift Amount.

Save

Go to Report

Pivot Reports

New

Select the query you just made

On the left hand side drop the field import ID (or constituent id)

along the top the field year

in the middle gift amount.



This will show the cumulative giving for each year only for your donors who gave over $1000.  Then you can export this to excel.  Copy the data from the pivot table into another excel worksheet.  Then you can sort the history by year and delete who has made a gift over $1000 in the past.  Then you have the constituent id's or import ID's which you can copy and paste into a query.  Then this query is only those who met your criteria.

 

Keri Eadie

You also could create your first query.  Then in export include total giving for every previous year and then clean it up in excel again.

Tina Wright

Thank you, JoAnn!  I will certainly give this a try.  And boy, do I know what you mean about a headachey brain!  Sometimes when working in Raiser's Edge, you get to the point where you can't see the forest for the trees!

Tina Wright

Thank you, Keri, for the suggestions. I'm glad to see that there really wasn't an easy answer to this one. Sometimes I feel like I'm not seeing the obvious when it comes to Raiser's Edge.



Although I haven't used Pivot Reports much in the past, I'm going to try what you suggested.  Thanks again!

Christine Cooke 2

You can run a Constit Query using the Summary Field for Gift:

Total Amount of Gifts greater than or equal to $1000

Gift Date between <your fiscal year dates>

AND Gift Type <whichever you are reporting on.. pledge, payments, etc>

AND Fund ID <IF you need to filter out event income or endowment or something then use this field>



AND Summary Field on Gift a second time

Total Amount of Gifts less than or equal to $999

AND Gift Date less than <the last day of the fiscal year previous to the FY that is your current one> example 06-30-14 last day vs. 07-01-14 first day

AND Gift Type one of <whichever you are reporting on.. pledge, payments, etc>

AND Fund ID <IF you need to filter out event income or endowment or something then use this field>



You can then pull that through Export and included the fields you want and filter out the dupes by choosed HoH.

Or take it to the canned report Analytical Reports .. Comparative Reports and get a list of names only

Alan French

...

AND Summary Field on Gift a second time

Total Amount of Gifts less than or equal to $999

AND Gift Date less than <the last day of the fiscal year previous to the FY that is your current one> example 06-30-14 last day vs. 07-01-14 first day

I'm not sure that would give Tina the list that she needs: someone could have given $500 per year for the previous 3 years and then given $1000 this year, which would exclude them from your query as they've given a total of $1500 prior to the start of the current FY, but they need to be included as they've never previously given over $1000 in any one year.