Monthly Flow Events

1-- core.klaviyo.monthly_flow_events
2-- Monthly Flow Events. Generates a report of monthly flow events from the Klaviyo
3-- integration. It calculates the total number of events triggered and the number of
4-- distinct persons triggered for each flow in a given month. The result includes
5-- the month, flow ID, flow name, flow status, trigger type, total events triggered,
6-- and distinct persons triggered, ordered by month and flow name.
7--
8-- Events are tied to a flow through Klaviyo's attribution data (the event's
9-- flow_id). Persons are counted by Klaviyo profile id.
10--
11-- From the weld.app template library, ported to the current synced schema. Not
12-- reconciled against Klaviyo; check the output against the source before relying
13-- on it.
14
15WITH MonthlyFlowEvents AS (
16    SELECT
17        date_trunc(date(e.timestamp), month) AS month,
18        e.flow_id,
19        COUNT(DISTINCT e.id) AS total_events_triggered,
20        COUNT(DISTINCT e.profile_id) AS distinct_persons_triggered
21    FROM
22       {{raw.klaviyo.event}} e
23    WHERE
24        e.flow_id IS NOT NULL
25    GROUP BY
26       month, e.flow_id
27)
28
29SELECT
30    mfe.month,
31    f.id AS flow_id,
32    f.name AS flow_name,
33    f.status AS flow_status,
34    f.trigger_type AS trigger_type,
35    mfe.total_events_triggered,
36    mfe.distinct_persons_triggered
37FROM
38    MonthlyFlowEvents mfe
39JOIN
40   {{raw.klaviyo.flow}} f ON mfe.flow_id = f.id