Monthly Campaign Status Breakdown by List

1-- core.klaviyo.monthly_campaign_status_breakdown_by_list
2-- Monthly Campaign Status Breakdown by List. Generates a monthly breakdown of
3-- campaign status for a specific integration with Klaviyo. It retrieves the number
4-- of campaigns, the number of campaigns sent, the number of campaigns in draft and
5-- scheduled status, as well as other campaign statuses if needed. Additionally, it
6-- includes the list name, the number of lists used, and the last send time. The
7-- results are grouped by campaign month and list name, and ordered in descending
8-- order of the campaign month.
9--
10-- A campaign's audiences are the lists and segments in
11-- campaign_audiences_included. list_name is the list's name, or the segment's
12-- name when the audience is a segment. A campaign sent to several audiences is
13-- counted once under each. Statuses are compared in lower case.
14--
15-- From the weld.app template library, ported to the current synced schema. Not
16-- reconciled against Klaviyo; check the output against the source before relying
17-- on it.
18
19select
20    date_trunc(date(c.created_at), month) as campaign_month
21  , count(distinct c.id) as number_of_campaigns
22  , count(distinct case when lower(c.status) = 'sent' then c.id end) as sent_campaigns
23  , count(distinct case when lower(c.status) = 'draft' then c.id end) as draft_campaigns
24  , count(distinct case when lower(c.status) = 'scheduled' then c.id end) as scheduled_campaigns
25  , -- Add other campaign statuses as needed
26    coalesce(l.name, s.name) as list_name
27  , count(distinct a.value) as number_of_lists_used
28  , max(c.send_time) as last_sent_date
29from
30    {{raw.klaviyo.campaign}} c
31    left join {{raw.klaviyo.campaign_audiences_included}} a on c.id = a.campaign_id
32    left join {{raw.klaviyo.list}} l on a.value = l.id
33    left join {{raw.klaviyo.segment}} s on a.value = s.id
34group by
35    campaign_month
36  , list_name
37order by
38    campaign_month desc