Weld logo

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.
2    MonthlyTags as (
3        select
4            conversation_id
5          , tag_id
6          , date_trunc(created_at, month) month
7        from
8            {{raw.intercom.conversation}} c
9            left join {{raw.intercom.conversation_tag}} t on c.id = t.conversation_id
10    )
12    mt.month
13  , t.name as tag_name
14  , count(mt.conversation_id) as tag_count
16    MonthlyTags mt
17    join {{raw.intercom.tag}} t on mt.tag_id = t.id
18group by
19    mt.month
20  , t.name
21order by
22    mt.month desc
23  , tag_count desc;
Example of output from model:

|        month        |  tag_name    | tag_count |
|    2022-01-01       |   Support    |    100    |
|    2022-01-01       |   Sales      |    80     |
|    2021-12-01       |   Support    |    75     |
|    2021-12-01       |   Sales  |    60     |
|    2021-11-01       |   Sales      |    50     |
|    2021-11-01       |   Support    |    40     |

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.

Ready to start modeling your own intercom data?

Get started building your data warehouse with intercom and 100+ more apps and databases available.

Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
© 2024 Weld. All rights reserved.