Monthly Campaign Status Breakdown by List
1select
2 date_trunc(c.created, month) as campaign_month
3 , count(distinct c.id) as number_of_campaigns
4 , sum(
5 case
6 when c.status = 'sent' then 1
7 else 0
8 end
9 ) as sent_campaigns
10 , sum(
11 case
12 when c.status = 'draft' then 1
13 else 0
14 end
15 ) as draft_campaigns
16 , sum(
17 case
18 when c.status = 'scheduled' then 1
19 else 0
20 end
21 ) as scheduled_campaigns -- Add other campaign statuses as needed
22 , l.list_name as list_name
23 , count(distinct cl.list_id) as number_of_lists_used
24 , max(c.sent_at) as last_sent_date
25from
26 {{raw.klaviyo.campaign}} c
27 left join {{raw.klaviyo.campaign_list}} cl on c.id = cl.campaign_id
28 left join {{raw.klaviyo.list}} l on cl.list_id = l.id
29 left join {{raw.klaviyo.email_template}} t on c.template_id = t.id
30group by
31 campaign_month
32 , l.list_name
33 , t.name
34order by
35 campaign_month desc+-----------------+---------------------+----------------+----------------------+----------------------+-----------------+------------------------+---------------------+
| campaign_month | number_of_campaigns | sent_campaigns | draft_campaigns | scheduled_campaigns | list_name | number_of_lists_used | last_sent_date |
+-----------------+---------------------+----------------+----------------------+----------------------+-----------------+------------------------+---------------------+
| 2022-06-01 | 10 | 7 | 2 | 1 | List A | 3 | 2022-05-30 |
| 2022-05-01 | 15 | 12 | 1 | 2 | List B | 4 | 2022-04-28 |
| 2022-04-01 | 8 | 6 | 1 | 1 | List C | 2 | 2022-03-29 |
+-----------------+---------------------+----------------+----------------------+----------------------+-----------------+------------------------+---------------------+The Monthly Campaign Status Breakdown by List SQL template is designed to provide insights into the performance of campaigns in Klaviyo, a popular email marketing integration. This SQL query retrieves data on various campaign statuses, such as sent, draft, and scheduled, and breaks them down by month and list name. By executing this SQL template, you can obtain the following information: 1. campaign_month: The month in which the campaign was created, truncated to the month level. 2. number_of_campaigns: The total number of distinct campaigns created within each month. 3. sent_campaigns: The count of campaigns that have been sent. 4. draft_campaigns: The count of campaigns that are still in draft status. 5. scheduled_campaigns: The count of campaigns that are scheduled for future sending. 6. list_name: The name of the list associated with each campaign. 7. number_of_lists_used: The total number of distinct lists used in the campaigns. 8. last_sent_date: The date of the most recent campaign sent. This SQL template is useful for gaining insights into campaign performance over time and understanding the distribution of campaigns across different lists. It allows you to analyze trends, identify the most active months, and track the usage of various campaign statuses. With this information, you can optimize your email marketing strategies and make data-driven decisions to improve campaign effectiveness.
