Gift, Gift Splits, and Soft Credits Tables

Victoria Mueller · May 1

Hi All,

I have just established a connection to Raisers Edge through Blackbaud API. As I am looking at the tables in Power BI and trying to build the metrics I need, I am getting confused with the gifts, gift split, and soft credit tables.

My first question is why the sum of amounts in the gifts table is different than the sum of amounts in the gift splits table? And how can I get an amount total for a certain appeal, when I add the appeal filter nothing changes.

My other question is the overall connection between the 3 tables. We always use the give credit to "soft credit recipients" in Raiser's Edge. I know I need to somehow combine these tables and I'm not sure where to start.

Alex Wong · May 4

This requires understanding of the data structure of gift in RE.

1 gift can have 1 or more gift splits - split means campaign, fund, appeal, package combination with amount associated with that CFAP combination, amount will total to the full gift amount

1 gift can have 0 or more gift soft credit - if there is soft credit, it will be the constituent system record id of the soft credit recipient and the soft credited amount. NOTE that total soft credit amount CAN be less than, equals, or more than the full gift amount.

gift split and gift soft credit are both SEPARATE 1 to many relationship with gift record. There is no "simple" view of this. Let's use an example.

Smith Corporation direct credit gift of $1000
- Gift Split
  - C1, F1, A1, P1 amount of $700
  - C1, F2, A1, P1 amount of $300
- Gift Soft Credit (let's start with a simple case of soft credit total is same as full gift amount)
  - John Smith soft credited $250
  - Mary Smith soft credited $750

Direct Gift Donor	Gift System ID	Gift Date	Gift Amount
Smith Corporation	1	1/1/2026	$ 1,000.00

Gift System ID	Campaign	Fund	Appeal	Package	Split Amount
1	C1	F1	A1	P1	$ 700.00
1	C1	F2	A1	P1	$ 300.00

Gift System ID	Soft Credit	Soft Credited Amount
1	John Smith	$ 250.00
1	Mary Smith	$ 750.00

In this situation, you are going to get 1 row from the gift table, 2 rows in the gift splits table, and 2 rows in the gift soft credit table. "Sum" of gift table is $1K when you sum the gift amount column. "Sum" of the gift split table is $1K when you sum the gift split amount column. "Sum" of the gift soft credit table is $1K when you sum the soft credited amount column. If you are to combine the 3 tables, you will have to do good "join/combine" table manipulation such that amount is correctly calculated.

Direct Gift Donor	Gift System ID	Gift Date	Gift Amount	Soft Credit	Soft Credited Amount	% SC	Campaign	Fund	Appeal	Package	Split Amount	SC Split Amount
Smith Corporation	1	1/1/2026	$ 1,000.00	John Smith	$ 250.00	25%	C1	F1	A1	P1	$ 700.00	$ 175.00
Smith Corporation	1	1/1/2026	$ 1,000.00	John Smith	$ 250.00	25%	C1	F2	A1	P1	$ 300.00	$ 75.00
Smith Corporation	1	1/1/2026	$ 1,000.00	Mary Smith	$ 750.00	75%	C1	F1	A1	P1	$ 700.00	$ 525.00
Smith Corporation	1	1/1/2026	$ 1,000.00	Mary Smith	$ 750.00	75%	C1	F2	A1	P1	$ 300.00	$ 225.00

So you can see when "combined" if you sum the wrong column, you will get the wrong "real amount donated". SC Split Amount when it is calculated (% soft credited multiply by the split amount), the sum of this column will give you the correct "real donated" amount of $1K.

Post already too long, so i'll leave everyone the pain and myself some time lol. But imagine, if soft credit is not totaling to 100% of the actual gift amount, what the table will look like =D

Alex Wong · May 5

last post is more about data structure and understanding 1-to-many relationship and how to handle supposedly "dup" row. this post is now more about power bi data.

You are going to need a minimal of 8 tables:

Constituent (all)
1. this will be all cosntituent records so you know who the donor is and who the soft credit donor is.
Gift
1. this will all the gift data, including soft credit and gift split, but all "jam" into 1
2. you will need to "separate" out the gift split table and the gift soft credit table and leave this table with only gift 1-to-1 relationship data (i.e. gift date, gift amount, gift type, etc)
Gift Split
1. "reference" (or dup) from the Gift table
2. one row per gift split (will definitely have one split per gift)
3. remove all other columns and keep gift_id and gift_splits
4. expand gift_splits to get gift split data column
5. campaign_id, appeal_id, fund_id, package_id are all just system record id of the specific campaign, appeal, fund, package, so you need those specific table as dimension table in power bi
Gift Soft Credit
1. "reference" (or dup) from the Gift table, filter out null (blank) soft_credits column
  1. only gift with soft credit(s) will have row(s) in this table
2. remoe all other columns and keep gift_id and soft_credits
3. expand soft_credits to get gift soft credit data column
4. constituent_id column is recordid from the Constituent (all) table of the soft credited constituent
Campaign
Fund
Appeal
Package

Once you got all these tables, and make the active relationship between them, you will be able to do a lot of dashboarding with them

Erik Leaver · May 5

Amazing @Alex Wong. Great detail.

@Victoria Mueller if you want a sample PBI template file that shows the relationships, @Ellen Smith's post is a great place to start: https://community.blackbaud.com/discussion/64602

You didn't specify how you are getting the data. If you are using the Power BI Connector, then @Rebecca Sundquist's template is a good start: https://community.blackbaud.com/discussion/63989

Carlos Pelay · May 8

This is a brilliant set of responses @Alex Wong !

In terms of getting all the gift data @Erik Leaver, we find it really straightforward to do via the Query API - just create a gift query in NXT with all the gift columns " all jamed into 1" like Alex notes :) and then we run a nightly PowerAutomate flow that ports the data to a csv file in Sharepoint.

There's a great post by @Rebecca Sundquist that was super helpful for me:

Sharing my Power Automate flow: Get multiple query results with Query connector and SharePoint list — Blackbaud Community

Victoria Mueller · May 13

@Alex Wong That was such a helpful explanation. I apologize it took me a few days to fully break it down. Will simply making the correct relationships allow me to report on soft credits when applied and hard credits elsewhere? Or would I need to create a new table? Thankfully I can say my organization consistently uses soft credits that are equal to the total gift amount. However, we may soft credit 2 individuals. For example, if a $1,000 gift was made from a company, we soft credit individual A $1,000 and also Individual B $1,000. We never soft credit a percentage of the gift.

I am thinking I will have to create a table along these lines:

If "gift_id" exists in both the gifts and soft credits table, replace the single row in gifts with the row/s from soft credits.

That would allow me to reference the soft credit donors when they exist but other wise credit the hard credit donor. I would not use this table for financial reporting but rather donor counts, retention, etc.

@Alex Wong am I on the right track with this? If so, do you have any advice on actually building that table.

@Erik Leaver I am using a Power BI connecter, so the second link was also very helpful! Let me know if you have any thought on my comment above as well, I appreciate you both!

Alex Wong · May 13

no new table needs to be created, the table i mentioned in my previous post would suffice. once all tables are in place, and relationship is correctly mapped, you can create visualization rather easily using default drag and drop using the right "amount field" depending on what you are trying to show.

Some needs may require creating some DAX measures, but if that's the case, you can use AI to help or post here with your needs.

Gift, Gift Splits, and Soft Credits Tables

Answers

Categories