Narrator Documentation



Marketing spend, clicks, and impressions from Pinterest

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

Type: Spend Transformation
Table: spend

     md5(extract('epoch' from  || p.pin_promotion_id ||p.campaign_id || p.ad_group_id || p.spend_in_micro_dollar) as enriched_activity_id
     ,  as enriched_ts
     , 'pinterest' as ad_source
     , as ad_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 ( = p.pin_promotion_id and between ph.valid_from and ph.valid_to)
LEFT join pinterest_ads.ad_group_history_view a 
    on ( = p.ad_group_id and between a.valid_from and a.valid_to)
LEFT join pinterest_ads.campaign_history_view c 
    on ( = p.campaign_id and between c.valid_from and c.valid_to)
lEFT JOIN pinterest_ads.advertiser_history_view ad
    on ( between ad.valid_from and ad.valid_to)
where p.spend_in_micro_dollar is not null 
    and < SYSDATE::DATE

Processing Configuration:

  • Delete Recent Days: 7
    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 minute 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


Suggested Edits are limited on API Reference Pages

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