One of the metrics most frequently used to track agent performance is the number of tickets solved, but do solves paint the whole picture? This tip will help pull back the curtain on agent contributions when the agent is not the assignee.
Your agents make a valuable contribution whenever they communicate with an end-user. In some cases, a ticket might require the attention of several agents. Each touch in the chain advances the ticket one step closer to resolution. So in addition to the tickets your agents solve, it's helpful to look at the number of tickets where they contributed to a solution.
For this level of detail you'll need to use the Updates dataset. Ticket updates enable you to create a metric that counts the number of tickets where your agents made a public comment, but aren’t the current assignee.
This tip covers the following topics:
Creating your metrics
To begin you'll need to create two custom metrics.
The following are the two metrics you will need to create:
# Solved Tickets Assigned to Updater
The first custom metric measures the number of tickets in the solved or closed status where a given updater has made a public comment and is the current assignee.
To create the # Solved Tickets Assigned to Updater metric:
- Click the Reporting icon (
) in the agent interface, then open the Insights tab.
- Click the GoodData link in the top right corner.
- Click Reports > Create Report .
- In the What panel, click Add New Metric .
- In the Simple Metric Editor panel, click the Advanced Metric Editor link at the bottom.
- Select the Custom metric option. You will automatically be redirected to the Custom Metric Editor .
- Enter a name for your metric
- Check the Add to Global Metrics box, and select a folder from the drop-down list.
- Enter in the following formula:
SELECT IFNULL(COUNT( Ticket Id , Ticket Updates ), 0) WHERE Ticket status IN( Solved , Closed ) AND Updater ID(fact) = Assignee ID (Fact) AND Updater Role IN ( Agent , Admin )
Please note, you cannot copy and paste the formula. You must select the items below from the Elements drop-down list:
- Ticket Id, Ticket status, Ticket Updates, and Updater Role are under Attributes.
- Solved and Closed are under Attribute values > Ticket Status.
- Updater ID(fact) and Assignee ID (Fact) are under Facts.
- Agent and Admin are under Attribute values > Updater role.
- Click Add when you are finished entering the formula.
# Solved Tickets Assisted by Updater
This metric will count the number of tickets where an updater made a public reply but was not the assignee.
To create the # Solved Tickets Assisted by Updater metric:
- In the Simple Metric Editor > Advanced Metric Editor , select the Custom metric option.
- Enter a name for your metric.
- Check the Add to Global Metrics box, and select a folder from the drop-down list.
- Enter in the following formula:
SELECT IFNULL(COUNT( Ticket Id , Ticket Updates ), 0) WHERE Ticket status IN( Solved , Closed ) AND Updater ID(fact) <> Assignee ID (Fact) AND Updater Role IN ( Agent , Admin ) AND Public Comment = true
Please note, you cannot copy and paste the formula. You must select the items below from the Elements drop-down list:
- Ticket Id, Ticket status, Ticket Updates, Updater Role, and Public Comment are under Attributes.
- Solved and Closed are under Attribute values > Ticket Status.
- Updater ID(fact) and Assignee ID (Fact) are under Facts.
- Agent and Admin are under Attribute values > Updater role.
- true is under Attribute values > Public Comments.
- Click Add when you are finished entering the formula.
Building your report
After you finish creating your custom metrics, you can begin to build your report.
- In the What panel, ensure that your custom metrics are selected.
- In the How panel, select the Updater attribute. The custom metrics used in this report will only function when you use this attribute.
- Adjust the display label in the Detail column.
- Click Done .
- Click the Filters button to create a new filter.
- Select the Select from a List of Values filter.
- For your attribute to filter select Week(Mon-Sun)/Year(Ticket Solved) . For the floating range select last week . This filter will capture solves from the previous calendar week.
- Your report will initially be displayed as a table, but the stacked bar chart is the recommended choice. Click the stacked bar chart icon to change your chart type.
Your report should resemble the image below:
Displaying totals
The report looks pretty good, but wouldn't it be nice to see the total of solves and solves assist right on the chart? If you want to add totals to your report, you can follow the steps below.
- In the upper right hand corner, click the Show configuration link.
- Underneath Advanced Configuration , click the + button next to Global Settings to expand the menu.
- Check the Data Labels and Totals boxes.
- Uncheck the Values box.
- Click Apply to save the configuration changes.
Your report should resemble the image below:
You did it! Now you can bring a little more nuance to your analysis of agent performance and recognize agents for all their contributions.
Hopefully you've been inspired to see what kinds of questions you can answer by digging into your tickets using the updates data set.
12 Comments
Hi,
i really appreciate this article, it already helped me so much.
However i am stuck since 2 hours on finding the statement to read out average number of public replies to solve a ticket. (per agent)
I am trying to start with your code in this example, but so far no luck.
Could you give me a hint?
Best,
Andreas
Hi Andreas - Once you get into the ticket updates the metrics can get a bit tricky. If you want to get an average number of public comments made by the assignee on a ticket I'd try something like this.
If you divide that by the "# Solves by Updater" metric above you should get the average for tickets and comments made by the assignee. In this case be sure to slice by the "Updater" attribute and not "Ticket Assignee".
Here's the plaintext syntax from that screenshot.
This is just off the top of my head. I'd give it a try and see if that does the job. If you have any trouble reply here and I'll spin up a ticket for you so we can dig a bit deeper.
Cheers!
I've been stuck trying to create the #Solve Assists Metric because I am unable to find the value below where it is listed
Can I be told where to find it, or if this has been updated and changed?
Hello, Walter!
I believe we have already talked about this issue in a ticket, but in any cases, I was able to recreate the metric and I managed to find the value true under the Attribute Public Comment:
Please remember when using Attribute Values, firstly you need to select the Attribute and only then you will be able to select its value.
Hope this helps.
First of all, this recipe is super helpful!
I'm looking to modify this slightly to simply collect the number of updates by agent (updater) on all tickets created within the specified month, solved tickets aren't really a metric we track.
We have a custom app that is (supposed to be) adding a tag of the agent's name to a ticket any time that agent contributes to the ticket. At the beginning of each month, we look at how many tags that agent has on relevant tickets, and boom - we have what we want for agent ticket activity. The app doesn't quite work and it causes some other annoying problems as well, so we would like to ditch it.
Could someone help suggest a modification to this report that can give me a result closer to what I have above?
Brad
Have a look at this comment. This shows the number of tickets updated by an agent. Sounds like it is similar to what you are after?
I'm trying to broaden the measurement a little and get a count of all public comments by an agent on tickets not assigned to them. I think I've got the metric:
My question is how this is going to measure updates on Unassigned tickets. When an agent updates an Unassigned ticket, the ticket is automatically assigned to the agent. Does this metric compare the Updater ID to the Assignee ID at the time of the update (meaning it would include updates to Unassigned tickets here?) or does it compare Updater ID to the Assignee ID at the time of running the report (so it would not include those updates here since the ticket has been assigned to the updater)?
Hey Chuck! Welcome to the Community, and apologies for the delayed response!
We're checking with some of our Insights experts to see if we can find an answer for you on this. Sit tight!
Hi Chuck,
Thank you for raising this up to us.
The report will show the data based on what Date attribute you use to slice the report. If you do use Date (Event) to slice your data, the report will show the metric will compare the Updater ID to Assignee ID by the time any update was done. However, if you use other Date attributes such as Date (Ticket Assigned) it will compare the by the time the action (Ticket Assigned) was done.
Hope this helps. But if you have any other concerns, please feel free to let us know and we will be happy to assist you.
Hi there, I have been inspired by this post to do stronger analysis of our agent's performance with Ticket Updates reporting.
A report I'd like to build is one that would tell me how long each Ticket Update has taken and what issue was addressed by the Ticket Update. Our agents will be filling out an 'Issue addressed by the Ticket Update' field each time they update the ticket. I've attached a screenshot of what the report I'm after looks like (google sheets mock up).
The reason for this is because in one ticket, our customers/agents generally cover a variety of issues before it's solved. Therefore, it's unfair to say that the whole time spent solving the ticket was because of 1 issue when there were actually 3 issues covered throughout the whole ticket, for example.
When building the report, I select the metric to measure the ticket update handling time (SELECT Total time spent (min) - new By Ticket Updates) in the 'What' section, which is fine. I then select 'Ticket ID' and 'Ticket Updates' under 'How', which also works. However, when I try to select '[Text Field] New Value' attribute under 'How', it can't be selected.
I've had a look around some articles and it seems that it's because the Ticket Updates dataset isn't linked with the Ticket Text Field Changes dataset.
Is there a way to link 2 unlinked datasets?
Am I doing something wrong, is there another way I can build this report?
Any help would be appreciated. Thanks in advance!
Hello Jacky,
I see you already have a ticket open with us on this with Dan. He will be able to work through any specific questions you have regarding your report.
To address your question more generally though, there is no way to connect 2 datasets that are unlinked. The best solution is to build separate reports and display them together on the same dashboard.
First of all thank you for the recipe. Its a great help. There is one tweak Id like to do. Id like to identify the number fo tickets someone not assigned to the ticket closes.
Ex
Agent A is assigned to the ticket
Agent B updates the ticket and sets it to solved.
Suggestions?
Please sign in to leave a comment.