Narrator Documentation

Facebook

Marketing spend, clicks, and impressions from Facebook



Prerequisite: Deal with History Views in Fivetran

Create a view in your warehouse to reshape the history tables into a usable format

Run this query on your warehouse in Admin mode, before creating the transformation below.

CREATE VIEW facebook_ad_account.ad_history_view   AS (
  SELECT
    CASE
        WHEN 
            LAG(updated_time) over (PARTITION by id ORDER BY updated_time) is NULL 
                    THEN created_time 
        ELSE updated_time 
        END AS valid_from
        , COALESCE(LEAD(updated_time) 
                   over (PARTITION by id ORDER BY updated_time), CAST('2100-01-01' AS TIMESTAMP)) 
             AS valid_to
                    , *
  FROM facebook_ad_account.ad_history
)



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

Type: Spend Transformation
Table: spend

SELECT
    md5(extract('epoch' from i.date)  || '-' || i.ad_id) as enriched_activity_id,
    i.date  as enriched_ts,
    i.date, 
    'facebook' as ad_source, 
    u.ad_name, 
    NULL as campaign_name,
    u.utm_source, 
    u.utm_campaign,
    u.utm_medium,
    u.utm_content,
    i.impressions,
    i.inline_link_clicks as clicks,
    i.spend as spend
FROM facebook.basic_ads i 
LEFT OUTER JOIN (
    SELECT 
        ah.account_id, 
            ah.name as ad_name, 
            ah.id as ad_id, 
            t.utm_medium, 
            t.utm_campaign, 
            t.utm_content,  
            t.utm_source, 
            valid_from, 
            valid_to
    FROM facebook_ad_account.ad_history_view ah
    LEFT OUTER JOIN (
        SELECT _fivetran_synced::DATE as synced_at, 
            t.creative_id, 
            MIN(CASE WHEN key = 'utm_medium' THEN value END ) AS utm_medium,
            MIN(CASE WHEN key = 'utm_source' THEN value END ) AS utm_source,
            MIN(CASE WHEN key = 'utm_campaign' THEN value END ) AS utm_campaign,
            MIN(CASE WHEN key = 'utm_content' THEN value END ) AS utm_content 
        FROM facebook_ad_account.url_tag t
        group by 1, 2 
        ) t
        on (t.creative_id = ah.creative_id 
            AND ah._fivetran_synced::DATE = t.synced_at)
) u
    on (i.account_id = u.account_id 
        AND i.ad_id = u.ad_id 
        AND i.date >= u.valid_from 
        AND i.date <= u.valid_to)
WHERE i.date < SYSDATE::DATE
SELECT
    CONCAT(CAST( UNIX_DATE(i.date) AS STRING), CONCAT("-", CAST( i.ad_id AS STRING))) as enriched_activity_id
    , CAST( i.date AS TIMESTAMP) AS enriched_ts
    , i.date
    , "facebook" AS ad_source
    , u.ad_name
    , CAST(NULL AS STRING) AS campaign_name
    , u.utm_medium
    , u.utm_source
    , u.utm_campaign
    , u.utm_content
    , coalesce(i.impressions, 0) as impressions
    , coalesce(i.inline_link_clicks,0) AS clicks
    , coalesce(i.spend,0) as spend
FROM facebook.basic_ads AS i
LEFT JOIN (
    SELECT
        ah.name AS ad_name
        , ah.id AS ad_id
        , ah.account_id
        , ah.valid_from
        , ah.valid_to
        , t.utm_medium
        , t.utm_source
        , t.utm_campaign
        , t.utm_content
    FROM facebook_ad_account.ad_history_view AS ah 
    LEFT JOIN (
        SELECT
            CAST( _fivetran_synced AS DATE) AS synced_at
            , creative_id
            , MIN(CASE WHEN key = "utm_medium" THEN value END) AS utm_medium
            , MIN(CASE WHEN key = "utm_source" THEN value END) AS utm_source
            , MIN(CASE WHEN key = "utm_campaign" THEN value END) AS utm_campaign
            , MIN(CASE WHEN key = "utm_content" THEN value END) AS utm_content
        FROM facebook_ad_account.url_tag AS t
        GROUP BY 1, 2
    ) AS t
        ON (
            t.creative_id = ah.creative_id  AND
            t.synced_at = CAST( ah._fivetran_synced AS DATE)
        )
) AS u
    ON (
        i.account_id = u.account_id  AND
        i.ad_id = CAST( u.ad_id AS STRING)  AND
        CAST( i.date AS TIMESTAMP) >= CAST( u.valid_from AS TIMESTAMP)  AND
        CAST( i.date AS TIMESTAMP) < CAST( u.valid_to AS TIMESTAMP)
    )

WHERE CAST( i.date AS TIMESTAMP) < CURRENT_TIMESTAMP()

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 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

Facebook


Suggested Edits are limited on API Reference Pages

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