Grain: CAMPAIGNS — one row per campaign. COMMUNICATIONS — one row per channel within a campaign. SEGMENTS — one row per recipient subgroup within a communication.
SEGMENT_KEY that links it to the marketing chain that solicited it. Following that key up through SEGMENTS → COMMUNICATIONS → CAMPAIGNS reveals which campaign drove the gift and how much each segment raised.
Key rule: The hierarchy flows top-down at setup but is queried bottom-up from the gift. Start at GIFTS.SEGMENT_KEY, then join upward to reach campaign totals.
Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).
Table columns
Tables on this page: CAMPAIGNS · COMMUNICATIONS · SEGMENTS · GIFTS (boundary) · CONTACTS (boundary)
CAMPAIGNS
One row per top-level marketing initiative. For all campaign-related tables including emails and events, see Campaigns — extended reference.
| Column | Type | Notes |
|---|
CAMPAIGN_KEY | TEXT | Primary key |
NAME | TEXT | Campaign name (e.g. “End of Year 2025”) |
START_DATE_TIME_UTC | TIMESTAMP | Campaign start date |
END_DATE_TIME_UTC | TIMESTAMP | Campaign end date |
GIVING_GOAL | NUMBER | Dollar goal for the campaign |
CAMPAIGN_IS_DELETED | BOOLEAN | Soft delete flag — always filter WHERE CAMPAIGN_IS_DELETED = FALSE |
CAMPAIGN_IS_ARCHIVED | BOOLEAN | Campaign is archived |
COMMUNICATIONS
One row per channel within a campaign (e.g. Direct Mail, Email Blast). Contains pre-aggregated performance totals.
| Column | Type | Notes |
|---|
COMMUNICATION_KEY | TEXT | Primary key |
CAMPAIGN_KEY | TEXT | FK → CAMPAIGNS |
NAME | TEXT | Communication name |
COMMUNICATION_TYPE_NAME | TEXT | Channel type (e.g. Email, Direct Mail) |
TOTAL_GIFTS | NUMBER | Total number of gifts received |
TOTAL_GIFT_AMOUNT | NUMBER | Total dollar amount of gifts received |
AVERAGE_GIFT_AMOUNT | NUMBER | Average gift amount |
COMMUNICATION_IS_DELETED | BOOLEAN | Soft delete flag |
SEGMENTS
One row per recipient subgroup within a communication. SEGMENT_KEY is the FK carried on GIFTS — it is the entry point for all campaign attribution queries.
| Column | Type | Notes |
|---|
SEGMENT_KEY | TEXT | Primary key — also FK on GIFTS.SEGMENT_KEY |
COMMUNICATION_KEY | TEXT | FK → COMMUNICATIONS |
NAME | TEXT | Segment name (e.g. “Board Members”, “Lapsed Donors”) |
CODE | TEXT | Segment code for mail/print processing |
TOTAL_GIFTS | NUMBER | Total gifts attributed to this segment |
TOTAL_GIFT_AMOUNT | NUMBER | Total gift revenue attributed to this segment |
SEGMENT_IS_DELETED | BOOLEAN | Soft delete flag |
GIFTS
GIFTS appears in this diagram as the attribution target — the transaction that carries SEGMENT_KEY back to the campaign hierarchy. The full gift structure is documented in Gifts — extended reference.
| Column | Type | Notes |
|---|
GIFT_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS — the giving unit |
SEGMENT_KEY | TEXT | FK → SEGMENTS — campaign attribution entry point |
GIFT_DATE_UTC | TIMESTAMP | Date the gift was made |
AMOUNT | NUMBER | Total gift amount |
GIFT_IS_DELETED | BOOLEAN | Soft delete flag |
CONTACTS appears in this diagram as the giving unit linked to each gift. The full contacts structure is documented in Contacts — extended reference.
| Column | Type | Notes |
|---|
CONTACT_KEY | TEXT | Primary key |
NAME | TEXT | Contact display name |
CONTACT_IS_DELETED | BOOLEAN | Soft delete flag |
Data freshness: These tables sync continuously from Virtuous CRM. Typical lag is under 4 hours. To check when a record was last updated, inspect the SF__ROW_SYNCED_DATE_TIME_UTC column on any table.
