In this Explore recipe, you'll learn how to create a query that reports the percentage of chats with a first reply time of under 30 seconds.
What you'll need
Skill level: Moderate
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- A subscription to Zendesk Chat
Creating the query
To create the query
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Chat > Engagement > Chat: Engagement, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Chats > Chats, then click Apply.
- Now, you'll create a custom Standard calculated metric to calculate the number of chats that were first responded to in under 30 seconds. From the Calculations (
) menu, click Standard calculated metric. - On the Standard calculated metric page, enter or paste the the metric below into the Formula field:
IF (VALUE(Engagement first reply time (sec)) <=30)
THEN [Chat ID]
ENDIF - Name the metric, Chats first replied in under 30 secs, then click Save. It will look like the screenshot below:
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.
- Add the new metric from the Calculated metrics list. Ensure the metric aggregator is set to D_COUNT. For more information, see Choosing metric aggregators.
- Now, add the percentage detail using a Result metric calculation. From the Result manipulation (
) menu, click Result metric calculation. - On the Result metric calculation page, click Add a new metric.
- In the name field, enter the name of your metric.
- Enter the formula below. You must select your metrics and functions from the drop-down next to Insert.
D_COUNT(Chats first replied in under 30 secs)/D_COUNT(Chats)
- When you are finished, click + Add to add the calculation.
- The final step is to edit the query to show the new calculation in a percentage format. From the Chart configuration (
) menu, click Display format. - On the Display format page, change the calculation metric to display in %.
The query is now complete. See the screenshot below for an example.
You can use date attributes in this report if you would like to filter the results by a specific date range. Check out Editing dates and date ranges for more information.
12 Comments
This is great! Thanks for sharing. Is there a way to only show the final metric (% Chats replied to under 30 seconds) in a visualisation. I tried playing around with it but can't find how to hide the other data and have the calculated metric still work.
Thanks!
Fiona
Hi Fiona,
Thanks for the question, glad you liked the recipe. :)
If you would like to show the percentage value on its own, you can create another Standard calculated metric rather than using the Result metric calculation option.
1. Open a new query in the Chat dataset, and then click Calculations and then Standard calculated metric. Copy the metric below and paste it into the metric editor assuming you named your metrics in the same way I did above. It will look like the screenshot below:
2. Then add the new % metric from Calculated metrics in the Metrics panel.
3. The last step is to click Chart Configuration, then Display format. Set the new metric to %.
Then your query should show only the percentage of chats first replied to in under 30 seconds.
Thank you, Jessica! This is perfect :)
Hi there Fiona, I also want to show the percentage value on its own, I followed the above recipe but I get the following error just after I add the Calculated Metric, any ideas where I'm going wrong?
Thanks a million.
Joan
Hi Joan
I'm only a beginner myself, but did you call the metrics the exact same thing? I made that mistake once - you have to make sure it's the same name. If it's not that, I'm not sure - maybe someone else can chip in?
Thanks!
Sorry Fiona, I meant to address that message to Jessica not to you! Apologies.
Jessica can you advise please?
Hey Joan,
I'm going to create a ticket on your behalf so our Customer Advocacy team can take a look at the formula you built. You'll receive an email shortly stating your ticket has been created.
Feel free to attach any additional information you have available to that email.
Cheers!
Hello -
What would be the appropriate substitution(s) if we wanted to do this for calls?
IF (VALUE(Call wait time (sec)) <=30)
THEN [Call ID]
ENDIF
Using the Talk Dataset, obviously?
Brandon
Hi Brandon,
We actually already have a similar recipe for you here: Percentage of calls answered in 30 seconds
Let me know if you have any problems modifying that to suit your needs.
Great, Thanks!!
Hey Bret,
I get the same error message as Joan. Any suggestions on why the recipe did not work?
Thanks
Hello Logan Bates,
I've gone ahead and created a ticket on your behalf so our Advocacy team can look into this for you. Expect an email shortly, notifying you that this ticket has been created on your behalf.
Best regards.
Please sign in to leave a comment.