Recent searches
No recent searches

Support Operations Manager
Joined Jun 28, 2022
·
Last activity Apr 18, 2023
Following
0
Followers
0
Total activity
5
Votes
2
Subscriptions
2
ACTIVITY OVERVIEW
BADGES
ARTICLES
POSTS
COMMUNITY COMMENTS
ARTICLE COMMENTS
ACTIVITY OVERVIEW
Latest activity by Support Operations Manager
Support Operations Manager commented,
+1 - would love to see this built into ZD Explore.
For anyone who is trying to find a workaround, I've added an example SQL query based on data from the Zendesk Tickets endpoint. If you are sending your Zendesk data to a warehouse / business intelligence tool, you can construct reporting on Problem / Incident tickets outside of Zendesk Explore.
The result of the below query will create a list of Problem Tickets, with the Created Date, Updated Date, Status, Subject, Assignee, as well as a sum of Total Handle Time custom field (created by the Zendesk Time Tracking App) for all Incident tickets that are linked to the Problem ticket, a calculation for Days Open, and a count for the # of Incidents linked.
Still hoping this can be implemented in Explore eventually, but hope this saves someone some time in the future as a starting point.
SELECT
A.PROBLEM_ID as "PROBLEM ID",
B.TS_CREATED_AT as "CREATED",
B.TS_UPDATED_AT as "UPDATED",
B.STATUS,
B.SUBJECT,
USER_NAME as "Assignee",
DATEDIFF(day, B.TS_CREATED_AT, GETDATE()) as "DAYS OPEN",
SUM(A.CUSTOM_TOTAL_TIME_SPENT_SEC) / 60 as "HANDLE TIME (m)",
COUNT(DISTINCT A.ZENDESK_TICKET_ID) as "INCIDENT COUNT"
FROM
DATA_WAREHOUSE.ZENDESK_TICKETS as A,
DATA_WAREHOUSE.ZENDESK_TICKETS as B
LEFT JOIN
DATA_WAREHOUSE.ZENDESK_USERS ON B.ASSIGNEE_ID = ZENDESK_USER_ID
WHERE
B.ZENDESK_TICKET_ID = A.PROBLEM_ID
AND A.TYPE = 'incident'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 9 DESC
If you are using a BI tool such as Redash, you can embed the results into a Google Sheet and create pivot tables (Recently created, Recently Updated, Most Incidents, Trending (Last 30d), Most Handle Time, Longest Open).
View comment · Edited Apr 07, 2023 · Support Operations Manager
0
Followers
0
Votes
0
Comments