Help with Query/Report: First Monetary Gift Greater than $1k

Lisa Wheeler · May 2017

I need help! I need to run a list of people whose first monetary (cash, pay-cash, stock, recurring gift, etc. - i.e. NOT Gift In-Kind) gift was received in the last 3 years and was over $1000. If I pull a query based on First Gift = Cash, then I miss out on anyone whose actual first gifts was in-kind, but, technically, their second (third, fourth, etc) is what I'm looking for. Also, it needs to be a constituent query because I need to merge/purge it with some other queries (unless someone knows some crazy magic to convert a gift query into a constituent query?).

I tried running a New Donor report (and creating a query based on it). It gave me the list of people who had a first monetary gift in the right range, but once I have the list in Query, I'm not sure how to filter by gift amount (again, it's all based on the first monetary gift).

Thoughts?

Crystal Chouinard · May 2017

Lisa Wheeler:

I need help! I need to run a list of people whose first monetary (cash, pay-cash, stock, recurring gift, etc. - i.e. NOT Gift In-Kind) gift was received in the last 3 years and was over $1000. If I pull a query based on First Gift = Cash, then I miss out on anyone whose actual first gifts was in-kind, but, technically, their second (third, fourth, etc) is what I'm looking for. Also, it needs to be a constituent query because I need to merge/purge it with some other queries (unless someone knows some crazy magic to convert a gift query into a constituent query?).

I tried running a New Donor report (and creating a query based on it). It gave me the list of people who had a first monetary gift in the right range, but once I have the list in Query, I'm not sure how to filter by gift amount (again, it's all based on the first monetary gift).

Thoughts?

This might sound crazy but most work arounds do. What if you did a constituent export and exported gift amount. You can apply a query that states the constituent first gift date was made within the last three years. Then include basic constituent information for you to review the resulsts (name, id, first gift date), and export gifts with the criteria of only the gift types you are looking for. Set the number of gifts being export to a high enough number to make sure you catch that first monetary donation. Then once in excel you can sort and filter to get the constituents that you are actually looking for.

I am sure this sounds crazy but it is the only way that I can think of to grabbing the constituents that you are looking for. Good luck and let me know how it goes.

Steven Cinquegrana 2 · May 2017

Lisa Wheeler:

I need help! I need to run a list of people whose first monetary (cash, pay-cash, stock, recurring gift, etc. - i.e. NOT Gift In-Kind) gift was received in the last 3 years and was over $1000. If I pull a query based on First Gift = Cash, then I miss out on anyone whose actual first gifts was in-kind, but, technically, their second (third, fourth, etc) is what I'm looking for. Also, it needs to be a constituent query because I need to merge/purge it with some other queries (unless someone knows some crazy magic to convert a gift query into a constituent query?).

I tried running a New Donor report (and creating a query based on it). It gave me the list of people who had a first monetary gift in the right range, but once I have the list in Query, I'm not sure how to filter by gift amount (again, it's all based on the first monetary gift).

Thoughts?

This would be very easy to do directly in SQL if you're comfortable with that approach.

Catherine Burns · May 2017

Lisa Wheeler:

I need help! I need to run a list of people whose first monetary (cash, pay-cash, stock, recurring gift, etc. - i.e. NOT Gift In-Kind) gift was received in the last 3 years and was over $1000. If I pull a query based on First Gift = Cash, then I miss out on anyone whose actual first gifts was in-kind, but, technically, their second (third, fourth, etc) is what I'm looking for. Also, it needs to be a constituent query because I need to merge/purge it with some other queries (unless someone knows some crazy magic to convert a gift query into a constituent query?).

I tried running a New Donor report (and creating a query based on it). It gave me the list of people who had a first monetary gift in the right range, but once I have the list in Query, I'm not sure how to filter by gift amount (again, it's all based on the first monetary gift).

Thoughts?

Hi Lisa

If you are not familiar with SQL you can use Export to further filter your query. You can make it a bit easier than Crystal suggested. You do not need to export multiple gifts.

I would create a constituent query of everyone who donated $1000+ in the last three years. Then use export to export the Constituent ID, select the First Gift Date but enter criteria on the first gift. This will export the date of the donor's first gift that meets the criteria, and not their first ever gift. This is where export is more useful than query for this scenario. Select only the Gift Types you want. Do not enter date criteria here or you will get their first gift in the period you enter, not their first ever.

Use Excel to remove those who made their first gift before the period you want, or whose only gift of $1000+ was a gift in kind - the first gift date will be blank for these. You then have a list of Constituent IDs in Excel and want to get them back into RE as a query.

There are a couple of ways to go with this. If there are less than 500 records you can paste the ID numbers into a query criteria Constituent ID one of. Scroll to the bottom of the list and paste into the bottom field. If there are only a few more than 500 you can use the OR operator and add another Constituent ID is one of for the next group of 500. See https://kb.blackbaud.com/articles/Article/41666 for more info.

If there are more than 500 records you can use Import to create a query of these donors. See https://kb.blackbaud.com/articles/Article/38639 for more info.

Christine Cooke 2 · May 2017

Lisa Wheeler:

I need help! I need to run a list of people whose first monetary (cash, pay-cash, stock, recurring gift, etc. - i.e. NOT Gift In-Kind) gift was received in the last 3 years and was over $1000. If I pull a query based on First Gift = Cash, then I miss out on anyone whose actual first gifts was in-kind, but, technically, their second (third, fourth, etc) is what I'm looking for. Also, it needs to be a constituent query because I need to merge/purge it with some other queries (unless someone knows some crazy magic to convert a gift query into a constituent query?).

I tried running a New Donor report (and creating a query based on it). It gave me the list of people who had a first monetary gift in the right range, but once I have the list in Query, I'm not sure how to filter by gift amount (again, it's all based on the first monetary gift).

Thoughts?

How about taking the First Donor Report query and exporting the gift info. That way you can sort it any way you want or filter or purge etc. that's what I would do.

P.S. you do mean that First Gift is ONE OF Cash, Pay-Cash, Pledge, Stock etc except InKind don't you? otherwise you will be missing some? Just checking that what you want vs. what you typed are the same/different

Help with Query/Report: First Monetary Gift Greater than $1k

Comments

Categories