Narrator Docs

Adwords

Marketing spend, clicks, and impressions from Adwords

This transformation updates the spend table with marketing data about the Adwords ad source.
Read more about spend transformations


Type: Spend Transformation
Table: spend

SELECT
    CONCAT( CAST(UNIX_SECONDS(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
    , s.date AS enriched_ts
    , s.date
    , "adwords" 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.basic_ads 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

Processing Configuration:

  • Delete Recent Days: 7
    Why?
    Clicks, impressions, and spend may continue to change up to one week after the ad was shown. Deleting recent days on update will ensure that the final value is captured in your spend table.
    When to use delete recent days



šŸ‘

Contribute to the Narrator Community

Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.




Have questions?


Our data team is here to help! Here are a couple ways to get in touch...

šŸ’¬ Chat us from within Narrator

šŸ’Œ Email us at [email protected]

šŸ—“ Or schedule a 15-min meeting with our data team



Updated about a month ago


What's Next

Don't see what you're looking for? Browse the Activity Index for more examples...

Transformations by Activity

Adwords


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.