Weld logo
klaviyo logo
Klaviyo

Monthly Flow Events

Generates a report of monthly flow events from the Klaviyo integration. It calculates the total number of events triggered and the number of distinct persons triggered for each flow in a given month. The result includes the month, flow ID, flow name, flow status, trigger type, trigger filter, customer filter, total events triggered, and distinct persons triggered, ordered by month and flow name.
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;
Example of output from model:
+---------------------+---------+-------------------+-------------+--------------+----------------+-----------------+--------------------------+----------------------------+
|        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.

Ready to start modeling your own klaviyo data?

Get started building your data warehouse with klaviyo and connect all your apps and databases.

klaviyo logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.