Constituent reporting on individual donors and their giving histories is invaluable information for a non-profit organization. One such report requirement was posted on the Power Of Us Hub where the user wanted to find the increase or decrease in donation amounts by their donors. Here is the question:
My fundraising team wants to know how many donors increased their donation size from the last donation to the current one. I told them I could do Total Gifts This Year vs Total Gifts Last Year, but not Current Gift vs Previous Gift. Can you all think of a way to measure an increase (or decrease) in donation amount from one to the next?
Reading the responses it became obvious that this report is not possible to create with the native Salesforce reporting. However, with Apsona Multi-step Reporting, this report can be built in a few minutes. This report will require two query steps and one calculated step. Let’s take a look at how to build out such a report.
For the query steps, you first get the donors and filter them by campaign name or opportunity close date depending on the required criteria. Next, retrieve the opportunities to the donors, filter by close date and Role. Multi-step has the ability to get ranked data, which will need to be applied when querying for the opportunities. Limiting the 2 donations in descending order will give you the last two donation amounts to each donor. The steps will be woven together with the contact ID.
As there is no direct link between the Contacts (donors) and Opportunities (Donations), data for the two query steps will come from the Opportunity Contact Role (OCR) object. This object is best suited for this task, since it is treated as a stand-alone object in Apsona, and is also the junction between Donors and Donations. Therefore, you retrieve fields from the Donor (Contacts) as well as Donation objects (Opportunities) as they are both have lookup relationships from the OCR. Thus, you can retrieve the contact ID in both the steps and weave them together. Unlike in Salesforce reporting, you can use the same object more than once in Apsona Multi-step with different filter criteria and is ideal for this use case.
Now that you have the donors and their last two donations, you will need to find the difference between the amounts. For this you use a calculated step and add a formula as follows – {!Last 2 Donations 1.Amount} – {!Last 2 Donations 2.Amount}. Once you save and run the report will see the donor information, the last 2 donations amounts to the donor and the difference between the donation amounts.
When you run the report, you will also want to see the two donation amounts to each donor in a single row to make the comparison.
In a multi-step report, you have the option to display the output of a step as a sequence of blocks of columns instead of the usual row layout. Below is an image of the final report.
Donor Adam Dunn has donated $250,000 and $ 300, 000. You also see the difference in the Amount column as $50,000. The reason the amount is showing in parentheses is because the formula in the calculated steps is set to subtract Donation 1 from donation 2.
This shows how you can combine two important operations – transposing rows to columns, and using calculated values – to produce the result you want.
We hope you found this blog useful and try out Apsona Multi-step Reporting.
