Monthly Average Conversations Rating
1select
2 date_trunc(conversation_rating_created_at, month) as month
3 , avg(conversation_rating_value) as average_rating
4from
5 {{raw.intercom.conversation}}
6where
7 conversation_rating_value is not null
8group by
9 month
10order by
11 month desc
+---------------------+----------------+
| month | average_rating |
+---------------------+----------------+
| 2022-06-01 | 4.5 |
| 2022-05-01 | 4.2 |
| 2022-04-01 | 4.8 |
| 2022-03-01 | 4.6 |
+---------------------+----------------+
The SQL template "Get Monthly Average Conversations Rating" is designed to retrieve the average rating of conversations on a monthly basis. It utilizes the integration with Intercom, a customer messaging platform. The SQL code begins by selecting the month and calculating the average conversation rating using the `date_trunc` and `avg` functions respectively. It then specifies the table `{{raw.intercom.conversation}}` from which the data is retrieved. The `where` clause ensures that only conversations with a non-null rating are considered. The results are grouped by month and ordered in descending order. This SQL template is useful for analyzing the average rating of conversations over time. It allows you to gain insights into the performance and satisfaction of conversations with customers. By aggregating the ratings on a monthly basis, you can identify trends, patterns, and potential areas for improvement in your customer interactions.
