Weld logo
klaviyo logo
Klaviyo

Monthly Campaign Status Breakdown by List

Generates a monthly breakdown of campaign status for a specific integration with Klaviyo. It retrieves the number of campaigns, the number of campaigns sent, the number of campaigns in draft and scheduled status, as well as other campaign statuses if needed. Additionally, it includes the list name, the number of lists used, and the last sent date. The results are grouped by campaign month, list name, and template name, and ordered in descending order of the campaign month.
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
Example of output from model:
+-----------------+---------------------+----------------+----------------------+----------------------+-----------------+------------------------+---------------------+
| 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.

Ready to start modeling your own klaviyo data?

Get started building your data warehouse with klaviyo and 100+ more apps and databases available.

klaviyo logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.