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

Explore recipe: Backlog tickets by month

Hannah Meier
  • Edited
Zendesk team member
Suite Professional, Enterprise, and Enterprise PlusExplore Professional only
In this recipe, you'll learn how to create a query that displays the number of tickets that were unsolved in your account at the end of each month. 
This article contains the following topics:

What you'll need

Skill level: Easy

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

Creating the query

    To create the query

    1. In Zendesk Explore, click the query () icon.
    2. In the Queries library, click New query.
    3. On the Choose a dataset page, click Support > Backlog history > Support: Backlog history, then click New query. Query builder opens.
    4. In the Metrics panel, click Add.
    5. From the list of metrics, choose Backlog tickets (unsolved) > Tickets, then click Apply. 
    6. In the Rows panel, click Add.
    7. From the list of attributes, choose Time - Backlog recorded > Backlog recorded - Year and Time - Backlog end of period > End of month, then click Apply.

    Optional: Applying a date filter

    1. In the Filters panel, click Add.
    2. From the list of attributes, choose Time - Backlog recorded > Backlog recorded - Date, then click Apply.
    3. Click the filter you just added, click Edit date ranges, and then configure your required dates.
    Return to top
    Have more questions? Submit a request

    23 Comments

    • Mark Leci

      I am rebuilding a backlog report from Insights, that report appears to be accurate, but when I follow the above instructions the backlog numbers are incorrect in Explore, and they are more incorrect going further back. For example, the number in Insights for last month is 216 vs 212 in Explore. If I go back to July 2019, the Insights number is 232, but the explore number is 15, which is definitely wrong. I'm also noticing that all months before July 2019 are missing. My report is exactly as above, with a date filter for the last 12 months. 

      0
    • Rob Stack
      Zendesk Documentation Team

      Hi Mark, I've created a ticket from this so someone can work with you to figure out what's going on here. Someone should be in contact soon.

      1
    • Admin Zendesk

      Hello,

      I followed the recipe above and I have a line with a huge number for the year in addition of the months number.

      What I have done wrong?

      Thanks.

      Mélodie

       

      0
    • Rob Stack
      Zendesk Documentation Team

      Hi Admin Zendesk. You can remove these NULL values by clicking on each filter in the Rows column and ensuring that the NULL field is not checked on the Selected tab. Hope this helps!

      0
    • Liam Tran

      How can I add the current month backlog as well? After following these steps, I got everything I needed to show in my report except this month's (march).

      0
    • Devan - Community Manager
      Zendesk Community Team

      Liam Tran,

      Your request currently is not possible in Explore right now in the product's current state. I would recommend posting in our Explore product feedback forums so our developers can consider this as an update for future patches. 

      Best regards. 

      0
    • Jason

      Hi Hannah


      Thank you for this, this is great. Our business has several different 'departments' and we store this at a ticket level in a custom field. Is there any enhancements in the pipeline that will allow us to add additional fields when using the Backlog History Data Source? 

      This would be ideal for us and i'm sure many businesses to be able to view backlog by a custom field. 

       

      I just want to clarify I know you could use Ticket Group in the Backlog dataset but we use this for different data. 

       

      Thank you 

      Jason

      0
    • Brett Bowser
      Zendesk Community Team

      Thanks for taking the time to share this with us Jason! We actually have a feedback post related to your comment here: Allow custom fields in the backlog dataset

      I'd recommend upvoting that post and providing your use-case there for our product managers to review as well.

      Cheers!

      0
    • Greg Vowles
      • Edited

      I want to show a list of tickets on my dashboard, only showing tickets that are unsolved (i.e. Open/New/On Hold) - I know how to create the query and it looks fine (all my columns appear just lovely), but the bit I am struggling with is getting the results to filter by the parameters I am setting at the top of the dashboard.

      For example, I want the list to filter to tickets between two dates and to filter down to tickets for a specific Customer.  Currently it shows me all records for all time. How do I tie these parameter fields in to my query so that they update the list depnding on what I set them to be.

      I am using a modified version of the built in Zendesk Support Dashboard and it's the options at the top I am talking about, i.e.:

      0
    • Devan - Community Manager
      Zendesk Community Team

      Hello Greg Vowles,

      Would you mind sharing with us a bit more information? What exactly are you trying to accomplish, and could you elaborate filter by date. 

      Best regards. 

      0
    • Jiri Kanicky
      • Edited

      This does not work for me. We don't have 25K unsolved tickets. The data displayed seems to be incorrect.

      What is wrong with my query?

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Jiri,

      One thing to keep in mind is that when you're using the attribute it's aggregating the sum of all your unsolved tickets for the month/year. If you'd like to know how many backlog tickets were at the end of the month/year, you may want to use the Backlog end of period attribute instead.

      You'll also want to make sure you exclude any Null values as mention by Rob above.

      Check out What is the difference between Backlog recorded and Backlog end of period? for more information.

      Let me know if that still doesn't get you the results you're looking for.

      Cheers!

      0
    • Jiri Kanicky
      • Edited

      Hi Brett.

      So why this tutorial teaches me something that does not work? I just followed the same steps. Does the tutorial need to be updated?

      Clearly the steps in this tutorial do not show me backlog by month.

      Thanks Jiri

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Jiri,

      I think the tutorial still works if you're looking to aggregate the results. However, I do agree that this could be made more clear in the article so I appreciate you taking the time to share this with us.

      Did you at least get to try the other attribute I recommended? Did those numbers look better?

      Let me know!

      1
    • Jiri Kanicky

      I fixed the issue as recommended with Backlog end of period.

      Thank you Brett.

       

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Jiri,

      Glad I was able to help! I'll look into getting this article updated so it's more clear what the expected results are.

      Cheers!

      0
    • Samantha Bellach
      • Edited

      Why can't backlog be filtered by Ticket form?  Is there a plan to add more ticket attributes to the backlog Explore data pool to be able to filter by?   I attempted to do a calculation on Ticket Form within brands but the data set available to me is extremely limited.

      Is Zendesk planning to add at least a few more attributes into the Backlog Explore dataset? 

      UPDATE: Please vote on adding custom ticket attributes to Backlog Explore dataset here :) https://support.zendesk.com/hc/en-us/community/posts/360029386274-Allow-custom-fields-in-Backlog-Dataset?page=1#community_comment_360012997853

      1
    • Rob Stack
      Zendesk Documentation Team

      Hi Samantha Bellach thanks for the feedback.

      This would be an excellent request to make in our product feedback forum where it will be seen by the product team and other customers can vote for it. Thanks! 

      https://support.zendesk.com/hc/en-us/community/topics/360001200913

       

       

      1
    • Mirek Kokes

      Hi there,

      I would like to know how can I get a monthly backlog of tickets for a certain organization? I want to show that each month we were able to lower the backlog of tickets and that we improved in this area. However, I wasn't able to figure out the correct setting. Is it even possible with the current Explore features? If not, where can I upvote the idea?

      Thank you.

      Mirek

      0
    • Marcus Spedding

      This doesn't work. it's displaying MILLIONS of tickets. We only have 17, 000 in total. 

      As soon 

       

      0
    • Matt
      Zendesk Customer Advocate

      Hi Mirek Kokes
      Unfortunately there is currently no way to report on Organisations from the Backlog History Dataset. There is also some more information on this here: 
      Why can't I see most of the attributes when reporting on Backlog Tickets in Explore?

      Feel free to create a Feedback Post about this here: Feedback on Explore
      To workaround this, you might need to create a report in a different Dataset, such as the Tickets Dataset using the Metric Unsolved Tickets. 

      I hope this helps answer your question. 

      Hi Marcus Spedding
      This might occur because the Metric aggregates the SUM of all your unsolved Tickets.
      To measure how many backlog tickets were recorded at the end of the month/year, you can use the Backlog end of period attribute. Then make sure to exclude the values for "NULL" in that attribute. 

      Feel free to look at What is the difference between Backlog recorded and Backlog end of period? for more information on the attributes.

      Let us know if this still does not help you get the result you are hoping to see. 

      1
    • Marcus Spedding

      Thanks. 

      How can this be set up to show a series of results across two years (for example April 2020 to April 2021)?

      With the setup as above, there are different graphs by years. I'd like all this information in a single year. 

      Thanks

      0
    • Mark Leci

      Marcus Spedding I prefer to run this report using the dates in the columns instead of rows. That should combine all the info into a single graph for you. 

      0

    Please sign in to leave a comment.

    Related articles

    Powered by Zendesk