Monthly Tickets By Team
Extracts monthly ticket data for each team, including the team ID, team name, and the count of tickets. It uses the Intercom integration and joins the ticket and team tables based on the team assignee ID. The results are grouped by month, team ID, and team name, and ordered by month and ticket count in descending order.
- 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_tickets_by_team
2-- Monthly Tickets By Team. Extracts monthly ticket data for each team, including
3-- the team ID, team name, and the count of tickets. It uses the Intercom
4-- integration and joins the ticket and team tables based on the team assignee ID.
5-- The results are grouped by month, team ID, and team name, and ordered by month
6-- and ticket count in descending order.
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(created_at, month) as month
13 , team_assignee_id as team_id
14 , te.name as team_name
15 , count(ti.id) as ticket_count
16from
17 {{raw.intercom.ticket}} ti
18 left join {{raw.intercom.team}} te on ti.team_assignee_id = te.id
19group by
20 month
21 , team_id
22 , te.name
23order by
24 month desc
25 , ticket_count descThis 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 Tickets By Team" is designed to retrieve monthly ticket data from the Intercom integration. This SQL query allows you to analyze ticket distribution and activity within different teams over time. The query begins by selecting the truncated creation date of each ticket, representing the month in which the ticket was created. It also retrieves the team assignee ID, team name, and the count of tickets associated with each team. To gather this information, the query joins the "ticket" table from the Intercom integration with the "team" table using the team assignee ID as the common field. The results are then grouped by month, team ID, and team name, providing an overview of ticket distribution across teams for each month. The output is ordered by month in descending order, followed by the ticket count in descending order. This SQL template is useful for tracking ticket trends, identifying high-performing teams, and understanding workload distribution among different teams over time. By analyzing the output, you can gain insights into team performance, resource allocation, and potential areas for improvement within your support or customer service operations.
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.