Weld logo
intercom logo
Intercom

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.
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
Example of output from model:

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

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.