SELECT
    CONCAT( CAST(UNIX_DATE(s.date) AS STRING)  , "-" , CAST(s.id AS STRING) , "-", CAST(s.ad_group_id AS STRING) ,"-" , CAST(s.criterion_id AS STRING) , "-" , s.ad_network_type_2 , s.device ,s.slot) AS enriched_activity_id
    , CAST( s.date AS TIMESTAMP) AS enriched_ts
    , s.date
    , "google" AS ad_source
    , CAST(NULL AS STRING) AS ad_name
    , r.campaign_name AS campaign_name
    , REGEXP_EXTRACT( lower(effective_final_url), '[?&]utm_medium=([^&]+)' ) AS utm_medium
    , REGEXP_EXTRACT( lower(effective_final_url), '[?&]utm_source=([^&]+)' ) AS utm_source
    , REGEXP_EXTRACT( lower(effective_final_url), '[?&]utm_campaign=([^&]+)' ) AS utm_campaign
    , REGEXP_EXTRACT( lower(effective_final_url), '[?&]utm_content=([^&]+)' ) AS utm_content
    , s.impressions
    , s.clicks
    , s.cost as spend
FROM adwords.google_ad_stats AS s
LEFT JOIN (
    SELECT
        date
        , campaign_id
        , campaign_name
        , customer_id
        , MIN(effective_final_url) AS effective_final_url
    FROM adwords.final_url_report AS t
    GROUP BY 1, 2, 3, 4
) AS r
    ON (
        r.date = s.date  AND
        r.campaign_id = s.campaign_id  AND
        r.customer_id = s.customer_id
    )
WHERE CAST( s.date AS TIMESTAMP) < CURRENT_TIMESTAMP()

SELECT
    md5(extract('epoch' from s.date)   || s.id || s.ad_group_id  || s.criterion_id  || s.ad_network_type_2 || s.device ||s.slot) AS enriched_activity_id,
    s.date AS enriched_ts,
    s.date AS date ,
    'adwords' AS ad_source,
    NULL AS ad_name ,
    r.campaign_name,
    nullif ( substring ( regexp_substr ( r.effective_final_url , 'utm_source=[^&]*' ) , 12 ) , '' ) AS "utm_source",
    nullif ( substring ( regexp_substr ( r.effective_final_url , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium",
    nullif ( substring ( regexp_substr ( r.effective_final_url , 'utm_campaign=[^&]*' ) , 14 ) , '' ) AS "utm_campaign",
    nullif ( substring ( regexp_substr ( r.effective_final_url , 'utm_content=[^&]*' ) , 13 ) , '' ) AS "utm_content",
    s.impressions  ,
    s.clicks  ,
    s.cost as spend 

from adwords.basic_ads s
LEFT JOIN (
    SELECT
        date, 
  			campaign_id, 
  			campaign_name, 
        customer_id, 
  			min(effective_final_url) AS effective_final_url
    from adwords.final_url_report
    group by 1, 2, 3, 4
) r 
    ON (r.date=s.date 
        AND r.campaign_id = s.campaign_id
        AND r.customer_id = s.customer_id)

where s.date < SYSDATE::DATE