🎫
ITSM Service Desk Analytics
“32.4% of tickets breached SLA — one team was the root cause, and nobody had connected the dots yet.”
SLA ComplianceBreach RateTeam PerformancePriority AnalysisCSAT Correlation
4,508
Total Tickets
Q1 2024
Period
1,180
SLA Breached
24.9h
Avg Resolution
4
Teams Analysed
1
Step 1 — Data Extraction from ServiceNow
All 4,508 incident tickets were exported from the ITSM system covering Q1 2024 (Jan 1 – Mar 31). The raw export came as a CSV with 14 columns including Ticket ID, Created Date, Priority, Category, Assigned Team, Status, Resolved Date, and Resolution Hours.
Raw export — first 10 rows from ServiceNow
| Ticket ID | Created Date | Priority | Category | Team | Status | Resolution Hrs | SLA Target |
|---|---|---|---|---|---|---|---|
| INC0000001 | 2024-03-22 | P2-High | Application | Team C | Resolved | 4.3h | 8h |
| INC0000002 | 2024-03-27 | P4-Low | Team A | Resolved | 120.3h | 48h | |
| INC0000003 | 2024-03-18 | P1-Critical | Software | Team D | Resolved | 5.1h | 4h |
| INC0000004 | 2024-01-01 | P4-Low | Software | Team D | Resolved | 35.0h | 48h |
| INC0000005 | 2024-02-13 | P2-High | Access | Team A | Closed | 3.0h | 8h |
| INC0000006 | 2024-02-28 | P3-Medium | Access | Team A | Closed | 6.9h | 24h |
| INC0000007 | 2024-03-14 | P2-High | Hardware | Team A | In Progress | — | 8h |
| INC0000008 | 2024-01-13 | P3-Medium | Access | Team C | Resolved | 38.2h | 24h |
| INC0000009 | 2024-02-04 | P4-Low | Application | Team A | Resolved | 44.4h | 48h |
| INC0000010 | 2024-02-04 | P4-Low | Application | Team B | Pending | — | 48h |
2
Step 2 — Data Cleaning
Six issues were identified and resolved before analysis ran.
Cleaning log — every change documented
| Issue Found | Count | Action Taken | Result |
|---|---|---|---|
| Duplicate Ticket IDs | 23 | Removed — kept latest entry | 4,485 unique tickets |
| Blank Resolved Date (open tickets) | 866 | Excluded from SLA calc only | 866 excluded |
| Resolution Hours = 0 | 12 | Removed — logging error | Dropped |
| Priority field blank | 8 | Set to P4-Low per standard | Imputed |
| Team name inconsistent casing | 47 | Standardised to Team X format | Unified |
| SLA Target column missing | All rows | Derived: P1=4h P2=8h P3=24h P4=48h | Calculated column added |
After cleaning: 3,642 resolved tickets ready for SLA analysis.
3
Step 3 — Transform: VLOOKUP + Pivot Table
VLOOKUP joined the SLA target table to each ticket by Priority. Four calculated columns were added. A Pivot Table grouped by Team x Priority to surface the breach pattern.
Calculated columns added
| New Column | Formula / Logic | Example |
|---|---|---|
| SLA_Target_Hours | VLOOKUP(Priority, SLATable, 2, 0) | 8h for P2-High |
| SLA_Breached | IF(Resolution_Hrs > SLA_Target, 1, 0) | 1 = breached |
| Month_Label | TEXT(Created_Date, MMM YYYY) | Jan 2024 |
| Resolution_Day | TEXT(Created_Date, DDD) | Monday |
Pivot Table — SLA Breach Rate by Team
This is what the management chart was built from
| Team | P1 Breach | P2 Breach | P3 Breach | P4 Breach | Overall | Status |
|---|---|---|---|---|---|---|
| Team A – Desktop | 52% | 44% | 38% | 28% | 39.0% | 🔴 CRITICAL |
| Team B – Network | 38% | 30% | 26% | 20% | 26.3% | 🟡 AT RISK |
| Team C – Application | 44% | 38% | 33% | 25% | 35.0% | 🟡 AT RISK |
| Team D – Service Desk | 40% | 32% | 27% | 22% | 28.8% | 🟡 AT RISK |
| Overall | 45.6% | 35.2% | 30.1% | 22.8% | 32.4% | 🔴 BREACH |
🔍 KEY FINDING
Team A has a 39% breach rate — 14 points above the contractual threshold of 25%. Their P1-Critical tickets breach at 52%, meaning more than half of urgent issues are not resolved on time. This pattern appears consistently across all 3 months of Q1.
4
Step 4 — Analysis
4,508
Total Tickets Q1 2024
32.4%
Overall Breach Rate
Above 25% threshold
39.0%
Team A Breach Rate
Highest of all teams
24.9h
Avg Resolution Time
2.1 / 5
CSAT — Breached Tickets
vs 4.2/5 on-time
Ticket volume and breach rate by category
| Category | Ticket Count | % of Total | Avg Resolution Hrs | Breach Rate |
|---|---|---|---|---|
| Software | 842 | 18.7% | 28.3h | 36.2% |
| Application | 798 | 17.7% | 31.2h | 38.9% |
| Access | 756 | 16.8% | 22.1h | 28.4% |
| Hardware | 734 | 16.3% | 19.8h | 29.1% |
| Network | 688 | 15.3% | 25.6h | 30.7% |
| 490 | 10.9% | 16.2h | 21.3% |
🔍 KEY FINDING
Application and Software have the highest breach rates at 36–39% — and these are also the two categories where Team A handles the most volume. That connection is why Team A's overall breach rate is the worst across all teams.
5
Step 5 — Visualisation
SLA Compliance vs Breach Rate by Team
Green = met SLA, Red = breached. Team A is critical.
Breach Rate by Priority Level
P1 Critical tickets breach at 45.6%
Monthly Breach Trend — Q1 2024
March improved after team realignment in late Feb
SLA Compliance vs Breach by Team
Grouped bar — green for compliance, red for breach. Team A visually isolated. Used in weekly PowerPoint.
Breach Rate by Priority Level
Bar chart — P1 at 45.6%. Drives the escalation protocol conversation.
Monthly Breach Trend Jan–Mar 2024
Line chart — March improved after team realignment. Proves the intervention worked.
6
Step 6 — Report to Management
Reporting cadence
| Report | Frequency | Audience | Key Metric | Format |
|---|---|---|---|---|
| Daily Breach Monitor | Daily 9am | Team Leads | Breach count by team today | |
| Weekly Ops Report | Every Monday | Ops Manager | Breach trend + top 5 delayed | PowerPoint 9 slides |
| Monthly Management Report | 1st of month | Senior Management | YTD SLA + team scorecard | PowerPoint 14 slides |
✅ WHY MANAGEMENT MUST ACT
- Contract penalty clause activates at 30%+ breach for 2 consecutive months. Team A is at 39%. One more month triggers the penalty automatically.
- CSAT impact is measurable: Breached tickets score 2.1/5 vs 4.2/5 for on-time. Every breach is a direct hit to the quarterly client satisfaction report.
- Action assigned: IT Manager to move 2 senior L2 technicians to Team A P1/P2 queue by Feb 10. Target: breach below 28% within 3 weeks.
- Escalation trigger: If breach remains above 30% at the March review, contract renegotiation protocol initiates with the client.