Reporting on Gifts by Constituency

Carrie Kran · August 2015

I need to create a new weekly report for our Director that shows the amount received by constituency this year, comparing it to previous years. The Comparisons and Summaries Report seems ideal for this, as the output is exactly what I need and it is simple to run.

We are a student exchange program and our constituency codes are similar to a school or alumni organization: parents, alumni, hosts parents, volunteers, etc. They are fairly clean, and while gift constituency is used (automatically entered when entering gifts via batch), we haven’t been consistent in ensuring that field is clean or correct.

My issue with the canned report lies in gift entry and soft crediting:

Currently, if an alumnus gives through a family foundation, we hard credit the foundation and soft credit the alumnus. This means that when I pull the C&S Report by either constituency or gift constituency the gift will show on the report under foundation. I could instead change the report criteria to select only soft credit recipients, but we have some gifts that are soft credited to more than one recipient (alumni spouses with their own records, for example) so gifts will be double-counted and totals incorrect.

Has anyone else dealt with this? The solutions I have come up with so far are: using the gift constituency differently: if a gift from an alumnus comes through a foundation, code the gift constituency as alumni, for example, but leave the constituent code as foundation.

Or simply pulling the report by soft credits, knowing the totals aren’t completely correct.

JoAnn Strommen · August 2015

Coding the gift constituency as 'alumni' sounds like a good way to work around. WIth the record still having a constituency code of foundation, you should be able to pull any reports needed on foundation giving. I could be missing something but it sounds a lot cleaner than manually filtering out SC.

Another option would be to use the gift code field if you aren't using it for anything else. I use it as a field in batch like you do gift constituency and can run a report specifically for our execs that pulls $ amounts in the codes they want. The gift code for most gifts is the same as the gift constituency but not all when I enter these. This has worked well for me for quite a few years.

Jen Claudy · August 2015

We use the Gift Constituency and had a similar issue. I needed to pull a report on Board Giving, including gifts from Foundations and even Companies where we got the gift only because of the board member (i.e. the board member is an employee, so they get credit...including SC in RE) or where they own their own business and gave via company check instead of personal check (again, they also get SC in RE). What I ended up doing is putting a second Constituent Code on those records of "Board Soft Credit" and then it is available on the Gift Record (I assume Batch works the same way...the available options for Gift Constituency are those assigned on the Constituent Record).

The Gift Constituency is automatically populated using the Primary Constituent Code from the Constituent Record (whichever is listed first). That Primary Constituent Code is a required field for us, which means every Constituent Record has at least one. Board members and employees have those codes as primary when they are active in those roles. In the SC situation, I put the "Board SC" as the primary code. This way, we don't have to remember to switch it when entering a gift.

In your situation, and since it sounds like you routinely use Batch, I would leave the SC option in a lower spot in the grid, and then select it as the Gift Constituency when doing gift entry. It sounds like you know when this needs to happen, especially if you're entering SCs at the same time. This way, you can run that report on the Constituent Code, which will put them under Foundation, or the Gift Constituent Code, which will put them under Alumni. Gives you options without the potential double-counting with SC records.

John Heizer · August 2015

I have a Query that I use for in situations like this that helps a lot. It pulls only the Head of Household. I use the query as the source for the report (or as the source for another query that might further filter the Constituents being reported on) and tell the report to use allocate Soft Credits to "Both - Use Amount in Grid." It does a fairly good job of accounting for Soft Credits from family foundations without double-counting Soft Credits with Spouses.

And everyone should add their votes here to so that we can get this HUGE issue fixed in a future release!

http://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/2990120-fix-spouse-soft-credits

Christina Swiszcz · August 2015

I also struggle with reporting in these situations. We have many trustees and parents who give through DAFs and have to do a lot of manual work to figure out who they are in order to include them on our Constituency Giving reports.

I'm confused about the suggestion to use Gift Constituency Code. If the gift was hard credited to a foundation, how would you add the gift constituency code of "alumni"? As far as I can tell, the only options in that field are the constituency codes on that record, and adding an "alumni" constituency code on a foundation record wouldn't really make sense. I do understand Jennifer's comment about adding an additional constituencey code of "Board SC" or something similar. But I'm not sure that the data entry folks would be consistent with this.

John's query sounds like it will work, I'll give it a try. Thanks!

Christine Cooke 2 · August 2015

I work in school environments and this is always a challenge. I have come to accept that there are actually two issues that are faced, depending on the school leadership and the type of stats they want to see. Often with schools, they want to see participations rates. And in that case, if a record has more than one constit code, you want it to count in both places so that each Constit Group gets credit for participation.

But -- then you get the reports where they want to see giving by Constituency. ugh! There are two ways to go -- one will add up properly and the other will not. And someone always adds the column and then says "this total does not match the total on the other page..." And this comes from the overlapping of records having multiple constit codes (one of the checkboxes). So if you must have the total add up and match another page, then you must not overlap the codes (an option in canned reporting) and this means you must set a hierarchy of constituent codes. and SC both with distribution on grid, just as John said. If you want participation rates by constit code, then overlap the codes.

In order for this type of reporting to be 'clean' or accurate as possible, you will need to dig down and tidy up some Gift Constit Codes. I just did that on the data ... otherwise if you have Current Board and Former Board and the hierarchy was not set properly, when you pull on Gift Constit Code, they numbers will not fall in the right place.

Another thing you can do -- is usually the DAFs are not the same type of Foundation that disburses grants from proposals. You could give then a Constit Code of Donor Advised Foundation, that would help you cull out which have the SC issue.

Bob Wiebe · August 2015

John Heizer:

I have a Query that I use for in situations like this that helps a lot. It pulls only the Head of Household. I use the query as the source for the report (or as the source for another query that might further filter the Constituents being reported on) and tell the report to use allocate Soft Credits to "Both - Use Amount in Grid." It does a fairly good job of accounting for Soft Credits from family foundations without double-counting Soft Credits with Spouses.

And everyone should add their votes here to so that we can get this HUGE issue fixed in a future release!

http://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/2990120-fix-spouse-soft-credits

This is a helpful query. I tried it, and I see that it works. I am trying to confirm WHY it works. I at first thought that it would pull up spouses, as I thought they were the ones marked yes this is the spouse but not this is not the head of household. I think what is happening is that those two messages, although they are viewed by clicking the spouse button on bio 1, are actually on the main consitutents record, i.e. the head of household's. Does that make sense?

Jen Claudy · August 2015

Christina Swiszcz:

I also struggle with reporting in these situations. We have many trustees and parents who give through DAFs and have to do a lot of manual work to figure out who they are in order to include them on our Constituency Giving reports.

I'm confused about the suggestion to use Gift Constituency Code. If the gift was hard credited to a foundation, how would you add the gift constituency code of "alumni"? As far as I can tell, the only options in that field are the constituency codes on that record, and adding an "alumni" constituency code on a foundation record wouldn't really make sense. I do understand Jennifer's comment about adding an additional constituencey code of "Board SC" or something similar. But I'm not sure that the data entry folks would be consistent with this.

John's query sounds like it will work, I'll give it a try. Thanks!

You can, when entering one of these gifts, add the needed Constituent Code (i.e. "Alumni") to the foundation's record and save. Then add your gift, selecting that code as the Gift Constituency. Save the gift, and then remove to Constituent Code from Org 2 and save the Constituent record. The Gift Constituency will remain as "Alumni".

Katherine Fritz · August 2015

I second the motion of using the Gift Code (appears on the Miscellaneous tab just about the gift constituency code) to handle these situations. This becomes essential for our reporting because our higher-ups want to break out event giving separately from "individual" giving. Prior to using the Gift Code, this involved a tremendous amount of manual work, as you might imagine. We use these Gift Codes to accomplish this specific reporting need: Board, Major Donor, Individual, Corporation, Foundation, Event, Planned Giving. So, for instance, a gift from a board member's family foundation would receive a gift code of Board and a gift constituency code of Family Foundation. A gift from a major donor for a gala sponsorship would get a gift code of Event, and a gift constituency code of Major Donor. A gift from a DAF in response to a direct mail appeal would get a gift code of Individual and a gift constituency code of Donor Advised Fund. This allows us to pull reports showing which constituent type drove the gift while not disturbing the constituent code or the gift constituent code. Hope this helps.

Katie

Katherine Fritz, bCRE

Database Coordinator

Pennsylvania SPCA

350 E. Erie Avenue | Philadelphia, PA 19134

Phone: 215.426.6300 x228

E-mail: kfritz@pspca.org | Website: www.pspca.org

With a 97% live release rate the Pennsylvania SPCA is the largest no-kill animal welfare organization in the state.

Make a donation today to save a life.

John Heizer · August 2015

Bob Wiebe:

John Heizer:

I have a Query that I use for in situations like this that helps a lot. It pulls only the Head of Household. I use the query as the source for the report (or as the source for another query that might further filter the Constituents being reported on) and tell the report to use allocate Soft Credits to "Both - Use Amount in Grid." It does a fairly good job of accounting for Soft Credits from family foundations without double-counting Soft Credits with Spouses.

And everyone should add their votes here to so that we can get this HUGE issue fixed in a future release!

http://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/2990120-fix-spouse-soft-credits

This is a helpful query. I tried it, and I see that it works. I am trying to confirm WHY it works. I at first thought that it would pull up spouses, as I thought they were the ones marked yes this is the spouse but not this is not the head of household. I think what is happening is that those two messages, although they are viewed by clicking the spouse button on bio 1, are actually on the main consitutents record, i.e. the head of household's. Does that make sense?

Yeah, it's not easy to wrap your head around this one because there are no "Spouse" or "Head of Household" flags available for the actual Constituent your looking at in the Query. Those flags only exist on a Relationship record, not on a Constituent record. So we have to use a process of elimination to determine if the Constituent being examined is who we want.

The Query performs two tests on each Constituent record. First; does the Constituent have a linked Spouse (Spouse Import ID = blank)? Second it checks ALL the Individual Relationships for Constituent to see if any of those Relationships are marked as a Spouse (This Individual Is The Spouse = Yes) AND if that Relationship is not the HoH (This Individual Is The Head Of Household = No).

If there's no linked Spouse then we know that the Constituent being examined must be the HoH. Or, if there is a Relationship that is the Spouse but not the HoH then we know that the Constituent being examined must be the HoH.

Reporting on Gifts by Constituency

Comments

Categories