A heatmap is a great way to see activity at different times in a compact and easy to read way.
What you'll need
Skill level: Easy
Time Required: 15 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
You can click the preview below to watch a video of this recipe, or continue to the walkthrough.
- In Zendesk Explore, click the queries (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets . Make sure to select both aggregators, COUNT and D_COUNT, then click Apply.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket created > Ticket created - Day of week, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Time - Ticket created > Ticket created - Hour, then click Apply.
- Using the visualization selector (
), change the chart to a Table.
- Click the Ticket created - Hour attribute and Edit date ranges if you want to filter the chart to show results for a specific period, for example the year to date.
- Click the D_Count(Tickets) metric. Change the treatment from Normal to Color, then click Apply.
- In the Result manipulation menu (
), click Result path calculation. - On the Result path calculation page under D_COUNT(Tickets), choose the pattern % of Total, and the path On whole result.
- In the Chart configuration menu (
), click Colors. - On the Colors page, choose Color encoding > Style 1.
- Click the left paintdrop and select white.
- Click the right paintdrop and select the color you want to represent heavy traffic. You can move the blue dot on the color range to show more contrast between the bulk of your results.
21 Comments
Hi Philippa,
Just curious, is there a way to hide the the Count of Tickets values by making the font color the same as the heatmap colors?
Thanks
Made a very short just under 4 minute demo to illustrate a similar heatmap recipe for those that would like a video demo.
Zendesk Explore Heatmap recipe demo
How can I show the hours in a format that our agents can more quickly understand? Thanks!
Hello Beth!
From the Time - Ticket Created section, add Ticket Created - Hour and click Apply. For more information, please refer to https://explore.zendesk.com/hc/en-us/articles/360002058067-Explore-recipe-Tickets-by-time-of-day-created.
Hi Beth,
It looks like Shera replied to your post here: https://explore.zendesk.com/hc/en-us/articles/360002062067?page=1#comment_360001556153
Can you confirm you're able to view this?
Let me know!
Shera and Brett,
What I meant was that the hours (0-23) are not in a format that is suitable for our agents to quickly understand. I ended up creating a renamed set and an ordered set to display the times in the conventional 12 hour am/pm format.
Thank you
Thanks for sharing Beth :)
Let us know if you have any other questions!
Hi there
My results aren't sorted by actual time. As a newbie, how can I order them by time in the left-hand column, and change it into time (10am or something identifiable).
Thanks!
Fiona
Hi Fiona, I'm a little surprised that your Ticket created - Hour metric isn't automatically sorting. However, you should be able to go to the result manipulation menu on the right hand side and select Sort.
One way of getting the time to show in a more friendly manner is to create a renamed set. You do this from the Calculations menu on the right hand side and select Renamed set.
Give the set a name like "Custom Ticket created - hour attribute"
Under "Computed from", select the Ticket Created - Hour attribute, You'll see in the menu that you can type a new value for each attribute value (for example, "2" could be changed to "2am")
Save when you are done, then use your new attribute instead of the "Ticket created - Hour" attribute.
That's a bit of a lightning run through, but if you want to read more, take a look at https://support.zendesk.com/hc/en-us/articles/360022184334-Organizing-values-by-groups-and-sets
I hope this helps!
Edit: I made a custom ordered set with the times I renamed. They are working for one of my heatmaps but not for the other. I tried to also sort that one by A-Z and it still puts 10am first, then 9am, 11am, 1pm, 2pm... If I choose Z-A though, it shows correctly but backwards.
Thanks, Rob. I have made the renamed set as you suggested. However, I tried all the sort options and none of them make a difference to the table. I'm not sure what I'm doing wrong...
Thanks for the update Fiona. The ordered set was indeed the right way to go, but I'm unsure of why you're getting the behavior on one of your tables. If you can't find a way around it, I'd suggest you consider opening a ticket so someone can investigate this with you. Thanks!
Thanks, Rob, for your help. I'll make a ticket as I'm not sure what I'm missing. Thanks!
Fiona
Is there a way to customise RAG these heatmaps with values? Eg like insights
https://support.zendesk.com/hc/en-us/articles/206381328-Insights-recipe-Ticket-creation-heatmap
Cheers
Beno
Hey Beno,
I'm a little confused on what values you're trying to customize. Can you provide some additional information on what you're trying to accomplish?
Thanks in advance!
Hey Brett,
No worries,
I'm after the ability to use these types of rules, where you can set different colour values per % value. and you can choose your hex colour values etc.
E.g if 95% make it Green if 10% make it Red
Below is an example of the gradient scheme that insight would allow you to use.
[>=.011][backgroundcolor=660011][color=660011]#%; [>=.01][backgroundcolor=99001A][color=99001A]#%; [>=.009][backgroundcolor=CC0022][color=CC0022]#%; [>=.008][backgroundcolor=FF002B][color=FF002B]#%; [>=.007][backgroundcolor=FF3355][color=FF3355]#%; [>=.006][backgroundcolor=FF667F][color=FF667F]#%; [>=.005][backgroundcolor=FF99AA][color=FF99AA]#%; [>=.004][backgroundcolor=FFCCD5][color=FFCCD5]#%; [>=.003][backgroundcolor=F7D4DA][color=F7D4DA]#%; [>=.002][backgroundcolor=F0DBDF][color=F0DBDF]#%; [<.002][backgroundcolor=ffffff][white]0; [=Null][backgroundcolor=ffffff][white]0
Hey Beno,
I contacted our Explore experts to see if this same functionality is possible with Explore. I'll keep you post on what I find out!
Hey Beno,
It looks like you can get similar functionality by using the chart configuration option mentioned here: Customizing your chart
Hope this points you in the right direction!
When there are 0 tickets for a particular hour and day, the row for that hour isn't listed.
Is there a way to force this to always show all hours even if there are none for that hour and day?
Hi Travis Tubbs,
Thank you for reaching out!
You can edit your report so that it displays NULL or 0 values using the Result metric calculation to replace these empty cells with 0 values.
For more information, please see this article: How can I include 0 values in Explore reports
Please let me know if this answers your question!
Adding to Travis's question, is there any way to force the column to show even if there were no tickets created that day? For example, if there were no tickets on Saturday, the column doesn't appear!
Hi Naghmeh Anvari,
I'm afraid Explore handles NULL and 0 values quite differently. If a column containing values also contains 0 values, you can configure your query such that it includes 0 values on Explore reports.
However, if an entire column contains NULL values, the column will always be hidden in Explore. This topic was also discussed in an Ask us Anything about Explore Recipes.
I will be sure to pass along your feedback internally and you can also post on our Feedback on Explore Community page!
Please sign in to leave a comment.