Ping pong cases

Applies to: CELONIS 4.6 CELONIS 4.7

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 Group
Indirect 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.

Query

Column1

"Tickets"."Country"

Column2

COUNT ( DISTINCT
    CASE
        WHEN
            "Activities"."Activity" = 'Change Assigned Group'
            AND
            "Activities"."Activity" = ACTIVITY_LEAD ( "Activities"."Activity" , 2 )
            AND
            "Activities"."Activity" != ACTIVITY_LEAD ( "Activities"."Activity" , 1 )
        THEN
            "Activities"."TicketId"
        ELSE
            NULL
    END
)
/
COUNT_TABLE ( "Tickets" )

Input

Output

Activities

TicketId : INT	Activity : STRING	Timestamp : DATE
1	'Create Ticket'	Wed Jan 01 2020 01:00:00.000
1	'Assign Ticket'	Wed Jan 01 2020 02:00:00.000
1	'Change Assigned Group'	Wed Jan 01 2020 03:00:00.000
1	'Review Ticket'	Wed Jan 01 2020 04:00:00.000
1	'Change Assigned Group'	Wed Jan 01 2020 05:00:00.000
1	'Resolve Ticket'	Wed Jan 01 2020 06:00:00.000
2	'Create Ticket'	Wed Jan 01 2020 01:00:00.000
2	'Assign Ticket'	Wed Jan 01 2020 02:00:00.000
2	'Change Assigned Group'	Wed Jan 01 2020 03:00:00.000
2	'Change Assigned Group'	Wed Jan 01 2020 04:00:00.000
2	'Resolve Ticket'	Wed Jan 01 2020 05:00:00.000
3	'Create Ticket'	Wed Jan 01 2020 01:00:00.000
3	'Assign Ticket'	Wed Jan 01 2020 02:00:00.000
3	'Change Assigned Group'	Wed Jan 01 2020 03:00:00.000
3	'Review Ticket'	Wed Jan 01 2020 04:00:00.000
3	'Do some work'	Wed Jan 01 2020 05:00:00.000
3	'Change Assigned Group'	Wed Jan 01 2020 06:00:00.000
3	'Resolve Ticket'	Wed Jan 01 2020 07:00:00.000

Tickets

TicketId : INT	Country : STRING
1	'DE'
2	'DE'
3	'US'

Foreign Keys

Tickets.TicketId

Activities.TicketId

Result

Column1 : STRING	Column2 : FLOAT
'DE'	0.5
'US'	0.0

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 WHEN-clause is FALSE, the CASE WHEN statement returns NULL. As in the example for direct ping pong cases, 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 as an indirect ping pong case. The result of the COUNT operator is then, again, divided by the total number of tickets to get the ratio of indirect ping pong cases. Thereby, the total number of tickets is calculated using the COUNT_TABLE operator.

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