I’d like to share some custom report ideas designed to give you a fast reading of how effective the support process is. They pertain to the “quality” of tickets that you receive. Basically you first divide your tickets into two categories: good and bad, or high quality and low quality. You then track the trend against a target good:bad ratio, with your goal of meeting or exceeding it. You want to identify the problem areas and address them so the support team can focus on legitimate support.

These categories may appear subjective at times, but in my situation a surprisingly small number of tickets don’t clearly meet the criteria for one or the other. The idea is as follows: Good tickets are those that generally cannot be prevented, are frequently (but not always) novel in nature, and legitimately warrant support staff assistance. Bad tickets, on the other hand, are generally preventable and typically caused by an improper deflection system, suboptimal processes inside or outside the support department, and so on.

There are obviously many ways to go about this, so I’ll just use my particular example to show how this is done. In our ticket form we have several mandatory fields that must be completed when the ticket is closed. One of these is “root cause” which contains a drop-down list of generic causes. Some of them are what we would consider good, the others not. You then create a metric for each of the two quality categories. The easiest way is to duplicate the # Tickets metric and modify it:

SELECT COUNT(Ticket Id, TicketTagId) WHERE Ticket Status <> Deleted AND Ticket Tag Deleted Flag <> true AND (Root Cause = cause_1 OR Root Cause = cause_2 OR Root Cause = cause_5)

I found it useful to create a stacked bar graph that shows the relative and total monthly number of good and bad tickets in the current year. Basic settings are like this:

• What: (your two new metrics)
• How: Month/Year (Ticket Created)
• Filter: Year (Ticket Created) is this year; Ticket Tag isn’t closed_by_merge

Select “Stack Bar Chart” with the following parameters:

• Horizontal (X): Month/Year (Ticket Created)
• Vertical (Y): Metric Values
• Series: Metric Names

For Global Settings make sure “Values” and “As %” are checked. Given that in my case these two categories are aggregates of the “root cause” attribute, I use that same attribute for the drill-in settings (a pie chart view is useful here), to see the more granular breakdown of what the specific issues are.

Here’s a sample output (I’ve removed the values and changed the totals, but the bars should show the percentages in your chart):

Too much of the “bad tickets” color will immediately alert you that the support team is burdened with unnecessary issues. It may be time to review your Help Center content, or communicate with the sales department to include additional information in their marketing campaign.

Next, we’ll get a more precise reading of how things are going, in a separate chart. We’re going to create a “Ticket Effectiveness Index” (TEI) and plot its monthly value from a year ago to the present day. This will let us know at a glance whether we’re meeting a predefined target regarding the ratio of good-to-bad tickets. Finally, a linear regression will also give us a pulse of the underlying trend.

First we create the TEI metric. This is a weighted value that takes into account the ratio of good-to-bad tickets as well as the total number of tickets for a given interval. The formula we’ll use is:

TEI = G/T + 1/T

 where,

• G = the number of “good tickets” (the “# Good Tickets” metric)
• T = the sum of “good” and “bad” tickets

This will give us a possible range of values from 0 to 2. We'll cover the target value in a little while.

The corresponding MAQL statement is as follows:

SELECT # (Good Tickets / (# Good Tickets + # Bad Tickets)) + (1 / (# Good Tickets + # Bad Tickets))

We’ll call the first part, G/T, the ratio. We’ll call the second part, 1/T, the weight. In the ratio, you’re expressing the percentage of good tickets as a decimal. In the denominator you could just use the standard “# Tickets” metric; in my situation there are some tickets that I do not want to include in the calculation, which are untracked as either “good” or “bad”, hence the sum of our newly created metrics. In the weight, when T is small – that is, when few tickets are opened – the weight is heavier and the resulting TEI is larger, because you’re adding a larger value (1/T) to your percentage. This makes sense because few tickets means higher effectiveness. As T increases – that is, as more tickets are opened – the weight decreases and your TEI will go down because you’re adding a smaller number to your percentage. However, G becomes more important to make up for the “lost weight”, and a higher G value will in turn have a positive influence on the TEI. In English, if more tickets are opened, then more of them should be "good" to maintain high ticket effectiveness.

For this measurement it’s probably more useful to have ample backlog data; therefore I’ve set the X axis to measure from a year ago to the present moment. This ensures enough history regardless of which point in the calendar year you check it.

The chart settings can be like this:

• What: # TEI
• How: Month/Year (Ticket Created)
• Filter: Date (Ticket Created) is the last 366 days; Ticket Tag isn’t closed_by_merge

Select “Line” with the following parameters:

• Horizontal (X): Month/Year (Ticket Created)
• Vertical (Y): Metric Values
• Series: (blank)

Set your Global Settings according to your preference. In this case we’ll set Data Labels to Boxed, and also select Line Smoothing and Data Points. Finally, set the Trendlines to “Linear” so you get the linear regression approximation as well to quickly gauge the trend. The ensuing chart something like this (I’ve changed my actual data and removed the values for display purposes):

You can use these reports to rapidly get a feel for how healthy your processes are in terms of the kind of problems your support team are engaged with. Your target value is arbitrary, and will probably fluctuate as time will reveal more accurately what is realistic in your particular case; generally, you should aim for at least 0.8, and that might be too low in many cases. Once you have a target value you're after, you can also create an "adjusted TEI" where the decimal value becomes a 1 for rapid identification. Multiply the raw TEI formula by a coefficient; to find the coefficient, calculate 1/(raw TEI target). For example, in the above example where your target is 0.8 or better, 1/0.8 is 1.25. To get the adjusted TEI, multiply your raw TEI by 1.25. This will inflate the values a bit and increase the top of the range from 2 to 2.5, but you'll know at a glance if you're hitting or missing the target by simply looking for values above or below 1, rather than 0.8. (This can be especially useful when tracking multiple trends with different target values). 

In actuality, some tickets that are processed as “bad” by the algorithm will in fact be legitimate, and vice-versa. Some “good” tickets, such as those that could not be foreseen, may in fact arise from subpar conditions further up the chain that could actually be improved. So it’s an approximation at best.

There will always be some limitations; however, if you craft the right categories up front and consistently mark each ticket accordingly (make it a required field for solving), you should be able to more efficiently address the areas that need the most improvement. Happy charting.