Monthly Tags By Conversation
1with
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 )
11select
12 mt.month
13 , t.name as tag_name
14 , count(mt.conversation_id) as tag_count
15from
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;
+---------------------+--------------+-----------+
| 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.
