Recent searches


No recent searches

Support Operations Manager's Avatar

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

Latest activity by Support Operations Manager

Support Operations Manager commented,

Community comment Feedback - Reporting and analytics (Explore)

+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