Ping Pong Cases
Description
This example shows how to identify ping-pong-cases with PQL.
IT service management (ITSM) refers to the measurements and methods performed by an organization to ensure the optimal support of IT services provided to customers. Service-level agreements (SLAs) between the organization (also referred to as service provider) and the customers (also referred to as service user) define particular aspects of the provided support like availability, responsibility, and most important quality. SLAs are important factors influencing service quality levels and customer happiness. Therefore, compliance with defined SLAs is essential.
Customer support within ITSM systems is usually carried out by creating a ticket for each customer inquiry in the system and solving these tickets. Thus, an important key figure for ITSM is the resolution time of a ticket. A ticket is ideally resolved without the interference of many departments or teams. However, in so-called ping-pong-cases, a ticket is repeatedly going back and forth between departments or teams. This is massively slowing down the resolution time. To prevent this, the identification of ping-pong-cases is crucial.
Ping-pong-cases can be classified into two categories:
Direct ping-pong-cases: The same activity appears (at least) two times with only one other activity in between, e.g.
Change Assigned Group → Review Ticket → Change Assigned GroupIndirect ping-pong-cases: The same activity appears (at least) two times with more than one other activity in between, e.g.
Change Assigned Group → Review Ticket → Do some work → Change Assigned Group
Challenge
Each ticket (i.e. each case) is assigned to a country. For every country, we want to calculate the direct and indirect ping-pong-case ratio.
Direct Ping-Pong-Cases
The following example shows a PQL query to identify direct ping-pong-cases. A case in this context is equivalent to a ticket. Direct ping-pong refers to tickets in which the same activity appears (at least) two times with only one other activity in between, e.g. 'Change Assigned Group' directly followed by 'Review Ticket' directly followed by 'Change Assigned Group'.
[1] This query calculates whether a ticket is a ping-pong-case or not within the CASE WHEN statement. If the current activity equals 'Change Assigned Group', the second next activity is equal to the current activity and the next activity is not equal to the current activity, the ticket is classified as ping-pong-case and the CASE WHEN statement returns the ticket ID. The comparison between the current activity, the next and the second next activity is achieved by using the ACTIVITY_LEAD operator. In general, the ACTIVITY_LEAD operator returns the activity from the row that follows the current activity by offset number of rows within a case. As the timestamp column of the activity table is defined in the data model, the ACTIVITY_LEAD operator can implicitly rely on the correct ordering of events. The CASE WHEN statement is wrapped in a COUNT operator to count the total number of ping-pong-cases. By adding DISTINCT to the COUNT operator, it is guaranteed that a ticket is only counted once although ping-pong activities can occur multiple times within a ticket. The result of the COUNT operator is then divided by the total number of tickets to get the ratio of ping-pong-cases. Thereby, the total number of tickets is calculated using the COUNT_TABLE operator. COUNT_TABLE is a performance-optimized function for counting the number of rows of a specified table. By specifying the country ("Tickets"."Country") as a dimension, the ratio of ping-pong-cases is calculated per country. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Indirect Ping-Pong-Cases
The following example shows a PQL query to identify indirect ping-pong-cases. Indirect ping-pong refers to tickets in which the activity 'Change Assigned Group' appears at least two times with more than one other activity in between, e.g., 'Change Assigned Group', directly followed by 'Review Ticket', directly followed by 'Do some work', directly followed by 'Change Assigned Group'.
[2] This query calculates whether a ticket is an indirect ping-pong-case or not by using the operators INDEX_ACTIVITY_TYPE and ACTIVITY_LAG within a CASE WHEN statement. INDEX_ACTIVITY_TYPE returns, for every activity, how many times it has already occurred (so far) in the current case. Within the CASE WHEN statement, the ticket ID is returned if the INDEX_ACTIVITY_TYPE is greater than 1 and the current activity is not equal to the last and the second last activity. The latter comparison is calculated by the ACTIVITY_LAG operator. In general, ACTIVITY_LAG returns the activity from the row that precedes the current activity by offset number of rows within a case. If one of the expressions in the The country ("Tickets"."Country") is specified as a dimension to calculate the ratio of indirect ping-pong-cases per country. The FILTER statement at the beginning of the query ensures that the current activity is 'Change Assigned Group'. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|