Creating a New Derived Field Using the sec_to_time Function (Example)
- Josh Daugherty (Deactivated)
In this article, we’ll build another derived field (see Example 1 here), one that will help us understand the experience customers are having when they reach out for Support.
We know that customers hate waiting to be connected to an agent. They also hate being put on hold, or being transferred (unless necessary).
Let's build a new derived field that will calculate the duration of customer chat interactions (similar to Agent Handle Time, but on the customer side).
Planning the Derived Field
When we consider the customer journey and think about how long it takes for them to have their issue resolved, we can identify several elements of time that add up.
Wait Time (how long it took the customer to be connected with the agent)
Agent Talk Time (how long it took for the agent to resolve the issue after "picking up" the interaction)
Agent Hold Time (how long the customer spent put on hold by the agent)
Switch Hold Time (how long the customer spent on hold due to the agent switching between multiple interactions)
This derived field, then, will be mostly* straightforward to construct:
Chat Duration (Customer) =
Wait Time + Agent Talk Time + Agent Hold Time + Switch Hold Time
* = This derived field will require the use of the sec_to_time function.
Understanding the sec_to_time Function
This example will make use of a specific function (sec_to_time) that may or may not be familiar to the average user.
When working with time-based fields, the sec_to_time function (specifically available for derived fields), allows us to easily work with time-based data without the need for manual conversion to hh:mm:ss. It works by converting hh:mm:ss to a total number of seconds, performing calculations, applying functions, and then re-formatting the resulting value into an understandable, time-formatted value.
Since this derived field uses multiple time-based fields (adding them all together), the sec_to_time function is rather essential. Based on the basic formula illustrated above, if we were to build this relatively simple field without the sec_to_time function, here's what would result:
Technically, the data in this row is correct...but it would be more helpful in hh:mm:ss format. If we would have used the sec_to_time function as part of the field's architecture, we would see a Chat Duration (Customer) of 00:09:48 (588 seconds).
First Steps
[1]
After accessing the Derived Fields Manager and selecting Queue Segment Activity for the data source, click the +New Derived Field button.
It make sense to name this derived field Chat Duration (Customer):
The process of building out the rest of this field, then, is pretty straightforward.
[2]
The first button we need to click is the add group button.
When the box appears with our parentheses inside, make sure the red outline is around that group.
[3]
With the red outline around the group (as shown in the screenshot above), add the four fields with plus signs between each.
Notice the blue set of parentheses around all of the fields and functions.
This indicates that if we apply a group function, Insights will first add together the time fields, and then apply the group function we select.
[4]
With the red outline around the entire group, we can choose the sec_to_time function from the little drop-down menu, as shown below:
[5]
Click Save, and then close the Derived Fields Manager.
When we return to our report, we can add our new derived field to the report—just like we would add any other type of field.
Variation: Average Support Chat Duration
If we wanted to, we could add one more component to our formula, in order to essentially perform a summary function (average) on our derived field. This has to be done within the derived field's architecture.
See what it looks like below: