obt_customer_support_tickets

Customer support ticket analytics for lifecycle, agent performance, and channel analysis.

Grain: One row per sm_ticket_key.
Date field: ticket_created_at_local_datetime.
Filters to start with: is_ticket_spam = FALSE to leave spam out; ticket_communication_channel for platform segmentation (email, instagram-direct-message, etc.).
Joins: dim_customers via customer_id (platform-dependent; primarily Shopify).

Query examples

Customer Support

Columns

Column	Type	Description
`sm_ticket_key`	STRING	Source-system-namespaced analytics key for the ticket; not related to BigQuery clustering
`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.
`customer_id`	STRING	Source of truth customer ID from ecommerce platforms (e.g., Shopify)
`is_ticket_from_agent`	BOOL	Indicates whether the ticket was initiated by an agent (true) or customer (false)
`is_ticket_one_touch`	BOOL	Flag indicating if the ticket was resolved with a single touch
`is_ticket_spam`	BOOL	Whether the ticket is marked as spam
`is_ticket_unread`	BOOL	Indicates whether the ticket has unread messages
`sm_channel`	STRING	Always online_dtc. Support data is not split by sales channel.
`source_system`	STRING	Source system identifier
`source_system_customer_id`	STRING	Customer identifier from the customer support platform (e.g., Gorgias). NULL for Richpanel tickets (no stable customer ID in the raw source).
`ticket_api_path`	STRING	Source-system API resource path or URL for the ticket. For Gorgias this is a relative API path (e.g., /api/tickets/123456/); for Richpanel this is a full helpdesk URL.
`ticket_assignee_email`	STRING	Full email of the user assigned to the ticket
`ticket_assignee_full_name`	STRING	Full name of the user assigned to the ticket
`ticket_assignee_id`	STRING	Unique identifier for the agent assigned to the ticket. Links to the agent user profile in the customer support platform.
`ticket_assignee_profile_picture_url`	STRING	URL of the assignee’s profile picture
`ticket_assignee_team_id`	STRING	Help desk team the ticket is assigned to. Joins to obt_customer_support_teams.team_id within the same source_system and sm_store_id. How each help desk fills it is in Platform notes below.
`ticket_assignee_team_name`	STRING	Name of the team assigned to the ticket
`ticket_closed_at`	TIMESTAMP	UTC timestamp when the ticket was closed/resolved. NULL for open tickets; used with ticket_created_at to calculate resolution time.
`ticket_closed_at_local_datetime`	DATETIME	Ticket closed timestamp in local timezone.
`ticket_communication_channel`	STRING	The specific platform or channel used for ongoing communication with the customer. Represents the “channel” field in Gorgias that indicates where the conversation takes place (instagram-direct-message, email, etc.).
`ticket_created_at`	TIMESTAMP	Timestamp when the ticket was created (UTC)
`ticket_created_at_local_datetime`	DATETIME	Timestamp when the ticket was created (local timezone)
`ticket_csat_score`	INT64	The ticket’s CSAT score given by the customer
`ticket_custom_field_csv`	STRING	Comma-separated list of custom field ID-value pairs for the ticket
`ticket_customer_email`	STRING	Email address of the customer who created the ticket
`ticket_customer_first_name`	STRING	First name of the customer who created the ticket. Extracted from the customer support platform; may be NULL if not collected.
`ticket_customer_full_name`	STRING	Full name of the customer who created the ticket (concatenation of first and last name). Extracted from the customer support platform; may be NULL if not collected.
`ticket_customer_last_name`	STRING	Last name of the customer who created the ticket. Extracted from the customer support platform; may be NULL if not collected.
`ticket_entry_method`	STRING	The method through which the ticket was initially created in the help desk. Gorgias supplies its “via” field; Richpanel exposes only “via.channel”, which is the single field it has for both this and ticket_communication_channel.
`ticket_excerpt`	STRING	Brief excerpt of the ticket content
`ticket_external_id`	STRING	External system identifier for the ticket
`ticket_id`	STRING	Original ticket identifier from the source system
`ticket_last_message_at`	TIMESTAMP	UTC timestamp of the last message sent or received in the ticket conversation. Used to track ticket activity and identify stale tickets.
`ticket_last_message_at_local_datetime`	DATETIME	Timestamp of the last message in the ticket in local timezone.
`ticket_message_count`	INT64	Total number of messages exchanged in the ticket conversation. Includes both customer and agent messages; used for interaction analysis.
`ticket_priority`	STRING	Priority level of the ticket
`ticket_resolution_time_hours`	INT64	Time taken to resolve the ticket in hours. In rare occasions where ticket creation time is after ticket closed time, the resolution time is 0.
`ticket_status`	STRING	Current status of the ticket, open or closed
`ticket_subject`	STRING	Subject line of the ticket
`ticket_tag_names_csv`	STRING	Comma-separated list of tag names associated with the ticket
`ticket_updated_at`	TIMESTAMP	UTC timestamp when the ticket was last updated/modified. Tracks any changes to ticket status, assignee, or new messages.
`ticket_updated_at_local_datetime`	DATETIME	Ticket latest updated timestamp in local timezone.

Platform notes

The Gorgias and Richpanel integrations fill this table today.

Gorgias. Supplies ticket_assignee_team_id directly and takes ticket_assignee_team_name from the ticket, so the name is set even when the team no longer joins obt_customer_support_teams. Carries a stable customer id in source_system_customer_id.
Richpanel. Has one assignee field for either a user or a team, so ticket_assignee_team_id and ticket_assignee_team_name are set only when that field matches a team in obt_customer_support_teams. When it does not, both are NULL and the assignee fields carry the team instead. Its tickets carry no stable customer id, so source_system_customer_id is NULL, and no spam flag, so is_ticket_spam is always false.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

obt_customer_support_tickets

Query examples

Columns

Platform notes

​Query examples

​Columns

​Platform notes

Query examples

Columns

Platform notes