Duplicate Data Question

[dizzneeguy]

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

 

[Jerry Sullivan]

Hi Chris and Welcome to the Board,

You could probably get Key Performance Indicators like the FCR you describe using COuNTIF and SUMIF formulas.

The example below shows an approach of adding a column to count whether each call is a "First Call" and another to count if that was a First Call that was Resolved without having a repeat call within the Call Back Window.

I've used named ranges for each column of data to make the formulas easier to follow.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Phone #	Date | Time	Agent	Nature of Call		First Call	Resolved First		Call Back Window:	2	Days
2	111-1111-111	08/01/2011 08:30	D. Duck	Reason A		1	0
3	222-2222-222	08/01/2011 14:15	M. Mouse	Reason A		1	1		Results by Agent	First Calls	Resolved	FCR
4	333-3333-333	08/01/2011 09:30	S. White	Reason A		1	1		D. Duck	2	1	50%
5	111-1111-111	08/02/2011 14:45	M. Mouse	Reason A		0	0		M. Mouse	3	3	100%
6	444-4444-444	08/02/2011 10:30	M. Mouse	Reason A		1	1		S. White	4	3	75%
7	555-5555-555	08/02/2011 11:00	S. White	Reason A		1	1
8	666-6666-666	08/03/2011 16:00	D. Duck	Reason A		1	1
9	777-7777-777	08/03/2011 17:15	M. Mouse	Reason A		1	1
10	888-8888-888	08/04/2011 08:45	S. White	Reason A		1	1
11	444-4444-444	08/04/2011 11:15	S. White	Reason A		1	0
12	444-4444-444	08/04/2011 15:30	M. Mouse	Reason A		0	0
First Call Resolved

#VALUE!

</td></tr></table></td></tr></table>

Also, setting up so a customer calling back for an entirely different reason also wouldn't count against FCR.

The formulas in the two added columns could be revised to account for this; however you'll need a standardized way for Excel to interpret whether a repeat call is for a different reason.

 

[dizzneeguy]

Hi Jerry,

Thanks for the welcome, and for the information! I'm trying to follow your steps right now, and while I'm pretty sure I'm understanding I have one clarifcation question.

Specfically, in your forumla in cell F2 (=IF(COUNTIFS(Phone,$A2,DateTime,"<"&B2,DateTime,">="&B2-CallBackWindow),0,1)) I assume where it says "phone" you actually want me to enter the cell being evaluated, not a column header? So in your example if I were to try and replicate in Excel instead of "phone" I would enter "A2". Same goes for things like DateTime actually needing to be cell B2, CallBackWindow needing to be cell J1, etc.?

I'll have to think about the repeat call for a different or same reason - clearly you are a master and I'm only the student. I might need some additional help with that however, if you are so inclined.

Thanks so much for the help - I'm excited to get into Excel and try this out. So glad I joined up here at MrExcel.com!

~ Chris

 

[tweedle]

Hi Jerry,

Thanks for the welcome, and for the information! I'm trying to follow your steps right now, and while I'm pretty sure I'm understanding I have one clarifcation question.

Specfically, in your forumla in cell F2 (=IF(COUNTIFS(Phone,$A2,DateTime,"<"&B2,DateTime,">="&B2-CallBackWindow),0,1)) I assume where it says "phone" you actually want me to enter the cell being evaluated, not a column header? So in your example if I were to try and replicate in Excel instead of "phone" I would enter "A2". Same goes for things like DateTime actually needing to be cell B2, CallBackWindow needing to be cell J1, etc.?

I'll have to think about the repeat call for a different or same reason - clearly you are a master and I'm only the student. I might need some additional help with that however, if you are so inclined.

Thanks so much for the help - I'm excited to get into Excel and try this out. So glad I joined up here at MrExcel.com!

~ Chris

I see JS is signed off; so ...
you can do the substitutions you suggest.

However;
He has provided Named Ranges for use.
So in selecting A2 to A12, going to the Range Name box (up by the insert function (fx)) and naming A2 to A12 'Phone', then that range is utilized in the formula wherever 'phone' is mentioned. Otherwise the formula would read
=IF(COUNTIFS($A$2:$A$12,$A2,DateTime, ...
instead of
=IF(COUNTIFS(Phone,$A2,DateTime, ...

Same thing for DateTime & CallBackWindow : He's using Named Rangess instead of cell references.
His post has a section "Names in Formulas " which identifies the Named Ranges he is using.

 

[dizzneeguy]

I see JS is signed off; so ...
you can do the substitutions you suggest.

However;
He has provided Named Ranges for use.
So in selecting A2 to A12, going to the Range Name box (up by the insert function (fx)) and naming A2 to A12 'Phone', then that range is utilized in the formula wherever 'phone' is mentioned. Otherwise the formula would read
=IF(COUNTIFS($A$2:$A$12,$A2,DateTime, ...
instead of
=IF(COUNTIFS(Phone,$A2,DateTime, ...

Same thing for DateTime & CallBackWindow : He's using Named Rangess instead of cell references.
His post has a section "Names in Formulas " which identifies the Named Ranges he is using.

Oh OK - I think I understand then. Totally makes sense. Let me go play a bit and I'll get back to you all. (My current data set is over 20K rows so it's a little time consuming to mess with formulae.)

 

[Jerry Sullivan]

Tweedle, Thanks for helping out - I normally only get to log on in the evenings.

Chris,

Let us know if you need any help setting up the named ranges. With your data set of ~20K rows, using named ranges is easier to maintain than selecting the cell ranges for the forumulas.

The next step would be to make your Named Ranges Dynamic, so they adjust in size as your data set gets more or less rows. That's fairly simple to do; however I'd suggest that you start by getting this to work with a smaller data set and static named ranges.

 

[dizzneeguy]

Tweedle, Thanks for helping out - I normally only get to log on in the evenings.

Chris,

Let us know if you need any help setting up the named ranges. With your data set of ~20K rows, using named ranges is easier to maintain than selecting the cell ranges for the forumulas.

The next step would be to make your Named Ranges Dynamic, so they adjust in size as your data set gets more or less rows. That's fairly simple to do; however I'd suggest that you start by getting this to work with a smaller data set and static named ranges.

Thanks guys! I got everything working at least at the basic level.

What's your opinion on maximum "workable" number of rows in a workbook though before Excel bombs out. I'm sure it has as much to do with PC power as anything but I get the feeling if I have more than 25K or 30K records things could get ugly. Knowing I could have 450K records or more each month I know that certainly won't work in the traditional way.

 

[Jerry Sullivan]

What's your opinion on maximum "workable" number of rows in a workbook though before Excel bombs out. I'm sure it has as much to do with PC power as anything but I get the feeling if I have more than 25K or 30K records things could get ugly. Knowing I could have 450K records or more each month I know that certainly won't work in the traditional way.

Chris,

It's good to hear that you got this to work. I don't have much experience working with that size dataset; however there are many experts on this Board who could help answer that.

From my observation, the workability of a workbook is more affected by the type and number of formulas than it is the number of rows of data. So a workbook with one worksheet with 1000 rows of complex formulas, might be less responsive than a workbook with 1 million rows by 2 columns of constants.

IMO, if you have processes that generate 450,000 records per month, then you would benefit from professional consulting from consulants with expertise in both Access and Excel (like the good folks at MrExcel that host this site).

Good luck!

 

[Mattd12345]

Hello everyone.

I am using these formulas and its working out great for an overall perspective of finding our FCR rate, my issue is finding the agents FCR rate, or First Touch Rate. If we get 3 calls on the same day and the same agent takes the call, how would we track that per agent? Example, customer calls in and gets agent A, they do not help the customer so they call in again to agent B, they do not help the customer so they call in yet again to agent C and they help the customer. Per the over all metric that's an FCR miss, BUT agent A and B should also get an FCR miss. I hope I am not confusing and any help would be appreciated.

Thank you

Matt

 

[Jerry Sullivan]

Hi Matt, If you define the outcome you want, we should be able to modify the formula to achieve that.

In the scenario you describe, would you want all 3 agents to get an FCR miss?
It would seem like agent B and agent C never had a chance to get a positive outcome.

You might take one of these three approaches depending on whether you want to reward Agent C for resolving the customers question on the first opportunity that agent had.

Excel 2013
	I	J	K
14	#1
15	Results by Agent	First Calls	Resolved
16	D. Duck	1	0
17	M. Mouse	1	0
18	S. White	1	1
19
20	#2
21	Results by Agent	First Calls	Resolved
22	D. Duck	1	0
23	M. Mouse	0	0
24	S. White	0	0
25
26	#3
27	Results by Agent	First Calls	Resolved
28	D. Duck	1	0
29	M. Mouse	0	0
30	S. White	1	1
First Call Resolved