How can I show the timestamp of a reopened ticket in Explore?

Question

I have a query in Zendesk Explore that uses the Reopens metric. How can I know the date the ticket was reopened and not the date the ticket was created or solved?

Answer

Explore doesn't offer you a default attribute that gives you the exact timestamp a ticket was reopened. To see the time and date of a reopen, create a custom attribute.

To create this attribute

1. In an Explore query from the Support: Ticket updates dataset, and the metric Reopens added in the query, open the calculations menu ().
2. On the Calculations page, choose Standard calculated attribute.
3. A new blank standard calculated attribute opens. Enter a name to the attribute, such as Timestamp - ticket reopens.
4. Copy and paste the code below into the Formula section.
   

   IF ([Changes - Field name] = "status"
   
       AND [Changes - Previous value] ="solved"
   
       AND [Changes - New value] !="solved"
   
       AND [Changes - New value] !="closed" )
   
       THEN [Update - Timestamp] ENDIF

5. Click Save.
6. In the Rows panel, click Add.
7. Expand Calculated attributes, then click your standard calculated attribute Timestamp - ticket reopens.

The query will display a list of reopened tickets with the respective timestamp of when that happened.

Use the COUNT (Reopens) metric and not the Tickets reopened metric. The latter only counts the individual ticket IDs, which means that if the same ticket was reopened multiple times, the metric only counts as 1.

Note: The formula editor in Explore is language-specific and does not recognize English formulas when the user profile is set to a non-English language. If you use custom code in Explore, reference the English article to How can I convert Explore formulas from English into another language?