How to calculate total expected recurring gift amount if we know the scheduled end date?

April Kulpa
April Kulpa
Tenth Anniversary Kudos 1 Name Dropper Facilitator 1
in Blackbaud CRM Developer Discussions

Hi -

Has anyone written code to calculate the total expected recurring gift amount when an end date is provided? I find it hard to believe it's not in query anywhere…

Thanx in advance.

Comments

  • Joe Huang
    Joe Huang
    Fifth Anniversary Facilitator 1

    Hello April,

    I think there are 2 ways to do this based on what approach you want to take:

    1. Option 1: the front end UI model already gives you the projection based on your start , end dates and frequency. This could be mimicked if you take deep dive in UI area
    2. Option 2 (which I took): sql approach. you need to get an appropriate count of payment times based on your revenue (1st payment) start date and revenue (last payment) end date, as well as frequency. more or less need to ensure the count matches what you see in the UI

    To get accurate data: You may also need to consider status of this recurring gift (active, cancelled), how many payments have been paid and how many are in the future, any write-offs, any revenue adjustments, any recurring gift amendments/adjustments made (frequency changes, amount changes), and any designation splits changes between start and end dates.

    To make it work in my org's data analytics and reporting, what I did was entirely create a new data model to calculate recurring gift with all scenarios taken into account, and validated with the system.

    Hope this helps.

    Joe

  • Spencer Lassen
    Spencer Lassen
    Tenth Anniversary Kudos 1 Name Dropper Participant

    I've built a custom query view that does something very similar to this which is it calculate all payments already received in the calendar year and then calculates any remaining recurring gift payments that we would expect in the given calendar year in order to get an expected sum total of giving. This was built out because we utilize recurring gifts to handle our employee giving campaign payroll deductions and wanted to be able to segment out donors that we expected to hit a certain threshold for specific stewardship.

    If you'd like I'm more than happy to chat or share what I've built out in case it can help you with that you're trying to do. ?

Categories