SELECT
     md5(extract('epoch' from p.date)  || p.pin_promotion_id ||p.campaign_id || p.ad_group_id || p.spend_in_micro_dollar) as enriched_activity_id
     , p.date  as enriched_ts
     , p.date
     , 'pinterest' as ad_source
     , a.name as ad_name
     , c.name as campaign_name
	 , nullif ( substring ( regexp_substr ( ph.destination_url , 'utm_source=[^&]*' ) , 12 ) , '' ) AS "utm_source"
	 , nullif ( substring ( regexp_substr ( ph.destination_url , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium"
	 , nullif ( substring ( regexp_substr ( ph.destination_url , 'utm_campaign=[^&]*' ) , 14 ) , '' ) AS "utm_campaign"
	 , nullif ( substring ( regexp_substr ( ph.destination_url , 'utm_content=[^&]*' ) , 13 ) , '' ) AS "utm_content"
     , NVL(p.impression_2,0) + NVL(p.impression_1, 0) as impressions
     , NVL(clickthrough_1) + NVL(clickthrough_2,0) as clicks
     , spend_in_micro_dollar/1000000.0  as spend    

FROM pinterest_ads.pin_promotion_report p 
LEFT join pinterest_ads.pin_promotion_history_view ph 
    on (ph.id = p.pin_promotion_id and p.date between ph.valid_from and ph.valid_to)
LEFT join pinterest_ads.ad_group_history_view a 
    on (a.id = p.ad_group_id and p.date between a.valid_from and a.valid_to)
LEFT join pinterest_ads.campaign_history_view c 
    on (c.id = p.campaign_id and p.date between c.valid_from and c.valid_to)
lEFT JOIN pinterest_ads.advertiser_history_view ad
    on (p.date between ad.valid_from and ad.valid_to)
    
where p.spend_in_micro_dollar is not null 
    and p.date < SYSDATE::DATE