Monthly Tickets By Team
1select
2 date_trunc(created_at, month) as month
3 , team_assignee_id as team_id
4 , te.name as team_name
5 , count(ti.id) as ticket_count
6from
7 {{raw.intercom.ticket}} ti
8 left join {{raw.intercom.team}} te on ti.team_assignee_id = te.id
9group by
10 month
11 , team_id
12 , te.name
13order by
14 month desc
15 , ticket_count desc
+---------------------+---------+----------------+--------------+
| month | team_id | team_name | ticket_count |
+---------------------+---------+----------------+--------------+
| 2022-01-01 | 1 | Support Team | 150 |
| 2022-01-01 | 2 | Sales Team | 120 |
| 2022-01-01 | 3 | Marketing | 90 |
| 2021-12-01 | 1 | Support Team | 130 |
| 2021-12-01 | 2 | Sales Team | 110 |
| 2021-12-01 | 3 | Marketing | 80 |
+---------------------+---------+----------------+--------------+
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.
