English · Русский

support-operations-analytics

SLA and ticket routing optimization

support request analysis, bottleneck discovery, auto-routing and SLA alert design, A/B test and BI dashboard

Table of contents

1. Project goal
2. Data
3. Project structure
4. Business problem
5. Analysis methodology
6. Data analysis results
7. Solution design
8. System diagrams
9. Requirements
10. A/B test
11. BI dashboard
12. SQL analysis
13. How to run the project
14. Data limitations
15. Final conclusions and recommendations

Project goal

The company receives customer requests through different channels: Email, Chat, Phone, Social Media. The current ticket-handling process depends on manual assessment of the category, priority and assignee. Because of this, part of the requests wait a long time for the first response, the SLA is breached, and the support manager sees the problem too late.

The goal of the project, based on historical data, is to:

• assess the scale of SLA breaches and first-response delays;
• find problematic categories, products and channels;
• design auto-routing by category, priority, complexity and customer segment;
• describe SLA alerts and events for BI;
• prepare requirements and acceptance criteria;
• propose an A/B test design to check the effect of the changes.

Data

The data is produced by a synthetic generator, src/generate_data.py (60,000 tickets, deterministic seed).

First, the public Kaggle dataset "200K support tickets" was tried, but it is uniform noise. In it, every channel, category and priority has the same ~50% SLA breach rate and the same ~3.0 CSAT, so any breakdown comes out "flat". The generator injects realistic signal into the data, and the analysis recovers it:

• first response time depends on the channel (chat fast, social slow), on the priority (higher priority — faster handling) and on a seasonal Nov–Jan surge;
• sla_breached = first response slower than the priority SLA threshold, so breach varies strongly by the channel × priority pair;
• customer_satisfaction_score falls as first response time grows relative to the threshold;
• escalated grows with issue_complexity_score and on a few hard categories (Integration / API, Billing Dispute).

Key fields:

Field	Meaning	Usage
ticket_id	ticket ID	key, A/B randomization
product	product	finding problematic products
category	request category	routing rules, SLA analysis
priority	priority	routing risk score
status	status	lifecycle
channel	request channel	channel load analysis
region	region	regional analytics
subscription_type	subscription type	service priority
customer_segment	customer segment	SLA policy and prioritization
previous_tickets	previous requests	recurring issue risk
customer_satisfaction_score	CSAT 1-5	quality metric
first_response_time_hours	first response time	main optimization metric
resolution_time_hours	resolution time	operational metric
escalated	whether escalation occurred	escalation rate
sla_breached	whether SLA was breached	main process quality metric
issue_complexity_score	request complexity	routing risk score

Project structure

.
├── data/
│   ├── raw/                         # generated support_tickets.csv (git-ignored)
│   └── processed/                   # enriched dataset + A/B simulation (git-ignored)
├── diagrams/
│   ├── src/                         # PlantUML + draw.io sources (incl. bpmn.drawio)
│   └── png/                         # rendered PNG
├── notebooks/                       # jupyter notebook
├── reports/figures/                 # analysis charts
├── sql/                             # sql queries
├── src/
│   ├── generate_data.py             # synthetic ticket generator (signal injected)
│   └── analyze_support.py           # analysis, charts, A/B simulation
├── README.md
└── requirements.txt

Business problem

Current process:

1. the customer creates a ticket;
2. the ticket goes into a shared queue;
3. an agent or manager manually assesses the category, priority and complexity;
4. the ticket is assigned to an executor;
5. the first response may be delayed;
6. escalation often happens after the situation has already worsened;
7. bi shows the problem after the fact.

Main problems:

• a high share of SLA breaches;
• long first response time;
• there is no single automatic prioritization;
• complex and urgent tickets do not always reach specialists right away;
• there are no proactive SLA alerts;
• future load balancing is impossible without agent_id.

Solution: auto-routing + SLA alerts + BI event stream.

Analysis methodology

The Python script src/analyze_support.py:

1. loads the generated CSV (and creates it if the file is missing);
2. normalizes dates and the sla_breached, escalated flags;
3. computes routing_risk_score;
4. assigns the recommended queue;
5. forms the SLA alert level;
6. builds charts;
7. saves the enriched dataset;
8. creates a design-stage A/B simulation to estimate the target effect of auto-routing.

routing_risk_score formula:

priority_score * 2.0
+ issue_complexity_score * 0.8
+ previous_tickets * 0.05
+ Premium segment bonus
+ Enterprise subscription bonus

Queues:

• Tier 1 standard queue — low risk;
• Tier 1 priority queue — medium risk;
• Tier 2 specialist — high risk;
• Tier 3 / Escalation queue — critical risk.

Data analysis results

Executive Summary

Metric	Value
Total tickets	60,000
SLA breach rate	29.4%
Average First Response Time	9.90 h
P90 First Response Time	23.94 h
Average Resolution Time	48.51 h
P90 Resolution Time	90.69 h
Average CSAT	4.21 / 5
Escalation rate	30.2%

First response time by channel

The channel is the main driver of speed. Chat answers on average in ~2.5 h, Phone ~5 h, Email ~15 h, Social Media ~26 h. The same ticket waits an order of magnitude longer only because of where it came from, so the channel must be a full routing and SLA dimension.

SLA breach by channel and priority

Breach is uneven (from 0% (Chat / Low) to 100% (Social Media / High–Critical)). The danger zone is slow channels on tight thresholds: Critical has a 1 h threshold that Email and Social almost never meet. These cells benefit most from auto-routing into a priority/specialist queue.

Relationship between CSAT and first response time

CSAT falls monotonically as the first response is delayed: 4.77 at <2 h down to 2.49 at >32 h. That is why First Response Time is the main optimization metric.

Volume and SLA dynamics

Volume grows over time, and the Nov–Jan surge lifts breach from ~24% to ~40% with unchanged staffing, since load alone overheats the queue. An SLA improvement in a single month after release does not prove a lasting effect, because seasonality must be taken into account.

SLA breach by category

Most categories are around ~25–27%, but three stand out: Integration / API (42%), Billing Dispute (35%) and Data Sync Issue (34%). They also escalate about 2.5× more often, so they need their own specialist queues and routing rules rather than one generic flow.

Escalation and resolution time vs complexity

Both escalation rate (≈5% → ≈80%) and average resolution time rise sharply with issue_complexity_score. Complexity is a clean input for the routing risk score. High-complexity tickets should skip the standard queue.

Key takeaway: the SLA problem is structural, not uniform, because it concentrates in slow channels, on tight priority thresholds, in hard categories and at the seasonal peak. So the solution changes routing and SLA control by these drivers, rather than adding manual control everywhere.

Solution design

Auto-routing rules

Routing rules:

1. Urgent tickets cannot get into the standard queue.
2. Enterprise and Corporate customers get an increased routing risk.
3. High-complexity tickets go to the specialist queue.
4. Repeat requests raise the risk.
5. If a ticket is both complex and urgent, it is sent to the escalation queue.
6. Manual reassignment must be logged as an event.

SLA alerts

Levels:

Alert level	Condition in the analytical prototype	Production logic
Normal	no risk	far from the SLA threshold
Warning	FRT >= 24 h	approaching the warning threshold
Critical risk	FRT >= 48 h	close to SLA breach
Breach	sla_breached = Yes	SLA already breached

In the production version the alert must be computed not from the actual first_response_time_hours, but from created_at, sla_due_at, the support calendar and the SLA policy.

Roles

Role	Tasks
Customer	creates a ticket, receives a response, rates the quality
Agent	works the queue, responds to the customer, resolves the ticket
Support manager	monitors SLA, load, escalations and rules
Auto-routing service	classifies the ticket and assigns the queue
SLA monitor	tracks SLA risk and sends alerts
BI/Event Store	collects events and updates dashboards

Events for BI

{
  "event_id": "evt_20240517_000001",
  "event_type": "ticket_routed",
  "event_time": "2024-05-17T10:15:00Z",
  "ticket_id": 12345,
  "customer_segment": "Corporate",
  "product": "Web Portal",
  "category": "Performance Issue",
  "priority": "High",
  "issue_complexity_score": 8,
  "routing_risk_score": 12.4,
  "recommended_queue": "Tier 3 / Escalation queue",
  "sla_alert_level": "Warning"
}

System diagrams

The diagrams are made in PlantUML and draw.io. The sources are in diagrams/src, the PNGs are in diagrams/png.

BPMN (As-Is)

As-Is Activity

Shows the current manual process: shared queue, manual triage, manual assignment, reactive escalation.

To-Be Activity

Shows the target process: auto-routing before the agent's work, parallel SLA monitoring, and sending events to BI.

ERD

The model separates customers, tickets, products, categories, events, SLA alerts and assignments. Note: agent_id is absent from the dataset (analysis is at ticket grain), but it is added to the target model as a required field for load analysis.

Use Case

The diagram captures the key roles: customer, agent, support manager, auto-routing system and BI dashboard.

Ticket State Machine

The ticket lifecycle is extended with SLA states: warning, critical, breached.

Sequence Diagram

The sequence shows the path from ticket creation to routing, SLA alert, first response, closure and BI events.

A separate note on the manual BPMN refinement is here: diagrams/manual_bpmn_checklist.md.

Requirements

Functional requirements

ID	Requirement
FR-01	The system receives a ticket creation event.
FR-02	The system computes the routing risk score.
FR-03	The system assigns the ticket to Tier 1, Tier 2 or the Escalation queue.
FR-04	The system creates an SLA alert when there is a risk of SLA breach.
FR-05	The agent sees the queue, priority, SLA status and recommended action.
FR-06	The manager sees SLA breach rate, FRT, RT, CSAT and load.
FR-07	Manual ticket reassignment is logged.
FR-08	BI receives the events ticket_created, ticket_routed, sla_alert_sent, ticket_resolved.

Non-functional requirements

ID	Requirement
NFR-01	The routing decision is computed in no more than 2 seconds for 95% of tickets.
NFR-02	Routing service availability: 99.5% during support working hours.
NFR-03	All status and assignment changes are audited.
NFR-04	Customer personal data is protected and masked in BI.
NFR-05	The BI dashboard refreshes at least once every 15 minutes.

User Stories

Role	User story
Customer	As a customer, I want to get the first response quickly, so I know my request has been accepted into work.
Agent	As an agent, I want to see priority and SLA status, so I can pick the most urgent tickets.
Manager	As a manager, I want to see the categories with the highest SLA breaches, so I can change the routing rules.
System	As a routing service, I want to send complex tickets to specialists, so I can reduce breaches.

Acceptance Criteria

1. For a new ticket the system computes routing_risk_score and recommended_queue.
2. An Urgent ticket does not go into the standard queue.
3. A ticket with Critical risk appears in the manager's queue.
4. Routing events are available for BI.
5. The A/B test is considered successful if the SLA breach rate is reduced by 15% and FRT is reduced by 20%.

A/B test

Hypothesis

Auto-routing + SLA alerts will reduce the average First Response Time and the share of SLA breaches without worsening CSAT.

Groups

Group	Process
A	old manual process
B	auto-routing + SLA alerts

Metrics

Primary:

• First Response Time;
• SLA Breach Rate.

Secondary:

• Resolution Time;
• CSAT;
• Escalation Rate;
• Agent Workload Balance after adding agent_id.

Success criteria

• SLA breach rate reduced by at least 15%;
• First Response Time reduced by at least 20%;
• CSAT did not get worse;
• Resolution Time did not grow by more than 5%.

Design-stage simulation result

The simulation uses the generated tickets as the baseline and models the auto-routing effect for group B.

Metric	A manual	B auto-routing	Change
First Response Time, avg h	9.88	7.37	-25.4%
SLA Breach Rate	29.6%	20.8%	-29.7%

Welch t-test for First Response Time: t = 23.80, p-value ≈ 2.8e-124.

Interpretation: the target criteria are met in the simulation. For a real rollout, a production A/B test must be run with stratification by priority, channel, category, customer_segment.

BI dashboard

Page 1. Executive Overview

KPI:

• total tickets;
• SLA breach rate;
• avg First Response Time;
• avg Resolution Time;
• avg CSAT;
• escalation rate.

Charts:

• ticket volume by month;
• SLA breach rate by month;
• FRT and RT trend;
• CSAT trend.

Page 2. SLA and Bottlenecks

Charts:

• SLA breach rate by category;
• SLA breach rate by product;
• FRT distribution by priority;
• top breached categories;
• product x category heatmap.

Page 3. Channels and Categories

Charts:

• tickets by channel;
• categories by volume;
• channel x category heatmap;
• CSAT by channel;
• escalation rate by channel.

Page 4. A/B-test Results

KPI:

• FRT change;
• SLA breach rate change;
• RT change;
• CSAT change.

Page 5. Agent Workload

This page requires the future agent_id field.

Charts:

• tickets per agent;
• active backlog per agent;
• breached tickets per agent;
• workload balance coefficient;
• reassignment count per agent.

SQL analysis

The SQL queries are in sql/support_analytics.sql.

Examples of the questions covered:

• how many tickets arrive per channel;
• which categories breach the SLA more often;
• which products create the highest load;
• how SLA and FRT change by month;
• how CSAT depends on the first-response bucket;
• which queue to recommend for a ticket.

How to run the project

From the project folder:

python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

python src/generate_data.py      # writes data/raw/support_tickets.csv
python src/analyze_support.py    # (also generates the data if it is missing)

The analysis creates:

• data/processed/support_tickets_enriched.csv;
• data/processed/ab_test_simulation.csv;
• data/processed/ab_test_metrics.csv;
• charts in reports/figures;
• a text summary in reports/metrics_summary.txt.

PlantUML compilation (the .drawio sources are edited and exported in draw.io):

plantuml -charset UTF-8 -tpng -o ../png diagrams/src/*.puml

Data limitations

1. The data is synthetic: it is collected to demonstrate the analysis and the target process, the relationships are injected on purpose.
2. The grain is ticket level, with no agent_id, so a full Agent Workload Balance metric requires real assignment data.
3. There is no exact first-response timestamp (only first_response_time_hours) and no sla_due_at, so the SLA policy cannot be fully rebuilt.
4. There is no reassignment history or reopen flag.
5. The A/B results are a design-stage simulation of the target effect, not a real production experiment.

For production analytics, we should add:

• agent_id;
• team_id;
• assigned_at;
• first_response_at;
• sla_due_at;
• reopened_flag;
• reassignment_count;
• handling_time_minutes;
• survey_submitted_at.

Final conclusions and recommendations

1. The SLA problem is systemic: the breach rate is about 50%, which means the manual process cannot cope sustainably.
2. The main optimization point is First Response Time. It can be reduced through automatic distribution and early SLA alerts.
3. Auto-routing must consider priority, complexity, product, category, customer segment and request history.
4. The support manager needs a BI dashboard with SLA, FRT, RT, CSAT, categories, products, channels and A/B test results.
5. For the next level of analysis, agent_id and assignment events must be collected, otherwise load balance cannot be fairly assessed.
6. Before rolling out to the whole support team, an A/B test should be run: the old manual process versus auto-routing + SLA alerts.