SIA - Soft Credits and Gift Splits

Lee Grisham

I know it is late on a Friday and most people may lose this in their weekend emails, but I have to put this down before I forget.

I am working on end of year reporting for FY23. After trying multiple report types, queries, exports, etc., I have landed on the Gift Detail and Summary Report. I used the criteria below removing the redundant sections we don't use:

Records to include: All

Include records with these Dates: Last fiscal yr (7/1/2022 to 6/30/2023)

Date to use: GL post date

Soft credit option: Recipients, Use distribution on gift

Include these constituents: Inactive = Yes; Deceased = Yes; Constituents with no valid addresses = Yes

Gift types to include for column Donations: Cash, Recurring Gift Pay-Cash, Pledge, MG Pledge, Stock/Property, Stock/Property (Sold), Gift-in-Kind, Other

Campaigns to include: All

Funds to include: All

Appeals to include: All

Constituent Codes to include: All

Include Balance brought forward: No

Once I pulled the detail report, I exported it in CSV and formated it, yada, yada. When checking it against some gifts (Trust and Verify), I noticed something strange. A little background: being a Feeding America (FA) food bank, we get disbursals from national campaigns as part of cause marketing conducted across the country. This comes via an ACH from FA in one lump sum. This means we create gift splits to match the funds to specific CFAP(Campaign, Fund, Appeal, Package) and then add in Soft Credits with the total disbursement from each entity. For some disbursements, this can be 15+ soft credits! On that file, the report takes the percentage of each split and applies it to multiple entry rows for each constituent. It's a mess! For one gift, I have 38 rows... ?The total for each constituent still equals their total on the record, thankfully, as does the appeal and campaign totals.

To find total number of gifts, I can use a Data Model from a Pivot to find Distinct Counts of the Gift IDs. For total giving for CFA, I can be confident in that those match up. For total giving by constituent, I can be confident on that as well.

To get to the main point of this post, this really just makes me realize that for total constituent giving by CFAP, this might not ever be possible. Since soft credits and gift splits don't talk, I won't be able to say ABC, Co has given $XXX to Campaign A. It makes me dread the rise of Benevity, Fidelity Charitable, BB Giving Fund and their like. We can have multiple corporations contained on a single disbursement giving to different campaigns and it will be a mess trying to get the totals off of that. Am I going crazy or is it just the end of the week working on an audit for three days straight? ?

Alex Wong

@Lee Grisham
Yes, the 1 to many relationship of 1 gift > multiple split and 1 gift > multiple soft credit is more difficult.

Here's what I do:

I start with soft credit, and calculate the % of amount soft credited to the donor.

For example, a gift of $10K, soft credit $2K to Mary and $8K to John means 20% of $10K to Mary and 80% of $10K to John.

If the same gift is split for $4K to CFAP1 and $6K to CFAP2, then:

Mary's giving to CFAP1 is 20% of $4K = $800
Mary's giving to CFAP2 is 20% of $6K = $1200
Mary's total giving on this gift is $2K ($800 + $1200). Numbers add up

John's giving to CFAP1 is 80% of $4K = $3200
John's giving to CFAP2 is 80% of $6K = $4800
John's total giving on this gift is $8K ($3200 + $4800). Numbers add up

You wouldn't do this manually for each gift, this can be done via proper querying of data and then use excel formula.

I used to do this via SQL directly from database tables when I had access to the database backend before we moved to RE NXT. Now I'm doing the same logic from my data warehouse through SKY API.

Faith Murray

@Lee Grisham, Alex Wong's advice is excellent. However, we simply avoid the situation by not using split gifts at all. I have found that they mess up reports because the total gift amount is recorded under both Funds, inflating the apparent revenue. Instead, if we get a $100 check split equally to two funds, we enter two separate gift lines in our batch ($50 to each fund) and simply denote by the gift reference and check number that they came in the form of one check.

Lee Grisham

@Alex Wong I would love to do this in a data warehouse! Unfortunately, I ran out of time before we hit the busy time of year to get that going. Maybe next year.