Campaigns

Grain: CAMPAIGNS — one row per campaign. COMMUNICATIONS — one row per channel within a campaign. SEGMENTS — one row per recipient subgroup within a communication.

The campaign hierarchy flows top-down at setup (Campaign → Communication → Segment) but is queried bottom-up from the gift (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

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

CONTACT_SEGMENT_STATISTICS

One row per contact per segment — pre-aggregated giving history scoped to a specific segment. Useful for measuring a contact’s historical response to a campaign or communication.

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.

Overview

Reference

Campaigns

Table columns

CAMPAIGNS

COMMUNICATIONS

SEGMENTS

EMAILS

EMAIL_VERSIONS

SENT_EMAILS

EVENTS

CONTACT_SEGMENT_STATISTICS

​Table columns

​CAMPAIGNS

​COMMUNICATIONS

​SEGMENTS

​EMAILS

​EMAIL_VERSIONS

​SENT_EMAILS

​EVENTS

​CONTACT_SEGMENT_STATISTICS

Table columns

CAMPAIGNS

COMMUNICATIONS

SEGMENTS

EMAILS

EMAIL_VERSIONS

SENT_EMAILS

EVENTS

CONTACT_SEGMENT_STATISTICS