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.
- Grain
- See the notes
- Built from
- 1Raw tables
- 1Core
- 1Analytics
- Reads from Intercom
- ticket
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.intercom.monthly_ticket_by_state
2-- Count of Tickets By State. Extracts the count of tickets grouped by month and
3-- ticket state from the Intercom integration. It uses the `date_trunc` function to
4-- truncate the `created_at` column to the month level. The result is then ordered
5-- by month in descending order, followed by the ticket count in descending order.
6--
7-- From the weld.app template library. Not reconciled against Intercom;
8-- check the output against the source before relying on it.
9
10select
11 date_trunc(created_at, month) month
12 , ticket_state
13 , count(id) as ticket_count
14from
15 {{raw.intercom.ticket}}
16group by
17 month
18 , ticket_state
19order by
20 month desc
21 , ticket_count descThis 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().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.intercom.ticket
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.