Last week, I introduced you to a classic problem of operational analytics. If you didn’t get a chance to check it, you can do it right here.
I had drafted it mainly for freshers who lack confidence in solving case studies. And, this becomes one of the key reason for their rejection in job interviews. Now, if you are still reading this, I take it that you ready to walk the next level with me!
I made the first level simple to understand to get you wanting to go to the next. It just required a logical understanding of how things happen in a call center.
However, that was an over simplified version of what actually happens in a call center. In this article, I will take a step forward and talk about a more real life case in a call center optimization problem. I believe it to be more helpful for R users as I’ve demonstrated the codes in R. However, even if you don’t know R, you can still work your way out in Excel.
Make sure you check out the ‘Ace Data Science Interviews‘ course for multiple such case studies. We have put together a very comprehensive course to help you successfully land your first data science job – don’t miss it!
We assumed multiple things in the previous case study. Some of which were:
Let’s ease out the first assumption to make the case study more realistic.
Assume, you are setting up a call center for a mid-sized E-Commerce firm. You have been asked to find the total strength of callers required for this requirement. This requirement will be outsourced to a call center which guarantees availability of caller for 24 hours with the exact same efficiency.
Using this efficiency, you have also estimated the time of each call from the customer and the duration of these calls. This estimation is based on your market research and prediction through customer behavior in past. You can assume that this prediction is accurate. Now, you need to estimate the following:
The data provided to you is of 10k calls which are made in a day. You can download the data here. The data looks something like this :
Here are few things you should consider:
As always I say, it’s essential to explore and analyze data distribution at first. So, here is the distribution of call duration in the data:
As you can observe most of the calls end up (call duration) between 3-7 minutes with a peak at 5 minutes. Let’s get to the next variable.
Here is the distribution of Call Timing :
To me, it also looks normally distributed i.e. it follows a similar shape like previous graph. We see maximum calls arrive between 9 am to 4 pm with a peak at 12 noon.
We are done with exploring data. Now, we’ll get to the solution.
Let us start with a very simple solution. If we ignore the time when the calls were made, the sum of all call duration comes out to be 50635 minutes.
Available time for a caller (24*60) = 1440 minutes
Number of callers required = (50635/ 1440) = 35.14
So we need approx. 36 callers if we had the choice to call back the customer whenever our caller is free. So, during interviews, when you don’t get much time but need an intuitive solution, such kind of assumptions work well! But the real life is not that simple. Here we need to account for the time at which customer called the call center.
Therefore, for the actual solution, you will need to simulate for every customer – caller combination. I am doing it in R, you can use any tool such as excel, python to accomplish this. Here is a simple R code:
#set working directory
> setwd("C:\\Tavs\\CC")
#Read data
> data <- read.csv("Case_Level2.csv")
> summary(data)
#Create a matrix where we will store the maximum waiting time for each value of the number of callers
> caller_opt <- matrix(0,100,2)
#Run loop for every number of callers possible. Here we have taken the range from 1 to 100
> for (number_of_callers in (1:100)){
#Initialize the available time for each caller
caller <- rep(0,number_of_callers)
#Index will be used to refer a caller
index <- 1:number_of_callers
#Here we store the difference of each callers availability from the time when the call was made
caller_diff <- rep(0,number_of_callers)
#We add two columns to the table : Caller assigned to the customer & Wait time for the customer
data$assigned <- 1
data$waittime <- 0
for (i in 1:length(data$Call))
{
caller_diff <- data$Time[i] - caller
best_caller_diff <- max(caller_diff)
index1 <- index[min(index[caller_diff == best_caller_diff])]
data$assigned[i] <- index1
data$waittime[i] <- max(-best_caller_diff,0)
caller[index1] <- caller[index1] + data$Duration.of.calls[i]
}
caller_opt[number_of_callers,1] = number_of_callers
caller_opt[number_of_callers,2] = max(data$waittime)
print(caller_opt[number_of_callers,])
}
Here is what we get as the result:
As you can observe from the graph that deciding the right number of callers is immensely important. Missing the number by just 10% can also increase the wait time for a customer significantly. In our case if we keep 4 call center reps less (~44), the maximum wait time for a customer becomes 87 minutes, which is something no company will ever want.
Therefore,
We have still managed to keep the case study simple enough by even varying the time of calling. However, two big assumptions still there are:
Beyond these two assumption, we haven’t touched how to make predictions for call duration and call time. But this case study will give you a good feel of how to simulate an entire environment in such an operation intensive function. In future case studies, we will start relaxing these assumptions as well, making to simulation even more closer to reality.
Did you like reading this article ? Can you think of other checks to make this case study mimicking the actual call center in a better way? Do share your experience / suggestions in the comments section below.
Lorem ipsum dolor sit amet, consectetur adipiscing elit,
hi, it was really interesting to read this case study, however i m new to the community and looking for a change, and such case studies and study material attract me a lot to shift my career in Analytics. thanks again for all your efforts in this site... and really appreciate...
can also include SAS in your next case studies. Because I have found any relevant material or case study and solutions on SAS as you do.
Hi Tavish, Thanks for the Post...! Can you explain how you visualized the result ,, because when i tried the Code, numbers are generating from 1 to 100 as output i.e., sample initially considered please let me know if missed something to understand
I use SAS. So if I have to write this code using SAS, can you tell me how to go about it? If it's difficult to write down the entire code, only if you could tell me in words how I should progress step by step. P.S. I appreciate the efforts you guys put!
This is very helpful and I look forward to seeing more as you increase the complexity of the case study. I was curious about data$waittime and data$assigned occurring in the outer for loop - wouldn't you create these variables before both loops begin since these variables are added to the original "data" data.frame? Wouldn't the variables be overwritten with "1" and "0" each time the outer for loop runs? Thanks.
Hi Margie, The two variables are assigned for each iteration on number of callers (which is the outer loop). So we need the two series for each value of number of callers, and so we are rewriting this variable again and again. For every iteration, we also store the maximum value of the wait time, to a matrix, which we will further use to find the maximum wait time for each "number of callers". Hope this helps. Tavish
I understand now, thank you for explaining!
Can you please explain the algorithm
First much appreciate that you share this article, i have learned a lot from your previous one for freshers, which is suitable for me as i am turning into a data analysis from product analyst. I have practiced your code in R and understand your concept, but some points still need your help. Could you explain a little more for your loop? For example, let's start from beginning, 1.. for (number_of_callers in (1:100))-> assume the number of callers is 1 to 100, which maximum number of callers is 100. 2. caller initialize the available time for each caller is zero, right?? 3. caller_diff initialize the difference of each callers availability from the time when the call was made is zero, right ???? 4. Then in the loop, for i=1, caller_diff is 28=28-0???? i am not following you afterwards, could you explain more detail? Thanks a lot !! When R read the file, for i =1, caller_diff is 28= 28-0?
Can u please explain the algorithm
Interesting problem! Something also very usefull to add up to the solution, is the Required caller distribution per hour. I attach an example: https://dl.dropboxusercontent.com/u/7554513/Call%20Center%20Problem.jpg
Hi Tavish, Can we mention the case studies in our resumes that we have done for practise purpose..
Sure
Hi Tanvish, I tried to understand your code, but couldnt quite get it, so I modified it and run on the same data set which gave me the answer to the- 0 wait time question as 134 callers. Now to verify this I created a sample dataset- data <- data.frame(Time=c(2,2,3,4,4,5),Duration.of.calls=c(8,6,9,8,7,8)) ran this on the given code(max. callers=4) which gave - [1] 1 33 [1] 2 11 [1] 3 4 [1] 4 3 Now I tried to solve this manually in excel which gave me different values- [1] 1 35 [1] 2 13 [1] 3 7 [1] 4 5 I am pasting a link to the image which shows calculation for 3 callers - https://www.dropbox.com/s/hmciyxg7wasqlqu/Screenshot%202016-04-21%2015.22.57.png?dl=0 Here's the link to the code which gives the same answer as the one calculated manually - http://hastebin.com/zeguripecu.vhdl Let me know if I got wrong somewhere. And thanks for posting this.
Hi Tavish, Can you please share some more case studies which might have resolution in SAS please? I am a beginner in the same and would request you to please share some case studies and their solution in SAS code please.
how to solve above sum by excel
how to solve above problem in excel . can you explain step wise procedure
Thanks again... Wow if this is a Simple R Code, I really must start to learn about R...