begin;
drop table if exists derived.ad_clicks;
commit;

begin;
create table derived.ad_clicks as (

with pages_with_ad_ids as (
select
id,
anonymous_id,
received_at,
substring(search, '.*[?&]gclid=([^$&]*)') as gcl_id,
substring(search, '.*[?&]fbad_id=([^$&]*)') as fbad_id
from marketing.pages
)

select
p.id,
p.received_at,
p.anonymous_id,
case
  when p.gcl_id is not null and p.fbad_id is null then 'Google'
  when p.fbad_id is not null and p.gcl_id is null then 'Facebook'
  else 'wtf'
end as platform,
case
  when p.gcl_id is not null then coalesce(g3.name, '[unknown]')
  when p.fbad_id is not null then coalesce(f3.name, '[unknown]')
  else 'wtf'
end as campaign_name,
case
  when p.gcl_id is not null then coalesce(g2.name, '[unknown]')
  when p.fbad_id is not null then coalesce(f2.name, '[unknown]')
  else 'wtf'
end as ad_group_name
from pages_with_ad_ids p
left join adwords.click_performance_reports g1 on g1.gcl_id = p.gcl_id
left join adwords.ad_groups g2 on g2.id = g1.ad_group_id
left join adwords.campaigns g3 on g3.id = g2.campaign_id
left join facebookads.ads f1 on f1.id = p.fbad_id
left join facebookads.ad_sets f2 on f2.id = f1.adset_id
left join facebookads.campaigns f3 on f3.id = f2.campaign_id
where p.gcl_id is not null or p.fbad_id is not null

);
commit;