Monthly User Engagement per Campaign
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+------------+-------------+-------------+----------------------+---------------------+-----------------------+-------------------+---------------------+-------------------+
| 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.
