Customer Support

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;