Myndbend Process Manager adds in specific metadata to each parent and child ticket. We can use that metadata in order to generate useful reports about parent-child tickets in Zendesk Explore.
Before we start, let's go over the data that we will be using in order to determine is ticket a parent or a child ticket.
Each child ticket has its external_id set to child_of_<PARENT_ID>. Each parent ticket has a tag, mpm_child_tickets_open that indicates this ticket has child tickets that are open. Knowing this will come in useful later on when we start creating calculated attributes and metrics in Zendesk Explore.
Once you navigate to Zendesk Explore, we'll need to create two queries for this example. Start by navigating to Queries > Add new query. All of our queries will use the same datasets:
The first query will be a list of child tickets grouped by their parent ticket ID.
We'll need to create a calculated metric. You can do that by clicking on Calculations > Standard Calculated Metric:
To populate this metric, fill in:
Name: Myndbend Child Tickets
Formula:
IF (CONTAINS([Ticket external ID], "child_of")) THEN [Ticket ID] ENDIF
This will filter all of the tickets, and return only tickets that have child_of in their external ID - meaning, we'll get a list of all child tickets.
Save that metric. Add it to the metrics part of your query, and make sure you select it as COUNT:
Next, we'll need to create a couple of Standard Calculated attributes:
We decided to show two attributes:
- Parent ticket ID - in order to group tickets by that
- Child ticket ID + Subject - to get more info about the ticket
Parent ID Attribute
Name: Myndbend Parent Ticket ID
Formula:
IF (CONTAINS([Ticket external ID],"child_of"))
THEN "#" + SUBSTR([Ticket external ID],9, LENGTH([Ticket external ID]))
ENDIF
Child ticket ID + Subject Attribute
Name: Myndbend Child Ticket Info
Formula:
IF (CONTAINS([Ticket external ID],"child_of"))
THEN "#"+ [Ticket ID] + " - " + [Ticket subject]
ENDIF
Now once we have those as well, add them to the Rows section (make sure that Parent ID Attribute comes first). You'll now get a list of your child tickets grouped by parent ticket ID:
In our second query, we're going to get more data about the parent ticket (besides ID), so that we could merge it in later on.
Start with a new query, and add new calculated metric:
Name: Myndbend Parent Tickets
Formula:
IF (CONTAINS([Ticket tags], "mpm_child_tickets_open")) THEN [Ticket ID] ENDIF
Also, create a new calculated attribute - You can create them for any data you need, but in this example, we'll just create for parent ticket ID + Subject:
Name: Myndbend Parent Ticket Info
Formula:
IF (CONTAINS([Ticket tags],"mpm_child_tickets_open"))
THEN "#" + [Ticket ID] + " " + [Ticket subject]
ENDIF
Now, use Myndbend Parent Tickets in metrics section (Don't forget to change to COUNT), and Myndbend Parent Ticket Info as a row. You'll get a list of Myndbend Parent Tickets.
Now, once we have these two queries, we'll put them together in a new dashboard, and export that data to CSV:
Once we have that data in CSV, you can follow this tutorial on how to merge it into a single table:
The end result will be something like this: