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.

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)