Snowflake data model: tables and columns

Hello Customer shares your feedback data through a Snowflake data share of 10 tables. This article describes each table, how the tables join, and how to keep your own copy up to date.

What is in the share

Every table carries EXT_TENANT_ID. Start from EXT_PARTICIPANT_TRANSACTIONS and join the other tables on the keys below.

Table	One row is	Joins on
`EXT_PARTICIPANT_TRANSACTIONS`	One participant in one transaction	—
`EXT_ANSWERS`	One answer to one question	Transaction id
`EXT_CUSTOM_ATTRIBUTES`	One custom metadata field of a transaction	Transaction id
`EXT_ISAAC_PREDICTIONS_V2`	One ISAAC mention in an answer	Answer id
`EXT_ISAAC_PREDICTIONS`	One ISAAC prediction item (redundant)	Answer id
`EXT_EMAIL_STATS`	Latest email status of a participant	Participant + touchpoint id
`EXT_EMAIL_STATS_HISTORY`	One email event	Transaction id
`EXT_EMAIL_STATS_SUMMARY`	Daily email counts of one touchpoint	Touchpoint id
`EXT_CONVERSATIONS`	Tags, notes and status of a transaction	Transaction id
`EXT_DELETED_TRANSACTIONS`	One deleted transaction	Transaction id

Rules that apply to every table

Timestamps are UTC, stored as TIMESTAMP_NTZ with no time zone attached.
Identifiers are text. Columns prefixed EXT_ are Hello Customer ids.
UPDATED_AT means “last changed”. Use it to load incrementally. To report per period, use DATE_ANSWERED in EXT_PARTICIPANT_TRANSACTIONS.
Long format. Answers and custom attributes have one row per question or field, so new ones never add columns.
Empty means not provided. An empty value means the field was not provided or does not apply.

EXT_PARTICIPANT_TRANSACTIONS

One row is one participant during one transaction, for example one survey submission. Standard metadata fields come from the Participant Attribute Mapper (PAM).

Column	Type	Description
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Id of the participant’s transaction (e.g. a survey submission)
`EXT_PARTICIPANT_ID`	TEXT	Id of the participant; one participant can have several transactions
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`EXT_TOUCHPOINT_ID`	TEXT	Id of the touchpoint as shown in the platform
`TOUCHPOINT_NAME`	TEXT	Name of the touchpoint
`DATE_SURVEYED`	TIMESTAMP_NTZ	When the survey was sent (UTC)
`DATE_ANSWERED`	TIMESTAMP_NTZ	When the survey was answered (UTC)
`DATE_INTERACTION`	DATE	Date of the interaction the survey is about, if provided
`PARTICIPANT_LANGUAGE`	TEXT	Language of the participant, if provided
`CUSTOM_ID`	TEXT	Customer id in the company’s own systems, if provided
`CUSTOMER_TYPE`	TEXT	Customer type, if provided
`CUSTOMER_YEARS`	NUMBER(38,2)	Customer tenure in years, if provided
`TITLE`	TEXT	Title, if provided
`FIRST_NAME`	TEXT	First name, if provided
`LAST_NAME`	TEXT	Last name, if provided
`EMAIL`	TEXT	Email address of the participant
`PHONE`	TEXT	Phone number, if provided
`GENDER`	TEXT	Gender, if provided
`AGE`	NUMBER(38,2)	Age, if provided
`BIRTHDATE`	DATE	Birth date, if provided
`ZIPCODE`	TEXT	Zip code, if provided
`COUNTRY_OF_TRANSACTION`	TEXT	Country where the transaction took place, if provided
`EMPLOYEE`	TEXT	Employee linked to the transaction, if provided
`REASON_OF_CONTACT`	TEXT	Reason of contact, if provided
`CATEGORIES`	TEXT	Categories linked to the transaction, if provided
`FIRST_TIME_RIGHT`	BOOLEAN	Whether the request was resolved first time right, if provided
`PIECES`	NUMBER(38,2)	Number of pieces in the transaction, if provided
`TRANSACTION_AMOUNT`	NUMBER(38,2)	Amount of the transaction, if provided

Updated metadata. Personal attributes can change when a participant is uploaded again. Replace the transaction you loaded earlier with the updated one.

Language. To filter on language, use PARTICIPANT_LANGUAGE, not the question text.

EXT_ANSWERS

One row is one answer to one question.

Column	Type	Description
`EXT_ANSWER_ID`	TEXT	Id of the answer
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Foreign key to `EXT_PARTICIPANT_TRANSACTIONS`
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`EXT_QUESTION_ID`	TEXT	Id of the question in the survey
`QUESTION_TYPE`	TEXT	Type of question (NPS, CSAT, Yes/No, multiple choice, 0–10 score, …)
`QUESTION`	TEXT	Question as asked, in the participant’s language; for a metric, the score question and its open-text question are concatenated
`ANSWER_VALUE`	NUMBER(38,0)	Score, or the Yes/No answer as a number
`ANSWER_TEXT`	TEXT	Text answer, or the selected multiple-choice options
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the answer (UTC), for incremental loading

Updated answers. When enabled on an email touchpoint, participants can change their answer. Replace the answer you loaded earlier with the updated one.

EXT_CUSTOM_ATTRIBUTES

One row is one custom metadata field of one transaction: metadata the PAM could not map to a standard field.

Column	Type	Description
`CUSTOM_ATTRIBUTES_ID`	TEXT	Id of the custom attribute row
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Foreign key to `EXT_PARTICIPANT_TRANSACTIONS`
`EXT_PARTICIPANT_ID`	TEXT	Id of the participant
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`METADATA_FIELD`	TEXT	Name of the custom field (e.g. “customer tier”)
`METADATA_DATA_TYPE`	TEXT	Data type set in the PAM (e.g. string, number)
`METADATA_VALUE`	TEXT	Value of the field (e.g. “Premium”), always stored as text
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the field (UTC), for incremental loading

EXT_ISAAC_PREDICTIONS_V2

One row is one mention that ISAAC detected in an answer, with a single category path. The question context is included, so no join with EXT_ANSWERS is needed.

Column	Type	Description
`EXT_MENTION_ID`	TEXT	Id of the mention
`EXT_ANSWER_ID`	TEXT	Foreign key to `EXT_ANSWERS`
`EXT_QUESTION_ID`	TEXT	Id of the question the answer belongs to
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`SCORE_TYPE`	TEXT	Metric the prediction is linked to: CSAT, NPS or CES
`QUESTION_TYPE`	TEXT	Type of question, as in `EXT_ANSWERS`
`QUESTION_TEXT`	TEXT	Text of the question
`POLARITY`	TEXT	Very negative to very positive, and everything in between
`MC0`	TEXT	Main category of the mention
`SC0_1 … SC0_4`	TEXT	First to fourth subcategory of the mention
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the prediction (UTC), including rescoring

Rescoring. Replace all mentions of an answer when updated rows arrive.

EXT_ISAAC_PREDICTIONS (redundant)

Use EXT_ISAAC_PREDICTIONS_V2 instead. Since the latest ISAAC update this table is redundant. It is still in the share for existing integrations, but new integrations should not rely on it.

Column	Type	Description
`EXT_PREDICTION_ITEM_ID`	TEXT	Id of the prediction item
`EXT_ANSWER_ID`	TEXT	Foreign key to `EXT_ANSWERS`
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`SCORE_TYPE`	TEXT	Metric the prediction is linked to: CSAT, NPS or CES
`POLARITY`	TEXT	Very negative to very positive, and everything in between
`AREA_NAME_0`	TEXT	Area of the first prediction line (an area groups combinations of main and subcategories)
`MC0`	TEXT	Main category of the first prediction line
`SC0_1 … SC0_4`	TEXT	First to fourth subcategory of the first prediction line
`AREA_NAME_1`	TEXT	Redundant; area of the second prediction line (the “regarding”)
`MC1`	TEXT	Main category of the second prediction line
`SC1_1 … SC1_4`	TEXT	First to fourth subcategory of the second prediction line
`GIC_GROUP_PATHS`	ARRAY	Redundant since the latest Vera update; do not build on this column
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the prediction (UTC), including rescoring

EXT_EMAIL_STATS

One row holds the latest email status of a participant on a touchpoint, available from 1 January 2021.

Column	Type	Description
`EXT_PARTICIPANT_ID`	TEXT	Id of the participant
`EXT_TOUCHPOINT_ID`	TEXT	Id of the touchpoint
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`CREATED_AT`	TIMESTAMP_NTZ	When the participant was first uploaded (UTC)
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the email status (UTC), for incremental loading
`FIRST_NAME`	TEXT	First name
`LAST_NAME`	TEXT	Last name
`EMAIL`	TEXT	Email address
`LATEST_STATUS`	TEXT	Unsubscribed, Not delivered, Send, Delivered, Opened, Clicked or Answered
`REASON_NOT_DELIVERED`	TEXT	Only filled when the status is Not delivered
`DATE_UNSUBSCRIBED`	TIMESTAMP_NTZ	Only filled when the participant unsubscribed (UTC)
`IS_REMINDER_MAIL`	BOOLEAN	True for a reminder mail, false for the first mail

EXT_EMAIL_STATS_HISTORY

One row is one email event for a participant. Rows are added, not updated: load new rows by EVENT_TIMESTAMP.

Column	Type	Description
`EXT_PARTICIPANT_ID`	TEXT	Id of the participant
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Foreign key to `EXT_PARTICIPANT_TRANSACTIONS`
`EXT_TOUCHPOINT_ID`	TEXT	Id of the touchpoint
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`FIRST_NAME`	TEXT	First name
`LAST_NAME`	TEXT	Last name
`EMAIL`	TEXT	Email address
`EVENT_STATUS`	TEXT	Status reached by this event
`EVENT_TIMESTAMP`	TIMESTAMP_NTZ	When the event happened (UTC)
`EVENT_MESSAGE`	TEXT	Extra information about the event, such as a bounce reason
`IS_REMINDER_MAIL`	BOOLEAN	True for a reminder mail, false for the first mail

EXT_EMAIL_STATS_SUMMARY

One row holds the email funnel counts of one touchpoint on one day.

Column	Type	Description
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`EXT_TOUCHPOINT_ID`	TEXT	Id of the touchpoint
`TOUCHPOINT_NAME`	TEXT	Name of the touchpoint
`STATS_DATE`	DATE	Day the counts apply to
`UPLOADED`	NUMBER	Participants uploaded
`UNSENT`	NUMBER	Participants uploaded but not (yet) sent a mail
`SENT_FIRST_MAIL · SENT_REMINDER_MAIL · SENT_TOTAL`	NUMBER	Mails sent: first mail, reminder, total
`DELIVERED_FIRST_MAIL · DELIVERED_REMINDER · DELIVERED_TOTAL`	NUMBER	Mails delivered: first mail, reminder, total
`OPENED_FIRST_MAIL · OPENED_REMINDER · OPENED_TOTAL`	NUMBER	Mails opened: first mail, reminder, total
`ANSWERED_FIRST_MAIL · ANSWERED_REMINDER · ANSWERED_TOTAL`	NUMBER	Surveys answered after the first mail, after a reminder, total
`UNSUBSCRIBED`	NUMBER	Participants who unsubscribed

EXT_CONVERSATIONS

One row holds the tags, notes and status of one transaction in the conversation manager.

Column	Type	Description
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Foreign key to `EXT_PARTICIPANT_TRANSACTIONS`
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`TAGS`	TEXT	All tags added to the conversation in the platform
`NOTES`	TEXT	All notes added to the conversation in the platform
`CONVERSATION_STATUS`	TEXT	Current status of the conversation
`UPDATED_AT`	TIMESTAMP_NTZ	Last change to the conversation (UTC), for incremental loading

EXT_DELETED_TRANSACTIONS

One row is one transaction that was deleted in the platform. Use it to remove the transaction and everything linked to it from your own copy.

Column	Type	Description
`EXT_PARTICIPANT_TRANSACTION_ID`	TEXT	Id of the deleted transaction
`EXT_TENANT_ID`	TEXT	Tenant id of the company
`DATE_DELETED`	TIMESTAMP_NTZ	When the transaction was deleted (UTC)

Keeping your copy up to date

Load only the rows changed since your last run and replace your existing rows on the key below. Process EXT_DELETED_TRANSACTIONS last.

Table	New or changed rows by	Replace on
`EXT_PARTICIPANT_TRANSACTIONS`	to be confirmed	`EXT_PARTICIPANT_TRANSACTION_ID`
`EXT_ANSWERS`	`UPDATED_AT`	`EXT_ANSWER_ID`
`EXT_CUSTOM_ATTRIBUTES`	`UPDATED_AT`	`CUSTOM_ATTRIBUTES_ID`
`EXT_ISAAC_PREDICTIONS_V2`	`UPDATED_AT`	All rows of the same EXT_ANSWER_ID
`EXT_ISAAC_PREDICTIONS (redundant)`	`UPDATED_AT`	All rows of the same EXT_ANSWER_ID
`EXT_EMAIL_STATS`	`UPDATED_AT`	`EXT_PARTICIPANT_ID + EXT_TOUCHPOINT_ID`
`EXT_EMAIL_STATS_HISTORY`	`EVENT_TIMESTAMP`	Append only
`EXT_EMAIL_STATS_SUMMARY`	`STATS_DATE`	`EXT_TOUCHPOINT_ID + STATS_DATE`
`EXT_CONVERSATIONS`	`UPDATED_AT`	`EXT_PARTICIPANT_TRANSACTION_ID`
`EXT_DELETED_TRANSACTIONS`	`DATE_DELETED`	Delete the transaction and its answers, custom attributes, predictions and conversation