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 2: Number of tickets containing events, broken down by the assigned group.
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:
Select the "Support" product, then select the "Tickets" dataset type and at the bottom click on "New Query" like this:
First up, give your report a name like "How many hours are agents spending on events each month".
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:
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:
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:
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:
Once added, ensure "Calendar event date - Year" is at the top by dragging it if needed.
Now click on "Calendar event date - Year" and select how far back in time you want to see in the report like this:
Then in the "Rows" section click on "Add", search for "assignee" and click on "Assignee name" like this:
Lastly, at the top right, click on the report icon to change the chart type to column like this:
This completes the chart (make sure you click "Save"):
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:
Select the "Support" product, then select the "Tickets" dataset type and at the bottom click on "New Query" like this:
First up, give your report a name like "How many events are each group doing?".
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:
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:
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:
Then set the default to "D_COUNT" and click save like this:
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:
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:
Once added, ensure "Calendar event date - Year" is at the top by dragging it if needed.
Now click on "Calendar event date - Year" and select how far back in time you want to see in the report like this:
Then in the "Rows" section click on "Add", search for "group" and click on "Ticket group" like this:
This completes the report. Make sure you click "Save" at the top right.
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