IT Helpdesk Ticketing Analysis

Goals: To provide actionable insights into your organization's trends, usage patterns, systems behaviors, service level agreement,

Dataset: An Excel file with two tabs containing Tickets, Agents table data

Project Description : This 2-page report was created keeping in mind the important KPIs to understand how tickets are distributed from 2016 to 2020

• Data Preparation – Power query (Text functions, Data types, etc.)

• Exploratory Data Analysis – Power Pivot and Pivot table were used to analyze the data (Unnecessary columns were identified, and new columns as stated by the client were added. Univariate analysis – Histograms, Box plots, Bivariate analysis – Scatter plots)

• Data Modelling – Power BI's Power Query was used for data modeling. Although the "Fecha" field contains the date of tickets raised. A Date table was created with the necessary columns. Tickets table [Fecha] – Date [Date], Tickets[Agent ID] - Agents[Agent ID]

• Data Visualization – The* entire report was designed in Power BI using different types of visualizations (bar, column, pie, stacked chart, KPI Cards, Slicers)

• Page - Home:

The header contains slicers/filters along with page navigation buttons.

The first section contains ticket distribution based on the SLA Status of tickets and across the Request category and issue type fields.

Secondly, Cards and KPI cards are used to display the import KPIs (Count of tickets under Severity – Urgent, Unclassified Priority – High, Unassigned, Most tickets raised on and closed to understand the important Day under the selection. Finally, the average satisfaction rate and resolution time against the target as KPIs cards. The final visual contains ticket distribution over selected months and is explained by Priority / Severity and segregated by SLA Status

• Page - Details:

A detailed table for tickets containing Employee ID, SLA status, REsolution time, Severity, Priority, and the agent is listed to drill down to the end. In addition to Year and quarter slicers, filters or Issue type, severity, priority, and SLA are added to ease the analysis for the user(s).

The second section contains pie charts and stacked chart to show the distribution of tickets in accordance with severity, priority, and SLA

Finally, a clustered-column chart shows the average resolution time of the tickets with various severity and priority.

• Page - Agents:

Again a table, containing Agents, their satisfaction rate, and the number of tickets they had handled together with the distribution of Priority-High, Severity-Urgent, and Major tickets. This could help us to understand the experience of agents in handling various critical tickets.

A histogram shows the distribution of Agents' age. and most of them were less than 39 years of age.

The last chart shows the distribution of tickets handled by various age groups and their average resolution time. It looks like agents from 30-35 years of age took more time to solve the tickets. The average time is 5.2 days

Necessary slicers on Age, Issue type, and Request category were provided to slice and dice the data.

The Entire report was provided with Parameters, Slicers, to slice and dice data for analyses. Some DAX examples are,

• Current Tickets for Unassigned = CALCULATE([Current Tickets], 'Tickets'[Priority Type] IN { " Unassigned" })
• Previous Tickets = CALCULATE([Current Tickets], SAMEPERIODLASTYEAR('Date'[Date]) )
• Non-working Day Tickets = CALCULATE([Current Tickets], 'Date'[Day Name] IN { "Sat","Sun" })
• Resolved Date = Tickets[Fecha]+ Tickets[Resolution Time (Days)]
• SLA Status = IF(Tickets[Resolution Time (Days)] > 3.5,"Outside SLA","Within SLA")