Objective
The primary goal was to efficiently manage IT Service Management (ITSM) data for a healthcare client. This involved processing incoming ticket data from various departments such as Aesthetics, Surgery, Sales, Admin, etc., concerning technical issues, training, concierge services, and more. The data was required to be cleaned, stored, and presented in an accessible manner, with the aim of tracking and improving key performance indicators (KPIs).
Problem
The client received a continuous stream of ITSM data through email feeds. This data needed to be processed and analyzed to track four critical KPIs: First Contact Resolution (FCR), First Level Resolution (FLR), Ticket Close %, and Same Day Close %. The challenge was to automate and streamline the data flow from the source (email) to the end visualization while ensuring data integrity and ease of access for the client.
Challenges
- Data Integration and Storage: The data originated in various formats and needed to be consolidated in Azure Blob Storage.
- Data Cleaning and Transformation: Given the diverse sources of data, cleaning and standardizing it for consistent analysis was a significant challenge.
- Performance Tracking: Accurately tracking the KPIs was crucial to assessing the efficiency of the client’s ITSM processes.
- User Accessibility: Creating an intuitive dashboard that provided easy access and insightful views into the data for different user levels.
- Data Privacy: Ensuring that sensitive information, such as names and phone numbers, was protected throughout the process.
Solutions
- Automated Data Integration: Leveraged Power Automate to automatically transfer email feeds to Azure Blob Storage.
- Data Factory for Cleaning: Utilized Azure Data Factory to perform multiple actions like Copy Activity and Upsert for data cleaning and transformation.
- SQL Tables for Structured Storage: Transferred the cleaned data into SQL tables for structured storage and better query performance.
- Power BI Dashboards: Developed interactive Power BI dashboards using Direct Query, allowing the client to access real-time data. The dashboard comprised:
- Search Page: Centralized filters for clutter-free navigation.
- Details Sheet: Displayed performance across various categories and variables.
- Trends Sheet: Offered trend analysis at different time intervals (Monthly, Weekly, Daily).
- Ageing Sheet: Analyzed ticket aging to identify pain points and ticket resolution delays.
- Data Anonymization: Implemented measures to anonymize confidential data, ensuring privacy and compliance.
KPIs for Solution Validation
To validate the effectiveness of the implemented solutions, the following Key Performance Indicators (KPIs) were used:
- First Contact Resolution (FCR): Measures the percentage of tickets resolved during the first interaction with the agent. A high FCR indicates effective agent training and fewer call transfers.
- First Level Resolution (FLR): Tracks the percentage of tickets resolved at the first level, without escalation. High FLR is indicative of reduced customer dissatisfaction and efficient issue handling at the initial stage.
- Ticket Close %: Represents the overall percentage of tickets closed at any given point, reflecting the general efficiency of the ticket resolution process.
- Same Day Close %: Assesses the percentage of tickets opened and closed on the same day. This KPI, in comparison with the Ticket Close %, provides insights into the process’s speed and efficiency.
These KPIs were crucial in measuring the success of the solutions and ensuring they met the client’s needs for efficient ITSM data management and analysis.
Results
The solution provided a seamless, automated process for managing ITSM data. The Power BI dashboards, with intuitive visuals and easy navigation, allowed the client to effectively monitor and analyze performance across various departments. The ability to track KPIs like FCR, FLR, Ticket Close %, and Same Day Close % enabled the client to identify areas of improvement and enhance overall service quality. Data privacy was maintained throughout the process, ensuring client confidence and compliance with data protection regulations.