Insights recipe: Agent contributions at the update level Follow

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 weren't the solving agent.

This tip covers the following topics:

Creating your metrics

To begin you'll need to create two custom metrics.

Note: If you are just starting out with custom metrics, Insights recipe: Solved tickets by agent is a great starting point. If custom metrics are new to you see GoodData's video, Creating custom metrics .

The following are the two metrics you will need to create:

# Solves by Updater

The first custom metric contains a modified version of the # Ticket Solved metric.

Note : This metric will not capture tickets that are assigned and solved without updates by the agent. If your use case involves assigning and solving tickets without any update from the assignee, be aware that those tickets will not be counted.

To create the # Solved by Updated metric:

    1. Click the Reporting icon ( ) in the agent interface, then open the Insights tab.
    2. Click the GoodData link in the top right corner.
    3. Click Reports > Create Report .
    4. In the What panel, click Add New Metric .
    5. In the Simple Metric Editor panel, click the Advanced Metric Editor link at the bottom.
    6. Select the Custom metric option. You will automatically be redirected to the Custom Metric Editor .
    7. Enter a name for your metric
    8. Check the Add to Global Metrics box, and select a folder from the drop-down list.
    9. 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 and Ticket status are in Attributes > Tickets.
      • Ticket Updates is in Attributes .
      • Solved and Closed are in Attribute values > Tickets > Ticket Status .
      • Updater ID(fact) and Assignee ID (Fact) are in Facts .
      • Updater Role is in Attributes>Ticket Updates .
      • Agent and Admin are in Attribute values > Ticket Updates > Updater role .
    10. Click Add when you are finished entering the formula.

# Solve Assists

This metric will count the number of tickets where an updater made a public reply but was not the assignee.

To create the # Solve Assists metric:

    1. In the Simple Metric Editor > Advanced Metric Editor , select the Custom metric option.
    2. Enter a name for your metric.
    3. Check the Add to Global Metrics box, and select a folder from the drop-down list.
    4. 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 and Ticket status are in Attributes > Tickets.
      • Ticket Updates is in Attributes .
      • Solved and Closed are in Attribute values > Tickets > Ticket Status .
      • Updater ID(fact) and Assignee ID (Fact) are in Facts .
      • Updater Role is in Attributes>Ticket Updates .
      • Agent and Admin are in Attribute values > Ticket Updates > Updater role .
      • Public Comment is in Attributes > Ticket Updates
      • true is in Attribute values
    5. 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.

  1. In the What panel, ensure that your custom metrics are selected.
  2. In the How panel, select the Updater attribute in Ticket updates .
  3. Adjust the display label in the Detail column.
  4. Click Done .
  5. Click the Filters button to create a new filter.
  6. Select the Select from a List of Values filter.
  7. 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.
  8. 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.

  1. In the upper right hand corner, click the Show configuration link.
  2. Underneath Advanced Configuration , click the + button next to Global Settings to expand the menu.
  3. Check the Data Labels and Totals boxes.
  4. Uncheck the Values box.

  5. 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.

Have more questions? Submit a request

Comments

  • 0

    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

  • 0

    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.

    SELECT IFNULL(COUNT(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

     

    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!

  • 0

    I've been stuck trying to create the #Solve Assists Metric because I am unable to find the value below where it is listed 

      • true is in Attribute values

    Can I be told where to find it, or if this has been updated and changed?

Please sign in to leave a comment.

Powered by Zendesk