Reporting on Pledge Totals

Emily Rubino 2

Hi all! I am trying to pull a list of donors who have given a gift of $1000 or more within a certain time frame. I have been able to pull this information. However, I am struggling to understand how it works with those who have pledges. For example, Consituent A has a pledge of $1000, and is paying in monthly installments of $100. Once Constituent A completes their pledge payments, they have given a total of $1000. How would I make sure that Constituent A is pulled into my list of major donors, even though their gifts have been in $100 installments?


Hope this makes sense! I'm relatively new to pledges, so want to make sure I am understanding it correctly. Thanks in advance!


Emily

Ryan Hyde

Is it critical that you find ONLY constituents who have given a one time gift of $1,000+ OR pledged $1,000+ and are paying it off? Because my first instinct is to say that you may be needlessly excluding people who did not make a pledge of $1000 in your time frame but have given two gifts of $500 within that time frame. 


If it is critical to definte things the way you've already defined them, then it seems like you need a copy of different summary gift info pieces.


#1: Summary number of gifts > 0

• gift amount >= $1000
• Gift type one of (all cash types, including pay-cash in this case)

#2: OR (Summary number of gifts > 0

• gift amount >= $1000
• gift type = pledge

#3 AND Summary Amount of Gifts > $1000)

• Gift type = pay-cash

I was thinking you could just put a statement of "pledge balance = $0" in the #2 summary field at first, but then you'd get results for people who have asked you to write off some portion of their pledge. 


Oh, and of course put your time frames into each summary field.

Joanne Felci 2

Hi Emily,

So are you pulling your list of donors via a query (that is what I'd recommend if you are not currently doing that)


I would then do a Constituent query using a Summary of Gift Criteria where Ttoal Amount of Gifts is $1,000 or more and in the filter section of that you can specify date range, gift types (I'd exclude pledge gifts here - or pay cash...basically you do not want to double count the donation), etc.


Hope that helps


Joanne

Emily Rubino 2

Thanks for the speedy reply and instructions below, Ryan! Good point about excluding donors who may have given two $500 gifts! I mentioned this to my director and she now wants to pull: Donors who have made cumulative gifts of $1000 or more for a specific set of funds within a set time frame. She would also want to include $1000 and up pledges.


I notice that when I try to pull the criteria below, I get many duplicate gifts and donors with several separate entries. Any suggestions to what I might have missed? Thank you!

 

• gift amount >= $1000
• Gift type one of (all cash types, including pay-cash in this case)
• fund id = fund a, fund b, fund c, etc.

 

 

Ryan Hyde:

Is it critical that you find ONLY constituents who have given a one time gift of $1,000+ OR pledged $1,000+ and are paying it off? Because my first instinct is to say that you may be needlessly excluding people who did not make a pledge of $1000 in your time frame but have given two gifts of $500 within that time frame. 


If it is critical to definte things the way you've already defined them, then it seems like you need a copy of different summary gift info pieces.


#1: Summary number of gifts > 0

• gift amount >= $1000
• Gift type one of (all cash types, including pay-cash in this case)

#2: OR (Summary number of gifts > 0

• gift amount >= $1000
• gift type = pledge

#3 AND Summary Amount of Gifts > $1000)

• Gift type = pay-cash

I was thinking you could just put a statement of "pledge balance = $0" in the #2 summary field at first, but then you'd get results for people who have asked you to write off some portion of their pledge. 


Oh, and of course put your time frames into each summary field.

 

 

Emily Rubino 2

Hi Joanne,


Thanks for your reply! I am working through query at the moment. Good point about excluding pay cash so I don't get duplicates. I appreciate your help! Thanks!

Joanne Felci:

Hi Emily,

So are you pulling your list of donors via a query (that is what I'd recommend if you are not currently doing that)


I would then do a Constituent query using a Summary of Gift Criteria where Ttoal Amount of Gifts is $1,000 or more and in the filter section of that you can specify date range, gift types (I'd exclude pledge gifts here - or pay cash...basically you do not want to double count the donation), etc.


Hope that helps


Joanne

 

Joanne Felci 2

Emily Rubino:

Thanks for the speedy reply and instructions below, Ryan! Good point about excluding donors who may have given two $500 gifts! I mentioned this to my director and she now wants to pull: Donors who have made cumulative gifts of $1000 or more for a specific set of funds within a set time frame. She would also want to include $1000 and up pledges.


I notice that when I try to pull the criteria below, I get many duplicate gifts and donors with several separate entries. Any suggestions to what I might have missed? Thank you!

 

• gift amount >= $1000
• Gift type one of (all cash types, including pay-cash in this case)
• fund id = fund a, fund b, fund c, etc.

 

Emily - are you seeing these duplicates in the Results part of the query?  That is because there are several gifts on a donors record that lead to the $1,000+ criteria.


To create the list you should tie the query to an export and use the export as your list (Export from the export tab not export of the query...that always gets so confusing 


Hope that helps!

Joanne

Ryan Hyde

To create the list you should tie the query to an export and use the export as your list (Export from the export tab not export of the query...that always gets so confusing 

 

That's certainly true for best practices, but if she's doing this particular query correctly, there shouldn't be duplicates in the results.


First, make sure you're using Total Amount of Gifts in this scenario, not Total Number of Gifts. If that one summary info field is the only thing in your criteria, you shouldn't get any duplicates in the results tab. 


You've got the >=$1k part right, but everything else you're looking for (date range, funds, gift types) should be in the filter tab of the Total Amount of Gifts window, not from the gifts tree in the general criteria tab of the query. From what you described, I think that's where you're going wrong and why you're seeing so many duplicates. Do correct me if I'm wrong though.


Once you've got that squared, consider if you want to also include soft credits. If you do (and if you don't know how to add them), go to Tools > Query Options > Gift Processing, and you'll find the soft credit options right there.


ALL THAT SAID, you should still filter this through export, especially if you want only one line per household. Any time you want a truly clean list, it's best to use Export, not the results tab. Results tab is typically just for quick and dirty lists.

 

Joanne Felci 2

Ryan Hyde:

To create the list you should tie the query to an export and use the export as your list (Export from the export tab not export of the query...that always gets so confusing 

 

That's certainly true for best practices, but if she's doing this particular query correctly, there shouldn't be duplicates in the results.


First, make sure you're using Total Amount of Gifts in this scenario, not Total Number of Gifts. If that one summary info field is the only thing in your criteria, you shouldn't get any duplicates in the results tab. 


You've got the >=$1k part right, but everything else you're looking for (date range, funds, gift types) should be in the filter tab of the Total Amount of Gifts window, not from the gifts tree in the general criteria tab of the query. From what you described, I think that's where you're going wrong and why you're seeing so many duplicates. Do correct me if I'm wrong though.


Once you've got that squared, consider if you want to also include soft credits. If you do (and if you don't know how to add them), go to Tools > Query Options > Gift Processing, and you'll find the soft credit options right there.


ALL THAT SAID, you should still filter this through export, especially if you want only one line per household. Any time you want a truly clean list, it's best to use Export, not the results tab. Results tab is typically just for quick and dirty lists.

 

 

wouldn't it depend on what she has in her output of the export - for example if she is looking for individual gifts, there will be dupes in the output.  But I see what you mean, if she uses the Summary of Total Gift Amount and just outputs name it should only appear once


I was SHOCKED that you were ok using the output from a query instead of an export (ok I know you didn't really say that...only trying to help with query criteria)

Joanne Felci 2

 

 

wouldn't it depend on what she has in her output of the export - for example if she is looking for individual gifts, there will be dupes in the output.  But I see what you mean, if she uses the Summary of Total Gift Amount and just outputs name it should only appear once


I was SHOCKED that you were ok using the output from a query instead of an export (ok I know you didn't really say that...only trying to help with query criteria)

 

oops my bad...I meant output of QUERY (see you had me so shocked I don't know what I'm saying...)  ;-)

Ryan Hyde

OOHhhh yes you're absolutely right. It's early yet :P


So Emily, what's in your output exactly? You can output stuff like name, primary zip code, birth date, and the summary field you programmed in the Criteria tab all just fine without getting duplicates, but if you're outputting any discreet gift info, you're almost certainly going to get dupes. 


As for using the output field, honestly I use it quite a lot, but it takes a lot of experience knowing exactly what will and what will not cause dupes in a particular database (which is why Export is best practices - totally fool proof when it comes to dupe results). But in this use case, I would certainly end the process with an export because I'm providing a list to another person for official use. 


 

Cathleen Mai

Emily Rubino:

Hi all! I am trying to pull a list of donors who have given a gift of $1000 or more within a certain time frame. I have been able to pull this information. However, I am struggling to understand how it works with those who have pledges. For example, Consituent A has a pledge of $1000, and is paying in monthly installments of $100. Once Constituent A completes their pledge payments, they have given a total of $1000. How would I make sure that Constituent A is pulled into my list of major donors, even though their gifts have been in $100 installments?


Hope this makes sense! I'm relatively new to pledges, so want to make sure I am understanding it correctly. Thanks in advance!


Emily

Good suggestions above and that's the way I would do it. That beeing said, has your org by any chance tables already set up under Configuration/ Tables/ Donor Categories that fit the minimum $1000 criteria? You could use them - under Reports, create a Donor category report, filter the Date/Funds/ Gift types etc.and on the last tab under Details move all the categories equal or above $1000 to the right side. This should give you a nicely formatted list, without duplicates. You can also get an output query of your results if you check the box on tab1.

Ryan Hyde

Cathleen Mai:

Emily Rubino:

Hi all! I am trying to pull a list of donors who have given a gift of $1000 or more within a certain time frame. I have been able to pull this information. However, I am struggling to understand how it works with those who have pledges. For example, Consituent A has a pledge of $1000, and is paying in monthly installments of $100. Once Constituent A completes their pledge payments, they have given a total of $1000. How would I make sure that Constituent A is pulled into my list of major donors, even though their gifts have been in $100 installments?


Hope this makes sense! I'm relatively new to pledges, so want to make sure I am understanding it correctly. Thanks in advance!


Emily

Good suggestions above and that's the way I would do it. That beeing said, has your org by any chance tables already set up under Configuration/ Tables/ Donor Categories that fit the minimum $1000 criteria? You could use them - under Reports, create a Donor category report, filter the Date/Funds/ Gift types etc.and on the last tab under Details move all the categories equal or above $1000 to the right side. This should give you a nicely formatted list, without duplicates. You can also get an output query of your results if you check the box on tab1.

 

Cathleen, you taught me something new! I've never used this report OR donor categories before (though we have some set up by our previous DBA). 


Thank you!

Cathleen Mai

Ryan Hyde:

Cathleen Mai:

Emily Rubino:

Hi all! I am trying to pull a list of donors who have given a gift of $1000 or more within a certain time frame. I have been able to pull this information. However, I am struggling to understand how it works with those who have pledges. For example, Consituent A has a pledge of $1000, and is paying in monthly installments of $100. Once Constituent A completes their pledge payments, they have given a total of $1000. How would I make sure that Constituent A is pulled into my list of major donors, even though their gifts have been in $100 installments?


Hope this makes sense! I'm relatively new to pledges, so want to make sure I am understanding it correctly. Thanks in advance!


Emily

Good suggestions above and that's the way I would do it. That beeing said, has your org by any chance tables already set up under Configuration/ Tables/ Donor Categories that fit the minimum $1000 criteria? You could use them - under Reports, create a Donor category report, filter the Date/Funds/ Gift types etc.and on the last tab under Details move all the categories equal or above $1000 to the right side. This should give you a nicely formatted list, without duplicates. You can also get an output query of your results if you check the box on tab1.

 

Cathleen, you taught me something new! I've never used this report OR donor categories before (though we have some set up by our previous DBA). 


Thank you!

Great. I thought I saw the little lightbulb in your profile picture shine even brighter than usual. Learned a thing or two from your postings before. ;-)