Monthly Ticket Resolution Time
1select
2 date_trunc(t.created_at, month) as month
3 , t.team_assignee_id as team_id
4 , te.name as team_name
5 , avg(date_diff(t.updated_at, t.created_at, day)) as avg_resolution_time_days FROM{{raw.intercom.ticket}} t
6 left join {{raw.intercom.team}} te on cast(t.team_assignee_id as string) = te.id
7where
8 ticket_state = 'resolved'
9group by
10 month
11 , team_id
12 , name
13order by
14 month desc
15 , avg_resolution_time_days asc
+---------------------+---------+-----------------+------------------------+
| month | team_id | team_name | avg_resolution_time_days |
+---------------------+---------+-----------------+------------------------+
| 2022-03-01 00:00:00 | 1 | Support Team | 2.5 |
| 2022-03-01 00:00:00 | 2 | Sales Team | 3.2 |
| 2022-02-01 00:00:00 | 1 | Support Team | 2.8 |
| 2022-02-01 00:00:00 | 2 | Sales Team | 3.5 |
+---------------------+---------+-----------------+------------------------+
The SQL template "Get Monthly Ticket Resolution Time" is designed to retrieve data related to the average resolution time of tickets on a monthly basis. This template is integrated with Intercom, a customer messaging platform, to fetch ticket information. The SQL code begins by selecting the following fields: the truncated creation date of the ticket as "month", the team assignee ID as "team_id", the name of the team as "team_name", and the average resolution time in days as "avg_resolution_time_days". The data is retrieved from the "intercom.ticket" table, with a left join to the "intercom.team" table based on the team assignee ID. The join is performed by casting the team assignee ID as a string and comparing it to the ID in the team table. To filter the results, only tickets in the "resolved" state are considered. The data is then grouped by month, team ID, and team name. The results are ordered in descending order by month and ascending order by average resolution time in days. This SQL template is useful for analyzing the average resolution time of tickets on a monthly basis, providing insights into team performance and identifying trends in ticket resolution efficiency.
