dizzneeguy
New Member
- Joined
- Aug 19, 2011
- Messages
- 6
Hi Everyone,
I have a unique question I can't seem to find an answer for anywhere. The ultimate answer may be that Excel isn't the right tool for the job but thought before taking time to involve "real" developer resource at my office I'd see if I can come up with a viable solution.
The issue is this - I have a list of thousands of upon thousands of customer calls to my call center, in which each row provides me the phone number of the person calling, the date they called, the agent they spoke to at my center, and the nature of their call. I'm trying to capture something called First Call Resolution rate (or FCR) with this data and essentially want to look for customers who have called more than once, and then be able to determine why they called and who they spoke with. I think that's pretty easy to get to with a Pivot table so I feel alright there.
However, I also want to determine the FCR rate for each agent. So for example, using the raw data I can see that John Doe spoke with two customers on Monday. One was customer 555-555-5555 and the other was customer 444-444-4444. While 444-444-4444 never had to call back, I do see that 555-555-5555 called back the next day. Therefore, John Doe's FCR rate is 50%. (50% of the customers he spoke to had to call back.) The ideal "next level" would be to be able to customize a call back window so customers calling back X days later are considered new contacts and wouldn't count against FCR. Also, setting up so a customer calling back for an entirely different reason also wouldn't count against FCR.
Tall order I know, but surely with the raw data in hand in Excel, there is some way to work up a filter, query or pivot that let's me answer these questions?
Thanks!
~ Chris
I have a unique question I can't seem to find an answer for anywhere. The ultimate answer may be that Excel isn't the right tool for the job but thought before taking time to involve "real" developer resource at my office I'd see if I can come up with a viable solution.
The issue is this - I have a list of thousands of upon thousands of customer calls to my call center, in which each row provides me the phone number of the person calling, the date they called, the agent they spoke to at my center, and the nature of their call. I'm trying to capture something called First Call Resolution rate (or FCR) with this data and essentially want to look for customers who have called more than once, and then be able to determine why they called and who they spoke with. I think that's pretty easy to get to with a Pivot table so I feel alright there.
However, I also want to determine the FCR rate for each agent. So for example, using the raw data I can see that John Doe spoke with two customers on Monday. One was customer 555-555-5555 and the other was customer 444-444-4444. While 444-444-4444 never had to call back, I do see that 555-555-5555 called back the next day. Therefore, John Doe's FCR rate is 50%. (50% of the customers he spoke to had to call back.) The ideal "next level" would be to be able to customize a call back window so customers calling back X days later are considered new contacts and wouldn't count against FCR. Also, setting up so a customer calling back for an entirely different reason also wouldn't count against FCR.
Tall order I know, but surely with the raw data in hand in Excel, there is some way to work up a filter, query or pivot that let's me answer these questions?
Thanks!
~ Chris
