I was privileged to be among those who reviewed the submission for the Datafest Africa program. Some of the submissions prompted the need for this article. This is a detailed approach to how I would tackle the business problem if Zentel Network were my client.
Background info
This is data from a Zentel Network Service centre. Every day, customers log their different types of complaints across their branches and expect quick responses and resolutions to their queries. Some of these customers have a Service level agreement with the Network service provider to resolve their daily queries within a particular average duration. This service center has different Managers and operators looking into the customers’ issues and performance can be measured weekly and daily.
Based on the Service Level Agreement between Zentel incorporated and our clients, here are the terms of engagement: - All issues must be responded to within 10 seconds of ticket initiation - All issues must be resolved within 3 hours of response - Average Ticket response time must not exceed 15 seconds - Any ticket not resolved within 3 hours must be escalated to the Manager
Before I think of the data, I define a clear objective. Why am I carrying out this analysis? What do I hope to achieve? Thankfully the goals have been provided, as seen below;
Kindly provide a general outlook of the performance of the business based on relevant metrics.
One of the top executives is of the opinion we should optimize TAT(Turn Around Time) between 6 pm to 9 pm every day due to backlash from disgruntled customers. Do you agree? Show us the data to support your position.
Based on SLA, issues are meant to be responded to within 10 seconds after they are raised. What are the key factors leading to a delay in ticket response time? Hint: Show how the different variables affect the ticket response time.
Which Managers and operators are performing well and struggling to meet the required resolution Time? Make recommendations as to how they can improve.
You can download data here.
Data Preparation
The following tables were loaded into PowerBI
Since the data provided is just a month’s data, there is not much need for a calendar table. The final model looks like this.
Here comes the tricky part. As much as creating charts and graphs is important, understanding the business problem is essential.
Let’s dive into the business problem a bit.
Earlier, we were told there was a service agreement between the company and its customers. Also, disgruntled customers feel the terms of the agreement aren’t being fulfilled on the customer’s end. Understanding this will then lead you to use the appropriate metrics.
Hence, your exploratory analysis should speak to helping you, the analyst, understand how zentel is performing in fulfilling that agreement. I’ve almost maintained that the greatest tool of an analyst is their mind, not PowerBI, Excel, Python etc. The ability to ask questions and think supersedes the tool itself. The tool is just a means to an end.
In our case, questions that help you understand if and where Zentel is underperforming in terms of fulfilling the agreement include
What is the percentage of issues responded to within the agreed 10secs?
Is the average response time not greater than 15secs?
What is the percentage of issues resolved within the agreed 3hours?
You can also leave it as no. of issues responded to with 10secs, but I prefer using percentages as it is easier to picture than a number. Those questions will determine what kind of DAX measure to use.
Now that we understand the business problem, let’s prepare our data.
To know the % of issues responded to within 10secs, we need to find the difference between the ticket open time and response time for every case. The DAX below gets that for us.
For the resolved time, a tiny detail specified can change the direction of the result. The agreement was that issues must be settled within 3 hours of response time, not ticket open time.
Although the DATEDIFF() allows you to calculate the hour difference, I discovered it is not entirely accurate, as it uses the start of the hour. This was a mistake I noticed in some of the submissions. See the sample below.
The hour difference is 1.5 hours ( 1 hour 30 minutes), but because it uses the start of the hour, the 30 minutes is ignored.
The recipe for understanding the general outlook of business performance is almost ready.
Kindly provide a general outlook of the performance of the business based on relevant metrics.
This is just simple exploratory analysis, and the questions we raised while understanding the business problem will help us create relevant metrics.
My approach to analysis has always been to ask the questions of what, when, where, how and why. This greatly helps in effective insight communication, which is the approach used in creating the report.
The following DAX measures will help us understand the performance of the business.
Terminated issues were excluded because it means the customer no longer needs assistance; a terminated issue is no longer on the operator’s desk and can’t be a factor in determining the operator’s performance.
I prefer dynamic measures to multiple pages if I have to deploy the report in the client’s environment. Dynamic measures were used for EDA here.
EDA tells us that less than 20% of issues were responded to within 10secs and less than 60% were resolved within 3 hours. We are beginning to understand why customers are disgruntled.
Across all areas, we would discover the organization is mainly underperforming.
See the full report here.
One of the top executives is of the opinion we should optimize TAT(Turn Around Time) between 6 pm to 9 pm every day due to backlash from disgruntled customers. Do you agree?
I wouldn’t agree, and data doesn’t agree with the executive. Let’s justify my disagreement.
Turnaround time is the difference between when the issue was open and closed. Optimizing TAT could be that operator has other tasks, but between 6pm — 9pm, their only focus should be resolving the issues raised by the customer. The executive assumption is based on customers raising more tickets during that period, but data says otherwise.
The essence of this article is to show you how to rather than show you the result.
To be able to justify when to optimize TAT, a critical question to ask is what period customers raise issues the most.
Sadly, ticket open time is not a categorical but a discrete variable.
We need to find a way to make it an ordinal variable. To do this, create a new column on your service data, as seen below.
What the column is saying is this.
Let’s see this visually.
Data clearly shows optimization should be done between 8am-12pm.
Based on SLA, issues are meant to be responded to within 10 seconds after they are raised. What are the key factors leading to a delay in ticket response time?
You can use the Key Influencer for this.
If we go back to our EDA report here, we will discover that social media has the highest number (1.7k) of issues raised, with only 12% of those issues responded to within 10secs.
Which Managers and operators are performing well, and which are struggling to meet up with the required resolution Time?
In this case study, the average is not the most efficient metric for determining who is performing well or not. Consider the following scenario,
Mr A and Mr B both worked on ten different issues. Before the 10th issue, Mr A’s average time to resolve the problem was 0.8 hr, while Mr B had an average time of 2.2 hours. But on the 10th issue, Mr A had outliers and didn’t conform to his typical performance, which then increased his average, making Mr B outperform Mr A.
Let’s imagine the required time to resolve an issue is 2 hrs. Mr A success rate becomes 90% while Mr B’s success rate is 20% if we use the percentage of issues resolved within the stipulated hours.
See the report here to see who was the best and struggling manager/operator.
Now you have been able to tackle the business problem effectively. I will leave you to make recommendations for me.
Comments