Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt

Use this file to discover all available pages before exploring further.

Use the same query standards from the SQL Query Library overview: is_order_sm_valid = TRUE for order analyses, sm_store_id scoping for multi-store setups, and your_project placeholders.
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;
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;
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;
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.
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;
Back to the SQL Query Library.

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.