obt_customer_support_ticket_messages

Every message in every support conversation: who wrote it, when, on which channel, and the text.

Grain: one row per sm_message_key.
Date field: message_created_at_local_datetime.
Filters to start with: is_message_from_agent to separate customer messages from agent replies; is_message_public to drop internal notes.
Joins: obt_customer_support_tickets via sm_ticket_key.

Query examples

Customer Support

Columns

Column	Type	Description
`sm_message_key`	STRING	Source-system-namespaced analytics key for the message
`sm_ticket_key`	STRING	Matching ticket key; joins to obt_customer_support_tickets
`sm_store_id`	STRING	SourceMedium’s unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.
`has_message_attachments`	BOOL	True when the message carried at least one attachment.
`is_message_from_agent`	BOOL	True when the help desk marks the message as sent by your team, which can include automations; false when the customer sent it. Filter on false to read what customers are saying.
`is_message_public`	BOOL	True when the customer could see the message, false for internal notes. Some help desks have no internal-note flag, so the value is always true for them.
`message_body_text`	STRING	The help desk’s plain-text version of the message body, where it supplies one. Some help desks remove quoted earlier replies; others can pass HTML markup through, so strip tags before matching keywords. This is the column to scan for themes and complaints.
`message_communication_channel`	STRING	Channel the message travelled on, in the help desk’s own names, so the values differ by help desk. Platform notes below lists them. Group on this for a channel split.
`message_created_at`	TIMESTAMP	UTC timestamp when the message was created in the help desk.
`message_created_at_local_datetime`	DATETIME	Message creation time in the store’s reporting timezone. Use this for daily and weekly reporting.
`message_entry_method`	STRING	How the message entered the help desk, in its own names: typed by an agent, sent by an automation, received by email, and so on. The values differ by help desk, and not every help desk separates automations; Platform notes below lists them.
`message_id`	STRING	The help desk’s own id for the message.
`message_receiver_email`	STRING	Email address of the recipient, when the channel carries one.
`message_receiver_full_name`	STRING	Display name of the recipient.
`message_receiver_id`	STRING	The help desk’s id for the recipient.
`message_sender_email`	STRING	Email address of the sender, when the channel carries one.
`message_sender_full_name`	STRING	Display name of the sender. For agent messages this is the agent’s name, so you can group on it for workload.
`message_sender_id`	STRING	The help desk’s id for whoever sent the message.
`message_sent_at`	TIMESTAMP	UTC timestamp when the message was sent, where the help desk records a send time. Usually NULL for internal notes, and always NULL for a help desk that records none; use message_created_at for timing.
`message_sent_at_local_datetime`	DATETIME	Message sent time in the store’s reporting timezone. NULL whenever message_sent_at is.
`message_subject`	STRING	Subject line of the message, where the channel has one. NULL for most chat, phone and social messages.
`sm_channel`	STRING	Always online_dtc. Support data is not split by sales channel.
`source_system`	STRING	Help desk the message came from, as a lower-case source id.
`ticket_id`	STRING	The help desk’s own id for the ticket this message belongs to.

Platform notes

The Gorgias and Richpanel integrations fill this table today.

Gorgias.
- Internal notes are the messages with is_message_public false, and most have no message_sent_at.
- message_body_text is Gorgias’s stripped text, with quoted earlier replies removed.
- Channels include email, chat, sms, phone, contact_form, help-center, instagram-direct-message, instagram-comment, facebook-messenger and internal-note. Entry methods include email, helpdesk (typed by an agent), rule (sent by a Gorgias rule), api, gorgias_chat and contact_form. Exclude message_entry_method = 'rule' to drop rule-sent replies.
- Calls from the Aircall integration arrive on the aircall channel as call events (answered, ended, a recording link), not a transcript. Each call is logged as a customer message and an agent message minutes apart, so its first response time reads near zero. Other calls arrive on phone.
Richpanel.
- Richpanel has no internal-note flag, so is_message_public is always true.
- It records no send time, so message_sent_at is always NULL.
- message_body_text can carry HTML markup where Richpanel supplies no plain-text version. Strip tags before matching keywords.
- Channels use Richpanel’s own names, such as email, gmail, comment, klaviyosms, instagram_message, instagram_comment, messenger and text. Entry methods are comment, text and voicecomment, which do not separate agents from automations.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

obt_customer_support_ticket_messages

Query examples

Columns

Platform notes

​Query examples

​Columns

​Platform notes

Query examples

Columns

Platform notes