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.
- Grain
- See the notes
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from Intercom
- teamticket
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.intercom.monthly_ticket_resolution_time
2-- Monthly Ticket Resolution Time. Extracts the monthly ticket resolution time for a
3-- specific integration (intercom). It selects the month, team ID, team name, and
4-- average resolution time in days. It joins the ticket and team tables, filters for
5-- resolved tickets, groups the results by month and team, and sorts them in
6-- descending order by month and ascending order by average resolution time.
7--
8-- From the weld.app template library. Not reconciled against Intercom;
9-- check the output against the source before relying on it.
10
11select
12 date_trunc(t.created_at, month) as month
13 , t.team_assignee_id as team_id
14 , te.name as team_name
15 , avg(date_diff(t.updated_at, t.created_at, day)) as avg_resolution_time_days
16 FROM{{raw.intercom.ticket}} t
17 left join {{raw.intercom.team}} te on cast(t.team_assignee_id as string) = te.id
18where
19 ticket_state = 'resolved'
20group by
21 month
22 , team_id
23 , name
24 ORDER by month desc
25 , avg_resolution_time_days ascThis model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.intercom.team
- raw.intercom.ticket
Put Intercom in front
of your agents.
Connect Intercom, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.