Weld logo
intercom logo
Intercom

Monthly Conversations By Team

Extracts monthly conversation data by team from the Intercom integration. It selects the month, team ID, team name, and ticket count from the conversation table. It then joins the conversation table with the team table and groups the results by month, team ID, and team name, ordering them 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(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
Example of output from model:
+---------------------+---------+-----------------+--------------+
|        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.

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.