jthandy

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

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 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

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

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

plan_changes as (

  select
    *,
    lag(total) over (partition by customer order by date_month) as prior_month_total,
    total - coalesce(lag(total) over (partition by customer order by date_month), 0) as change,
    lag(period_end) over (partition by customer order by date_month) as prior_month_period_end
  from invoices

),

jthandy

with invoices as (

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

), all_months as (

  select distinct date_month from invoices

),

jthandy

select date_month, d.customer, period_start, period_end,
  "interval" as period,
  case "interval"
    when 'yearly'
      then coalesce(i.total, 0)::float / 12 / 100
    else
      coalesce(i.total, 0)::float / 100
  end as total,
  case min(date_month) over(partition by d.customer)

jthandy

customers as (

  select customer, min(period_start) as active_from, max(period_end) as active_to
  from invoices
  where period_start <= current_date
  group by customer

), customer_dates as (

  select m.date_month, c.customer

jthandy

days as (

  select (min(period_start) over () + row_number() over ())::date as date_day
  from invoices

), months as (

  select distinct date_trunc('month', date_day)::date as date_month
  from days
  where date_day <= current_date