In this recipe, you'll learn how to create a report that shows the percentage of inbound calls that were abandoned during a time frame you specify.
What you'll need
Skill level: Intermediate
Time required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Call data in Zendesk Talk
How to create the report in Explore
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Talk > Calls > Talk: Calls, then click New query.
Query Builder opens. - In the Calculations menu (
), click Standard calculated metric. - On the Standard calculated metric page, enter or paste the following formula:
IF ([Call direction]="Inbound"
AND [Call completion status]="Abandoned in queue")
AND (VALUE(Call duration (sec))>0
AND VALUE(Call duration (sec))<=20)
THEN [Call ID] ENDIF - Name the calculated metric "Calls abandoned in queue within 20 sec" and save it. The metric returns the [Call ID] for: Inbound calls that were Abandoned in queue with a Call talk time that is 20 sec or less.
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - In the Metrics panel, click Add, and then choose "Calls abandoned in queue within 20 sec". Make sure to select the D_COUNT aggregator and then click Apply.
- In the Metrics panel, click Add, and then choose the default metric "Inbound Calls".
- In the Result manipulation panel, click Result metric calculation, then click Add new metric.
- Name the metric Percentage of abandoned calls in queue within 20 sec and using the Insert metric drop down menu, select both metrics from step 5 and 6 to obtain the following one:
D_COUNT(Calls abandoned in queue within 20 sec)/D_COUNT(Inbound calls)
-
Check the box Clear used metrics, and then click Apply.
-
In the Chart configuration menu, click Display format and select % for the metric created in Percentage of abandoned calls in queue within 20 sec.
-
Make sure the visualization type is either Auto or KPI, then save the query.
14 Comments
Is there a way to have the percentage turn red/green to visually communicate the percentage of calls is over or under what it should be? I'm trying to phrase the question in the contest of this recipe. However, I'm ultimately trying to create a KPI that shows how far over or under this month's ticket volume versus last month's ticket volume. Do you have any resources or ideas on how I can accomplish this? Thank you.
Hi Eric, here's a link to where I gave you an answer to this in another article. https://support.zendesk.com/hc/en-us/articles/360022184374/comments/360004007613
Hi Zendesk, this is a great recipe, delighted to have found it, thank you. Do you have a similar recipe for calculating the abandoned rate for chats please?
Hi Joan, we're glad you like the recipe!
We don't have a specific recipe for abandoned chats, but I believe if you open a new query using the Chat: Engagement dataset you can add the metric % Chat drop rate to the Metrics panel and get the information you need (you can add a date filter to restrict the results to a time period you specify).
I hope this helps!
If I follow the advice in step 5 (You could also use "Call raw wait time" instead of "Call raw talk time" to categorize the calls based on the time that end-users spend waiting during a call.) will this calculate the number of calls abandoned within 20 seconds of entering the queue, before speaking to any agent? How is this different from what the metric is supposed to be measuring? Because using raw talk time, I got 0 for everything - 20 seconds, 30 seconds, 60 seconds, 120...
Hi Grace, I've reviewed the article and agree that step 5 is probably a bit misleading as you'd also need to change the Call completion status also (or even remove that condition). I think it's distracting to have this in the article and I've removed it.
The definition of call wait time is the average time in minutes that end-users spend waiting during a call.
If you're still not getting the numbers you are expecting, we'll open a ticket so we can look into this case further. Thanks!
Rob Stack Just wanted to ask about the "raw talk time" and "raw wait time". I tried using the formula "raw talk time" but the result doesn't seem to give the correct output. I noticed an earlier comment regarding step 5 being removed. When I use "raw wait time" I actually get a result that reflects abandoned within 20 seconds. Can you confirm that raw wait time is the length of time a customer waits before abandoning?
Rob I'd also like to know what the update is here from step 5. When copying and pasting what you have above, and even with updating to wait time, I come up with 0s, which I know is incorrect.
Hi all, thanks for the comments. I'm going to investigate this and will get back to you as soon as I can. Thanks!
Hi David Wa and Sarah S
We tested this recipe and managed to get the correct results so I've opened tickets for both of you to try and get to the bottom of what's wrong. I've added myself to the tickets and will update the recipe as necessary. You should hear from someone soon.
I've tried this recipe and when I set it to 'KPI' or 'Auto' I do not get the result metric calculation to show up but instead see the value for the first metric. I know I've done something similar in the past for other metrics and actually SEE those queries showing the result metric calculation (i.e. abandon %) but when I compare all of my settings I can't find a difference.
Any clue what I might be doing wrong?
Hi Jay,
In the Result calculated menu, have you made sure that the "clear used metrics" box is selected (Step 11 above)?
ahhhh...I thought I had but maybe I didn't select "apply" after I made that change. In any case, it works now. Thanks!
I copy/pasted the formula from step 5 but I'm getting 0 results. Is there something I'm missing? I don't have any other filters or anything.
Please sign in to leave a comment.