How can I set the ticket age including all ticket status?

Respondida

9 Comentarios

  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Irene,

    You can get ticket age for all statuses by creating this calculated metric:

    IF ([Ticket status] = "Solved" OR [Ticket status] = "Closed")
    THEN DATE_DIFF([Ticket solved - Timestamp], [Ticket created - Timestamp], "nb_of_days")
    ELIF ([Ticket status] != "Solved" AND [Ticket status] != "Closed")
    THEN DATE_DIFF(NOW(), [Ticket created - Timestamp], "nb_of_days")
    ENDIF

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Analyst @ helphouse.io

    0
  • Irene Yung

    Hi Marie-Cathrine Sørensen,

    Thanks for the help!

    When the average become lower than the one exclude solved+closed, is it normal?

     

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    It is normal. The average ticket age will change and go up or down depending on what statuses you have included or excluded in filters and attributes in the query.

    0
  • Irene Yung

    Hi Marie-Cathrine Sørensen

    Having another question. I'd like to set up "Ticket age brackets" (includes all ticket status) and the brackets options similar with Unsolved Ticket age brackets: 1 day, 2-7 days, 8-30 days, 31-60 days, 61-90 days, >90 days"

    How could I set up?

    Also, the default "Unsolved Ticket age brackets" are 1 day, 1-7 days, 7-30 days, >30 days. Would the days on 1,7,30 be overlapped? Can you explain if is it normal industrial counting?

    Thanks a lot!

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Irene,

    I don't have an answer right now as to how to set up the brackets, but if you want, I can investigate this and come back with an answer to whether it possible or not, and if it is, how to set it up?

    And regarding the already existing brackets, 1, 7 and 30 are only counted once: 1 is included in the 1 day bracket, 7 in the 1-7 days and 30 in the 7-30 days.

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Analyst @ helphouse.io

    0
  • Lohith S

    Hi Marie,

    How do we set up the Ticket Age < 30, Between 30 - 60, Between 60 - 90 and > 90 days ?

    In MAQLI have set this up using below query

    select # Unsolved Tickets where( select Ticket Age (days) [AVG] by Ticket Id ) < 30

    How do I set it up in Explore ?

    Thanks

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Lohith,

    There are two ways to approach this one: You can create a standard calculated metric for each bracket or create a standard calculated attribute that divides your unsolved tickets into brackets. Both are using the Tickets dataset.

    Using calculated metric

    1. Go to Calculations > Standard calculated metric.
    2. Create 4 metrics using the following formulas:

      Ticket age <30 days
      IF (VALUE(Unsolved tickets age (days)) < 30) 
      THEN [Ticket ID]
      ENDIF
      Ticket age 30-60 days
      IF (VALUE(Unsolved tickets age (days)) >= 30 
      AND VALUE(Unsolved tickets age (days)) <= 60)
      THEN [Ticket ID]
      ENDIF
      Ticket age 60-90 days
      IF (VALUE(Unsolved tickets age (days)) > 60 
      AND VALUE(Unsolved tickets age (days)) <= 90)
      THEN [Ticket ID]
      ENDIF
      Ticket age >90 days
      IF (VALUE(Unsolved tickets age (days)) > 90) 
      THEN [Ticket ID]
      ENDIF
    3. Add the new Standard calculated metrics as Metrics using the aggregator COUNT.

    Using calculated attribute

    1. Add Unsolved tickets as a Metric.
    2. Go to Calculations > Standard calculated attribute.
    3. Add the following formula:
      IF (VALUE(Unsolved tickets age (days)) < 30) 
      THEN "<30 days"
      ELIF (VALUE(Unsolved tickets age (days)) >= 30
      AND VALUE(Unsolved tickets age (days)) <= 60)
      THEN "30-60 days"
      ELIF (VALUE(Unsolved tickets age (days)) > 60
      AND VALUE(Unsolved tickets age (days)) <= 90)
      THEN "60-90 days"
      ELIF (VALUE(Unsolved tickets age (days)) > 90)
      THEN ">90 days"
      ELSE "Other"
      ENDIF
    4. Select the new attribute as either a Row or Column depending on what kind of visualization you want.

    I hope this helps :)

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Specialist & Junior Developer @ helphouse.io

    0
  • David Young

    This answer seems incomplete to me because a tickets status can be opened again after it is closed.  This query would only show age for when the ticket was first closed unless you had it search for MAX(updated) with the status = 'closed' or status = 'solved'.

    I would also suggest using a CASE statement instead of using nested IF statements for efficiency.

    Example:

    WITH add_age AS
    (
      SELECT
         t.id,
         t.created_at,
         t.status,
        CASE WHEN t.status in ('closed', 'solved', 'deleted') THEN timestamp_diff(t.updated_at, t.created_at, day)
                    ELSE timestamp_diff(current_timestamp, t.created_at, day)
         END AS ticket_age
    FROM
         staging_zendesk.ticket AS t

    ), enhanced_ticket AS
    (
      SELECT
         t.*,
         af.ticket_age,
         CASE WHEN af.ticket_age BETWEEN 0 AND 30 THEN 0
                    WHEN af.ticket_age BETWEEN 31 AND 60 THEN 1
                    WHEN af.ticket_age BETWEEN 61 AND 90 THEN 2
                    WHEN af.ticket_age > 90 THEN 3
         END AS range_sort,
         CASE WHEN af.ticket_age BETWEEN 0 AND 30 THEN '30 days'
                    WHEN af.ticket_age BETWEEN 31 AND 60 THEN '30 - 60 days'
                    WHEN af.ticket_age BETWEEN 61 AND 90 THEN '61 - 90 days'
                    WHEN af.ticket_age > 90 THEN '> 90 days'
                    ELSE 'Other'
         END AS ticket_age_range

      FROM
         staging_zendesk.ticket AS t
         LEFT OUTER JOIN add_age AS af ON af.id = t.id
    )

      SELECT
         et.range_sort,
         et.ticket_age_range,
        COUNT(et.ticket_age)
      FROM
         enhanced_ticket as et
      WHERE
    /* Optional limiter for date. Can use BETWEEN here as well to set a range. */
      et.created_at >= '2021-01-01'
      group by 1,2

    Something else to consider:  What if a ticket is placed in 'Pending' status.  Is the ticket still considered as 'aging' since the time clock for the SLA is halted?

    1
  • Eric G. Gao
    Zendesk team member

    Thanks for sharing the example, David!
    In regards to the Pending status, it will be included in the "Unsolved tickets age" since it's essentially capturing the total time elapsed between the ticket creation and resolution. If you're looking for SLA-specific data, we recommend following a similar approach as this recipe (https://support.zendesk.com/hc/en-us/articles/360023884994-Explore-recipe-Reviewing-SLA-performance) to report on the SLA data for your tickets.

    Warm Regards,
    Eric

    Eric G. Gao | Technical Support Architect | Zendesk

    0

Iniciar sesión para dejar un comentario.

Tecnología de Zendesk