Skip to main content

How to create a report showing the impact of failed SLAs on Ticket satisfaction - Developer & Reporting / DPQL Examples - Deskpro Support

How to create a report showing the impact of failed SLAs on Ticket satisfaction

Authors list

Deskpro allows support teams to track 2 crucial Customer Service KPIs: CSAT (Customer Satisfaction Score) and SLA (Service Level Agreement).

CSAT can be tracked using the Satisfaction Survey in Deskpro while SLAs have their own feature which is called SLAs.

As part of the New Reports interface, it is now possible to combine two different queries into one by using the LAYER WITH DPQL data access layer. 

If you wish to create a graph displaying data on failed SLAs and positive ticket satisfaction to get a sense of how response times can affect customer feedback on support, you can use the following DPQL raw query:

SELECT DPQL_COUNT() AS 'Failed SLAs'

FROM tickets

WHERE tickets.date_created = %LAST_MONTH% AND tickets.ticket_slas.sla_status IN ('fail')

GROUP BY DPQL_DATE(tickets.ticket_slas.fail_date) AS 'Dates'

LAYER WITH

SELECT DPQL_COUNT() AS 'Positive Rating'

FROM ticket_feedback

WHERE ticket_feedback.date_created = %LAST_MONTH% AND ticket_feedback.rating = 1

GROUP BY DPQL_DATE(ticket_feedback.date_created)

This query will parse data for %LAST_MONTH%, but you can use different date placeholders to render information for the time period you are interested in. 

The best formats to display the results of this report are the Area, Lines and Bars charts.

The Lines and the Area chart look very similar as they facilitate the same kind of trend analysis.  The only difference is that the Area chart is filled with color. The key on the right-hand side of the charts shows that the yellow line and area represent the number of tickets which have failed the agreed SLAs and the orange line and area show the number of tickets which received a positive feedback from users or customers for the month of June 2018.

The Bar chart is great to compare figures and stats.

All of these 3 charts are quite insightful: at first glance, it is possible to notice how met SLAs have an impact on positive rating when it comes to ticket satisfaction. On the other hand, on days when SLAs have failed, the amount of positive feedback decreases or is totally absent.

If you wish to create a report on how failed SLAs can have an impact on negative ratings instead, you can use this slightly different raw query:

SELECT DPQL_COUNT() AS 'Failed SLAs'

FROM tickets

WHERE tickets.date_created = %LAST_MONTH% AND tickets.ticket_slas.sla_status IN ('fail')

GROUP BY DPQL_DATE(tickets.ticket_slas.fail_date) AS 'Dates'

LAYER WITH

SELECT DPQL_COUNT() AS 'Negative Rating'

FROM ticket_feedback

WHERE ticket_feedback.date_created = %LAST_MONTH% AND ticket_feedback.rating = - 1

GROUP BY DPQL_DATE(ticket_feedback.date_created)


Βοηθητικό Δεν βοηθάει

7 από 10 χρήστες πιστεύουν ότι αυτή η σελίδα βοηθάει

Προσθήκη σχολίου

Please log in or register to submit a comment.

Ξεχάσατε τον κωδικό πρόσβασης;