Query to find Multi-Year donors who have no balance

Chris Zello 2

Is there a way to find multi-year donors after they have paid off their pledge?

For example, if a constituent pledges $5,000 to be paid over 5 years, is there a way find these donors? I know there would not be a pledge balance.

Is there a way to count installments ≥ 2 and with a zero pledge balance?

Or is the only option manual tracking with an Attribute?

Alan French

Could you use the field ‘Number of Instalments Scheduled’ to do that? I've never used it myself but it looks like it gives you the total number of instalments on the pledge, so you could query for gifts with 2+ instalments and a pledge balance of $0

Karen Diener 2

Wouldn't pledge balance = 0 work? That's what I have always used to see who has an open pledge, but maybe you're looking to find something more refined that I'm missing.

Karen

Christine Cooke 2

I think you're going to have to experiment a bit.

Definitely Pledge Balance = $0

I question the Number of Installments because some folks pay it off in one installment so asking for 2+ would exclude those folks.

Pledge Balance zero and any installments?

Or Pledge Balance zero and type of Pledge Schedule? monthly, quarterly, annual etc.?

Pledge Balance is really the main thing

Alan French

Hmm yes good point Christine, although if they pay it off in one year would they still be a multi-year donor then? Or would they be if they pledged in one year but fulfilled in another? We don't really use that term here, I'm not sure exactly how it's defined.

You've made me think a bit more about my previous suggestion and it would include donors who make a pledge with multiple instalments in the same year and it shouldn't - maybe just ignore me!

Alan French

I've been giving this some more thought and I can't help but feel that you'll need to use a constituent query with summary fields, otherwise if you're looking at individual gifts then you'll end up with donors in your results who have at least one pledge that fits your criteria but may have another more recent pledge that still has a balance (I'm assuming you don't want these because the purpose of identifying them is to solicit them for another pledge? Although I know what they say about assumptions! ?)

What about a constituent query with two summary fields for total gift amount:

• [total amount of gifts] > $0 where:
  • Gift Type = Pledge
  • Balance = $0
  • Instalments Starting On < [start of current year]
  • Instalments Ending on >= [start of current year] (this assumes you're only interested in people who paid off their pledge this year, not in a previous year)

• [total amount of gifts] = $0 where:
  • Gift Type = Pledge
  • Balance > $0

Unfortunately I think this will include gifts that have been written off, rather than paid off, but it's the best solution I can think of at the moment.