This table provides aggregates (min, max, first, last, etc.) of gifts grouped by constituent record according to credit assigned to the constituent and listed in the field name (H, HS, HSM). H_ indicated hard credit, HS_ indicated hard + soft credit, and HSM was hard + soft + matching.
The proposed table is a combination of RE DW tables Fact_ConstituentLTGiving and Fact_ConstituentGivingSummary. In each case Partners had customized the existing tables by changing the back-end logic to match our Partners standard definitions for commitments, and added the preface to fields that indicates credits included in each aggregate (H=Hard Credit, HS= Hard Credit and Soft Credit, HSM = Hard Credit, Soft Credit, and Matching Gifts)
The table contains one row for each constituent.
Note: to add/edit information in this area about the table in general, choose edit entry from the black menu at the top of the page. To add/edit information in the table below for specific fields, choose the edit link UNDER the table. These options are only available if you have edit permissions AND you have logged in to wordpress – any questions pls let Helen know
| Field Name | Sample Data | Comments | Credit |
|---|---|---|---|
| ConstituentLTGivingFactID | 1 | PK | |
| ConstituentDimID | 158245 | key to constituent record in DW | |
| SourceID | 3 | ID of the affiliate database, do we need an indicator like this? | |
| ETLControlID | 1 | needed?? | |
| H_PledgeBalance | 123 | Sum of all outstanding pledge balances | Added 9/10/19 |
| H_HasRecurringGifts | Yes/No | Yesif this donor a recurring gift | Added 9/10/19 |
| H_FirstGiftFactID | 557769 | key to first gift in DW | Hard |
| H_LastGiftFactID | 557769 | key to last gift in DW | Hard |
| H_FirstFundDimID | 19 | key to fund of first gift in DW | Hard |
| H_LastFundDimID | 19 | key to fund of last gift in DW | Hard |
| H_FirstCampaignDimID | 55 | key to campaign of first gift in DW | Hard |
| H_LastCampaignDimID | 55 | key to campaign of last gift in DW | Hard |
| H_FirstAppealDimID | 136 | key to appeal of first gift in DW | Hard |
| H_LastAppealDimID | 136 | key to appeal of last gift in DW | Hard |
| H_TotalAmount | 100 | Sum of amounts of all of their gifts | Hard |
| H_AvgAmount | 100 | Average of amounts of all gifts | Hard |
| H_TotalMaxGiftAmount | 100 | Largest total amount of all gifts | Hard |
| H_TotalMinGiftAmount | 100 | Smallest total amount of all gift | Hard |
| H_GiftCount | 1 | Count of gifts | Hard |
| H_FirstGiftDate | 00:00.0 | Date of first gift | Hard |
| H_FirstGiftDateDimID | 20040331 | key to first gift date in DW | Hard |
| H_LastGiftDate | 00:00.0 | Date of last gift | Hard |
| H_LastGiftDateDimID | 20040331 | key to last gift date in DW | Hard |
| H_LastGiftAmount | 100 | amount of last gift | Hard |
| H_FirstGiftAmount | 100 | amount of first gift | Hard |
| H_MaxGiftDate | 00:00.0 | date of largest gift | Hard |
| H_MaxGiftDateDimID | 20040331 | key to date of largest gift in DW | Hard |
| H_MaxGiftFactID | 557769 | key to largest gift in DW | Hard |
| H_MaxGiftSplitDate | 00:00.0 | date of largest gift split | Hard |
| H_MaxGiftSplitDateDimID | 20040331 | key to date of largest gift split in DW | Hard |
| H_RevenueTotalCurrentFY | 100 | Sum of commitments in a specific fiscal year | Hard |
| H_RevenueTotalCFYminus1 | 100 | Hard | |
| H_RevenueTotalCFYminus2 | 100 | Hard | |
| H_RevenueTotalCFYminus3 | 100 | Hard | |
| H_RevenueTotalCFYminus4 | 100 | Hard | |
| H_RevenueTotalCFYminus5 | 100 | Hard | |
| H_RevenueTotalCFYminus6 | 100 | Hard | |
| H_RevenueTotalCFYminus7 | 100 | Hard | |
| H_RevenueTotalCFYminus8 | 100 | Hard | |
| H_RevenueTotalCFYminus9 | 100 | Hard | |
| H_RevenueTotalCFYminus10 | 100 | Hard | |
| H_RevenueCountCurrentFY | 1 | Gift count NOT at the split level but at the parent level; | Hard |
| Counting commitments | |||
| H_RevenueCountCFYminus1 | 1 | Hard | |
| H_RevenueCountCFYminus2 | 1 | Hard | |
| H_RevenueCountCFYminus3 | 1 | Hard | |
| H_RevenueCountCFYminus4 | 1 | Hard | |
| H_RevenueCountCFYminus5 | 1 | Hard | |
| H_RevenueCountCFYminus6 | 1 | Hard | |
| H_RevenueCountCFYminus7 | 1 | Hard | |
| H_RevenueCountCFYminus8 | 1 | Hard | |
| H_RevenueCountCFYminus9 | 1 | Hard | |
| H_RevenueCountCFYminus10 | 1 | Hard | |
| HS_ rows for the household should be determined by analyzing all commitments where either member of the household has any sort of recognition credit (EXCLUDING Matching Soft Credit and EXCLUDING Peer to Peer Fundraising Credit), and then pulling in each revenue transaction only once. E.g. if spouse A has hard credit for a gift and spouse B has soft credit for the same gift, the gift will only be counted once. | |||
| HS_ rows for a NON-household member should only include commitments where the individual or organization has hard or recognition credit. | |||
| HS_PledgeBalance | 123 | Sum of all outstanding pledge balances | Added 9/10/19 |
| HS_HasRecurringGifts | Yes/No | Yesif this donor a recurring gift | Added 9/10/19 |
| HS_FirstGiftFactID | 557769 | key to first gift in DW | Hard + Recognition |
| HS_LastGiftFactID | 557769 | key to last gift in DW | Hard + Recognition |
| HS_FirstFundDimID | 19 | key to fund of first gift in DW | Hard + Recognition |
| HS_LastFundDimID | 19 | key to fund of last gift in DW | Hard + Recognition |
| HS_FirstCampaignDimID | 55 | key to campaign of first gift in DW | Hard + Recognition |
| HS_LastCampaignDimID | 55 | key to campaign of last gift in DW | Hard + Recognition |
| HS_FirstAppealDimID | 136 | key to appeal of first gift in DW | Hard + Recognition |
| HS_LastAppealDimID | 136 | key to appeal of last gift in DW | Hard + Recognition |
| HS_TotalAmount | 100 | Sum of amounts of all of their gifts | Hard + Recognition |
| HS_AvgAmount | 100 | Average of amounts of all gifts | Hard + Recognition |
| HS_TotalMaxGiftAmount | 100 | Largest total amount of all gifts | Hard + Recognition |
| HS_TotalMinGiftAmount | 100 | Smallest total amount of all gift | Hard + Recognition |
| HS_GiftCount | 1 | Count of gifts | Hard + Recognition |
| HS_FirstGiftDate | 00:00.0 | Date of first gift | Hard + Recognition |
| HS_FirstGiftDateDimID | 20040331 | key to first gift date in DW | Hard + Recognition |
| HS_LastGiftDate | 00:00.0 | Date of last gift | Hard + Recognition |
| HS_LastGiftDateDimID | 20040331 | key to last gift date in DW | Hard + Recognition |
| HS_LastGiftAmount | 100 | amount of last gift | Hard + Recognition |
| HS_FirstGiftAmount | 100 | amount of first gift | Hard + Recognition |
| HS_MaxGiftDate | 00:00.0 | date of largest gift | Hard + Recognition |
| HS_MaxGiftDateDimID | 20040331 | key to date of largest gift in DW | Hard + Recognition |
| HS_MaxGiftFactID | 557769 | key to largest gift in DW | Hard + Recognition |
| HS_MaxGiftSplitDate | 00:00.0 | date of largest gift split | Hard + Recognition |
| HS_MaxGiftSplitDateDimID | 20040331 | key to date of largest gift split in DW | Hard + Recognition |
| HS_RevenueTotalCurrentFY | 100 | Sum of commitments in a specific fiscal year | Hard + Recognition |
| HS_RevenueTotalCFYminus1 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus2 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus3 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus4 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus5 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus6 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus7 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus8 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus9 | 100 | Hard + Recognition | |
| HS_RevenueTotalCFYminus10 | 100 | Hard + Recognition | |
| HS_RevenueCountCurrentFY | 1 | Gift count NOT at the split level but at the parent level; | Hard + Recognition |
| Counting commitments | |||
| HS_RevenueCountCFYminus1 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus2 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus3 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus4 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus5 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus6 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus7 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus8 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus9 | 1 | Hard + Recognition | |
| HS_RevenueCountCFYminus10 | 1 | Hard + Recognition | |
| HSM_ rows for the household should be determined by analyzing all commitments where either member of the household has any sort of recognition credit (including Matching Soft Credit, but EXCLUDING Peer to Peer Fundraising Credit), and then pulling in each revenue transaction only once. E.g. if spouse A has hard credit for a gift and spouse B has soft credit for the same gift, the gift will only be counted once. | |||
| HSM_ rows for a NON-household member should only include commitments where the individual or organization has hard or recognition credit. | |||
| HSM_FirstGiftFactID | 557769 | key to first gift in DW | Hard + Recognition + Matching |
| HSM_LastGiftFactID | 557769 | key to last gift in DW | Hard + Recognition + Matching |
| HSM_FirstFundDimID | 19 | key to fund of first gift in DW | Hard + Recognition + Matching |
| HSM_LastFundDimID | 19 | key to fund of last gift in DW | Hard + Recognition + Matching |
| HSM_FirstCampaignDimID | 55 | key to campaign of first gift in DW | Hard + Recognition + Matching |
| HSM_LastCampaignDimID | 55 | key to campaign of last gift in DW | Hard + Recognition + Matching |
| HSM_FirstAppealDimID | 136 | key to appeal of first gift in DW | Hard + Recognition + Matching |
| HSM_LastAppealDimID | 136 | key to appeal of last gift in DW | Hard + Recognition + Matching |
| HSM_TotalAmount | 100 | Sum of amounts of all of their gifts | Hard + Recognition + Matching |
| HSM_AvgAmount | 100 | Average of amounts of all gifts | Hard + Recognition + Matching |
| HSM_TotalMaxGiftAmount | 100 | Largest total amount of all gifts | Hard + Recognition + Matching |
| HSM_TotalMinGiftAmount | 100 | Smallest total amount of all gift | Hard + Recognition + Matching |
| HSM_GiftCount | 1 | Count of gifts | Hard + Recognition + Matching |
| HSM_FirstGiftDate | 00:00.0 | Date of first gift | Hard + Recognition + Matching |
| HSM_FirstGiftDateDimID | 20040331 | key to first gift date in DW | Hard + Recognition + Matching |
| HSM_LastGiftDate | 00:00.0 | Date of last gift | Hard + Recognition + Matching |
| HSM_LastGiftDateDimID | 20040331 | key to last gift date in DW | Hard + Recognition + Matching |
| HSM_LastGiftAmount | 100 | amount of last gift | Hard + Recognition + Matching |
| HSM_FirstGiftAmount | 100 | amount of first gift | Hard + Recognition + Matching |
| HSM_MaxGiftDate | 00:00.0 | date of largest gift | Hard + Recognition + Matching |
| HSM_MaxGiftDateDimID | 20040331 | key to date of largest gift in DW | Hard + Recognition + Matching |
| HSM_MaxGiftFactID | 557769 | key to largest gift in DW | Hard + Recognition + Matching |
| HSM_MaxGiftSplitDate | 00:00.0 | date of largest gift split | Hard + Recognition + Matching |
| HSM_MaxGiftSplitDateDimID | 20040331 | key to date of largest gift split in DW | Hard + Recognition + Matching |
| HSM_RevenueTotalCurrentFY | 100 | Sum of commitments in a specific fiscal year | Hard + Recognition + Matching |
| HSM_RevenueTotalCFYminus1 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus2 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus3 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus4 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus5 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus6 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus7 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus8 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus9 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueTotalCFYminus10 | 100 | Hard + Recognition + Matching | |
| HSM_RevenueCountCurrentFY | 1 | Gift count NOT at the split level but at the parent level; | Hard + Recognition + Matching |
| Counting commitments | |||
| HSM_RevenueCountCFYminus1 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus2 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus3 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus4 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus5 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus6 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus7 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus8 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus9 | 1 | Hard + Recognition + Matching | |
| HSM_RevenueCountCFYminus10 | 1 | Hard + Recognition + Matching |