mailchimp_email_summary.sql

with 
sends as (
    select campaign_id, email_id, sent_date
    from {{env.schema}}.mailchimp_sends
),

hard_bounces as (
    select campaign_id, email_id, min(bounced_date) as hard_bounced_date
    from {{env.schema}}.mailchimp_bounces
    where bounce_type = 'hard'
    group by campaign_id, email_id
),

opens as (
    select
        campaign_id, email_id, min(opened_date) as first_opened_date,
        max(opened_date) as last_opened_date, count(*) as total_opens
    from {{env.schema}}.mailchimp_opens
    group by campaign_id, email_id
),

clicks as (
    select
        campaign_id, email_id, min(clicked_date) as first_clicked_date,
        max(clicked_date) as last_clicked_date, count(*) as total_clicks
    from {{env.schema}}.mailchimp_clicks
    group by campaign_id, email_id
),

unsubscribes as (
    select campaign_id, email_id, unsubscribed_date
    from {{env.schema}}.mailchimp_unsubscribes
)

select
    s.campaign_id, s.email_id, sent_date, hard_bounced_date, first_opened_date,
    last_opened_date, total_opens, first_clicked_date, last_clicked_date,
    total_clicks, unsubscribed_date
from sends s
left outer join hard_bounces b
    on s.email_id = b.email_id and
    s.campaign_id = b.campaign_id
left outer join opens o
    on s.email_id = o.email_id and
    s.campaign_id = o.campaign_id
left outer join clicks c
    on s.email_id = c.email_id and
    s.campaign_id = c.campaign_id
left outer join unsubscribes u
    on s.email_id = u.email_id and
    s.campaign_id = u.campaign_id
order by email_id, sent_date