Calculate who has the most repeat calls in the call center

[kgowett]

Hi all!
I have A TON of SQL data, and I've been racking my brain to figure out how to make this work. I need to identify the phone numbers who call in within certain time increments, and then figure out who the previous employee was who took the first call. Both pieces of information are available in the raw data. What I've been finding are formulas IF the data falls within certain date/time cell counts (referencing B1:B11 for example)- which isn't helpful. I have hundreds of thousands of rows of data and one day could use 100k rows while another day could use 20k.

Essentially, I have:

(Column A) Date/Time (Column B) Employee # (Column C) Customer Phone Number
01/01/2019 12:00:00 ................ 123456.......................(800)1234567

I've tried a pivot table, but it pulls so much information that doesn't give me my desired outcome. What I'm hoping to achieve is a couple extra columns at the end of my existing sheet so I can pivot that.

Future state:

Column A:Date/Time ..Column B:Employee # ..Column C:Customer Phone Number ..Column D:Same Day Repeat (Y/N=1/0) ..Column E:Original Agent (Pull previous employee #)
01/01/2019 12:00:00 ................ 123456.......................(800)1234567................................1............................................................123459

Future state metrics would include same-day repeats, 2 day repeats, and 2 week repeats. Any ideas?

[CK76]

I'd recommend uploading sample workbook with raw data, and few sample of expected output.
Replacing sensitive data with some generic data (while maintaining same data structure and/or string pattern).

To upload workbook, use "Go Advanced" button, follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

[kgowett]

The upload repeatedly fails. Thank you for providing the steps, though! I wouldn't have had any idea how to do it

Here's an image. I hope this helps:
Example book.png

[CK76]

Unfortunately no. Image is of little help.

If file size is larger than 1,000.0 KB, you'll need to trim it down.

[kgowett]

Ok, got it to upload as a zip!

[CK76]

Oi, you've got unnecessary formats all the way down to row 1048576. Bloating file size.

Some odd issue is found in workbook... but you can fix it by selecting all sheet, then applying clear formats.

Apply formatting to data range only. It should reduce file size from 5MB + to 12KB.

However, just 2 lines of data isn't enough to help you. Can you upload about 50 rows of data?

[kgowett]

Alright, let's try this!

Thanks so much for your help

[CK76]

If I understood your requirement...
In D2:
=IF(COUNTIFS($A$1:A2,"<="&INT(A2)+TIME(23,59,59),$A$1:A2,">="&INT(A2),$C$1:C2,C2)>1,"Y","N")

Same concept applies to F & H columns.

Now, for EMPL what should be returned when there are more than 1 that meets condition?
Ex: When there are more than 2 matching result, which of the EMPL should be returned? The original (i.e. earliest call) or one previous? What if original record was found 3 weeks prior? Should it be return EMPL from 2 week prior date, or go all the way back to beginning of record?

[kgowett]

You're amazing!

For EMPL, we only need the most recent touch-point assignment. Whoever got the call last was responsible for the customer.

[CK76]

Then you can use something like...

In E2:

Please Login or Register  to view this content.

Confirmed as Array formula (CTRL + SHIFT + ENTER)

See attached.
Note: I've edited Row 24 to have same CustomerPhone# as Row 26 & 35 to demonstrate Case where customer called 3 times in a 2 week period.

[CK76]

@kgowett

Thanks for the rep

If the solution provided satisfies your need, please mark the thread as solved by using thread tool found at top of your initial post.