I'm trying to create a customer service application. We have data that has timestamps for calls.
We have the concept of ‘conversations’. That is any time a customer calls multiple times over a 24 hour period that counts as a single unique conversation.
Let’s say customer 3 has one conversation and customer 4 has 2 conversations. That’s ‘cause customer 3 called all within 24hrs and customer 4 called once beyond 24 hrs. So the data looks like this:
| Date | CustID |
| 5/2/2014 2:00 | 3 |
| 5/2/2014 3:00 | 3 |
| 5/2/2014 17:00 | 3 |
| 5/3/2014 1:00 | 3 |
| 5/2/2014 3:00 | 4 |
| 5/2/2014 4:00 | 4 |
| 5/2/2014 18:00 | 4 |
| 5/3/2014 2:00 | 4 |
| 5/3/2014 4:00 | 4 |
We want the load script to track the unique conversations. I guess the thing to do would be to have each record track the 1st time within 24 hours the customer called us. So when QV's script is done it looks like this:
| Date | CustID | StartConversation | ConversationLength | UniqueVisits |
| 5/2/2014 2:00 | 3 | 5/2/2014 2:00 | 0 | 1 |
| 5/2/2014 3:00 | 3 | 5/2/2014 2:00 | 1 | 0 |
| 5/2/2014 17:00 | 3 | 5/2/2014 2:00 | 15 | 0 |
| 5/3/2014 1:00 | 3 | 5/2/2014 2:00 | 23 | 0 |
| 5/2/2014 3:00 | 4 | 5/2/2014 3:00 | 0 | 1 |
| 5/2/2014 4:00 | 4 | 5/2/2014 2:00 | 1 | 0 |
| 5/2/2014 18:00 | 4 | 5/2/2014 2:00 | 15 | 0 |
| 5/3/2014 2:00 | 4 | 5/2/2014 2:00 | 23 | 0 |
| 5/3/2014 4:00 | 4 | 5/3/2014 4:00 | 0 | 1 |
Obviously when the conversation length reaches 24 hours, it acts like a new conversation so the conversation length would never reach 25.
Any ideas how to do something like this? I've attached a sample excel file with 2 tabs from above.
.png "Former Employee"){kind=icon}
