Monthly Conversations 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(co.id) as ticket_count FROM{{raw.intercom.conversation}} co
6 left join {{raw.intercom.team}} te on cast(co.team_assignee_id as string) = te.id
7group by
8 month
9 , team_id
10 , te.name
11order by
12 month desc
13 , ticket_count desc+---------------------+---------+-----------------+--------------+
| month | team_id | team_name | ticket_count |
+---------------------+---------+-----------------+--------------+
| 2022-01-01 | 123 | Support Team | 150 |
| 2022-01-01 | 456 | Sales Team | 120 |
| 2021-12-01 | 789 | Marketing | 100 |
+---------------------+---------+-----------------+--------------+The SQL template "Get Monthly Conversations By Team" is designed to retrieve information about the number of conversations per month, grouped by team. This template is integrated with Intercom, a customer messaging platform. The SQL code begins by selecting the truncated month from the "created_at" field as "month". It also selects the "team_assignee_id" as "team_id" and retrieves the corresponding team name from the "team" table. Additionally, it counts the number of conversations per team using the "count(co.id)" function. The SQL code then performs a left join between the "conversation" table and the "team" table, matching the team assignee IDs. This allows for retrieving the team names associated with each conversation. The results are grouped by month, team ID, and team name using the "group by" clause. The "order by" clause is used to sort the results in descending order by month and ticket count. This SQL template can be useful for analyzing conversation trends over time and identifying the teams that handle the most conversations. By visualizing the output, you can gain insights into the distribution of workload among different teams and track the overall volume of conversations on a monthly basis.
