Calculating time increment/differences between Agent Statuses & variance between them

John-Peddle

Hello Dojo's! Trying to see if the request we received can be done at the card level with a beast mode calculation rather than a MySql Dataflow, similar to something that was used in this instance (not the exact same but in the ballpark, so to speak:  https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Calculating-Average-Time-Between-First-Last-Login/m-p/27653#M3792).

 

What we'd like to accomplish is a way to show the time difference for agents coming and beginning their shifts, and what they're doing over those first XX number of minutes. Generally speaking, an agent will be in OFFLINE status once a shift ends, and until they log back in the following day (some work M-F, some work varying shifts).

 

So what we'd like to measure is 

1) How long from their last OFFLINE status to their first ON A CALL status

2) Time from their first AVAILABLE status to their first ON A CALL status

3) The time difference between the two

4) Best way to represent this data, i.e. visualization for our Call Center Management team

 

* The visual we had in mind was a basic table graph since it might the simplest thing to do for this KPI, but prefer to use a more  appealing visual chart that's easy to understand if there's a better way to represent the data - we're definitely open to suggestions! 

 

Lastly, the dataset we're using is basically what's seen below, and will contain Agent Name, Status, Start Time, End Time, and Duration which I'm guessing should be enough information, if this is even possible.

 

If there's anything else that you might need, please don't hesitate to ask! Thank you in advance for your time and assistance...

 

John

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ST_-Superman-_

So, you basically want to calculate the duration column in rows 3, 33, and 66. Is that right?

 

that seems to be the answer to both questions 1 and 2 to me, so I’m not sure what the difference is. Could you elaborate?

John-Peddle

Thanks for the reply, @ST_-Superman-_. The image example basically shows 3 days of data for an agent (Thur, Fri, & Mon). Each night they go "offline" until the start of their next shift, at which time they go into "available" status, waiting for a call to come in. Once the call arrives, they go into "on a call" status.

 

We'd like to determine the amount of time from the END TIME of their "offline" status to the START TIME of their first "on a call" status, then the time from the START TIME of their "available" status to the  START TIME of their first "on a call" status, and ultimately the difference between those two numbers.

 

EXAMPLE: 

1) Starting on Friday, the time elapsed between the end of their "Offline" status in row 32 (END TIME on 11/2/17 at 7:56am) to their first "On a Call" status in row 34 (START TIME at 7:59am)

 

2) Next, because it can be different from "Offline" status above we're hoping to determine the time elapsed between their first "Available" status in row 33 (START TIME on 11/2/17 at 7:56am) to their first "On a Call" status in row 34 (START TIME at 7:59am)

 

3) Then determine the difference between 1 and 2 above, if applicable (this is the least important aspect of our request since more times than not those 2 numbers will be the same, but not always)

 

4) Repeat steps for Monday, using the same statuses with START/END TIMES but they would now be rows 65, 66, & 67 as opposed to rows 32, 33, & 34, and so on for the each shift worked (we're thinking this might have to be done as a daily card, not sure though)

 

 

** Honestly, I'm not sure that this is even possible, so at the very least we're hoping that number 1 above can be done - time elapsed between END TIME of their overnight "Offline" status and START TIME of their first "On a Call" each day they work (this would use different rows/same columns every day).

 

In the example image, that would consist of row/column 2D & 4C, then row/column 32D & 34C, then row/column 65D & 67C, and so on for each day they have statuses from shifts.

 

Please let me know if I can explain this further, or provide additional examples to clarify the ask. Again, any assistance you're able to provide is greatly appreciated!

Marc_H

Hi, John,

 

I'm a little confused. Isn't what you're wanting already calculated in Column E - Duration? If we look closely at what you're wanting for Friday:

 

Sample: 1) Starting on Friday, the time elapsed between the end of their "Offline" status in row 32 (END TIME on 11/2/17 at 7:56am) to their first "On a Call" status in row 34 (START TIME at 7:59am)

 

Isn't that the same as Available Start and End? Which is Start: 7:56am and Ends 7:59am giving you a duration of 3.43 Min?

 

I.E. This is assuming the End time of Offline is always the Start time of Available and End time of Available is always the Start time of On a Call. Because the logic here is if the agent goes into Available then it ends the Offline time. Then when the agent is On a Call then he ends the Available time.

 

Warm regards,

 

 

 

Marc Ha

mkozai

FWIW - I just did a very similar project, but did it all in a redshift data flow using the window functions.  Unfortunatley my solution ended up being pretty complex. .  Intrigued to see if someone knows a trick to do this in beast mode.  

 

Things I had to consider: 

1. Daylight Savings Time different offsets at different times of the year.
2. All data stored in UTC
3. Tons of irrelevent data in the logs 
4. Random sequencing of relevent log data (similar to you where available is not always present)
5. Removal of non working hours to ensure response time only calculates working hours.  
6. And some other things too complicated to explain......

 

Here is what I would do to get your first call time via Redshift.

 

 

select "Agent Name", status, "Start Time", "End Time" 
	,Lead("Start Time",1) over (partition by "Agent Name" order by "Start Time") as First_Call_Start
From Data
where Status in ('Offline','On a Call')

you can then do the time delta between "End Time" and "First_Call_Start"on all records with the status of Offline to see how long it takes for them to start their first call.  

 

 

mkozai

FWIW - I just did a very similar project, but did it all in a redshift data flow using the window functions.  Unfortunatley my solution ended up being pretty complex. .  Intrigued to see if someone knows a trick to do this in beast mode.  

 

Things I had to consider: 

1. Daylight Savings Time different offsets at different times of the year.
2. All data stored in UTC
3. Tons of irrelevent data in the logs 
4. Random sequencing of relevent log data (similar to you where available is not always present)
5. Removal of non working hours to ensure response time only calculates working hours.  
6. And some other things too complicated to explain......

 

Here is what I would do to get your first call time via Redshift.

 

 

select "Agent Name", status, "Start Time", "End Time" 
	,Lead("Start Time",1) over (partition by "Agent Name" order by "Start Time") as First_Call_Start
From Data
where Status in ('Offline','On a Call')

you can then do the time delta between "End Time" and "First_Call_Start"on all records with the status of Offline to see how long it takes for them to start their first call.  

 

 

mkozai

I just had to solve a similar problem.  I don't think this is possible via beast mode but if it is, I would love to learn the trick.  I solved this using the window function in Redshift.  Since you are trying to figure out the time between when the user ends their offline time and begins their first call, you would want to isolate just that data in a transform and use the lead parameter to get the next value.

select "Agent Name", status, "Start Time", "End Time" 
	,Lead("Start Time",1) over (partition by "Agent Name" order by "Start Time") as First_Call_Start
From Data
where Status in ('Offline','On a Call')

Since the where clause isolates to only entries where the status is offline or on a call, you can be assued that no matter what happens betweenthe 2 it will be ignored.  Then the window function will section off data based on the agent, sort by start time and reliably give you the start time of the first occurance of "On A Call" after each offline entry.  you can then do the datediff in redshift or in beastmode.  Repeat this for any other time delta you are looking for.  

John-Peddle

@Marc_H, column E was actually added to transform seconds into minutes, but yes, the tiem elapsed is available to a degree but as pointed out an agent won't ALWAYS go from Offline to On a Call, at times going into one of a handful of statuses such as WORKING OFFLINE, WORKING CHATS, etc. and then go available to take a call.

 

Additionally, our goal is to isolate these instances for reporting, but filtering will not work for these and our understanding is we'd need to do it by either 1) a beast mode or 2) as @mkozai pointed out in his reply, which is how it appears he was able to do this for something similar. Thanks for your reply, appreciate all the info we're able to get for this!

John-Peddle

@mkozai, thanks for the response on how you solved a similar problem; haven't received anything yet on how this might be donw with a beast mode, and will likely forward your resolution along to our Engineering Team to see if it's something that they can get to work on the back end for our situation. Have a great day!