Grain: CAMPAIGNS — one row per campaign. COMMUNICATIONS — one row per channel within a campaign. SEGMENTS — one row per recipient subgroup within a communication.
GIFTS.SEGMENT_KEY). This page covers all tables in the hierarchy plus the email and event tables that connect to it.
Key rule: GIFTS.SEGMENT_KEY is the entry point into this hierarchy. Start there and join upward to reach campaign-level totals.
Table columns
Tables on this page: CAMPAIGNS · COMMUNICATIONS · SEGMENTS · EMAILS · EMAIL_VERSIONS · SENT_EMAILS · EVENTS · CONTACT_SEGMENT_STATISTICS
CAMPAIGNS
One row per top-level marketing initiative. Contains giving goals and date range for the campaign.
| Column | Type | Notes |
|---|
CAMPAIGN_KEY | TEXT | Primary key |
NAME | TEXT | Campaign name (e.g. “End of Year 2025”) |
DESCRIPTION | TEXT | Extended description |
START_DATE_TIME_UTC | TIMESTAMP | Campaign start date |
END_DATE_TIME_UTC | TIMESTAMP | Campaign end date |
GIVING_GOAL | NUMBER | Dollar goal for the campaign |
NEW_GIVER_GOAL | NUMBER | Goal for new donors |
TOTAL_GIFT_GOAL | NUMBER | Goal for number of gifts |
HASHTAG | TEXT | Associated social media hashtag |
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) |
DESCRIPTION | TEXT | Extended description |
START_DATE_TIME_UTC | TIMESTAMP | Communication start date |
GIVING_GOAL | NUMBER | Dollar goal for this communication |
INTERNAL_COST_ESTIMATE | NUMBER | Estimated internal cost |
VENDOR_COST_ESTIMATE | NUMBER | Estimated vendor cost |
TOTAL_CONTACTS | NUMBER | Total contacts in this communication |
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 |
MEDIAN_GIFT_AMOUNT | NUMBER | Median gift amount |
HIGHEST_GIFT_AMOUNT | NUMBER | Largest single gift received |
TOTAL_PLEDGES | NUMBER | Total number of pledges |
TOTAL_AMOUNT_PLEDGED | NUMBER | Total dollar amount pledged |
COMMUNICATION_IS_DELETED | BOOLEAN | Soft delete flag |
COMMUNICATION_IS_ARCHIVED | BOOLEAN | Communication is archived |
SEGMENTS
One row per recipient subgroup within a communication. SEGMENT_KEY is the FK carried on GIFTS — it is the entry point for 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”) |
DESCRIPTION | TEXT | Extended description |
CODE | TEXT | Segment code for mail/print processing |
PACKAGE_CODE | TEXT | Package code |
PACKAGE_DESCRIPTION | TEXT | Package description |
TOTAL_CONTACTS | NUMBER | Number of contacts in this segment |
TOTAL_GIFTS | NUMBER | Total gifts attributed to this segment |
TOTAL_GIFT_AMOUNT | NUMBER | Total gift revenue attributed to this segment |
AVERAGE_GIFT_AMOUNT | NUMBER | Average gift amount |
MEDIAN_GIFT_AMOUNT | NUMBER | Median gift amount |
HIGHEST_GIFT_AMOUNT | NUMBER | Largest single gift |
COST_PER_CONTACT | NUMBER | Production cost per contact |
TOTAL_PLEDGES | NUMBER | Total pledges attributed to this segment |
TOTAL_AMOUNT_PLEDGED | NUMBER | Total pledge amount |
SEGMENT_IS_DELETED | BOOLEAN | Soft delete flag |
EMAILS
One row per email created within a communication. Contains aggregate performance metrics across all versions.
| Column | Type | Notes |
|---|
EMAIL_KEY | TEXT | Primary key |
COMMUNICATION_KEY | TEXT | FK → COMMUNICATIONS |
SEGMENT_KEY | TEXT | FK → SEGMENTS (optional) |
EMAIL_LIST_KEY | TEXT | FK → EMAIL_LISTS — the list the email was sent to |
NAME | TEXT | Email name |
SUBJECT | TEXT | Email subject line |
FROM_NAME | TEXT | Sender display name |
FROM_EMAIL | TEXT | Sender email address |
RECIPIENTS | NUMBER | Total recipients |
OPENS | NUMBER | Total opens |
UNIQUE_OPENS | NUMBER | Unique individuals who opened |
OPEN_RATE | FLOAT | Open rate (opens / recipients) |
CLICKS | NUMBER | Total clicks |
UNIQUE_CLICKS | NUMBER | Unique individuals who clicked |
CLICK_RATE | FLOAT | Click rate |
USE_SPLIT_TEST | BOOLEAN | Email uses A/B split testing |
EMAIL_IS_DELETED | BOOLEAN | Soft delete flag |
EMAIL_IS_ARCHIVED | BOOLEAN | Email is archived |
EMAIL_VERSIONS
One row per version of an email (e.g. version A and version B in a split test). Individual delivery records live in SENT_EMAILS.
| Column | Type | Notes |
|---|
EMAIL_VERSION_KEY | TEXT | Primary key |
EMAIL_KEY | TEXT | FK → EMAILS |
NAME | TEXT | Version name |
SUBJECT | TEXT | Subject line for this version |
FROM_NAME | TEXT | Sender display name |
FROM_EMAIL | TEXT | Sender email address |
PUBLISHED_DATE_TIME_UTC | TIMESTAMP | Date this version was published and sent |
RECIPIENTS | NUMBER | Recipients for this version |
OPENS | NUMBER | Total opens |
UNIQUE_OPENS | NUMBER | Unique opens |
OPEN_RATE | FLOAT | Open rate |
CLICKS | NUMBER | Total clicks |
UNIQUE_CLICKS | NUMBER | Unique clicks |
CLICK_RATE | FLOAT | Click rate |
PUBLISHED | BOOLEAN | Version has been published and sent |
EMAIL_VERSION_IS_DELETED | BOOLEAN | Soft delete flag |
SENT_EMAILS
One row per email delivered to a contact individual. This is the most granular email table — use it for individual-level engagement analysis (opens, clicks, unsubscribes).
| Column | Type | Notes |
|---|
SENT_EMAIL_KEY | TEXT | Primary key |
EMAIL_VERSION_KEY | TEXT | FK → EMAIL_VERSIONS — the specific version sent |
CONTACT_KEY | TEXT | FK → CONTACTS |
CONTACT_INDIVIDUAL_KEY | TEXT | FK → CONTACT_INDIVIDUALS — the specific recipient |
EMAIL_ADDRESS | TEXT | Email address the message was sent to |
DELIVERY_STATUS | NUMBER | Delivery outcome — decode with TYPE_REFERENCES (Entity: SentEmail, Property: DeliveryStatus) |
SCHEDULED_TO_SEND_UTC | TIMESTAMP | Scheduled send time |
FIRST_OPENED_UTC | TIMESTAMP | First open timestamp |
LAST_OPENED_UTC | TIMESTAMP | Most recent open timestamp |
FIRST_CLICKED_UTC | TIMESTAMP | First click timestamp |
LAST_CLICKED_UTC | TIMESTAMP | Most recent click timestamp |
UNSUBSCRIBE_DATE_TIME_UTC | TIMESTAMP | Date the recipient globally unsubscribed |
GROUP_UNSUBSCRIBE_DATE_TIME_UTC | TIMESTAMP | Date the recipient unsubscribed from the email list |
OPENED | BOOLEAN | Email was opened at least once |
CLICKED | BOOLEAN | Email was clicked at least once |
UNSUBSCRIBE | BOOLEAN | Recipient globally unsubscribed |
GROUP_UNSUBSCRIBE | BOOLEAN | Recipient unsubscribed from this list |
MARKED_AS_SPAM | BOOLEAN | Recipient marked the email as spam |
SENT_EMAIL_IS_DELETED | BOOLEAN | Soft delete flag |
EVENTS
One row per event linked to a campaign or communication. Contains date, location, attendance goals, and actuals.
| Column | Type | Notes |
|---|
EVENT_KEY | TEXT | Primary key |
CAMPAIGN_KEY | TEXT | FK → CAMPAIGNS |
COMMUNICATION_KEY | TEXT | FK → COMMUNICATIONS (optional) |
SEGMENT_KEY | TEXT | FK → SEGMENTS (optional) |
NAME | TEXT | Event name |
EVENT_TYPE | TEXT | Descriptive event type |
DESCRIPTION | TEXT | Event description |
START_DATE_TIME_UTC | TIMESTAMP | Event start date and time |
END_DATE_TIME_UTC | TIMESTAMP | Event end date and time |
LOCATION_NAME | TEXT | Venue name |
ADDRESS1 | TEXT | Venue address line 1 |
CITY | TEXT | Venue city |
STATE_CODE | TEXT | Venue state |
POSTAL | TEXT | Venue postal code |
TIME_ZONE | TEXT | Event time zone |
TOTAL_INVITES | NUMBER | Total invitations sent |
TOTAL_RSVPS | NUMBER | Total RSVPs received |
TOTAL_ATTENDED | NUMBER | Total contacts who attended |
INVITE_ONLY | BOOLEAN | Event is invite-only |
RSVP_REQUIRED | BOOLEAN | RSVP is required |
EVENT_IS_DELETED | BOOLEAN | Soft delete flag |
EVENT_IS_ARCHIVED | BOOLEAN | Event is archived |
| Column | Type | Notes |
|---|
CONTACT_SEGMENT_STATISTIC_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
SEGMENT_KEY | TEXT | FK → SEGMENTS |
COMMUNICATION_KEY | TEXT | FK → COMMUNICATIONS |
LIFE_TO_DATE_GIVING | NUMBER | Total giving attributed to this segment (all time) |
LIFE_TO_DATE_GIFT_COUNT | NUMBER | Total gift count for this segment |
ROLLING_YEAR_GIVING | NUMBER | Giving in the past 12 months |
LAST_GIFT_AMOUNT | NUMBER | Most recent gift amount in this segment |
LAST_GIFT_DATE_UTC | TIMESTAMP | Most recent gift date |
FIRST_GIFT_DATE_UTC | TIMESTAMP | First gift date in this segment |
LARGEST_GIFT_AMOUNT | NUMBER | Largest gift in this segment |
AVERAGE_GIFT_AMOUNT | NUMBER | Average gift amount |
CONTACT_SEGMENT_STATISTIC_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.
