With our Calendar app, any data from calendar events can be populated directly into Zendesk tickets. This data can then be used to create reports in Zendesk Explore, providing valuable insights into various aspects of your calendar events.
To start, please ensure the relevant calendar information is being stored on your tickets using the calendar app workflows.
The two recipes we'll 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.
This recipe requires at least the following custom ticket fields created and populated with placeholders using the calendar app workflows. The ticket field names will match what we create in Explore, but you are free to use your own naming.
| Ticket Field Name | Placeholder |
| Calendar event duration (in minutes) | {{calendar_event.duration_in_minutes}} |
| Calendar event date | {{calendar_event.start_at | in_time_zone: calendar_event.time_zone | date: "%Y-%m-%d"}} |
Once you have...
a) Created the ticket fields above,
b) Have set the corresponding data to be injected into the ticket fields using workflows,
c) Created a test ticket where the data is injected into those ticket fields, and
d) Waited till the data appears in Explore (this could be 1 hour or 24 hours depending on your Zendesk plan)
...you will then be able to head over to Explore to build this report.
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.
This recipe requires at least the following custom ticket fields created and populated with placeholders using the calendar app workflows. The ticket field names will match what we create in Explore, but you are free to use your own naming.
| Ticket Field Name | Placeholder |
| Calendar event time | {{calendar_event.start_at | in_time_zone: calendar_event.time_zone | date: "%H:%M"}} |
| Calendar event date | {{calendar_event.start_at | in_time_zone: calendar_event.time_zone | date: "%Y-%m-%d"}} |
Once you have...
a) Created the ticket fields above,
b) Have set the corresponding data to be injected into the ticket fields using workflows,
c) Created a test ticket where the data is injected into those ticket fields, and
d) Waited till the data appears in Explore (this could be 1 hour or 24 hours depending on your Zendesk plan)
...you will then be able to head over to Explore to build this report.
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.com