Explore recipe: Creating alternate SLA metrics

What's my plan?

There are some rare cases where SLAs have accurate metric data but are saved in the wrong status. For example, this can happen when an existing schedule is changed. Any tickets that were active before the change may end up with split data. The SLA status and badge still use the original schedule, while the duration metrics use the updated schedule.

Use this recipe to create alternate SLA metrics. These metrics use the durations instead of the status to determine when tickets are Achieved or Breached.

Most tickets will have accurate SLA data with accurate statuses with the native metrics working. These alternate metrics should be necessary only in rare cases. Using these alternates will ensure you get accurate reports if the SLAs are saved in the wrong status.

This article contains the following topics:

• What you'll need
• Creating the calculated metric and attribute
• Using the attribute to create alternate SLA metrics
• Comparing native and alternate SLA metrics

What you'll need

Skill level: Advanced

Time required: 30-40 minutes

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

Creating the calculated metric and attribute

This section starts with a metric that finds the difference between the SLA target and the amount of time it took to fulfill. That difference is then used in a calculated attribute to label achievements and breaches.

Standard calculated metric

1. In Explore, click the reports () icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, select Support > Support - SLAs , then click Start report. The report builder opens. 
4. Open the Calculations menu (), then click Standard calculated metric.
5. Name the new metric Alternate: SLA breach time.
6. In the Formula field, enter the following:

   VALUE(SLA metric completion time (min)) - VALUE(SLA metric target time (min))

7. Click Save.

SLA metric completion time (min) is the amount of time an SLA metric was active on a ticket. SLA metric target time (min) is the SLA target. For more information, see Metrics and attributes for Zendesk Support.

Standard calculated attribute

1. In the report builder, open the Calculations menu (), then click Standard calculated attribute.
2. Name the new attribute Alternate: SLA target status.
3. In the Formula field, enter the following:

   IF VALUE(SLA metric completion time (min)) 
   - VALUE(SLA metric target time (min))>=0 
   THEN "Breached" ELIF VALUE(SLA metric completion time (min)) 
   - VALUE(SLA metric target time (min))<0 
   THEN "Achieved" ELSE "Unknown" 
   ENDIF

4. Click Save.

Below is a simple example to demonstrate how the metric works.

An SLA policy has a target of 30 minutes. Ticket A was fulfilled after 60 minutes. Ticket B was fulfilled after 15 minutes. 

• The SLA metric target time (min) value is 30 for both tickets. 
• Ticket A has an SLA metric completion time (min) value of 60. The metric will calculate 60-30 and get a result of 30. That is greater than 0, so the attribute will return Breached.
• Ticket B has an SLA metric completion time (min) value of 15. The metric will calculate 15-30 and get a result of -15. That is less than 0, so the attribute will return Achieved.

Note: The metric will work only for tickets where the SLA target is fulfilled. The SLA can't accurately measure active targets. That's why the attribute uses Unknown instead of Active or Active (Breached).

Using the attribute to create alternate SLA metrics

The calculated attribute can be used wherever the native SLA target status attribute is used. This recipe will cover counts of achieved and breached tickets, along with a % achievement metric.

Achieved tickets metric

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 - SLAs dataset, then select Start report. The report builder opens. 
4. Open the Calculations menu (), then click Standard calculated metric.
5. Name the new metric Alternate: Achieved SLA tickets. 
6. In the Formula field, enter the following:

   IF [Alternate: SLA target status] = "Achieved"
   THEN [Ticket ID]
   ENDIF

7. Click Save. 

Breached tickets metric

1. In the report builder, open the Calculations menu (), then click Standard calculated metric.
2. Name the new metric Alternate: Breached SLA tickets. 
3. In the Formula field, enter the following:

   IF [Alternate: SLA target status] = "Breached"
   THEN [Ticket ID]
   ENDIF

4. Click Save. 

% Achieved metric

1. In the report builder, open the Calculations menu (), then click Standard calculated metric.
2. Name the new metric Alternate: % Achieved SLA. 
3. In the Formula field, enter the following:

   COUNT(Alternate: Achieved SLA tickets)/
   (COUNT(Alternate: Achieved SLA tickets)+COUNT(Alternate: Breached SLA tickets))

   • The line break is not necessary for the calculation. It just makes the metric easier to read.
4. In the Standard calculated metric window, check the Compute separately box.
5. Click Save. 

Optional: After the metric is saved, change the default display format to a percentage.

1. Under Metrics, click Add and locate the Alternate: % Achieved SLA metric.
2. Click the pencil icon to open the metric for editing.
3. In the Standard calculated metric window, click the Options arrow in the top right and select Edit display format. 
4. In the Display format window, select %.
5. Click the arrow on the top left to return to the metric.
6. Click Save.

Comparing native and alternate SLA metrics

These alternate SLA metrics should work just like the native SLA metrics, with two main exceptions:

1. The alternate metric won't count active tickets.
2. If tickets are saved in the wrong status, the alternate metrics will still return accurate results.

Below are two sample reports showing the differences between the native and alternate SLA metrics. The reports contain 8 tickets.

• 6 fulfilled tickets achieved their SLAs.
  • 2 of the 6 hit edge cases where they saved the wrong status.
• 1 fulfilled ticket breached its SLA.
• 1 active ticket is past its SLA target.

In blue, the Alternate metrics count all 6 achievements and do not include the active ticket. In red, the native metrics count the active ticket and both edge case tickets as breaches.

Below shows the same 8 tickets with their native and alternate attribute values. 

This shows the two edge case tickets are Achieved in the Alternate: SLA target status row and the active ticket is Unknown. Those tickets are all counted in the Breached column for the native SLA target status attribute.