Weld logo
klaviyo logo
Klaviyo

Monthly User Engagement per Campaign

Calculates the monthly user engagement per campaign using data from Klaviyo integration. It extracts the year, month, campaign ID, event name, and counts the number of unique persons for specific events. It then aggregates the counts for each event type and campaign, grouping them by year, month, campaign ID, and campaign name. The final result is ordered by year, month, and campaign name.
1with
2    MonthlyUniquePersonsPerEvent as (
3        select
4            extract(
5                year
6                from
7                    e.timestamp
8            ) as event_year
9          , extract(
10                month
11                from
12                    e.timestamp
13            ) as event_month
14          , e.campaign_id
15          , e.event_name
16          , count(distinct e.person_id) as unique_persons
17        from
18            {{raw.klaviyo.event}} e
19        where
20            e.event_name in (
21                'Dropped Email'
22              , 'Bounced Email'
23              , 'Received Email'
24              , 'Unsubscribed'
25              , 'Clicked Email'
26              , 'Opened Email'
27            )
28            and e.campaign_id is not null
29        group by
30            event_year
31          , event_month
32          , e.event_name
33          , e.campaign_id
34    )
35select
36    mupe.event_year
37  , mupe.event_month
38  , c.id as campaign_id
39  , c.name as campaign_name
40  , sum(
41        case
42            when mupe.event_name = 'Dropped Email' then unique_persons
43            else 0
44        end
45    ) as dropped_email_count
46  , sum(
47        case
48            when mupe.event_name = 'Bounced Email' then unique_persons
49            else 0
50        end
51    ) as bounced_email_count
52  , sum(
53        case
54            when mupe.event_name = 'Received Email' then unique_persons
55            else 0
56        end
57    ) as received_email_count
58  , sum(
59        case
60            when mupe.event_name = 'Unsubscribed' then unique_persons
61            else 0
62        end
63    ) as unsubscribed_count
64  , sum(
65        case
66            when mupe.event_name = 'Clicked Email' then unique_persons
67            else 0
68        end
69    ) as clicked_email_count
70  , sum(
71        case
72            when mupe.event_name = 'Opened Email' then unique_persons
73            else 0
74        end
75    ) as opened_email_count
76from
77    MonthlyUniquePersonsPerEvent mupe
78    join {{raw.klaviyo.campaign}} c on mupe.campaign_id = c.id
79group by
80    mupe.event_year
81  , mupe.event_month
82  , c.id
83  , c.name
84order by
85    mupe.event_year desc
86  , mupe.event_month desc
87  , c.name
Example of output from model:
+------------+-------------+-------------+----------------------+---------------------+-----------------------+-------------------+---------------------+-------------------+
| event_year | event_month | campaign_id |    campaign_name     | dropped_email_count | bounced_email_count  | received_email_count | unsubscribed_count | clicked_email_count |
+------------+-------------+-------------+----------------------+---------------------+---------------------+-----------------------+-------------------+---------------------+
|   2022     |     12      |     1001    |   Winter Campaign    |         50          |         20          |          200          |        10         |         150         |
|   2022     |     12      |     1002    |   Holiday Special    |         30          |         10          |          150          |        5          |         100         |
|   2022     |     11      |     1001    |   Winter Campaign    |         40          |         15          |          180          |        8          |         120         |
|   2022     |     11      |     1002    |   Holiday Special    |         25          |         8           |          130          |        3          |         80          |
+------------+-------------+-------------+----------------------+---------------------+---------------------+-----------------------+-------------------+---------------------+

The SQL template "Monthly User Engagement per Campaign" is designed to analyze user engagement metrics for different campaigns in a monthly timeframe. This template is integrated with Klaviyo, a popular email marketing platform. The SQL code begins by creating a common table expression (CTE) called "MonthlyUniquePersonsPerEvent". This CTE extracts the year, month, campaign ID, event name, and counts the number of unique persons for specific events such as "Dropped Email", "Bounced Email", "Received Email", "Unsubscribed", "Clicked Email", and "Opened Email". The data is retrieved from the Klaviyo event table, filtering out events without a campaign ID. The main query then joins the "MonthlyUniquePersonsPerEvent" CTE with the Klaviyo campaign table using the campaign ID. It calculates the sum of unique persons for each event type, grouping the results by year, month, campaign ID, and campaign name. The final result is ordered by descending year, month, and campaign name. This SQL template is useful for tracking and analyzing user engagement metrics for different email campaigns. By aggregating the unique person counts for each event type, it provides insights into the effectiveness of email campaigns. Marketers can identify trends, compare engagement across campaigns, and make data-driven decisions to optimize their email marketing strategies.

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.