Count of Tickets By State

Extracts the count of tickets grouped by month and ticket state from the Intercom integration. It uses the `date_trunc` function to truncate the `created_at` column to the month level. The result is then ordered by month in descending order, followed by the ticket count in descending order.

1select
2    date_trunc(created_at, month) month
3  , ticket_state
4  , count(id) as ticket_count
5from
6    {{raw.intercom.ticket}}
7group by
8    month
9  , ticket_state
10order by
11    month desc
12  , ticket_count desc;

Example of output from model:

+---------------------+--------------+--------------+
|        month        | ticket_state | ticket_count |
+---------------------+--------------+--------------+
| 2022-03-01 00:00:00 |   Completed  |     150      |
| 2022-03-01 00:00:00 |    Pending   |     100      |
| 2022-02-01 00:00:00 |   Completed  |     120      |
| 2022-02-01 00:00:00 |    Pending   |     80       |
| 2022-01-01 00:00:00 |   Completed  |     100      |
| 2022-01-01 00:00:00 |    Pending   |     50       |
+---------------------+--------------+--------------+

The SQL template "Count of Tickets By State" is designed to retrieve data from the Intercom integration. This SQL code calculates the number of tickets grouped by their state and the month they were created. The output includes three columns: "month" (representing the truncated creation date), "ticket_state" (representing the state of the ticket), and "ticket_count" (representing the count of tickets in each state for a given month). This SQL template can be useful for analyzing the distribution of tickets across different states over time. By aggregating the ticket data by month and state, it allows you to identify trends and patterns in ticket volume and state distribution. For example, you can determine which states have the highest ticket count or observe changes in ticket distribution over time. By visualizing the output of this SQL template, you can gain insights into the ticketing system's performance, identify bottlenecks in ticket resolution, and track the effectiveness of support efforts. This information can help optimize resource allocation, improve customer satisfaction, and streamline ticket management processes.

Count of Tickets By State

Ready to start modeling your own intercom data?