Weld
Start free
Intercom model

Monthly Tags By Conversation

This SQL template extracts monthly tags by conversation from the Intercom integration. It uses a common table expression (CTE) to join the conversation and conversation_tag tables, and then retrieves the month, tag name, and tag count for each conversation. The results are grouped by month and tag name, and ordered by month in descending order.

See the SQLAll Intercom models
Grain
See the notes
Built from
  1. 3
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Intercom
conversationconversation_tagtag
Ships as
Weld and dbt SQL, open source on GitHub
core.intercom.monthly_conversation_tags
1-- core.intercom.monthly_conversation_tags
2-- Monthly Tags By Conversation. This SQL template extracts monthly tags by
3-- conversation from the Intercom integration. It uses a common table expression
4-- (CTE) to join the conversation and conversation_tag tables, and then retrieves
5-- the month, tag name, and tag count for each conversation. The results are grouped
6-- by month and tag name, and ordered by month 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
11with
12MonthlyTags as (
13select
14conversation_id
15, tag_id
16, date_trunc(created_at, month) month
17from
18{{raw.intercom.conversation}} c
19left join {{raw.intercom.conversation_tag}} t on c.id = t.conversation_id
20)
21select
22mt.month
23, t.name as tag_name
24, count(mt.conversation_id) as tag_count
25from
26MonthlyTags mt
27join {{raw.intercom.tag}} t on mt.tag_id = t.id
28group by
29mt.month
30, t.name
31order by
32mt.month desc
33, tag_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 Tags By Conversation" is designed to retrieve monthly tag information from the Intercom integration. This SQL model allows you to analyze and understand the distribution of tags used in conversations over time. The SQL code begins by creating a temporary table called "MonthlyTags" using a common table expression (CTE). This table includes the conversation ID, tag ID, and the month in which the conversation was created. The data is obtained by joining the "conversation" table from the Intercom integration with the "conversation_tag" table. Next, the SQL code selects the month, tag name, and the count of conversation IDs from the "MonthlyTags" table. It joins the "tag" table from the Intercom integration based on the tag ID. The result is grouped by month and tag name, and ordered by month in descending order and tag count in descending order. This SQL template can be useful for various insights, such as identifying the most commonly used tags in conversations over time, tracking changes in tag usage patterns, and analyzing the distribution of tags across different months. By visualizing the output of this SQL, you can gain valuable insights into how tags are utilized within your Intercom conversations and make data-driven decisions based on the tag distribution.

Raw tables it reads

  • raw.intercom.conversation
  • raw.intercom.conversation_tag
  • raw.intercom.tag

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