Weld
Start free
Intercom model

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.

See the SQLAll Intercom models
Grain
See the notes
Built from
  1. 2
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Intercom
conversationteam
Ships as
Weld and dbt SQL, open source on GitHub
core.intercom.monthly_conversations_by_team
1-- core.intercom.monthly_conversations_by_team
2-- Monthly Conversations By Team. Extracts monthly conversation data by team from
3-- the Intercom integration. It selects the month, team ID, team name, and ticket
4-- count from the conversation table. It then joins the conversation table with the
5-- team table and groups the results by month, team ID, and team name, ordering them
6-- by month 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(co.id) as ticket_count
16    FROM{{raw.intercom.conversation}} co
17    left join {{raw.intercom.team}} te on cast(co.team_assignee_id as string) = te.id
18    group by
19    month
20    , team_id
21    , te.name
22    order by
23    month desc
24    , ticket_count desc

This 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().

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.intercom.conversation
  • raw.intercom.team

More Intercom models

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.

Start for freeTalk to us