Monthly Flow Events
1with
2 MonthlyFlowEvents as (
3 select
4 date_trunc(e.timestamp, month) as month
5 , e.flow_id
6 , count(distinct e.id) as total_events_triggered
7 , count(distinct e.person_id) as distinct_persons_triggered
8 from
9 {{raw.klaviyo.event}} e
10 where
11 e.flow_id is not null
12 group by
13 month
14 , e.flow_id
15 )
16select
17 mfe.month
18 , f.id as flow_id
19 , f.name as flow_name
20 , f.status as flow_status
21 , f.trigger_type as trigger_type
22 , f.trigger_filter as trigger_filter
23 , f.customer_filter as customer_filter
24 , mfe.total_events_triggered
25 , mfe.distinct_persons_triggered
26from
27 MonthlyFlowEvents mfe
28 join {{raw.klaviyo.flow}} f on mfe.flow_id = f.id
29order by
30 mfe.month desc
31 , f.name;+---------------------+---------+-------------------+-------------+--------------+----------------+-----------------+--------------------------+----------------------------+
| month | flow_id | flow_name | flow_status | trigger_type | trigger_filter | customer_filter | total_events_triggered | distinct_persons_triggered |
+---------------------+---------+-------------------+-------------+--------------+----------------+-----------------+--------------------------+----------------------------+
| 2022-10-01 00:00:00 | 123 | Welcome Email | Active | On Event | New Signup | All Users | 500 | 400 |
| 2022-10-01 00:00:00 | 456 | Abandoned Cart | Active | On Event | Cart Abandon | All Users | 1000 | 800 |
| 2022-09-01 00:00:00 | 123 | Welcome Email | Active | On Event | New Signup | All Users | 300 | 250 |
| 2022-09-01 00:00:00 | 456 | Abandoned Cart | Active | On Event | Cart Abandon | All Users | 800 | 600 |
+---------------------+---------+-------------------+-------------+--------------+----------------+-----------------+--------------------------+----------------------------+The Monthly Flow Events SQL template is designed to provide insights into the flow events triggered within a given month using Klaviyo integration. This SQL model calculates the total number of events triggered and the number of distinct persons triggered for each flow within a specific month. By utilizing the Klaviyo integration, the SQL code retrieves data from the `{{raw.klaviyo.event}}` table. It filters out events where the `flow_id` is not null, ensuring that only flow events are considered. The `date_trunc` function is used to group the events by month. The resulting output includes the month, flow ID, flow name, flow status, trigger type, trigger filter, customer filter, total events triggered, and distinct persons triggered. This information can be used to analyze the performance of different flows over time and identify trends or patterns in customer engagement. The SQL code is structured to join the MonthlyFlowEvents table with the `{{raw.klaviyo.flow}}` table, allowing for additional details about each flow to be included in the output. The final result is ordered by month in descending order and then by flow name. Overall, this SQL template provides a powerful tool for analyzing flow events within a specific month, enabling businesses to gain valuable insights into customer behavior and optimize their marketing strategies accordingly.
