Query/Export for Scholarship Reporting (Stewardship)

Chris Zello 2

We are wondering if there are other academic institutions who produce a stewardship scholarship report to send the donors? This is not an acknowledgment of their gift (we're doing that already). The point is to let the donors know what their scholarship has provided.

Is there a way to query on a gift total of $500 or more to a specific Fund Category, but the export output needs to include name, address, soft-credit recipient (and ConsID), listing of the individual gifts including the Fund Description to the Fund Category that met the $500+ criteria. It is possible that a constituent will give to multiple scholarships.

The reasoning for soft-credits is to know the individual who gave through a donor advised fund. If the soft-creditee is the spouse then we wouldn't need that information.

Does anyone do something similar to this? If a discussion is too lengthy to post, simply reply, and we'll find a time to talk. Thanks.

Judy Spigarelli

At my prior org we did send a Scholarship Stewardship Report to let them know the impact of their scholarship. As you said, it was not an acknowledgment. We had two versions: one for endowed scholarships and one for annual scholarships. I'm looking to implement them at my current org as well. Happy to chat with you!

You can group your constituents by summary information of Total Amount of Gifts based on Fund Category. Then I would use the Export Module, not Query Output, for the fields you need, but that will not fit all the data you want.

You may have to do a gift query and gift export and then look for duplicate donors. It will give you the fields you need. Let me think on this for a bit - I bet there's a better solution.

Karen Diener 2

I haven't had a lot of time to think this through carefully, but I have also done this in the past. My needs were slightly different and required two separate exports.

My first thought is to set up the query as Judy suggested. You can use summaries and would need a summary for each Fund category. Total gift amount greater than or equal to $500, to the specific Fund category, during the date range you specify, and the gift types you specify. Make sure you use OR between each of the summary fields, and I think you would also need to group them using parentheses. Also make sure that your query is set to select both hard and soft credit donors.

In your Export, you would need to set up all of those gift summary fields again, and make sure you are pulling both hard and soft credited gifts. This will give you a list of every donor, and how much they gave to each Fund category. You should be able to remove any DAFs from your final file - the soft credited gifts for those would be in the total for the appropriate donor.

I don't know how many Fund categories you are working with, but if this is (or will be) a process to be repeated every year, it will be worth investing the time to set it up. You would only need to change dates each year.

Hopefully I haven't misunderstood your question, and this is helpful!

Karen

Marla Hager

We do this, but with the scholarships as constituents and relationships to funds, donors, and recipients. The endowment info (spend amounts and such) is imported every fall as dated attributes onto the scholarship record, and then depending on whether we're exporting from the Donor side or the Scholarship side, we can produce letters and market statements. We didn't want to pull from the funds themselves, because the business office tracks all the endowment info and our RE isn't linked to anywhere else, and thus need to import info on gains/losses and such. We also found that fund info could be difficult to pull through to the donors, if we were exporting from the constituent side of things.