English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore Recipe: Compare last month's tickets to the 12 month average

Taylor Bowser
  • Edited
Zendesk Customer Care

What's my plan?

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

In this Explore recipe you'll learn to create a report that shows last month's ticket count compared to a 12 month average.

What you'll need

技术水平:Intermediate

Time Required:5-10 minutes

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

How to create the report in Explore

    Use the following steps to create this report in Explore:

    1. In Explore, click the reports () icon.
    2. In the Reports library, clickNew report.
    3. On theSelecta datasetpage, clickSupport>Support -Tickets, then clickStart report. The report builder opens.
    4. Next, you'll create astandard calculated metricto find the monthly average for all time. You can use this formula to do so:
      COUNT(Tickets)/DCOUNT_VALUES([Ticket created - Month])
      Be sure to checkcompute separately.
    5. Next, using the above custom metric,create adate range calculated metricto only return monthly averages for the last 12 months. In a newDate range calculated metric,underOriginal metricselect your newly created custom metric, and forDefined onselectTicket created.
    6. UnderDate range, select theAdvancedtab and select:
      • From the beginning of:12 months in the past
      • To the end of:1 month in the past
        calcmetric.png
    7. When you are finished, clickSave.
    8. Now that you've created the necessary metrics, you can build your report.In theMetricspanel of the report, add the default metricCOUNT(Tickets Created - Last Month)and your custom date range calculated metric. Be sure to keepTickets Created - Last Monthlisted as the first metric.
    9. UnderVisualization typeselect theKPIvisualization. This will show the variation between tickets created last month and your monthly average.
    10. After changing to aKPIchart, go toChart configuration > Chartand selectShow variation.Choose to display this variance as a percentage or difference.
      query.png
      Return to top

      4 Comments

      • Sebastian Craiovan

        This worked great for me, is it possible to get a weekly average though so I can compare last week to the weekly average?

        2
      • Marco
        Zendesk Customer Care
        Hi Sebastian, thanks for reaching out and great question. Just tested this out and yes you can also do it weekly. This would mean that the formula, instead of it being COUNT(Tickets)/DCOUNT_VALUES([Ticket created - Month]), will be COUNT(Tickets)/DCOUNT_VALUES([Ticket created - Week of year]). Also, since you will be using a smaller date range, then you can also change how much data the average is taken from by changing the "Date Range" on theDate range calculated metric.Hope this helps. Cheers!
        0
      • CJ Johnson

        What is the "end date" supposed to be for the "12 month average" metric? That step isn't listed, and "All time" makes the report break, but if I set it to "ended 1 month ago" it's not actually a 12 month rolling average.

        0
      • Erin O'Callaghan
        Zendesk Documentation Team

        HiCJ Johnson, excellent question! I tested this out, and it looks like "To the end of: 1 month ago" gives the right number for a 12-month average.* I updated the recipe with that as the end date for the date range calculated metric.

        * 12 -更多的解释month average: "From the beginning of: 12 months in the past" looks back as far as the beginning of the current month in the previous year—meaning, if I run the report today, it looks back as far as May 1, 2021. "To the end of: 1 month in the past" stops at the last day of the previous month (so April 30, 2022 for my example report from today). So that should give you 12 full months of data to calculate the average.

        0

      Pleasesign into leave a comment.

      Powered by Zendesk