You can retrieve satisfaction ratings in your own custom reports. For historical reasons the table that stores ratings is called ticket_feedback (but note that the satisfaction rating system is a completely separate concept from the Feedback section on the portal).
Field Name | Data Type |
date_created | datetime |
id | number |
message | string |
message_id | number |
person_id | number |
rating | number |
person | Person |
ticket_id | number |
ticket | Ticket |
ticket_message | Ticket Message |
The possible values of rating are 1 (positive), 0 (neutral), -1 (negative). Here's a simple example report:
SELECT ticket_feedback.id, ticket_feedback.rating
FROM ticket_feedback
WHERE ticket_feedback.ticket.date_resolved = %LAST_MONTH%
SPLIT BY ticket_feedback.ticket.agent
Here's a more advanced report which shows how many of each rating each agent received last month:
SELECT DPQL_COUNT () AS 'NUMBER’
FROM ticket_feedback
WHERE ticket_feedback.ticket.date_resolved = %LAST_MONTH%
SPLIT BY ticket_feedback.ticket.agent
GROUP BY ticket_feedback.rating
The below report gives you access to the ratings and comments left:
SELECT ticket_feedback.id, ticket_feedback.date_created, ticket_feedback.rating, ticket_feedback.message
FROM ticket_feedback
WHERE ticket_feedback.ticket.date_resolved = %LAST_MONTH%
SPLIT BY ticket_feedback.ticket.agent
Finally the report below gives you a matrix table containing a count per rating per agent. Note this uses the ticket messages table in the GROUP BY and WHERE clauses. This is because if we used the tickets field, if the agent was then changed after the satisfaction response was recieved, the satisfaction feedback would be assigned to the current ticket asignee.
SELECT DPQL_COUNT() FROM ticket_feedback
WHERE ticket_feedback.date_created = %LAST_MONTH%
AND ticket_feedback.ticket_message.person.is_agent = 1 GROUP BY DPQL_MATRIX(REPLACE(REPLACE(REPLACE(ticket_feedback.rating, '-1', 'Negative'), '0', 'Neutral'), '1', 'Positive'), ticket_feedback.ticket_message.person)
Comment (1)