The table name says revenue, but this table was updated to use recognition totals so that amounts here will match smart fields and recognition programs in Atlas. Note that year breakouts are done using the recognition date, and totals are recognition amount totals.
Some interesting things to note:
- Recognition date and recognition amount on commitments are used in this table
- Recognition type is used to determine the different amounts. If recognition type is blank, it is assumed to be “Soft Credit”
- H_ values = commitments with recognition type = ‘Hard Credit’
- HS_ values = commitments with recognition type not like %matching%
- HSM_ values = all commitments with any recognition type
- All transactions received on the same day are considered as one donation for purposes of determining largest gift
- Tiebreakers are needed if the constituent has multiple transactions that could be the first, last or largest.
- First – recognition effective date asc, amount desc, financial transaction line item systemid asc
- Last – recognition effective date desc, amount desc, financial transaction line item systemid asc
- Max/Largest – amount desc, recognition effective date, recognition amount desc, financial transaction line item systemid asc
- Campaign – if there are multiple campaigns for a transaction the tiebreaker is campaign lookup ID asc
- This table is summarized at the household level, so if spouses in the same household have recognition credits on the same transaction but have different recognition dates or amounts, the largest amount or the most recent date are used in calculations.
- Counts are at the revenue level, not the application level
| Columns | Comments |
|---|---|
| HOUSEHOLDCONSTITUENTDIMID | |
| H_PLEDGEBALANCE | |
| H_HASRECURRINGGIFTS | |
| H_FIRSTFINANCIALTRANSACTIONLINEITEMFACTID | |
| H_LASTFINANCIALTRANSACTIONLINEITEMFACTID | |
| H_FIRSTDESIGNATIONDIMID | |
| H_LASTDESIGNATIONDIMID | |
| H_FIRSTCAMPAIGNDIMID | |
| H_LASTCAMPAIGNDIMID | |
| H_FIRSTAPPEALDIMID | |
| H_LASTAPPEALDIMID | |
| H_TOTALAMOUNT | |
| H_AVGAMOUNT | |
| H_MAXAMOUNT | |
| H_MINAMOUNT | |
| H_GIFTCOUNT | |
| H_FIRSTGIFTDATE | |
| H_LASTGIFTDATE | |
| H_FIRSTGIFTDATEDIMID | |
| H_LASTGIFTDATEDIMID | |
| H_FIRSTGIFTAMOUNT | |
| H_LASTGIFTAMOUNT | |
| H_MAXGIFTDATE | |
| H_MAXGIFTDATEDIMID | |
| H_MAXFINANCIALTRANSACTIONLINEITEMFACTID | |
| H_MAXGIFTLINEITEMDATE | |
| H_MAXGIFTLINEITEMDATEDIMID | |
| H_TOTALAMOUNTCURRENTFY | |
| H_TOTALAMOUNTFY_1 | |
| H_TOTALAMOUNTFY_2 | |
| H_TOTALAMOUNTFY_3 | |
| H_TOTALAMOUNTFY_4 | |
| H_TOTALAMOUNTFY_5 | |
| H_TOTALAMOUNTFY_6 | |
| H_TOTALAMOUNTFY_7 | |
| H_TOTALAMOUNTFY_8 | |
| H_TOTALAMOUNTFY_9 | |
| H_TOTALAMOUNTFY_10 | |
| H_TOTALCOUNTCURRENTFY | |
| H_TOTALCOUNTFY_1 | |
| H_TOTALCOUNTFY_2 | |
| H_TOTALCOUNTFY_3 | |
| H_TOTALCOUNTFY_4 | |
| H_TOTALCOUNTFY_5 | |
| H_TOTALCOUNTFY_6 | |
| H_TOTALCOUNTFY_7 | |
| H_TOTALCOUNTFY_8 | |
| H_TOTALCOUNTFY_9 | |
| H_TOTALCOUNTFY_10 | |
| HS_PLEDGEBALANCE | |
| HS_HASRECURRINGGIFTS | |
| HS_FIRSTFINANCIALTRANSACTIONLINEITEMFACTID | |
| HS_LASTFINANCIALTRANSACTIONLINEITEMFACTID | |
| HS_FIRSTDESIGNATIONDIMID | |
| HS_LASTDESIGNATIONDIMID | |
| HS_FIRSTCAMPAIGNDIMID | |
| HS_LASTCAMPAIGNDIMID | |
| HS_FIRSTAPPEALDIMID | |
| HS_LASTAPPEALDIMID | |
| HS_TOTALAMOUNT | |
| HS_AVGAMOUNT | |
| HS_MAXAMOUNT | |
| HS_MINAMOUNT | |
| HS_GIFTCOUNT | |
| HS_FIRSTGIFTDATE | |
| HS_LASTGIFTDATE | |
| HS_FIRSTGIFTDATEDIMID | |
| HS_LASTGIFTDATEDIMID | |
| HS_FIRSTGIFTAMOUNT | |
| HS_LASTGIFTAMOUNT | |
| HS_MAXGIFTDATE | |
| HS_MAXGIFTDATEDIMID | |
| HS_MAXFINANCIALTRANSACTIONLINEITEMFACTID | |
| HS_MAXGIFTLINEITEMDATE | |
| HS_MAXGIFTLINEITEMDATEDIMID | |
| HS_TOTALAMOUNTCURRENTFY | |
| HS_TOTALAMOUNTFY_1 | |
| HS_TOTALAMOUNTFY_2 | |
| HS_TOTALAMOUNTFY_3 | |
| HS_TOTALAMOUNTFY_4 | |
| HS_TOTALAMOUNTFY_5 | |
| HS_TOTALAMOUNTFY_6 | |
| HS_TOTALAMOUNTFY_7 | |
| HS_TOTALAMOUNTFY_8 | |
| HS_TOTALAMOUNTFY_9 | |
| HS_TOTALAMOUNTFY_10 | |
| HS_TOTALCOUNTCURRENTFY | |
| HS_TOTALCOUNTFY_1 | |
| HS_TOTALCOUNTFY_2 | |
| HS_TOTALCOUNTFY_3 | |
| HS_TOTALCOUNTFY_4 | |
| HS_TOTALCOUNTFY_5 | |
| HS_TOTALCOUNTFY_6 | |
| HS_TOTALCOUNTFY_7 | |
| HS_TOTALCOUNTFY_8 | |
| HS_TOTALCOUNTFY_9 | |
| HS_TOTALCOUNTFY_10 | |
| HSM_PLEDGEBALANCE | |
| HSM_HASRECURRINGGIFTS | |
| HSM_FIRSTFINANCIALTRANSACTIONLINEITEMFACTID | |
| HSM_LASTFINANCIALTRANSACTIONLINEITEMFACTID | |
| HSM_FIRSTDESIGNATIONDIMID | |
| HSM_LASTDESIGNATIONDIMID | |
| HSM_FIRSTCAMPAIGNDIMID | |
| HSM_LASTCAMPAIGNDIMID | |
| HSM_FIRSTAPPEALDIMID | |
| HSM_LASTAPPEALDIMID | |
| HSM_TOTALAMOUNT | |
| HSM_AVGAMOUNT | |
| HSM_MAXAMOUNT | |
| HSM_MINAMOUNT | |
| HSM_GIFTCOUNT | |
| HSM_FIRSTGIFTDATE | |
| HSM_LASTGIFTDATE | |
| HSM_FIRSTGIFTDATEDIMID | |
| HSM_LASTGIFTDATEDIMID | |
| HSM_FIRSTGIFTAMOUNT | |
| HSM_LASTGIFTAMOUNT | |
| HSM_MAXGIFTDATE | |
| HSM_MAXGIFTDATEDIMID | |
| HSM_MAXFINANCIALTRANSACTIONLINEITEMFACTID | |
| HSM_MAXGIFTLINEITEMDATE | |
| HSM_MAXGIFTLINEITEMDATEDIMID | |
| HSM_TOTALAMOUNTCURRENTFY | |
| HSM_TOTALAMOUNTFY_1 | |
| HSM_TOTALAMOUNTFY_2 | |
| HSM_TOTALAMOUNTFY_3 | |
| HSM_TOTALAMOUNTFY_4 | |
| HSM_TOTALAMOUNTFY_5 | |
| HSM_TOTALAMOUNTFY_6 | |
| HSM_TOTALAMOUNTFY_7 | |
| HSM_TOTALAMOUNTFY_8 | |
| HSM_TOTALAMOUNTFY_9 | |
| HSM_TOTALAMOUNTFY_10 | |
| HSM_TOTALCOUNTCURRENTFY | |
| HSM_TOTALCOUNTFY_1 | |
| HSM_TOTALCOUNTFY_2 | |
| HSM_TOTALCOUNTFY_3 | |
| HSM_TOTALCOUNTFY_4 | |
| HSM_TOTALCOUNTFY_5 | |
| HSM_TOTALCOUNTFY_6 | |
| HSM_TOTALCOUNTFY_7 | |
| HSM_TOTALCOUNTFY_8 | |
| HSM_TOTALCOUNTFY_9 | |
| HSM_TOTALCOUNTFY_10 |