Explore recipe: Reporting on tickets with an internal comment before a public comment

What's my plan?

In this Explore recipe, you'll learn how to use the ticket updates dataset to report on tickets that had had an internal agent comment update on the ticket before the first agent public comment.

This will involve creating two custom calculated attributes to report on the first timestamp of the internal and public comment events using our DATE_FIRST_FIX function. From there, you'll create three custom calculated metrics to convert the timestamps of the calculated attributes to Unix epoch values, then calculate whether the internal comment timestamp occurred before the public comment timestamp.

What you'll need

Skill level: Advanced

Time required: 20 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving users access to Explore)
• Ticket data in Zendesk Support 

Creating the report

Use the following steps to create this report in Explore.

To create the custom calculated attributes

1. In Explore, click the reports () icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates history, then click Start report.
4. In the Calculations menu (), click Standard calculated attribute.
5. On the Standard calculated attribute page, give your attribute a name like First internal comment - Timestamp.
6. In the Formula field, enter or paste the following formula:

   IF ([Comment present]=TRUE
   AND [Comment public]=FALSE
   AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])=[Update - Timestamp])
   THEN [Update - Timestamp] ENDIF

7. When you're finished, click Save.
8. In the Calculations menu (), click Standard calculated attribute.
9. On the Standard calculated attribute page, give your attribute a name like First public agent comment - Timestamp.
10. In the Formula field, enter or paste the following formula:

    IF ([Comment present]=TRUE
    AND [Comment public]=TRUE
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])=[Update - Timestamp])
    THEN [Update - Timestamp] ENDIF

11. When you're finished, click Save.

To create the custom calculated metrics

1. In the Calculations menu (), click Standard calculated metric.
2. On the Standard calculated metric page, give your attribute a name like First internal comment - Timestamp (Converted).
3. In the Formula field, enter or paste the following formula:

   DATE_TO_TIMESTAMP([First internal comment - Timestamp])

4. When you're finished, click Save.
5. In the Calculations menu (), click Standard calculated metric.
6. On the Standard calculated metric page, give your attribute a name like First public agent comment - Timestamp (Converted).
7. In the Formula field, enter or paste the following formula:

   DATE_TO_TIMESTAMP([First public agent comment - Timestamp])

8. When you're finished, click Save.
9. In the Calculations menu (), click Standard calculated metric.
10. On the Standard calculated metric page, give your attribute a name like # Tickets with first internal comment before public comment.
11. In the Formula field, enter or paste the following formula:

    IF ( (SUM(First internal comment - Timestamp (Converted))) < (SUM(First public agent comment - Timestamp (Converted))) )
    THEN [Ticket ID]
    ENDIF

12. When you're finished, click Save.

To create the report

1. In the Metrics panel, click Add.
2. From the list of metrics, choose Calculated metrics > # Tickets with first internal comment before public comment, then click Apply.
3. Click the metric you just added and select the D_COUNT aggregator.
4. In the Filters panel, add the Updater role attribute and configure it to show Agents and Admins.
5. In the Rows panel, click Add.
6. From the list of attributes, choose Time - Ticket created > Ticket Created - Month, then click Apply. The report should match the following:
   
   Optionally, you can use other Time - Ticket Created attributes in the report to suit your specific reporting needs.