Explore recipe: How to capture the date when a macro is applied to a ticket

What's my plan?

All Suites | Professional, Enterprise, or Enterprise Plus

Support with | Explore Professional or Enterprise

In this Explore recipe, you'll discover how to generate a report that tracks the instances when a macro is utilized by an agent.

There is no built-in method to report on macro activity, such as when a macro is applied to a ticket. The system does not track macro usage by timestamp or support reporting through tags added by macros.

To track macro usage, you can create a custom checkbox ticket field and add it to the macro. Update the macro to check the box. This allows you to use the field change to identify when the macro was applied.

For more information see the articles:

Creating custom ticket fields

How can I use macros to add tags on a ticket

Below is an example macro with the checkbox added.

Note: (Enterprise only) If you have multiple ticket forms, edit the ticket forms where you want the field to appear, drag the field from the right onto the ticket form, then click Save. Repeat for additional ticket forms.

Skill level: Average

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

In Explore, click the reports ( ) icon.

In the Reports library, click New report.

On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.

Next, you'll create a standard calculated attribute that records changes to your custom checkbox. From the Calculations menu ( ), click Standard calculated attribute.

On the Standard calculated attribute page, enter a name for your attribute like Macro Usage Date, then enter or paste the following formula:

IF ([Changes - Field name]="Macro used")
AND (([Changes - Previous value]="0")
OR ([Changes - Previous value]=NULL))
AND ([Changes - New value]="1")
THEN [Update - Date]
ENDIF

Replace "Macro used" in the first line of the formula with the name of your custom checkbox.

Optionally, you can use the [Update - Timestamp] instead to capture the timestamp.

When you're finished, click Save.

Create another standard calculated attribute to list the name of the macros. In the Calculations menu ( ), click Standard calculated attribute.

On the Standard calculated attribute page, Under Functions, click Add.

Search the SWITCH function and click +.

Give the attribute name like Macro name.

Select the attribute containing the values you want see in the list or the attribute name. In this case we use the Ticket tags attribute.

In CASE _value1, enter the tag of the macro.

In _return_value, enter the name of the macro.

Enter your other cases for more macro you used, you will need to manually type out additional CASE expressions. The formula for this example looks like below:

SWITCH ([Ticket tags]) {
CASE "greetings_macro": "Greeting"
CASE "closing_ticket": "Closing Ticket"
CASE "closing_tickets": "Closing Ageing Tickets"
}

When you're finished, click Save.

To create the report

In the Metrics panel, click Add.

From the list of metrics, choose Updates > Agent updates, then click Apply. Ensure that the metric aggregator is set to D_COUNT.

In the Filters panel, add the Updater role attribute and configure it to show Agents and Admins.

In the Rows panel, click Add.

From the list of attributes, choose Calculated attributes > Macro Usage Date, click Excluded and select NULL then Apply.

Go back to Rows panel, click Add. From the list of attributes, choose Calculated attributes > Macro name then Apply.

You can also add Ticket ID in Rows. From the list of attributes, search Ticket ID > Apply.

The report should match the following: