select purchase_month, utm_source, hear_about_us, sum("Purchases") as "Purchases"
--, sum("Net Sales") as "Net Sales", sum("GMV") as "GMV", sum(consultations) as "Consultations"
from
	(
		select utm_source, hear_about_us,
			case
				when tv_user = true then 'TV'
				when utm_source is null and hear_about_us is null then 'Organic (Unknown)'
				when utm_source is null and hear_about_us is not null then hear_about_us || ' (User provided)'
				when utm_source in ('Google') and utm_campaign like '%Branded%' and google_branded_search is not null then google_branded_search
				when utm_source in ('Google') and utm_campaign like '%Branded%' then 'Google: Branded'
				when utm_source in ('Google') then 'Google: Non-branded'
				else utm_source end as "Source",
			purchase_month,
			purchase_month_num,
			sum(net_sales) as "Net Sales",
			sum(gmv) as "GMV",
			count(case when refunded in (false) then user_id end) as "Purchases"
		from (
			select a.id as user_id,
			coalesce(f.source, g.utm_source) as utm_source,
			coalesce(f.medium, g.utm_medium) as utm_medium,
			google_branded_search,
			f.campaign as utm_campaign, b.hear_about_us,
			to_char(start_date, 'Month') as purchase_month,
			date_part('month'::text, start_date) AS purchase_month_num,
			case when t.user_id is not null then true end as tv_user,
			refunded,
			(total_amount_in_cents - amount_refunded) / 100 as net_sales,
			(subtotal_amount_in_cents + shipping_amount_in_cents - amount_refunded) / 100 as  gmv
			from users a
			inner join profiles b on a.id = b.user_id
			inner join (
				select row_number() over (partition by user_id order by created_at) as order_number, user_id,
				created_at at time zone 'UTC' as start_date, order_type,
				case when total_amount_in_cents in (amount_refunded) then true else false end as refunded,
				total_amount_in_cents, subtotal_amount_in_cents, shipping_amount_in_cents, amount_refunded
				from orders
				where order_type like ('plan%')
			) c on b.user_id = c.user_id and order_number = 1
			left join (
				select row_number () over (partition by user_id order by updated_at desc) as i, *
				from utm_params
				where (source not in ('customer.io') and campaign not in ('FB-lead-ad'))
			) f on f.i = 1 and a.id = f.user_id
			left join scratch.channel_to_utm_mappings g on b.hear_about_us = g.hear_about_us
			left join scratch_tv_users_all_5 t on a.id = t.user_id
			left join consultations cons on cons.user_id = a.id
			where a.email not like '%@thirtymadison%'
			and a.email not like '%@keeps%'
			and start_date >= '2018-01-16'
		) a
		group by "Source", purchase_month, purchase_month_num, utm_source, hear_about_us
		order by "Purchases" desc
) a left join scratch.marketing_source_bucketing b on lower(a."Source") = lower(b.source)
where source_bucket = 'Other - Paid'
group by
--source_bucket,
purchase_month, purchase_month_num, utm_source, hear_about_us
order by purchase_month_num, sum("Purchases") desc;