Reporting on no giving to specific funds by specific constituencies

Jessica Smith 4 · January 2016

Hi, we are trying to gather numbers on specific constituent codes who have not given to certain funds during this fiscal year. We are a school and need to let our Alumni committee and Parents committee know how many in a certain group have NOT given to one of the 6 funds that make up our Annual Unrestricted Fund. They may have given to a restricted fund, Baseball, Scholarship etc, but if they have not given an UR donation, we want to know. The committees want a percentage of parents and alumni who have given to the Annual Fund. And then we need to run a list of names with contact info so the committees can reach out to those folks who have not given and encourage them. And we definitely want to have it accurate so we don't ask for money from someone who did in fact give to the UR fund already.
My original plan was to query on the code, the funds that comprise our Annual Fund, and gift date not between the dates of this fiscal year and then use Export (not an export from query but the real one) to run a spreadsheet on the names and contact info.
Long story short, that did not work the way we wanted and the numbers were so off every time I'd try a different variation I was not confident in using them. I have run so many queries, reports and exports I have lost track so I decided I needed help.

So I have queries set up for each code we need info from, Freshmen parent, sophomore parent etc. I also have queries set up for our 2 alumni groups. In theory, these queries should tell me the total number in those groups so I can divide the number who gave into and tell them X% have given and, conversely, divide the number who have NOT given and say X% of people have not given to the Annual Fund. I only have criteria as Constituent Code equals and one code only, no Output fields so the number should be a true value on the number of people with that code.

From there I have run the Non-Contributors Report using the query, date set to this FY, soft-crediting to both spouses, MG credit to MG company and on the Filter I have selected to include only the 6 Fund IDs that make up the UR fund. I have a Const query set to generate when I run the report and want to do an Export using that query to get my contact info. Is this the best way to go and have I forgotten to account for anything? I have also tried running the Gift Detail and Summary report to get a list of who gave to only the non-Annual funds to get the query I need. On some I get the same number of non-givers as with the Non-Contributors report but on the larger constituencies it tends to vary by 5-15 people.

Sorry for the long post but I have been working on this for what feels like forever and we are getting to the point we need to be able to report to our committees. Thanks in advance!

Stephanie Ulmer · January 2016

I hope I am understanding your questions correctly. But have your tried to run the gift detail and summary report with summarizing by constituency code? You can add the fund you are looking for specifically as the filter.

I would do the same thing with the query to see how many of each code there are in order to figure out percentages.

Joshua Bekerman bCLO bCRE · January 2016

Not sure if I understand, but I would approach this starting with one query to find your entire population (parents and alumni?). Then another query containing everyone that has given to the Annual Unrestricted Fund. Then using the SUB operator merge the two queries and that should leave you with those that have not given to the AUF.

I'll start with this and see if I'm understanding correctly. For me it's always easier to keep it positive (i.e. donated, attended, etc.) as opposed to trying to find constituents that have not done something.

Ryan Hyde · January 2016

It sounds like your first instinct was almost right, but you were going about looking for the gift information in the wrong way.

Here's how I would go about it (assuming I'm understading all of your caveats):

Make one constituent query with these parameters

Constiuency Code = ONE OF [list each code you want]

Summary Information > Total Number of Gifts:

# of gifts = 0

Fund = ONE OF [list each fund]

Gift Date Between [range]

Gift Type = ONE OF [cash, pay-cash, recurring gift pay-cash . . . basically, looking for cash gifts only, unless your board wants to eliminate people who have made a pledge, assuming there are any]

It's this summary information that is the crucial element. Simply adding gift parameters to a constituent query will give you unexpected and unreliable results. Summary info for gifts, on the other hand, basically pipes a gift query into a constituent query, which is exactly what you need to get the results you want.

Then you can pipe this query into your report. If you want to differentiate the report by category, then you can limit the report itself by constituency code. Much easier to have one query creating multiple reports than having a separate query for each report.

Again, this is assuming I totally understand what you're trying to do and what your dilemma actually is. If this doesn't help, I'd suggest opening a case with RE chat support and letting one of their folks walk you through what you need to do.

Joshua Bekerman bCLO bCRE · January 2016

Sorry, that should be:

"then using the SUB operator merge the two queries, subtracting the second query (those that have given) from the first (entire population)"

Jessica Smith 4 · January 2016

Ryan Hyde:

Make one constituent query with these parameters

Constiuency Code = ONE OF [list each code you want]

Summary Information > Total Number of Gifts:

# of gifts = 0

Fund = ONE OF [list each fund]

Gift Date Between [range]

Gift Type = ONE OF [cash, pay-cash, recurring gift pay-cash . . . basically, looking for cash gifts only, unless your board wants to eliminate people who have made a pledge, assuming there are any]

Hi Ryan,

Thanks for the input. I tried the query you suggested and I got a return of 0, which is telling me we have had no parent donations to any of those funds and I know that's wrong. I attached a screenshot of what I did. Maybe I misunderstood what you said.

Ryan Hyde · January 2016

Nope, you're still missing the key part of the query here. You should absolutely not go into the Gift critera tree in your criteria tab. Look all the way at the bottom and find the criteria tree called Summary Infomration (or something along those lines - not logged into RE right now). In there, find "Summary for Gift," then "total number of gifts."

You'll get a window with two tabs, one called "operation" and one called "crteria" (or thereabouts). In the first tab, select Equals and enter 0 as the operator value. In the second tab, select all of your gift parameters, including gift date, fund, amount, gift type, whatever. Then when you're done there, hit OK.

Again, you should absolutely NOT grab individual gift parameters one at a time from within the Criteria tab of the your constuent query. This way lay only madness. Using the Summary Information criteria is the only way to get the results you're looking for.

Jessica Smith 4 · January 2016

Ah ha! I gotcha now. I see where I ran amuck. So I corrected it and got an actual number, which is awesome. But I decided to test it against my current foray into Non-Contributors reports and see if I got the same number and I did not. I changed the Const code to one of my alumni since it's a smaller quantity than all my parents together. When I run the Non-Contributor report using All Records, this FY, SC to spouse, MG to company and Filter on the same Const Code and my 6 funds I come out with 2,335 non donors and with the query I get 2,339. It seems to be the soft credit aspect. If I re-run the Non-Contrib report crediting only donor it goes to 2,339. We auto SC spouses gifts so if Mr. gave, Mrs. also shows a gift. I guess I need the query to look at SC's too but there doesn't seem to be a filter for that under the Soft Credits, it only looks for info pertaining to the name.

Ryan Hyde · January 2016

You can tell the query to count SC to the constituent profile by going to Tools in the top bar of the query, and then Query Options. In there, you should see a tab called "gift processing," and you should see the options about how to handle soft credits.

However, the matching numbers when SCs are not included tell me that your report is set up correctly, so at this point, maybe it's just better to rely on the report itself. But it's worth knowing these tricks about queries anyway - summary information is how you start making really precise queries, and knowing how to tell your query whether or not to count soft credits can make a world of difference.

Either way, I'm glad I was able to help

Jessica Smith 4 · January 2016

Jessica Smith:

Ah ha! I gotcha now. I see where I ran amuck. So I corrected it and got an actual number, which is awesome. But I decided to test it against my current foray into Non-Contributors reports and see if I got the same number and I did not. I changed the Const code to one of my alumni since it's a smaller quantity than all my parents together. When I run the Non-Contributor report using All Records, this FY, SC to spouse, MG to company and Filter on the same Const Code and my 6 funds I come out with 2,335 non donors and with the query I get 2,339. It seems to be the soft credit aspect. If I re-run the Non-Contrib report crediting only donor it goes to 2,339. We auto SC spouses gifts so if Mr. gave, Mrs. also shows a gift. I guess I need the query to look at SC's too but there doesn't seem to be a filter for that under the Soft Credits, it only looks for info pertaining to the name.

Ryan, that was exactly right on the Gift Processing tab. I forget that Query options are up there. When I do remember they are handy to have. Your advice on using the summaries I have to remember. It sounds like I have run alot of useless queries by not using that Summary Information group.

Thanks again for your help!

Christine Cooke 2 · January 2016

If you are reporting on separate classes, as you say you are, in regards to Parents, then you have to have a way to identify them, because you can get a duplicate with parents that have more than one student enrolled, or if they have had more than one student enrolled (if you only use Parent as a code vs. Current Parent and Alum Parent).

That may be where you are off by 5-15.

Jessica Smith 4 · January 2016

Hi Christine,

We have thought of that on the parents and we are still working on a solution to that issue. We are a small enough school we can possibly manually filter it after we have our lists but we are hoping to make it a simplified process.

Right now we have Const Codes of Freshman parent, Sophomore parent, Junior Parent, and Senior Parent. I am hoping to figure out a way to merge the queries together and get our numbers but I am still figuring out the whole merge process.

Any suggestions on that front would be appreciated!

Ryan's suggestions worked great on our 2 Alumni groups and I think we are confident in giving our numbers to the Alumni committee so now I am on to the parents!

Reporting on no giving to specific funds by specific constituencies

Comments

Categories