0

I'm using the LiveChatTranscriptEvent object to try and determine the time difference between the events ChatRequest and TransferredToQueue. Each Type is a different row on the object so it would look something like this.

Time Type
2021-01-07T15:35:02.000Z ChatRequest
2021-01-07T15:35:06.000Z ChatbotEstablished
2021-01-07T15:35:07.000Z TransferredToQueue

First I created a filter transformation to only include ChatRequest and TransferredToQueue.

Time Type
2021-01-07T15:35:02.000Z ChatRequest
2021-01-07T15:35:07.000Z TransferredToQueue

In the dataflow I made a compute expression with the fields ChatRequest and TransferredToQueue for easy reference.

ChatRequest

case
when 'Type' == "ChatRequest" then toDate(Time_sec_epoch)
end

TransferredToQueue

case
when 'Type' == "TransferredToQueue" then toDate(Time_sec_epoch)
end

So now my dataset would look like this

Time Type ChatRequest TransferredToQueue
2021-01-07T15:35:02.000Z ChatRequest 2021-01-07T15:35:02.000Z null
2021-01-07T15:35:07.000Z TransferredToQueue null 2021-01-07T15:35:07.000Z

Next I created a compute relative transformation. Every time the ChatRequest type occurs, calculate the difference between the ChatRequestDat and TransferredToQueueDate

case
when 'Type' == "ChatRequest" then date_diff("second", ChatRequestDate, next(TransferredToQueueDate))
end

End product would look like this

Time Type ChatRequest TransferredToQueue CalculatedField
2021-01-07T15:35:02.000Z ChatRequest 2021-01-07T15:35:02.000Z null 2
2021-01-07T15:35:07.000Z TransferredToQueue null 2021-01-07T15:35:07.000Z null

When I run my dataflow it returns the error that there's invalid arguments for function date_diff. I'm assuming the next() function isn't working. And I can't find any documentation about the next() function.

What is another solution to try and accomplish the same effect?

0

I created a solution but I don't believe it's ideal. If someone has a better way to solve this please let me know.

I started with my filtered data. I included PushAssignment in my filter transformation because I wanted to find how long a client was in queue before they were pushed to an agent.

Sample data

Time Type
2021-01-07T15:35:02.000Z ChatRequest
2021-01-07T15:35:07.000Z TransferredToQueue
2021-01-07T15:41:07.000Z PushAssignment

Added a compute expression convert the Time field to epoch seconds

SAQL Expression

date_to_epoch(toDate(Time_sec_epoch))

Sample data

Time Type TimeToNumber
2021-01-07T15:35:02.000Z ChatRequest 1,610,033,702
2021-01-07T15:35:07.000Z TransferredToQueue 1,610,033,707
2021-01-07T15:41:07.000Z PushAssignment 1,610,034,067

Next I added a compute relative partitioning by key/unique Id then by TimeToNumber. I created a field that would grab the value of TimeToNumber from the next row.

SAQL Expression

next(TimeToNumber)

Sample data

Time Type TimeToNumber SegmentEndTime
2021-01-07T15:35:02.000Z ChatRequest 1,610,033,702 1,610,033,707
2021-01-07T15:35:07.000Z TransferredToQueue 1,610,033,707 1,610,034,067
2021-01-07T15:41:07.000Z PushAssignment 1,610,034,067

Finally I created a another compute expression with a field to calculate the difference between the previous two fields.

SAQL Expression

'SegmentEndTime' - 'TimeToNumber'

Sample data

Time Type TimeToNumber SegmentEndTime EventDuration
2021-01-07T15:35:02.000Z ChatRequest 1,610,033,702 1,610,033,707 5
2021-01-07T15:35:07.000Z TransferredToQueue 1,610,033,707 1,610,034,067 360
2021-01-07T15:41:07.000Z PushAssignment 1,610,034,067 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.