jthandy

data as (

  select *,
    case
      when first_payment = 1
        then 'new'
      when last_payment = 1
        and dateadd('month', 1, period_end) < current_date
        then 'churn'
      when change > 0

jthandy

news as (

  select date_month, sum(total) as value
  from data
  where revenue_category = 'new'
  group by 1

), renewals as (

  select date_month, sum(renewal_component_of_change) as value

jthandy

select all_months.date_month,
  news.value as new,
  renewals.value as renewal,
  prepaids.value as committed,
  churns.value * -1 as churned,
  upgrades.value as upgrades,
  downgrades.value as downgrades
from all_months
  left outer join news on all_months.date_month = news.date_month
  left outer join renewals on all_months.date_month = renewals.date_month

jthandy

select a.campaign_id, a.email_id, sent_date
from {{env.schema}}.mailchimp_sent_to a
-- add information about when the campaign was sent
inner join {{env.schema}}.mailchimp_campaigns b
	on a.campaign_id = b.campaign_id
	and a.list_id = b.list_id

jthandy

select campaign_id, email_id, action_date as opened_date
from {{env.schema}}.mailchimp_email_actions
where action = 'open'

jthandy

select campaign_id, email_id, action_date as clicked_date
from {{env.schema}}.mailchimp_email_actions
where action = 'click'

jthandy

select campaign_id, email_id, "timestamp" as unsubscribed_date
from demo_data.mailchimp_unsubscribes

jthandy

select a.campaign_id, a.email_id, action_date as bounced_date, status as bounce_type
from {{env.schema}}.mailchimp_email_actions a
inner join {{env.schema}}.mailchimp_sent_to b
	on a.campaign_id = b.campaign_id
	and a.email_id = b.email_id
where action = 'bounce'

jthandy

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'

jthandy

with events as (
  	select
	  	campaign_id, email_id, sent_date,
	  	decode(hard_bounced_date, null, 0, 1) as hard_bounced,
		decode(first_opened_date, null, 0, 1) as opened,
		decode(first_clicked_date, null, 0, 1) as clicked,
    	decode(unsubscribed_date, null, 0, 1) as unsubscribed
  	from {{env.schema}}.mailchimp_email_summary
)