Rachel Maro
Back to work

Case study · Operations & CX analytics

Independent demonstrationSynthetic dataAI-assisted project

Customer Service Performance: where should a support team focus?

A fictional retailer's 1,800 support tickets, created January–June 2025 and measured at 1 July 2025 00:00 UTC. Excel, SQLite and Python. All figures are simulated.

The decision

Which queues miss service targets, and which unresolved cases deserve attention first? An operations manager needs timely responses without encouraging rushed resolutions or ignoring dissatisfied customers.

Data-quality audit

The project starts from 1,820 generated records with deliberate problems, ending with 1,800 unique tickets.

20
exact duplicate rows removed
25
channel labels trimmed and standardised
5
invalid CSAT scores set to missing
10
missing priorities kept as Unknown

Unknown priorities stay visible in volumes and are excluded only from target-based measures — never silently assigned a favourable target. Every change is in the cleaning log.

Interactive dashboard

Every number below is recomputed in your browser from the 1,800 ticket rows. Month views are creation cohorts measured at the 1 July 2025 snapshot, not monthly backlog history.

1,800 of 1,800 tickets match · filters combine with AND · All tickets created Jan–Jun 2025, at 1 Jul 2025 snapshot

Tickets
1,800
1642 resolved
Open backlog
158
134 older than 7 days (>168h)
Response SLA
61.2%
1,094 / 1,789 eligible met target
Resolution SLA
56.5%
1,002 / 1,775 eligible met target
CSAT (4–5 of 5)
61.9%
n = 762 valid surveys
Valid survey coverage
46.4%
762 / 1,642 resolved tickets
Median first response
7.09h
n = 1796 responded; censored
P90 first response
27.55h
Linear interpolation, (n−1)×0.9

Unknown-priority tickets count in volumes but have no target, so SLA shows “Not available” when no eligible tickets remain. Simulated data.

Response attainment by channelSimulated

First-response target attainment by channel, sorted high to low, simulated
GroupAttainmentMetEligible
Web chat89.1%327367
WhatsApp63.7%383601
Email46.8%384821
First-response target attainment by channel, sorted high to low · axis 0–100% · labels show met/eligible

Resolution attainment by issueSimulated

Resolution target attainment by category, sorted high to low, simulated
GroupAttainmentMetEligible
Billing63.2%222351
Product information61.2%219358
Delivery60.0%201335
Account access57.4%220383
Refund40.2%140348
Resolution target attainment by category, sorted high to low · axis 0–100% · labels show met/eligible

Open backlog age at 1 Jul 2025Simulated

Open tickets by age at 1 July 2025 snapshot, simulated
Age bandOpen tickets
≤24h3
>24–72h10
>72–168h11
>168h134
Open tickets by age (snapshot minus creation, hours) · zero baseline · snapshot, not a time series
Oldest open tickets (158 in current filter)
Open tickets at snapshot, sortable
T01463WhatsAppNormalAccount access4306.0Breached
T00776EmailNormalRefund4301.7Breached
T00268Web chatHighDelivery4299.3Breached
T01574EmailNormalBilling4292.9Breached
T00851EmailLowDelivery4222.0Breached
T01299EmailHighRefund4213.6Breached
T01301EmailNormalBilling4199.8Breached
T00892Web chatLowAccount access4151.7Breached
T00079WhatsAppHighProduct information4141.3Breached
T01664EmailNormalDelivery4135.7Breached
T01232WhatsAppNormalBilling4027.1Breached
T00388WhatsAppHighDelivery4006.0Breached
T00063EmailNormalRefund4003.3Breached
T00045EmailNormalDelivery3964.5Breached
T01671WhatsAppNormalBilling3952.3Breached
Page 1 of 11

Definitions

  • Response / resolution SLA — tickets meeting target ÷ eligible tickets. Eligible means known priority and either an observed event or a passed deadline. Exact boundaries count as met; overdue open cases count as breaches; cases still within their window are pending, not failures.
  • CSAT — valid scores of 4–5 ÷ all valid scores (1–5). Valid survey coverage — valid scores ÷ resolved tickets.
  • Open backlog — tickets without resolution at snapshot; older than 7 days means age over 168 hours.
Assumed targets and clocks

Training assumptions, not industry benchmarks. Elapsed calendar hours, 24/7, no holiday or pause adjustment.

PriorityFirst responseResolution
Urgent1h8h
High4h24h
Normal12h72h
Low24h120h
Duration statistics

Median and P90 first response use responded tickets only (nulls excluded). P90 uses linear interpolation at (n−1)×0.9. Open cases are censored, so durations do not describe every ticket's experience.

Methodology

Excel formulas calculate ticket-level eligibility, breaches and backlog. SQLite queries compare channels, categories and creation cohorts. Python validates chronology and deadline edge cases and reconciles SQL with Excel. Unlike a dashboard that simply averages handling times, this analysis counts overdue open cases and excludes cases not yet due.

Generation: tickets spread uniformly over January–June 2025; channel weights Email 45%, WhatsApp 35%, Web chat 20%; priority weights Urgent 8%, High 22%, Normal 55%, Low 15%. Email responses and Refund resolutions were deliberately made slower.

Findings

  1. Email is the main first-response pressure point. 384 of 821 eligible Email tickets met target (46.8%), versus 327 of 367 for Web chat (89.1%). Email accounts for 437 of 695 response breaches.
  2. Refunds take longest to resolve within target. 140 of 348 eligible Refund tickets (40.2%) — the lowest category.
  3. Backlog and survey coverage change the story. 158 tickets remain open, 134 older than 7 days. CSAT is 61.9% (472/762), but valid coverage is only 46.4% (762/1,642), so it describes respondents, not every customer.

These patterns were engineered into the synthetic data; comparisons are descriptive, not causal.

Action hypotheses

Proposed next steps to test — not implemented, no impact claimed:

  • Pilot scheduled Email triage and measure response attainment against a comparable queue.
  • Map refund handoffs and approval steps before proposing a fix.
  • Review the oldest unresolved tickets, taking priority into account.
  • Track response and resolution attainment alongside reopen rate and CSAT as guardrails.

Limitations

  • Synthetic data; targets, bottlenecks and satisfaction patterns are assumptions.
  • No business-hours SLA engine, staffing or handoff data; staffing or financial benefits cannot be estimated.
  • Survey non-respondents may have different experiences.

Source & attribution

Original synthetic dataset generated with ChatGPT on 10 October 2026 (seed 20261011). No external, personal or employer records were used. Rachel chose the project direction; data and implementation are AI-assisted.

Downloads