Constituent Data in a Gift Export

Pam Gealy

I have a request for daily gift data (constituent, Gift Type, Gift amount, Gift Type,).  Also requested (in the same row) is some consituent data (address, class year, fiscal YTD giving and last gift date).  I created a Gift Export (fed by a gift query) and everything works accept that LAST GIFT DATE.  (she really wants to know if the donor was a SYBUNT, LYBUNT...at the beginning of the FY), but is willing to have LAST GIFT DATE.  I can't figure out how to get the LAST GIFT DATE in a GIFT export.  I know that I can get it in a Constituent EXPORT, but my query is a gift query, so I think my export also needs to be a 'GIFT' export.


(I'm new to this...so kinda struggling!)

Alan French

The only thing I can come up with off the top of my head is to use a constituent summary to give you the total number of gifts this year, then another summary for last year's gifts etc. Doesn't give you the exact date but would indicate which year the gift was in, albeit across multiple columns rather than 1.

Suzanne Murray 2

Does your query have to be a gift query? Unless you have donors giving multiple gifts on the same day, I would think it's okay to use a constituent query and say "last gift date equals yesterday" or whichever date you prefer. Then you'll be able to use a constituent export.


And I only learned this recently, but it is possible to export the same field multiple times but with different settings - this was hugely helpful for me so I am passing it on just in case! https://kb.blackbaud.com/articles/Article/41839


Also - I definitely understand the requests for things in Excel, but how is the requester going to use these lists? I have set up a dashboard panel for my development users that will show a listing of this week's gifts (it's called "Recent Gifts" or something similar). Then I placed that on their homepages and reminded them to refresh, and that has helped them know exactly what has arrived, just as soon as it's entered into our system.

Marie Stark

I would also use a constituent export. Even if a donor has more than one gift per day, I think you could use the consituent export

Melissa S Graves

You do know that last gift date from a constituent export is going to give you the gift they just made, not the one that they made before that.


This is an issue I have had in the past and it is actully not as easy to solve as you would hope. There is no second to last gift to show the gift they made before the current one. 


In a consitutent export you can pull from "gifts" (instead of last gift) and get the last 2 (or 3) gifts they made if you do it carefully by choosing "order gifts by date" in descenging order. You may need to then test it to be sure you are getting what you want.


You could alternatively pull from summay information and get their total giving last FY, the FY before that, etc. to give a picture of their giving. 

Pam Gealy

Suzanne Murray:

Does your query have to be a gift query? Unless you have donors giving multiple gifts on the same day, I would think it's okay to use a constituent query and say "last gift date equals yesterday" or whichever date you prefer. Then you'll be able to use a constituent export.


And I only learned this recently, but it is possible to export the same field multiple times but with different settings - this was hugely helpful for me so I am passing it on just in case! https://kb.blackbaud.com/articles/Article/41839


Also - I definitely understand the requests for things in Excel, but how is the requester going to use these lists? I have set up a dashboard panel for my development users that will show a listing of this week's gifts (it's called "Recent Gifts" or something similar). Then I placed that on their homepages and reminded them to refresh, and that has helped them know exactly what has arrived, just as soon as it's entered into our system.

Thanks for the feedback.  The main reason that I'm doing this in Excel is to give her FY to date giving (using EXPORT).  But, I do like the dashboard idea.  She does also want to be able to run the 'daily' gift report for a range of dates, so I could end up with multiple gifts for the same constituent in one data set. 


One other twist that I might be overthinking.  I'm going off of 'date added' rather than gift date for my original gift query.  My thought is that we could have 2 batches with gifts with the same gift date.  If only one batch gets committed, and the other one is held up for some reason, a 'daily gift' query based on gift-date is only going to pick up the gifts from the first batch.  Whereas, if I go by 'date added', I'm sure to pick up everything that was committed on a certain day.  Does anyone else use 'date added' rather than gift date? 

Pam Gealy

Melissa Graves:

You do know that last gift date from a constituent export is going to give you the gift they just made, not the one that they made before that.


This is an issue I have had in the past and it is actully not as easy to solve as you would hope. There is no second to last gift to show the gift they made before the current one. 


In a consitutent export you can pull from "gifts" (instead of last gift) and get the last 2 (or 3) gifts they made if you do it carefully by choosing "order gifts by date" in descenging order. You may need to then test it to be sure you are getting what you want.


You could alternatively pull from summay information and get their total giving last FY, the FY before that, etc. to give a picture of their giving. 

Thanks, that's a good point.  I like the summary idea.  I could add this last FY giving and Lifetime Giving.  That would give her a picture of LYBUNT, SYBUNT or NEVER.

Carolyn Moatz

This is precisely the sort of thing I'd use "Date Added" for, especially if you're running this daily. The way our credit cards process, coupled with what our business office wants to see, has me entering gifts that came in last night with that yesterday as the gift date rather than today. Just make sure everyone knows that, so if someone wants to recreate it, or look at historic data, they pull on Date Added and not Gift Date, or know that there will be discrepencies.

Suzanne Murray 2

Right, Melissa, I didn't think of that! I am usually doing this export for a prospect list so the last gift date is what I need (and I do usually include summary info as well).


 

Alan French

Melissa Graves:

In a consitutent export you can pull from "gifts" (instead of last gift) and get the last 2 (or 3) gifts they made if you do it carefully by choosing "order gifts by date" in descenging order.

I don't have RE in front of me at the moment to check this, but wouldn't the export pick the first 2 gifts it came to (not necessarily chronological, probably depends on how the tables are indexed) and then order those 2 in descending order, rather than doing the sorting first and then selecting the gifts to include?