Contacts

Grain: CONTACTS — one row per giving unit (household or organization). CONTACT_INDIVIDUALS — one row per person within a contact.

CONTACTS is the giving unit — every gift, pledge, and recurring commitment belongs to a contact. This page maps all tables that hang off the contact: the people within it, stewardship activity, memberships, engagement statistics by segment, and receipting history.

Key rule: Gifts belong to CONTACTS, not CONTACT_INDIVIDUALS. Email is sent to individuals. Always join to CONTACT_INDIVIDUALS when you need a person’s name or email address.

Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).

Table columns

Tables on this page: CONTACTS · CONTACT_INDIVIDUALS · NOTES · TASKS · CONTACT_MEMBERSHIPS · MEMBERSHIPS · CONTACT_SEGMENT_STATISTICS · RECEIPTS · RECEIPTING_STATEMENT_RUNS

One row per giving unit. Pre-aggregated giving totals (LIFE_TO_DATE_GIVING, YEAR_TO_DATE_GIVING) are maintained by Virtuous and do not require joining to GIFTS.

Column	Type	Notes
`CONTACT_KEY`	TEXT	Primary key
`CONTACT_TYPE`	TEXT	Customizable type — base types are Household, Foundation, Organization
`BASE_CONTACT_TYPE`	NUMBER	Numeric base type — decode with TYPE_REFERENCES (Entity: `Contact`, Property: `BaseContactType`)
`NAME`	TEXT	Contact display name
`INFORMAL_NAME`	TEXT	Informal or nickname-based display name
`PRIMARY_CITY`	TEXT	City from primary address
`PRIMARY_STATE_CODE`	TEXT	State from primary address
`PRIMARY_POSTAL`	TEXT	Postal code from primary address
`PRIMARY_COUNTRY_CODE`	TEXT	Country code from primary address
`LIFE_TO_DATE_GIVING`	NUMBER	Pre-aggregated total giving (all time)
`YEAR_TO_DATE_GIVING`	NUMBER	Pre-aggregated total giving (current calendar year)
`FIRST_GIFT_AMOUNT`	NUMBER	Amount of the first gift
`FIRST_GIFT_DATE_UTC`	TIMESTAMP	Date of the first gift
`LAST_GIFT_AMOUNT`	NUMBER	Amount of the most recent gift
`LAST_GIFT_DATE_UTC`	TIMESTAMP	Date of the most recent gift
`LARGEST_GIFT_AMOUNT`	NUMBER	Largest single gift amount
`LARGEST_GIFT_DATE_UTC`	TIMESTAMP	Date of the largest gift
`AVERAGE_GIFT_AMOUNT`	NUMBER	Average gift amount
`GIFTS_PER_YEAR`	FLOAT	Average number of gifts per calendar year
`GIFT_ASK_AMOUNT`	NUMBER	Current gift ask amount on the contact record
`FINANCIAL_SCORE`	NUMBER	Virtuous-calculated score based on giving amount, frequency, and recency
`WEBSITE`	TEXT	Contact’s website
`DESCRIPTION`	TEXT	Free-text description
`ANNIVERSARY_DATE`	TIMESTAMP	Wedding anniversary date
`CONTACT_IS_DELETED`	BOOLEAN	Soft delete flag — always filter `WHERE CONTACT_IS_DELETED = FALSE`
`CONTACT_IS_PRIVATE`	BOOLEAN	Contact is marked private
`CONTACT_IS_ARCHIVED`	BOOLEAN	Contact is archived

CONTACT_INDIVIDUALS

One row per person within a contact. Filter CONTACT_INDIVIDUAL_IS_PRIMARY = TRUE to get the main person. Email and phone live here, not on CONTACTS.

Column	Type	Notes
`CONTACT_INDIVIDUAL_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`FIRST_NAME`	TEXT	First name
`MIDDLE_NAME`	TEXT	Middle name
`LAST_NAME`	TEXT	Last name
`PREFIX`	TEXT	Name prefix (Mr., Dr., etc.)
`SUFFIX`	TEXT	Name suffix
`GENDER_NAME`	TEXT	Gender
`PRIMARY_EMAIL`	TEXT	Primary email address
`PRIMARY_PHONE`	TEXT	Primary phone number
`BIRTH_DATE`	TIMESTAMP	Full date of birth
`DECEASED_DATE`	TIMESTAMP	Date of death (if applicable)
`PASSION`	TEXT	Areas of interest logged on the individual
`FIRST_EMAIL_SENT_UTC`	TIMESTAMP	Date of the first email sent to this individual
`LAST_EMAIL_SENT_UTC`	TIMESTAMP	Date of the most recent email sent
`FIRST_EMAIL_OPENED_UTC`	TIMESTAMP	Date of the first email opened
`LAST_EMAIL_OPENED_UTC`	TIMESTAMP	Date of the most recent email opened
`CONTACT_INDIVIDUAL_IS_PRIMARY`	BOOLEAN	True for the primary person on the contact
`CONTACT_INDIVIDUAL_IS_DECEASED`	BOOLEAN	Individual is deceased
`CONTACT_INDIVIDUAL_IS_GLOBALLY_UNSUBSCRIBED`	BOOLEAN	Individual has globally unsubscribed from email
`CONTACT_INDIVIDUAL_IS_DELETED`	BOOLEAN	Soft delete flag

NOTES

One row per stewardship note logged against a contact or individual. PLAIN_TEXT_VALUE contains the note content without formatting.

Column	Type	Notes
`NOTE_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`CONTACT_INDIVIDUAL_KEY`	TEXT	FK → CONTACT_INDIVIDUALS (optional) — individual the note is about
`DATE_TIME_UTC`	TIMESTAMP	Date and time the note was recorded
`VALUE`	TEXT	Note content (may include HTML formatting)
`PLAIN_TEXT_VALUE`	TEXT	Note content as plain text
`CONTACT_NOTE_TYPE`	TEXT	Note type (e.g. Meeting, Call, Email)
`TIME_SPENT`	NUMBER	Time spent (in minutes) if logged
`IMPORTANT`	BOOLEAN	Note is flagged as important
`NOTE_IS_PRIVATE`	BOOLEAN	Note is private
`NOTE_IS_DELETED`	BOOLEAN	Soft delete flag

TASKS

One row per action item on a contact. Can be linked to a gift ask. TASK_STATUS is a numeric code.

Column	Type	Notes
`TASK_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`CONTACT_INDIVIDUAL_KEY`	TEXT	FK → CONTACT_INDIVIDUALS (optional)
`GIFT_ASK_KEY`	TEXT	FK → GIFT_ASKS (optional) — linked gift ask
`NAME`	TEXT	Task name or title
`DESCRIPTION`	TEXT	Extended task description
`DUE_DATE_TIME_UTC`	TIMESTAMP	Task due date
`RESOLUTION_DATE_TIME_UTC`	TIMESTAMP	Date the task was completed or dismissed
`NOTIFICATION_DATE_TIME_UTC`	TIMESTAMP	Date a reminder notification is set
`TASK_STATUS`	NUMBER	Current status — decode with TYPE_REFERENCES (Entity: `Task`, Property: `TaskStatus`)
`RESOLUTION_TYPE`	NUMBER	How the task was closed — decode with TYPE_REFERENCES (Entity: `Task`, Property: `ResolutionType`)
`MILESTONE_TYPE`	NUMBER	If system-generated, the milestone that triggered it — decode with TYPE_REFERENCES (Entity: `Task`, Property: `MilestoneType`)
`TASK_IS_DELETED`	BOOLEAN	Soft delete flag

CONTACT_MEMBERSHIPS

One row per membership held by a contact or individual. Optionally linked to a recurring gift that funds the membership.

Column	Type	Notes
`CONTACT_MEMBERSHIP_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`CONTACT_INDIVIDUAL_KEY`	TEXT	FK → CONTACT_INDIVIDUALS (optional) — if membership is held by a specific individual
`MEMBERSHIP_KEY`	TEXT	FK → MEMBERSHIPS — the membership program
`RECURRING_GIFT_KEY`	TEXT	FK → RECURRING_GIFTS (optional) — the recurring gift funding this membership
`START_DATE`	TIMESTAMP	Membership start date
`EXPIRATION_DATE`	TIMESTAMP	Membership expiration date
`LAST_PAYMENT_DATE`	TIMESTAMP	Date of the most recent payment
`LAST_PAYMENT_AMOUNT`	NUMBER	Amount of the most recent payment
`TOTAL_PAYMENTS`	NUMBER	Total number of payments made
`CONTACT_MEMBERSHIP_IS_DELETED`	BOOLEAN	Soft delete flag

MEMBERSHIPS

One row per membership program defined in Virtuous. Referenced by CONTACT_MEMBERSHIPS.

Column	Type	Notes
`MEMBERSHIP_KEY`	TEXT	Primary key
`NAME`	TEXT	Membership program name
`DESCRIPTION`	TEXT	Program description
`COST`	NUMBER	One-time cost
`RECURRING_COST`	NUMBER	Recurring payment cost
`FREQUENCY`	NUMBER	Payment frequency — decode with TYPE_REFERENCES (Entity: `Membership`, Property: `Frequency`)
`EXPIRES_IN_MONTHS`	NUMBER	Duration in months before expiration
`GRACE_PERIOD_IN_DAYS`	NUMBER	Grace period in days after expiration
`MEMBERSHIP_IS_ARCHIVED`	BOOLEAN	Program is archived
`MEMBERSHIP_IS_DELETED`	BOOLEAN	Soft delete flag

CONTACT_SEGMENT_STATISTICS

One row per contact per segment — pre-aggregated giving statistics scoped to a specific campaign segment. Useful for comparing a contact’s response to individual campaigns.

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 to this segment (all time)
`LIFE_TO_DATE_GIFT_COUNT`	NUMBER	Total gift count to this segment (all time)
`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 in this segment
`FIRST_GIFT_AMOUNT`	NUMBER	First gift amount in this segment
`FIRST_GIFT_DATE_UTC`	TIMESTAMP	First gift date in this segment
`LARGEST_GIFT_AMOUNT`	NUMBER	Largest gift amount in this segment
`AVERAGE_GIFT_AMOUNT`	NUMBER	Average gift amount in this segment
`CONTACT_SEGMENT_STATISTIC_IS_DELETED`	BOOLEAN	Soft delete flag

RECEIPTS

One row per receipt issued to a contact. RECEIPT_TYPE and RECEIPT_MEDIUM are numeric codes. Individual gifts included in the receipt are tracked in RECEIPTED_GIFTS (see Receipts — extended reference).

Column	Type	Notes
`RECEIPT_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`RECEIPTING_STATEMENT_RUN_KEY`	TEXT	FK → RECEIPTING_STATEMENT_RUNS (optional) — statement batch this receipt came from
`RECEIPT_TYPE`	NUMBER	Type of receipt — decode with TYPE_REFERENCES (Entity: `Receipt`, Property: `ReceiptType`)
`RECEIPT_MEDIUM`	NUMBER	Delivery medium — decode with TYPE_REFERENCES (Entity: `Receipt`, Property: `ReceiptMedium`)
`YEAR`	NUMBER	Tax year for the receipt
`EMAIL_ADDRESS`	TEXT	Email address the receipt was sent to
`RECEIPT_IS_AVAILABLE_TO_DONOR`	BOOLEAN	Receipt is visible in the donor portal
`RECEIPT_IS_DELETED`	BOOLEAN	Soft delete flag

RECEIPTING_STATEMENT_RUNS

One row per annual statement run — a batch process that generates year-end tax receipts for a group of contacts.

Column	Type	Notes
`RECEIPTING_STATEMENT_RUN_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS (optional) — populated when run is for a single contact
`TITLE`	TEXT	Statement run title
`STATEMENT_DATE`	TIMESTAMP	Date the statement covers
`RECEIPT_FORMAT`	NUMBER	Output format (PDF, email, etc.)
`RECEIPTING_STATEMENT_RUN_IS_AVAILABLE_TO_DONOR`	BOOLEAN	Statement is visible in the donor portal
`RECEIPTING_STATEMENT_RUN_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.

​Table columns

​CONTACTS

​CONTACT_INDIVIDUALS

​NOTES

​TASKS

​CONTACT_MEMBERSHIPS

​MEMBERSHIPS

​CONTACT_SEGMENT_STATISTICS

​RECEIPTS

​RECEIPTING_STATEMENT_RUNS

Table columns

CONTACTS

CONTACT_INDIVIDUALS

NOTES

TASKS

CONTACT_MEMBERSHIPS

MEMBERSHIPS

CONTACT_SEGMENT_STATISTICS

RECEIPTS

RECEIPTING_STATEMENT_RUNS