Marketing spend, clicks, and impressions from LinedIn

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

Type: Spend Transformation
Table: spend

    CONCAT( CAST(UNIX_SECONDS( AS STRING) , "-" , CAST(c.campaign_id  AS STRING)) AS enriched_activity_id 
    , as enriched_ts
    , as date
    , "linkedin" AS ad_source
    , CAST(NULL AS STRING) AS ad_name
    , AS campaign_name
    , CAST(NULL AS STRING) AS utm_medium
    , CAST(NULL AS STRING) AS utm_source
    , CAST(NULL AS STRING) AS utm_campaign
    , CAST(NULL AS STRING) AS utm_content
    , c.impressions
    , c.clicks
    , c.cost_in_local_currency as spend
FROM linkedin_media.ad_analytics_by_campaign c
  		WHEN LAG(last_modified_time) over (PARTITION by id ORDER BY last_modified_time) is NULL 
  		THEN created_time ELSE last_modified_time END 
  		AS valid_from
  			, COALESCE(LEAD(last_modified_time) over (PARTITION by id ORDER BY last_modified_time), CAST('2100-01-01' AS TIMESTAMP)) 
  AS valid_to
        , *
    FROM linkedin_media.campaign_history   ) h 
    on = c.campaign_id 
    	AND >= h.valid_from 
    	AND <= valid_to

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

What’s Next

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