Explore recipe: Creating run rate calculations

A run rate calculation helps you to determine how far towards a target you are and estimate your projected results on the target date.

In this Explore recipe, you'll work through an example of a run rate calculation with the following requirements:

• Create a chart showing the running total of tickets solved each day with a trend line showing the target number of tickets to be solved.
• Create Key Performance Indicators (KPIs) showing:
  • The number of days elapsed so far this month
  • The total number of days this month
  • The current run rate
  • The forecasted monthly total of solved tickets
• Create and arrange a dashboard that shows these results
• Optionally, create a KPI that shows the compares the forecast monthly total to your target number of solved tickets

This article contains the following topics:

• What you'll need
• Getting started
• Report 1 - Tickets solved month to date
• Report 2 - The run rate calculation
• Report 3 (optional) - Compare forecasted monthly total to target
• Arranging your dashboard

What you'll need

Skill level: Advanced

Time Required: 45 minutes

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

Getting started

To create a new report

1. In Zendesk Explore, click the reports ( ) icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens with a blank report.

Report 1 - Tickets solved month to date

First, create an area chart that shows a running total of tickets solved over the last six months.

To create the report

1. In the Metrics panel, click Add.
2. From the list of metrics, choose Tickets > Solved tickets, and then click Apply.
3. In the Rows panel, click Add.
4. From the list of attributes, choose the following two attributes, then click Apply: 
   • Time - Ticket solved > Ticket solved - Year
   • Time - Ticket solved > Ticket Solved - Month
5. Click one of the attributes you just added (it doesn't matter which). On the properties page for the attribute, click Edit date ranges.
6. On the Date range page, click Advanced, configure a date range of 6 months in the past to Today, then click Apply.
7. In the Columns panel, click Add.
8. From the list of attributes, choose Time - Ticket solved > Ticket solved - Day of month, and then click Apply.
9. From the Visualization type menu (), choose Area.
10. From the Chart configuration menu (), choose Row Selector.
11. On the Row Selector page, choose the following options:
    • Type: Simple list
    • Selected position: Last value
    • Clear the Display Row Selector checkbox
12. From the Result manipulation menu (). choose Result path calculation.
13. On the Result path calculation page, choose the following options:
    • Pattern: Running total
    • Path: On columns
    • Aggregation: SUM

    

14. When you are finished, click Apply.
15. Next, you'll add a trend line on the chart showing the target number of solved tickets for the month. From the Chart configuration menu (), choose Trend line.
16. On the Trend line page, select Constant, then enter the target number of tickets.

    

17. Save the report and add it to a new dashboard.

Report 2 - The run rate calculation

In this report, you'll create a table that lists your current status for the month and a forecast for how many tickets will be solved the end of the month.

To create the run rate calculation

1. Create a new report using the Support: Tickets dataset.
2. In the Metrics panel, click Add.
3. From the list of metrics, choose Tickets > Solved tickets, and then click Apply.
4. In the Filters panel, click Add.
5. From the list of attributes, choose Time Ticket solved > Ticket solved - Year.
6. Click the filter you just added, and then click Edit date ranges.
7. On the Date range page, click Simple, then configure a date range of This month.

   

8. Create four standard calculated metrics for each line of the table as follows:

   Metric name	Formula
   Days elapsed in month	NUMBER(DAY(TODAY()))
   Total days in month	NUMBER(DAY(END_OF_MONTH(TODAY())))
   Run rate	COUNT(Solved tickets)/SUM(Days elapsed in month)
   Forecast monthly total	COUNT(Solved tickets)/SUM(Days elapsed in month)*SUM(Total days in month)

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
9. In the Metrics panel, click Add.
10. From the list of metrics, choose the four standard calculated metrics you just created, then click Apply.
11. From the Visualization type menu (), choose Table.
12. From the Chart configuration menu (), choose Columns.
13. On the Columns page, select Metrics on rows.

    

14. This gives you a table with your current run rate and forecasted monthly total:

    

15. Save your report and add it to the dashboard you created for Report 1.

Report 3 (optional) - Compare forecasted monthly total to target

Finally, you can create a KPI chart that compares the forecast monthly total to your target number of solved tickets.

This KPI shows you the estimated difference from target at the end of the month allowing you to deploy extra resources if necessary to anticipate any shortfall.

To create the KPI chart

1. Create a new report using the Support: Tickets dataset.
2. In the Metrics panel, click Add.
3. From the list of metrics, choose Calculated metrics > Forecast monthly total (one of the standard calculated metrics you created earlier), and then click Apply.
4. In the Filters panel, click Add.
5. From the list of attributes, choose Time - Ticket solved > Ticket solved - Year, and then click Apply.
6. Click the filter you just added, and then click Edit date ranges.
7. On the Date range page, click Simple, configure a date range of This month, then click Apply. 
8. Create a standard calculated metric for your target. For the formula, enter the target number, eg 250. When you are finished, click Save.
9. In the Metrics panel, click Add.
10. From the list of metrics, choose Calculated metrics > 250 (or whatever you called the metric above), and then click Apply. Ensure that the aggregator for this metric is set to average (AVG).
11. From the Visualization type menu (), choose KPI.
12. From the Chart configuration menu (), choose Chart.
13. On the Chart page, check Show variation and choose a Variation type of Difference.

    

14. Save your report and add to the dashboard you created for Report 1.

Arranging your dashboard

To finish off this recipe, arrange the various reports you've created in this recipe on the dashboard you created earlier. For the purposes of this example, we've added another report that uses Explore's forecasting capability to predict the number of ticket solves you'll get in upcoming months. For help with forecasting, see Using forecasting to predict results.

For help formatting your dashboard, see Adding and arranging dashboard widgets.

Your dashboard will now look something like the following example: