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_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 |
| 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 |
| 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.
