I'm working with a table of conversation data, all conversations start out as a bot chat and can be escalated to a human agent. The ConversationId remains persistent through the escalation.
Each ConversationEntry is a message, inbound or outbound, in a MessagingSession.
ConversationId is the MessagingSession parent to the individual entries in/out
All MessagingSessions I'm looking at will have an EventType=ChatbotEstablished, not all will have an EventType=BotEscalated.
I can't figure out how to calculate the percentage of conversations that had an escalation. Below is my query and a stats output. I'm trying to figure out how I get BotEscalated/ChatbotEstablished.
index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(ConversationId) as EntryCount by EntryType
EntryType EntryCount
BotEscalated | 3 |
ChatbotEstablished | 10 |
HI@arist0telis !
A percentage is number of escalations out of the total established, times 100. Or with more math notation:
(BotEscalated/ChatbotEstablished) x 100 = Percentage Escalated
So we convert that to eval statements. I haven't tested it below, but it should be pretty close.
index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(eval(EntryType=='BotEscalated')) as "BEcount", count(eval(EntryType=='ChatbotEstablished')) as "CEcount" ``` get the counts```
| eval mypercentage = round(('BEcount'/'CEcount')*100, 2) ```get the percentage and round to 2 places```
I think I may have figured it out myself, just had to take a step away for a minute. Pasting what I got here in case this comes up in a Google search and someone else needs help.
index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(eval(if(EntryType="ChatbotEstablished",1,null()))) as ChatCount count(eval(if(EntryType="BotEscalated",1,null()))) as EscalationCount by ConversationId
| stats sum(ChatCount) as sumChat sum(EscalationCount) as sumEscalation
| eval pctEscalation=round(((sumEscalation/sumChat)*100),2)
| table sumChat, sumEscalation, pctEscalation