Reporting recipes for Calendar in Zendesk Explore

The calendar app pushes vital information into Zendesk ticket fields that allow you to report on their activity in Zendesk Explore. A couple of fields that particularly come in handy when building reports are "calendar event duration" and "calendar event date". The data stored in these two fields allows us to calculate how much time has been spent on events and slice the reports based on when the events took place and other things, like the ticket assignee, group, organisation etc. 

The two recipes well cover in this article are: 

Recipe 1: Total calendar event duration (in hours), broken down by the month the event took place and the ticket assignee.

Zendesk_Explore.png

Recipe 2: Number of tickets containing events, broken down by the assigned group. 

Zendesk_Explore.png

 

Recipe 1 

Total calendar event duration (in hours), broken down by the month the event took place and the ticket assignee.

In Explore you'll first need to head to the queries section and click on "New Query" at the top right like this: 

Zendesk_Explore.png

Select the "Support" product, then select the "Tickets" dataset type and at the bottom click on "New Query" like this:

Zendesk_Explore.png

First up, give your report a name like "How many hours are agents spending on events each month".

Zendesk_Explore.png

Now we need to build a calculated metric. To do this click on the calculator icon on the right of the screen and select "Standard calculated metrics" like this:

Zendesk_Explore.png

Now give your metric a name like "Duration of all calendar events" and in the formula copy and paste this code:

SUM(Calendar event duration (in minutes))/60

 Your metric should now look like this: 

Zendesk_Explore.png

Click "Save". 

Now in the "Metrics" section on the left, click "Add", go into calculated metrics and select the metric you just created like this: 

Zendesk_Explore.png

Next, in the "columns" section, click add, search for "calendar event date" and add the items "Calendar event date - Month" and "Calendar event date - Year" like this:

Zendesk_Explore.png

Once added, ensure "Calendar event date - Year" is at the top by dragging it if needed.  

Zendesk_Explore.png

Now click on "Calendar event date - Year" and select how far back in time you want to see in the report like this: 

Zendesk_Explore.png

Then in the "Rows" section click on "Add", search for "assignee" and click on "Assignee name" like this:

Zendesk_Explore.png

Lastly, at the top right, click on the report icon to change the chart type to column like this: 

Zendesk_Explore.png

This completes the chart (make sure you click "Save"): 

Zendesk_Explore.png

Recipe 2 

The number of tickets containing events, broken down by assigned group. 

In Explore you'll first need to head to the queries section and click on "New Query" at the top right like this: 

Zendesk_Explore.png

Select the "Support" product, then select the "Tickets" dataset type and at the bottom click on "New Query" like this:

Zendesk_Explore.png

First up, give your report a name like "How many events are each group doing?".

Zendesk_Explore.png

Now we need to build a calculated metric. To do this click on the calculator icon on the right of the screen and select "Standard calculated metrics" like this:

Zendesk_Explore.png

Now give your metric a name like "Number of tickets with events" and in the formula copy and paste this code:

IF ([Calendar event time] = NULL)
THEN
0
ELSE
[Ticket ID]
ENDIF

 Your metric should now look like this: 

Zendesk_Explore.png

Click "Save". 

For this metric to successfully work, rather than using "SUM" to calculate the value, we need to use "D_COUNT" to ensure we only count each ticket once. To make sure this is always the case whenever this metric is used we need to set the default aggregator. To do this, in "Metrics" on the left of the screen, click "Add" (1), then under "Calculated metrics" (2) select the one you just created (3). Then in the metrics screen at the top right you'll now be able to click on "Options" (4) and then on "Edit aggregators" (5) like this: 

Screen_Shot_2020-06-09_at_1_58_46_pm.png

Then set the default to "D_COUNT" and click save like this:

Zendesk_Explore.png

Now that the default has been set, add the metric to the query by clicking "Add" under metrics, then under "Calculated metrics" select it from the list and click "Save" at the bottom like this: 

Zendesk_Explore.png

Next, in the "columns" section, click "Add", search for "calendar event date" and add the items "Calendar event date - Month" and "Calendar event date - Year" like this:

Zendesk_Explore.png

Once added, ensure "Calendar event date - Year" is at the top by dragging it if needed.  

Zendesk_Explore.png

Now click on "Calendar event date - Year" and select how far back in time you want to see in the report like this: 

Zendesk_Explore.png

Then in the "Rows" section click on "Add", search for "group" and click on "Ticket group" like this:

Zendesk_Explore.png

This completes the report. Make sure you click "Save" at the top right. 

Zendesk_Explore.png

If you'd like to make any alterations to the chart type, like making it a bar chart or an area chart, you can do this by clicking the chart icon on the right.

If you'd like to change any of the chart details, like labelling of data points or how the x & y axis labelling appears, you can do this by clicking the brush icon on the right. 

If you have any questions or other reports that you need a recipe for email us at support@sweethawk.co

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk