Monthly User Engagement per Campaign

1-- core.klaviyo.monthly_user_engagement_per_campaign
2-- Monthly User Engagement per Campaign. Calculates the monthly user engagement per
3-- campaign using data from Klaviyo integration. It extracts the year, month,
4-- campaign ID, event name, and counts the number of unique persons for specific
5-- events. It then aggregates the counts for each event type and campaign, grouping
6-- them by year, month, campaign ID, and campaign name. The final result is ordered
7-- by year, month, and campaign name.
8--
9-- The event name comes from the metric table and persons are counted by Klaviyo
10-- profile id. Events are tied to a campaign through Klaviyo's attribution data
11-- (the event's campaign_id).
12--
13-- From the weld.app template library, ported to the current synced schema. Not
14-- reconciled against Klaviyo; check the output against the source before relying
15-- on it.
16
17WITH MonthlyUniquePersonsPerEvent AS (
18    SELECT
19        EXTRACT(YEAR FROM e.timestamp) AS event_year,
20        EXTRACT(MONTH FROM e.timestamp) AS event_month,
21        e.campaign_id,
22        m.name AS event_name,
23        COUNT(DISTINCT e.profile_id) AS unique_persons
24    FROM
25        {{raw.klaviyo.event}} e
26    JOIN
27        {{raw.klaviyo.metric}} m ON m.id = e.metric_id
28    WHERE
29        m.name IN ('Dropped Email', 'Bounced Email', 'Received Email', 'Unsubscribed', 'Clicked Email', 'Opened Email')
30        AND e.campaign_id IS NOT NULL
31    GROUP BY
32        event_year, event_month, event_name, e.campaign_id
33)
34
35SELECT
36    mupe.event_year,
37    mupe.event_month,
38    c.id AS campaign_id,
39    c.name AS campaign_name,
40    SUM(CASE WHEN mupe.event_name = 'Dropped Email' THEN unique_persons ELSE 0 END) AS dropped_email_count,