> ## 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.

# Customer Support

> Support ticket volume, resolution, and CSAT analysis queries.

<Tip>
  Use the same query standards from the [SQL Query Library overview](/data-activation/template-resources/sql-query-library): `is_order_sm_valid = TRUE` for order analyses, `sm_store_id` scoping for multi-store setups, and `your_project` placeholders.
</Tip>

<AccordionGroup>
  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="Unread open-ticket share by team and channel (ops triage)">
    **What you'll learn:** Which teams/channels have the highest unread share of open tickets. Useful for triage and staffing.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```

    <Info>
      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.
    </Info>
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>
</AccordionGroup>

***

## Related Categories

Back to the [SQL Query Library](/data-activation/template-resources/sql-query-library).

<CardGroup cols={2}>
  <Card title="Customers & Retention" icon="users" href="/data-activation/template-resources/sql-query-library/customers-and-retention">
    Compare support patterns with repeat behavior and lifecycle segments.
  </Card>

  <Card title="Orders & Revenue" icon="dollar-sign" href="/data-activation/template-resources/sql-query-library/orders-and-revenue">
    Evaluate support load alongside sales and revenue trends.
  </Card>

  <Card title="Attribution & Data Health" icon="heart-pulse" href="/data-activation/template-resources/sql-query-library/attribution-and-data-health">
    Check data quality when support metrics shift unexpectedly.
  </Card>
</CardGroup>