Support operations analytics

Support analytics should help a team staff demand, find broken workflows, meet service commitments, and improve customer outcomes. That requires a lifecycle model, not a count of tickets created.

Keep the support platform authoritative for ticket state, assignment, message content, and SLA enforcement. Send normalized transitions and approved snapshots to GraphJSON for analysis. Avoid ticket bodies and message text; they often contain personal data, credentials, and customer content.

For investigation of one customer’s experience, use Customer support account timelines. This recipe focuses on operating the support function across tickets.

Define the ticket grain#

One ticket_id identifies one support case. Preserve:

account and requester pseudonymous IDs
created time
channel
issue category
priority and severity
product area
current status transitions
assignment team
first human response
resolution and closure
reopen and escalation events

Do not treat every inbound message as a ticket or every agent reply as a first response.

Emit authoritative lifecycle events#

Ticket creation:

{
  "event": "support_ticket_created",
  "ticket_id": "tkt_91",
  "account_id": "acct_7",
  "channel": "in_app",
  "category": "data_connection",
  "severity": "s2",
  "priority": "high",
  "entitlement": "business",
  "support_policy_version": 4
}

First human response:

{
  "event": "support_first_response_sent",
  "ticket_id": "tkt_91",
  "assignment_team": "technical_support",
  "response_type": "human",
  "minutes_elapsed": 38,
  "business_minutes_elapsed": 22,
  "clock_policy_version": 3
}

Status transition:

{
  "event": "support_ticket_status_changed",
  "ticket_id": "tkt_91",
  "previous_status": "pending_customer",
  "status": "open",
  "reason": "customer_replied",
  "assignment_team": "technical_support",
  "transition_id": "tr_184"
}

Resolution:

{
  "event": "support_ticket_resolved",
  "ticket_id": "tkt_91",
  "resolution_category": "configuration_changed",
  "assignment_team": "technical_support",
  "minutes_to_resolution": 1840,
  "business_minutes_to_resolution": 620,
  "resolution_policy_version": 3
}

Use provider event IDs or transition IDs to make retries diagnosable.

Calculate clocks at the authority#

First-response and resolution clocks may pause during:

non-business hours
customer-pending status
planned holidays
incident linkage
a documented entitlement exception

Business calendars and pause/resume logic are operational policy. Calculate approved elapsed values in the support platform or a governed worker and send the policy version. A simple dateDiff in dashboard SQL rarely reproduces the contract correctly.

Keep both wall-clock and business-clock values when each serves a decision. Do not compare them under one unlabeled “time to resolution” metric.

Model SLA decisions explicitly#

Emit the evaluated outcome:

{
  "event": "support_sla_evaluated",
  "ticket_id": "tkt_91",
  "sla_key": "first_response_business_high",
  "sla_version": 5,
  "target_minutes": 60,
  "actual_minutes": 22,
  "status": "met",
  "evaluated_at": "2026-07-25T20:00:00Z"
}

GraphJSON should analyze the decision, not enforce the support promise. Keep the support system or SLA service authoritative.

Separate:

eligible tickets
tickets whose evaluation window is mature
met
breached
still open but at risk
excluded with reason

Do not count unresolved tickets as successful merely because they have not yet crossed the deadline.

Measure backlog as a stock#

Ticket creation and resolution are flows. Backlog is the number open at a point in time. Reconstructing it from transitions is possible, but daily approved snapshots make operational reporting simpler:

{
  "event": "support_backlog_snapshot",
  "snapshot_at": "2026-07-26T00:00:00Z",
  "assignment_team": "technical_support",
  "priority": "high",
  "status": "open",
  "age_bucket": "24h_72h",
  "ticket_count": 14,
  "source_complete_through": "2026-07-25T23:58:00Z",
  "definition_version": 2
}

One snapshot has one exact grain. Do not sum daily snapshots to calculate tickets handled.

Query demand and resolution flow#

SELECT
  toStartOfWeek(toDateTime(timestamp), 1, 'UTC') AS week,
  countIf(
    JSONExtractString(json, 'event') = 'support_ticket_created'
  ) AS created_tickets,
  countIf(
    JSONExtractString(json, 'event') = 'support_ticket_resolved'
  ) AS resolved_tickets
FROM support_events
WHERE
  JSONExtractString(json, 'event') IN (
    'support_ticket_created',
    'support_ticket_resolved'
  )
GROUP BY week
ORDER BY week

This flow comparison explains whether backlog pressure is increasing. It does not produce the backlog count itself.

Query mature SLA attainment#

WITH evaluations AS (
  SELECT
    JSONExtractString(json, 'ticket_id') AS ticket_id,
    JSONExtractString(json, 'sla_key') AS sla_key,
    argMax(JSONExtractString(json, 'status'), timestamp) AS status,
    argMax(JSONExtractInt(json, 'sla_version'), timestamp) AS sla_version
  FROM support_events
  WHERE JSONExtractString(json, 'event') = 'support_sla_evaluated'
  GROUP BY ticket_id, sla_key
)
SELECT
  sla_key,
  sla_version,
  count() AS evaluated_tickets,
  countIf(status = 'met') AS met_tickets,
  met_tickets / nullIf(evaluated_tickets, 0) AS attainment
FROM evaluations
WHERE status IN ('met', 'breached')
GROUP BY sla_key, sla_version

Report exclusions and not-yet-mature tickets beside the rate.

Track reopens and escalations#

A reopen may signal an incomplete resolution, but it can also represent a new question on an old ticket. Emit a governed reason and define the window:

{
  "event": "support_ticket_reopened",
  "ticket_id": "tkt_91",
  "hours_since_resolution": 31,
  "reason": "same_issue_persisted",
  "reopen_definition_version": 2
}

Escalation should capture the source team, destination team, severity at the time, and a controlled reason. Do not infer escalation solely from assignment changes.

Measure CSAT with the correct denominator#

Separate invitation, response, and score:

CSAT response rate
  = completed eligible surveys / delivered eligible invitations

CSAT
  = positive eligible responses / eligible responses

Show both. A high score with very low response can be unrepresentative. Deduplicate by survey response ID, define which score values are positive, and preserve the survey version.

For a complete model, see Survey, NPS, and feedback analytics.

Join product context carefully#

Useful analysis may relate category and ticket rate to:

product area used
plan and lifecycle stage at ticket creation
recent release exposure
account activity
incident impact

Use state as of ticket creation, not today’s latest plan. Normalize counts per eligible active account when comparing customer segments with very different sizes.

An association between feature use and tickets does not prove the feature caused the problem. More active customers produce both more usage and more support opportunities.

Build the operating dashboard#

Include:

created and resolved flow
current backlog and age distribution
first-response and resolution distributions
mature SLA attainment with exclusions
reopen and escalation rates
demand by category, channel, severity, and product area
CSAT and response rate
staffing or assignment-team context
source freshness and missing lifecycle events

Show medians and upper percentiles, not only averages. A small number of very old tickets can be operationally important even when the median is healthy.

Launch checklist#

Ticket, message, and account grains are distinct.
The support platform remains authoritative for state and SLA enforcement.
Business-hour clocks and pause rules have policy versions.
Backlog is modeled as a point-in-time stock.
Open immature tickets are not counted as SLA successes.
Reopen and escalation reasons use controlled categories.
CSAT always shows response coverage.
Ticket text and unnecessary personal data are excluded.
Product joins use event-time context and compatible denominators.
Dashboard totals reconcile with the support platform.