Create a Running Backlog for a Specific Customer
By adding two new metrics to insights, it's possible to create a running backlog for a specific customer.
Create the Backlog Impact metric
- Open a new report in GoodData
- Under What, select Add New Metric > Advanced Metric Editor > Custom Metric
- Name your metric Backlog Impact
- Enter the formula: SELECT IFNULL (# Tickets Created,0)-IFNULL(# Tickets Solved,0)
- Click Save
Create the Running Backlog Metric
- Under What, select Add New Metric > Advanced Metric Editor > Custom Metric
- Name your metric Backlog
- Enter the formula: SELECT RUNSUM (Backlog Impact)
- Click Save
Create the Report
- Under What, select:
- # Tickets Created
- # Tickets Solved
- Backlog
- Under How, select:
- Date (Event)
- Organization
- Under Filter*, select:
- Organization is <selected organization>
- Select Line Chart as the display (if you want to see the backlog displayed as a line chart)
*Further ticket filters can also be applied, such as Ticket Group or Ticket Type.
What I have not figured out how to do yet is filter this chart by dates, since filtering the chart by dates eliminates previous days' worth of backlog, which changes the numbers. Any feedback is appreciated!
-
Kristin
That is beautiful.
-
Thanks for sharing this, Kristin!
-
Hi Kristin,
nice job, this is exactly what we are looking for since weeks. But as for you we would like to filter by time range (e.g. Last 3 month). Did you found a solution to keep the good backlog value?
Baptiste
-
@Baptiste,
Unfortunately, I've been unable to find a solution for that so far. I will update if I do :)
-
Hi Kristin and Baptiste,
You can add a floating date filter into the metric. For example, if you want to report just on tickets created during last 90 days you can modify the Backlog Impact metric like this:SELECT IFNULL ((SELECT # Tickets Created WHERE Date (Ticket Created) BETWEEN THIS - 89 AND THIS),0) - IFNULL ((SELECT # Tickets Solved WHERE Date (Ticket Created) BETWEEN THIS - 89 AND THIS),0)
Screenshot:
-
Hi Eugene,
Perhaps I missed something, but the purpose of the backlog metric is to take all historical created/solved data to display a full runsum that gives you a current backlog. By not including data from all time, wouldn't the displayed backlog be incorrect? I think the way that you created it would just be identical to adding filters.
I tested out your metric and my runsum displays a negative backlog, since we have solved more tickets in the last 90 days than have been opened. I know that this is not the case, since I can see that I have tickets open still.
-
@Kristin,
The second part of the metric is:
IFNULL ((SELECT # Tickets Solved WHERE Date (Ticket Created) BETWEEN THIS - 89 AND THIS),0)
Because the Date (Ticket Created) attribute is used just the tickets created during last 90 days will be taken into consideration when metric is computed.So, from my perspective the running backlog should be calculated as expected, just it will be computed starting from 90 days ago.
-
@Eugene, while your metric works as expected, it doesn't give me the result that I would like. In order to calculate my backlog, I would need to have the runsum from all time, which would be the only way to display my live backlog. Calculating a runsum from only the past 90 days would display the change in my backlog from 90 days to today, which is useful, but not what I'd be looking for here.
-
@Krisitn, Thank you for the clarification. Yes indeed, the workaround I have suggested above will not be useful for your use case.
-
Any idea on how we could show this on a month-on-month basis instead of day wise?
-
Hi Manish,
Yep, it's a quick fix. Under How you will change Date (Event) to Month/Year (Event).
-
Just tried it Kristin, and it worked like a charm.
Thank you for the quick assist!
-
One more question:
I understand that if I add a filter for last 12 months or so, I run into negative values because it is a running sum.
I just need the view of last 6 months. (It does not make sense to send the entire data for 10+ years to the executives). How would we achieve this?
-
What I've done in this case is to export the data into Excel, hide the rows of all previous months, and then just create and display a line chart using those months. There isn't a way to do it this way in Insights, sadly, according to Zendesk support.
-
Hi Kristin,
Thanks for this one!
1/
Can I also use this for a specific brand in stead of an organization?2/
Can you also help me with the metric "Running Backlog"? I can't find the metric 'Backlog Impact' to add in my metric "running backlog". Where is it located after making the metric 'backlog impact'?Thanks for the help!
-
Just to add to this method. If you create a new metric for outstanding tickets:
You can then filter your report by a shorter date and breakdown the backlog by customer or organisation. For example, for backlog by user:
-
hello,
I'm having an error on the creation of the new metric. Could you please let me know what I'm doing wrong
thank you in advance
-
Gaëtan
Unfortunately, you cannot just type the code into the editor. Instead you must select each element from the tool on the right hand side.
See this article for how to create custom metrics.
-
perfect. thank you
-
thank you for sharing this recipe ! excellent
-
Hi,
I would like to check if this similar report can be built in Zendesk Explore Tickets Data Set ? Can you help how to create standard metric to compute backlog?
-
Hey Arunachalam,
Have you had a chance to take a look at our Explore recipe: Backlog tickets by month? I believe that should help you get started with reporting on backlog.
I hope this helps!
Por favor, entrar para comentar.
22 Comentários