Complex Call Centre Data

My manager has requested that I create a spreadsheet with a lot of information for our call centre. As a quick bit of background information, we're a call centre that receives approximately 1000-1500 calls daily, with anywhere from 14-20 people working the phones. It's your standard 9-5 job, just a larger scale numberwise than what i'm used to working with.

She wants to be able to enter in start time and stop times for calls and have the spreadsheet populate the call time and then also the average length of the calls.

This is where it gets fun. She wants to be able to see how many calls a representative takes during any given hour, and then the average length of those calls.

Example:

If Scott gets 13 calls between 8:00-9:00, averaging about 3 minutes a call, how can we get that data into a spreadsheet?

She intends on using these to see the efficiancy of the way we handle the calls and when we are most busy.


I have a general idea of how the spreadsheet might look, but I don't have a clue how to link up all the data, and since you guys have helped me in the past, I figured I might check with you.

I'm thinking that there's probably an easy way to figure out how long each call takes and then what the average length is, but I'm not sure how to section it off by the hour, and find the averages from those.

If it helps, as she put it, she wants to know, "how many calls Scott on average takes between 8-9, 9-10, 10-11, 11-12, on any given day (monday, tuesday, wednesday), and approximately how longs those calls take (per hour)." I believe she would also like a more general number for end of the day/week totals, but that shouldn't be an issue.


Any help, suggestion, information, or other ideas would all be appreciated.


thanks!

If this was my problem I would start by speaking to your "service provider" i.e. telephone company. They should be able to provide you with all the information you need.

If you are lucky they may give an excel file or at least a text file so you can extract the information you need.

Some years ago I made an "Operators log" where the number of calls (internal or external) should be traced by the operator clicking on the appropriate button on the "Register" tab.

I've uploaded the file so you can have a look at it but a model like this would never work in "proper" call centre.

Alf

I think I like that idea, but if I was going to enter just the start and stop times of calls, what formula would i use to count the number of calls between a certain time

example:

start time stop time
8:15 8:17
8:25 8:31
8:33 8:37
8:52 8:56
9:10 9:16
9:17 9:23


If i want to know how many calls were recieved between 8-9:00, what formula should I use?

I know there has to be a way to figure it out, I'm just not

and those times would be in two seperate columns

I think I like that idea, but if I was going to enter just the start and stop times of calls

This is the problem with my model becuse I'm quite certain a number of people will forget to click the appropriate buttons so the quality of the collected data will probably be poor.

You are absolutely sure the phone company can't help you out? The certaily keeps loggs of all calls and the time they lasts how else are they going to bill you?

Well if you wish to have a go at it I would sugest you change the name of the macro buttons to something like "Call starts" and "Call stopped"

Then you get start time in column C as before (sheet and then you set a time heading in cell J5 and add a "Stop time" macro something like this perhaps?

Please Login or Register  to view this content.

So you got the start time in column C and stop time in column J so by subtracting you get the time the call lasted and one probably need to add some error handling as well. Think about it and if you really wish to have a go at it I'll add what help I can.

Alf

mazzaropi,

when you say "D1 = 08:20 - Limit - Start time and E1 = 09:00 - Limit - Stop time"

do you mean that I should put my limit in for what my brackets would be.

Hypothetically, if I'm going hour by hour, my limits would be 8:00-8:59, 9:00-9:59, etc.

How would I set that up? and would i then use the same formula?






Alf,

unfortunately, the service provider won't be much help. I know that we do have a database with all the call information already in it, but my manager wants a seperate spreadsheet that she can enter numbers into when she shadows reps. She wants all this information so that she can test each individual reps efficiency and call volume. It's a pain, but I'm sure it can be done.





as always, thanks everyone, you've all been a lot of help

alright so Mazzaropi, your formula worked perfectly once i realized what you were saying, sorry about that

That being said, would there be a similar formula for average length of calls during that same period? I'm sure it's just changing one or two field selectors, I just can't get it right



thanks to anyone who can help th

that's perfect!

One last question and then we're finished!

What if a call is from 9:56-10:04, it wouldn't come up in either time field since the cut off times eliminate it by the formulas, right?

Would these just be lost, or is there a way to work them in?



thanks!