Customer Support

Ticket volume + one-touch rate by communication channel (last 30 days)

What you’ll learn: How ticket volume and one-touch resolution varies by support channel (email, chat, Instagram DM, etc.). Use this to identify which channels are driving the most workload and where your team is resolving issues efficiently.

-- Assumptions: timeframe=last_30_days | metric=ticket_volume+one_touch_rate | grain=communication_channel | scope=exclude_spam
SELECT
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'open') AS open_tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY tickets DESC;

Resolution time + CSAT coverage by assignee team (last 90 days)

What you’ll learn: Which teams are closing tickets fastest and how complete your CSAT data is by team. Use this for staffing, training, and process improvement.

-- Assumptions: timeframe=last_90_days | metric=resolution_time_hours+csat_coverage | grain=assignee_team | scope=closed_tickets_exclude_spam
SELECT
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COUNT(DISTINCT sm_ticket_key) AS closed_tickets,
  AVG(ticket_resolution_time_hours) AS avg_resolution_time_hours,
  APPROX_QUANTILES(ticket_resolution_time_hours, 101)[OFFSET(50)] AS p50_resolution_time_hours,
  APPROX_QUANTILES(ticket_resolution_time_hours, 101)[OFFSET(90)] AS p90_resolution_time_hours,
  COUNTIF(ticket_csat_score IS NOT NULL) AS csat_responses,
  SAFE_DIVIDE(COUNTIF(ticket_csat_score IS NOT NULL), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS csat_response_rate,
  AVG(ticket_csat_score) AS avg_csat_score
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_resolution_time_hours IS NOT NULL
  AND ticket_status IS NOT NULL
  AND LOWER(ticket_status) = 'closed'
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1
HAVING closed_tickets >= 25
ORDER BY avg_resolution_time_hours ASC;

Support backlog aging by team and channel (open tickets)

What you’ll learn: How old your open ticket backlog is (age buckets + p50/p90) broken out by team and channel. Useful for backlog management and escalation.

-- Assumptions: timeframe=current_state | metric=open_ticket_age_buckets | grain=team+channel | scope=open_tickets_exclude_spam
WITH open_tickets AS (
  SELECT
    COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
    COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
    DATE_DIFF(CURRENT_DATE(), DATE(ticket_created_at_local_datetime), DAY) AS ticket_age_days
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND ticket_status IS NOT NULL
    AND LOWER(ticket_status) = 'open'
    AND ticket_created_at_local_datetime IS NOT NULL
)
SELECT
  ticket_assignee_team_name,
  ticket_communication_channel,
  COUNT(*) AS open_tickets,
  COUNTIF(ticket_age_days = 0) AS age_0d,
  COUNTIF(ticket_age_days BETWEEN 1 AND 2) AS age_1_2d,
  COUNTIF(ticket_age_days BETWEEN 3 AND 6) AS age_3_6d,
  COUNTIF(ticket_age_days BETWEEN 7 AND 13) AS age_7_13d,
  COUNTIF(ticket_age_days >= 14) AS age_14d_plus,
  APPROX_QUANTILES(ticket_age_days, 101)[OFFSET(50)] AS p50_ticket_age_days,
  APPROX_QUANTILES(ticket_age_days, 101)[OFFSET(90)] AS p90_ticket_age_days
FROM open_tickets
GROUP BY 1, 2
HAVING COUNT(*) >= 25
ORDER BY open_tickets DESC;

What you’ll learn: Which teams/channels have the highest unread share of open tickets. Useful for triage and staffing.

-- Assumptions: timeframe=current_state | metric=unread_open_ticket_share | grain=team+channel | scope=open_tickets_exclude_spam
SELECT
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COUNT(DISTINCT sm_ticket_key) AS open_tickets,
  COUNTIF(is_ticket_unread = TRUE) AS unread_open_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_unread = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS unread_open_ticket_rate
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_status IS NOT NULL
  AND LOWER(ticket_status) = 'open'
GROUP BY 1, 2
HAVING COUNT(DISTINCT sm_ticket_key) >= 25
ORDER BY unread_open_ticket_rate DESC, open_tickets DESC;

Top support tags by ticket volume + one-touch + resolution time (last 90 days)

What you’ll learn: Which tagged issue types generate the most tickets, and whether they tend to be one-touch or slow to resolve. Useful for product feedback loops, macro coverage, and staffing.

-- Assumptions: timeframe=last_90_days | metric=ticket_volume+one_touch_rate+resolution_time | grain=tag | scope=exclude_spam
WITH base AS (
  SELECT
    sm_ticket_key,
    ticket_status,
    is_ticket_one_touch,
    ticket_resolution_time_hours,
    ticket_tag_names_csv
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND ticket_created_at_local_datetime IS NOT NULL
    AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND ticket_tag_names_csv IS NOT NULL
    AND TRIM(ticket_tag_names_csv) != ''
),
exploded AS (
  SELECT DISTINCT
    sm_ticket_key,
    ticket_status,
    is_ticket_one_touch,
    ticket_resolution_time_hours,
    LOWER(TRIM(tag_raw)) AS tag
  FROM base
  CROSS JOIN UNNEST(SPLIT(ticket_tag_names_csv, ',')) AS tag_raw
  WHERE TRIM(tag_raw) != ''
)
SELECT
  tag,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  AVG(IF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed', ticket_resolution_time_hours, NULL)) AS avg_resolution_time_hours_closed
FROM exploded
GROUP BY 1
HAVING tickets >= 50
ORDER BY tickets DESC
LIMIT 50;

One ticket can have multiple tags, so a single ticket may appear in multiple tag rows. Use this for per-tag diagnostics, not for global totals. Many tags are routing labels (channel, store, automation, priority) rather than topics, so read the list before treating it as themes.

What customers are writing about: keyword themes in customer messages (last 30 days)

What you’ll learn: Which topics come up most in what customers actually wrote, counted once per ticket, for tickets created in the last 30 days. Edit the keyword list to match your business. A ticket that mentions two keywords counts under both, so the rows do not add up to the ticket total. HTML tags are stripped first, because some help desks pass markup through and an inline style such as font-size would count as a mention of “size”.

-- Assumptions: timeframe=last_30_days | metric=tickets_mentioning_keyword | grain=keyword | scope=customer_messages_exclude_spam
WITH customer_messages AS (
  SELECT
    m.sm_ticket_key,
    LOWER(REGEXP_REPLACE(m.message_body_text, r'<[^>]*>', ' ')) AS body
  FROM `your_project.sm_transformed_v2.obt_customer_support_ticket_messages` m
  JOIN `your_project.sm_transformed_v2.obt_customer_support_tickets` t USING (sm_ticket_key)
  WHERE t.is_ticket_spam = FALSE
    AND DATE(t.ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND m.is_message_from_agent = FALSE
    AND m.message_body_text IS NOT NULL
)
SELECT
  keyword,
  COUNT(DISTINCT sm_ticket_key) AS tickets
FROM customer_messages
CROSS JOIN UNNEST(['return', 'exchange', 'refund', 'size', 'shipping', 'tracking', 'deliver', 'damaged', 'defect', 'discount', 'code', 'cancel', 'wholesale', 'unsubscribe']) AS keyword
WHERE STRPOS(body, keyword) > 0
GROUP BY 1
ORDER BY tickets DESC;

Some channels carry no transcript, so their tickets never match a keyword. Check message_communication_channel before reading a low count as a quiet topic.

First response time by communication channel (last 30 days)

What you’ll learn: How long customers wait for the first reply they can see, by channel, for tickets created in the last 30 days. There is no first-response column on the ticket table, so this derives it from the messages: the earliest public agent message after the customer’s first message. Tickets an agent started, tickets with no reply yet, and internal notes are left out. The median sits next to the average because a few slow replies pull the average up.

-- Assumptions: timeframe=last_30_days | metric=first_response_hours | grain=communication_channel | scope=customer_started_tickets_with_a_public_agent_reply
WITH tickets AS (
  SELECT
    sm_ticket_key,
    COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
first_messages AS (
  SELECT
    t.sm_ticket_key,
    t.ticket_communication_channel,
    MIN(IF(m.is_message_from_agent = FALSE, m.message_created_at, NULL)) AS first_customer_message_at,
    MIN(IF(m.is_message_from_agent = TRUE AND m.is_message_public = TRUE, m.message_created_at, NULL)) AS first_agent_reply_at
  FROM tickets t
  JOIN `your_project.sm_transformed_v2.obt_customer_support_ticket_messages` m USING (sm_ticket_key)
  GROUP BY 1, 2
),
response_times AS (
  SELECT
    ticket_communication_channel,
    TIMESTAMP_DIFF(first_agent_reply_at, first_customer_message_at, SECOND) / 3600 AS first_response_hours
  FROM first_messages
  WHERE first_customer_message_at IS NOT NULL
    AND first_agent_reply_at > first_customer_message_at
)
SELECT
  ticket_communication_channel,
  COUNT(*) AS tickets_with_reply,
  ROUND(AVG(first_response_hours), 2) AS avg_first_response_hours,
  ROUND(APPROX_QUANTILES(first_response_hours, 101)[OFFSET(50)], 2) AS p50_first_response_hours,
  ROUND(APPROX_QUANTILES(first_response_hours, 101)[OFFSET(90)], 2) AS p90_first_response_hours
FROM response_times
GROUP BY 1
ORDER BY tickets_with_reply DESC;

A phone channel can come out near zero when the help desk logs both sides of a call at once; hold time has to come from the phone system. Automated replies count as agent messages. Where the help desk marks them, leave them out of first_agent_reply_at, for example message_entry_method = 'rule' in Gorgias. The messages table’s platform notes say which calls and automations each help desk marks.

What you’ll learn: How much of the conversation volume customers generate versus your team, and which agents send the most replies. Internal notes are excluded so the split only counts messages customers could see.

-- Assumptions: timeframe=last_30_days | metric=message_count | grain=sender | scope=public_messages
WITH public_messages AS (
  SELECT
    is_message_from_agent,
    message_sender_full_name,
    sm_ticket_key
  FROM `your_project.sm_transformed_v2.obt_customer_support_ticket_messages`
  WHERE is_message_public = TRUE
    AND DATE(message_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  IF(is_message_from_agent, message_sender_full_name, '(customers)') AS sender,
  is_message_from_agent,
  COUNT(*) AS messages,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER ()) AS share_of_messages
FROM public_messages
GROUP BY 1, 2
ORDER BY messages DESC;

Help desk bots appear as agents under their own sender name, so the agent rows include automation unless you filter them out by name.

Products behind support tickets, via the customer's recent orders (last 30 days)

What you’ll learn: Which products were in the orders of customers who opened tickets, as a stand-in for “which products generate tickets”. Nothing on a ticket names a product, so this links each ticket to the same customer’s orders in the 90 days before the ticket. A ticket counts once per product in those orders, and only tickets whose customer_id matches an order in the same store are covered. linked_ticket_share is the share of all tickets in the window that linked to an order, the same on every row; quote it with the result. Non-product lines such as package protection show up near the top; exclude them by title.

-- Assumptions: timeframe=last_30_days | metric=tickets_per_product | grain=product_title | scope=tickets_linked_to_an_order_in_prior_90_days
WITH tickets AS (
  SELECT
    sm_ticket_key,
    sm_store_id,
    customer_id,
    ticket_created_at
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
linked AS (
  SELECT DISTINCT
    t.sm_ticket_key,
    o.product_title
  FROM tickets t
  JOIN `your_project.sm_transformed_v2.obt_order_lines` o
    ON o.sm_store_id = t.sm_store_id
    AND o.customer_id = t.customer_id
  WHERE o.is_order_sm_valid = TRUE
    AND o.order_processed_at BETWEEN TIMESTAMP_SUB(t.ticket_created_at, INTERVAL 90 DAY) AND t.ticket_created_at
),
coverage AS (
  SELECT
    SAFE_DIVIDE((SELECT COUNT(DISTINCT sm_ticket_key) FROM linked), COUNT(*)) AS linked_ticket_share
  FROM tickets
)
SELECT
  l.product_title,
  COUNT(DISTINCT l.sm_ticket_key) AS tickets,
  ANY_VALUE(c.linked_ticket_share) AS linked_ticket_share
FROM linked l
CROSS JOIN coverage c
GROUP BY 1
ORDER BY tickets DESC
LIMIT 25;

Support workload by priority × channel × team (last 30 days)

What you’ll learn: Which priority/channel/team combinations generate the most tickets, and whether they are being resolved quickly. Useful for triage rules and staffing.

-- Assumptions: timeframe=last_30_days | metric=ticket_volume+resolution_time | grain=priority+channel+team | scope=exclude_spam
SELECT
  COALESCE(NULLIF(LOWER(TRIM(ticket_priority)), ''), '(unknown)') AS ticket_priority,
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'open') AS open_tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate,
  AVG(IF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed', ticket_resolution_time_hours, NULL)) AS avg_resolution_time_hours_closed
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_created_at_local_datetime IS NOT NULL
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3
HAVING tickets >= 25
ORDER BY tickets DESC;

Customers & Retention

Compare support patterns with repeat behavior and lifecycle segments.

Orders & Revenue

Evaluate support load alongside sales and revenue trends.

Attribution & Data Health

Check data quality when support metrics shift unexpectedly.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Customers & Retention

Orders & Revenue

Attribution & Data Health

​Related Categories

Customers & Retention

Orders & Revenue

Attribution & Data Health

Related Categories