jthandy

select 
  ad_performance.*,
  sessions.*
from ad_performance
left outer join sessions on ad_performance.id = sessions.ad_performance_id

jthandy

  select
    md5(
      coalesce(date_day::varchar, '') ||
      coalesce(destination_url, '') ||
      coalesce(utm_medium, '') ||
      coalesce(utm_source, '') ||
      coalesce(utm_campaign, '') ||
      coalesce(utm_term, '') ||
      coalesce(utm_content, '') ||
      coalesce(ad_group_id::varchar, '') ||

jthandy

with email_summary as (

  select *
  from {{env.schema}}.mailchimp_email_summary

)

select email_id, sum(clicked)::float / sum(opened)
  from email_summary
 group by 1

jthandy

with email_summary as (

  select *
  from {{env.schema}}.mailchimp_email_summary

), best_day_of_week as (

  select date_part(dow, sent_date), avg(opened::float)
  from email_summary
  group by 1

jthandy

with
gains as (
	select date_trunc('month', signup_date) as month, count(*) total_gains
	from {{env.schema}}.mailchimp_members
	group by date_trunc('month', signup_date)
),

hard_bounces as
(
	-- get the first hard bounce date for each email

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
)

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

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

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

jthandy

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