Creating common SLA reports with KQL

Service level agreements (SLAs) and escalations both play a major role in support industry. It is one of the ways to ensure you're providing good customer service in a timely manner. Using Kayako Classic's reporting query language (KQL) you can build reports that will give you insight into your SLA and escalations metrics.

In this article, we'll show you how to build a few commonly requested reports. The reports we'll outline below cover the following:

• Overdue tickets
• Escalated tickets, by department
• Average first response time for tickets, overall 
• Average response time for tickets created today
• Breakdown of tickets that have been waiting on a reply for 24 hours, 24-48 hours, and more than 72 hours
• Average response time for ticket posts created during working hours

Building SLA and escalation reports with KQL

In order to create each report, you'll follow the same steps: 

1. Log into the staff control panel.
2. Click on Reports in the top navigation bar.
3. Click the New Report link that appears below it. 
4. In the 'New Report' pop-up that appears, enter a descriptive title for your report in the Title field.
5. From the Primary Source pulldown select 'Tickets'.
6. Click the Next button to create your blank report. 
7. On the next page, you'll see a text area with the word 'SELECT' in it. This is where you'll enter the KQL query that will pull your report.
8. From 'KQL Queries' list at the bottom of this article, find the report you want to create, copy the query code, and paste it into the text area on your report page.
9. Click the Run report button to see your results. 

KQL Queries

Overdue tickets

SELECT 'Tickets.Ticket Mask ID', 'Tickets.Status', IF(('Tickets.Reply Due Date' <= DateNow()) AND ('Tickets.Reply Due Date' != ''), 'Overdue', 'Not Overdue') as 'OVERDUE Status' FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() AND 'Tickets.Is Resolved' = '0'

Escalated tickets, by department

SELECT SUM(IF(('Tickets.Is Escalated' = '1') ,1,0)) AS Escalated , SUM(IF(('Tickets.Is Escalated' = '0') ,1,0)) AS 'Non-Escalated' FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() GROUP BY 'Tickets.Department'

NOTE: You can also set up an SLA based on 'Ticket Priority' or 'Ticket Status' by specifying it in the criteria. This helps in automatically changing the SLA plan when a ticket is moved from one priority to another or from one status to another.

Average first response time for tickets, overall

SELECT AVG('Tickets.First Response Time') FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() AND  'Tickets.First Response Time' > '0'

NOTE: We do have default reports available for average of first response time that you can find by navigating to Staff Control Panel > Reports > Manage Reports > search with 'First Response'. It will list all related reports.

Average response time for tickets created today

SELECT AVG('Ticket Posts.Response Time') FROM 'Ticket Posts' WHERE 'Ticket Posts.Creation Date' = today() AND 'Ticket Posts.Creator' = 'Staff' AND 'Ticket Posts.Response Time' > '0' Group by 'Ticket Posts.Full Name'

Breakdown of tickets that have been waiting on a reply for 24 hours, 24-48 hours, and more than 72 hours

SELECT Sum(IF('Tickets.Last User Reply' <= '86400', 1,0)) as 'Within 24 hours', Sum(IF('Tickets.Last User Reply' > '86400' AND 'Tickets.Last User Reply' <= '172800', 1,0)) AS ' 24-48 hours', Sum(IF('Tickets.Last User Reply' > '259200', 1,0)) As 'More than 72 hours' FROM 'Tickets' WHERE 'Tickets.Status' = 'Open' AND 'Tickets.Creation Date'>= Date_Sub(NOW(), INTERVAL 5 Day)

Average response time for ticket posts created during working hours

SELECT AVG('Ticket Posts.SLA Response Time') FROM 'Ticket Posts' WHERE 'Ticket Posts.Creation Date' = ThisMonth() AND 'Ticket Posts.Creator' = 'Staff' AND 'Ticket Posts.Response Time' != '0' GROUP BY Y('Ticket Posts.Full Name')