Weld logo
intercom logo
Intercom

Monthly Ticket Resolution Time

Extracts the monthly ticket resolution time for a specific integration (intercom). It selects the month, team ID, team name, and average resolution time in days. It joins the ticket and team tables, filters for resolved tickets, groups the results by month and team, and sorts them in descending order by month and ascending order by average 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
Example of output from model:

+---------------------+---------+-----------------+------------------------+
|        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.

Ready to start modeling your own intercom data?

Get started building your data warehouse with intercom and connect all your apps and databases.

intercom logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.