Discrepancy between Total Gift Amount and total from adding up Gifts

Daniel Noga 2

Hello everyone,



I am working on our Annual Report, which calls for Summary information and thus a Constituent-based export. For this listing, we want to include all gifts between 9.01.2014 and 9.30.2015, excluding those gifts that are coded with one of two Campaign IDs. To check which Gifts are being included, I also wrote a corresponding Gift Query.



Using the same criteria in each Query (to the best of my knowledge), I am getting two totally different figures, with the figure from Summary information being much lower than the figure obtained by tallying separate Gifts for the same period and Campaigns.



I'm not sure where to go looking for discrepancies; split gifts, maybe?

JoAnn Strommen

My first spot to check would be to make sure gift type selections are accurate.  Are you counting the pledge amount or cash paid? Gift query will pull every monthly, quarterly payment.  That will change # of gifts.  For amount differences I'd still check gift types.  Are pledges and their payments being double counted? 



Split gifts could be a factor.  Can't test for you as quit using them years ago due to reporting issues.  Another factor could be write-offs if you have those. 



That's where I would start checking.

Jen Claudy

Depending on the volume of records you're looking at, and if what JoAnn suggested didn't help or didn't fully resolve your problems, I would shorten the timeframe and look at a month at a time.  Look at your totals for just Sep 2014 and if there are discrepancies, go record by record to figure out what they are.  Chances are, you'll figure out any and all issues before you get half way through the 13 months of data.



And once you figure out what's up, document the heck out of your process and results.  It will save you from repeating this work next year because you don't remember.  I often put a Query Description (Tools > Query Options > Description) with what dates to change or what documentation to refer to...that way, next time, I know from within RE what to do or where to go.

Daniel Noga 2

Thanks, you two! I'll try these suggestions and see if they help.



Policies and Procedures are King! We've definitely been talking about that this morning. There's never been a manual here before, I think I will write it.

Daniel Noga 2

Update:



Checked the following, but there is still a discrepancy (it's just shifted around a bit):

• Split Gifts: We've had a strict No Split Gift policy in place here for a couple of years, probably for the same reason JoAnn's org doesn't use them--they cause problems like this! I'm happy to see the policy's being followed; there's not a single split gift listed.
• No Write-Offs present, either.
• Gift Type matches in both Queries and both Exports: Cash, Pledge, Stock/Property, Other and Gift-in-Kind. We tend to count Pledges as revenue rather than Payments.
• Date Range: I was so deflated; I discovered the two Queries (each written by different people) were targeting different date ranges and got so excited, but correcting for this did not completely resolve the discrepancy.

It's time to start going month-by-month as Jennifer suggested.

JoAnn Strommen

Double check the queries - are they marked the same for in/excluding deceased, inactive etc.

Daniel Noga 2

They are both set to include everyone.

JoAnn Strommen

It was worth a shot.  I recently ran one and didn't get expected results because previous time I had excluded deceased when I ran the list and forgot to check box again. 



If you can't find anything when looking at a smaller gift date, I'd contact support.  They may have something else to check or be able to a screen share and see what is causing the difference. 

Krys Fritz bCREPro bCRP

Have you checked the Gift Processing under the Query Options?

Daniel Noga 2

Krys: I just checked, and the Gift Processing tabs under Query Options are both set the same way: Soft credit the Donor, and MG gifts are credited to the company.



Something that just occurred to me vis-a-vis comparing apples and oranges:



If...

...my Constituent query is looking only for people who donated a total of $250 or more (and it is,) and then outputting their Total Gift Amount...

...and...

...my Gift query is outputting *all* gifts regardless of cumulative giving amount (except those with the excluded Campaign IDs) for the same time period...

...then...

...the amounts are necessarily going to differ because the Gift query will be counting money that the Constituent query is ignoring.



Therefore, any process to check for accuracy must factor in the $250 minimum threshold.



The devil is the details. 



 

Daniel Noga 2

Adjusting for the above explained about half of the gap between the two reports. Back to the drawing board! 

Kelly Wilkinson

This is an old thread, but I'm posting this for anyone who finds this going forward…

When using Summary for Gifts in a query, don't use criteria for anything that can show up more than once. This includes campaign, fund, appeal and package for split gifts. It could also include gift attributes if there are two of the same attribute on the record.

Example of good criteria:
Total amount of gifts greater than $1,000
Gift date between 1/1/24 and 12/31/24

Since there can only be one date and amount field on a gift, this will do what you want it to do.

Example of bad criteria:
Total amount of gifts greater than $1,000
Gift date between 1/1/24 and 12/31/24
Campaign is Annual Campaign

In this case, if you have a $500 gift with the Annual Campaign but split between Fund A + Fund B, then it will show up as $1,000.

Here's why: The query is finding the annual campaign twice, and it's returning two rows. Since it's a summary row, the output only shows one row, but it's summed up (Instead of two duplicate rows of $500 each, it will show one summary row of $1,000).

Here's how to avoid this pitfall: Build your query without using campaign/fund/appeal. Then export with an Export. In the export, you can limit the summary info based on campaign/fund/appeal.

In the above example, your exported excel file may show summary gifts below $1,000. If someone donated $1,000 to the Capital Campaign, and $100 to the Annual Campaign, they'll be included in the Query, but the Export will only show $100. Query isn't sophisticated enough to exclude them, but in the exported file, you can simply delete any rows below $1,000.

Hope that helps. Good luck!

Alex Wong

@Kelly Wilkinson
Hi, for the record as people who may see this.

I don't believe what you said is true. Query is actually quite ok to export from and have used Query's summary of gift for long time and we do have split gift. So I went and did a test.

this is the gift:

exactly as you said would double count:

In this case, if you have a $500 gift with the Annual Campaign but split between Fund A + Fund B, then it will show up as $1,000.

in my query criteria and result

you can see that the amount is $500, not $1000.

if i change the criteria to filter the summary by fund

the result is the sum of amount coded to the fund, which is $200 only.