Bulk updating pledges after a certain period of time.

Luisa Rampin

Hi

YMCA creates new pledges to record the details of donors that have set up recuring debit/credit card payments or Direct Debits to repeatedly give to new appeals.

When these pledges are set up, we code them to a code ID for Acquisition, but if the pledges still running after one year, we want them recoded to Retention. We have a global update to change the pledges coding, but if the pledge has been amended in some way during the year the global update fails. Currently we than have to manually update these pledges, and most of these pledges have been updated and need manual changing, which is time-consuming and can lead to keying errors.

Can anyone think of a way to bulk update records that have been amended in the past? Can this be done by using SQL to recode the pledges after a year?

If anyone has ideas, I would be very grateful.😁

Luisa

Karen Diener

I know this isn't exactly what you're asking, but I really question the choice to add a pledge in the first place. Are these actual pledges or recurring gifts? What do you mean by "coding them for Acquisition" and then "changing the code to Retention"? Is that an Appeal or Package, or some other code?

I often think of nouns and adjectives when it comes to database management too. Is "acquisition" describing the gift or the constituent? I think the first gift could indicate that it was from an acquisition - if it TRULY was a new donor acquired from intentional outreach. But retention sounds to me like a constituent-level description. The constituent was retained. That's a philosophical discussion of course, but I'm just trying to explain my rationale.

You definitely cannot globally change gifts if there is an adjustment or amendment. Instead of a fix that might require SQL or Power Automate, I would challenge the need for this level of coding in the first place. It sounds like the organization is overcomplicating things, but also understand that you may have a valid reason for this setup.

Jeannie Goings

I agree with Karen; the current process at your org is overcomplicated. Retention can be observed by giving history; why label it? Acquisition = intentional outreach can also be gleaned by a thoughtful appeal structure.

Luisa Rampin

Thanks for the help here, sorry that I'm late in getting back to you, but I was a little poorly. It could be that the system is a bit overcrowded with logic as we've been using it for a long while and adapting it to changes on the hoof.

How we currently have this set up if that new regular givers/pledgers (we use these terms interchangeably, so I may be giving an incorrect name) is that new regular payments are coded to an appeal and then a fund that are all part of the Acquisition Activity Campaign coding so that we can have an overview assessment of how much new DDs/RCPs bring in overall before breaking it down to particular appeals. But if the regular donation continues for more then a year we want the income to be coded to our Retention Activity Campaign coding so that we can easily report on long term regular donors.

I do think that this is something that might be better done by the tracking the lifetimes of regular payments, but I think this has been decided on to make the reporting easier for non-data people. I also thought that SQL might be the way forward, so I'll mention it to management.

Maybe we should just leave the payment data alone and add a custom field of 'new ongoing payer' that can be ended and replaced with a custom field of 'long term ongoing payer' far more easily, with dates clearly obvious?

Thanks for your help

Luisa