Splunk Search

How to create an eval metric based on a sequence of events?

KyleMcDougall
Path Finder

Hi all,

I'm trying to create a "Fallback escalation rate" for a chatbot. This rate would be calculated by users that hit the fallback intent, and then ask for an agent anytime after that, within a given session. 

For context, when a user says something, we use an intent classifier to try and match it to an intent. If we can't match the user input to an intent, it hits our fallback intent. And if a user asks for an agent, it hits the followup_live_agent intent.

Each session contains multiple events, and each event represents one intent. 

Today, we calculate "Escalation rate" by counting the sessions with at least one "followup_live_agent" intent. Here's the search query I created for that: 

 

 

index=conversation sourcetype=cui-orchestration-log botId=123456
| eval AgentRequests=if(match(intent, "followup_live_agent"), 1, 0)
| stats sum(AgentRequests) as Totaled by sessionId
| eval Cohort=case(Totaled=0, "Cooperated", Totaled>=1, "Escalated")
| stats count by Cohort
| eventstats sum(count) as Total
| eval Agent_Request_Rate = round(count*100/Total,2)."%"
| fields - Total
| where Cohort="Escalated"

 

 

 

I need to know how to calculate this same thing, but only after the fallback intent is hit. I figure I need to retain the timestamp and do some calculation using that. I'm not even sure how to get started on this, so if anyone could point me in the right direction, that would be really helpful. 

Labels (3)
0 Karma
1 Solution

jdunlea
Contributor

OK, so now you have to get the earliest fallback time and then the latest escalation time within the sessionId, and then find out which one came first. So the search below should do it. It will bring out 3 fields:

total_fallback_sessions, total_escalated_sessions, total_escalated_sessions_after_a_fallback

 

Then you should be able to use those values to calculate the rate you need. 

Let me know if this works!

 

...
| eval is_fallback=if(match(intent,"THE_FALLBACK_INTENT_VALUE"),1,0)
| eval is_agent_escalation=if(match(intent, "followup_live_agent"),1,0)
| eval is_fallback_timestamp=if(is_fallback=1,_time,null())
| eval is_agent_escalation_timestamp=if(is_agent_escalation=1,_time,null())
| stats min(is_fallback_timestamp) as earliest_fallback_timestamp max(is_agent_escalation_timestamp) as latest_escalation_timestamp max(is_fallback) as had_a_fallback, max(is_agent_escalation) as had_an_escalation by sessionId
| eval time_diff=latest_escalation_timestamp-earliest_fallback_timestamp
| eval is_escalated_after_fallback=if(time_diff>0,1,0)
|  stats sum(had_a_fallback) as total_fallback_sessions, sum(is_escalated_after_fallback) as total_escalated_sessions_after_a_fallback (sum(is_agent_escalation) as total_escalated_sessions

 

View solution in original post

jdunlea
Contributor

Sorry, I used the wrong field name in the lat stats. The following should work! 

 

....
| eval fallbacks=if(match(intent,"fallback"),1,0)
| eval agent_escalation=if(match(intent, "support.contact_agent"),1,0)
| eval fallback_timestamp=if(fallbacks=1,_time,null())
| eval agent_escalation_timestamp=if(agent_escalation=1,_time,null())
| stats min(fallback_timestamp) as earliest_fallback_timestamp max(agent_escalation_timestamp) as latest_escalation_timestamp max(fallbacks) as had_a_fallback, max(agent_escalation) as had_an_escalation by sessionId
| eval time_diff=latest_escalation_timestamp-earliest_fallback_timestamp
| eval escalated_after_fallback=if(time_diff>0,1,0)
| stats sum(had_a_fallback) as total_fallback_sessions, sum(escalated_after_fallback) as total_escalated_sessions_after_a_fallback sum(had_an_escalation) as total_escalated_sessions
| eval Fallback_escalation_rate=round(total_fallback_sessions/total_escalated_sessions*100, 1)

 

0 Karma

jdunlea
Contributor

OK, so now you have to get the earliest fallback time and then the latest escalation time within the sessionId, and then find out which one came first. So the search below should do it. It will bring out 3 fields:

total_fallback_sessions, total_escalated_sessions, total_escalated_sessions_after_a_fallback

 

Then you should be able to use those values to calculate the rate you need. 

Let me know if this works!

 

...
| eval is_fallback=if(match(intent,"THE_FALLBACK_INTENT_VALUE"),1,0)
| eval is_agent_escalation=if(match(intent, "followup_live_agent"),1,0)
| eval is_fallback_timestamp=if(is_fallback=1,_time,null())
| eval is_agent_escalation_timestamp=if(is_agent_escalation=1,_time,null())
| stats min(is_fallback_timestamp) as earliest_fallback_timestamp max(is_agent_escalation_timestamp) as latest_escalation_timestamp max(is_fallback) as had_a_fallback, max(is_agent_escalation) as had_an_escalation by sessionId
| eval time_diff=latest_escalation_timestamp-earliest_fallback_timestamp
| eval is_escalated_after_fallback=if(time_diff>0,1,0)
|  stats sum(had_a_fallback) as total_fallback_sessions, sum(is_escalated_after_fallback) as total_escalated_sessions_after_a_fallback (sum(is_agent_escalation) as total_escalated_sessions

 

KyleMcDougall
Path Finder

Wow, extremely helpful!! I almost got it working. But, for some reason, I can't get the last eval command to show up. Even if I use a table command, it won't show up.

I need to create a simple fallback escalation rate as a % for this query. 

....
| eval fallbacks=if(match(intent,"fallback"),1,0)
| eval agent_escalation=if(match(intent, "support.contact_agent"),1,0)
| eval fallback_timestamp=if(fallbacks=1,_time,null())
| eval agent_escalation_timestamp=if(agent_escalation=1,_time,null())
| stats min(fallback_timestamp) as earliest_fallback_timestamp max(agent_escalation_timestamp) as latest_escalation_timestamp max(fallbacks) as had_a_fallback, max(agent_escalation) as had_an_escalation by sessionId
| eval time_diff=latest_escalation_timestamp-earliest_fallback_timestamp
| eval escalated_after_fallback=if(time_diff>0,1,0)
| stats sum(had_a_fallback) as total_fallback_sessions, sum(escalated_after_fallback) as total_escalated_sessions_after_a_fallback sum(agent_escalation) as total_escalated_sessions
| eval Fallback_escalation_rate=round(total_fallback_sessions/total_escalated_sessions*100, 1)

 

0 Karma

KyleMcDougall
Path Finder

Nm, got it to work. Thanks for your help!!

0 Karma

jdunlea
Contributor

Oh I see now that the fallback rate is a fallback escalation rate. So it is also keying off of whether or not the user escalated after hitting the fallback. 

 

I still think you need to eval a field for "is_fallback" and then marry that with another eval field for "is_escalated" and then do your calc from there. Maybe something like:

 

...
| eval is_fallback=if(match(intent,"THE_FALLBACK_INTENT_VALUE"),1,0)
| eval is_agent_escalation=if(match(intent, "followup_live_agent"),1,0)
| stats max(is_fallback) as had_a_fallback, max(is_agent_escalation) as had_an_escalation by sessionId
|  stats sum(had_a_fallback) as total_fallback_sessions, sum(is_agent_escalation) as total_escalated_sessions
| eval fallback_escalation_rate=total_escalated_sessions/total_fallback_sessions

 

jdunlea
Contributor

I think you can create a new field to mark where an intent is a fallback intent or not, and then do your stats calcs on top of that field. Something like:

...
| eval is_fallback=if(match(intent,"<THE_FALLBACK_INTENT_VALUE>"),1,0)
| stats sum(is_fallback) as fallback_intent_count by sessionId
| eventstats dc(sessionId) as total_session_count
| stats sum(is_fallback) as total_fallback_sessions max(total_session_count) as total_session_count
| eval fallback_rate=total_fallback_sessions/total_session_count

 

Are you trying to do this over time, like on a time chart? Or just create a fallback rate for a specific period of time as selected in your time range picker?

KyleMcDougall
Path Finder

I like where you're headed! But it's missing the sequence of events. The "escalation rate" is if the session contains any instance of followup_live_agent. 

The fallback escalation rate is if a followup_live_agent intent exists AFTER the fallback intent. This is the one I can't seem to figure out. 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...