-- Step 1: Aggregate Facebook platform data to daily granularity
WITH facebook_daily_agg AS (

    SELECT

        -- Dimensions
        identifier,
        date,
        mta_tiers_join_key,

        -- Platform engagement metrics
        SUM(clicks) AS clicks,
        SUM(impressions) AS impressions,

        -- Platform conversion metrics
        SUM(COALESCE(view_1d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_1d_view,
        SUM(COALESCE(click_7d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_7d_click,

        -- Platform revenue metrics
        SUM(COALESCE(view_value_usd_1d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_revenue_1d_view,
        SUM(COALESCE(click_value_usd_7d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_revenue_7d_click

    FROM {platform_facebook_table}

    WHERE
        date >= {start_date}
        AND date <= {end_date}

    GROUP BY
        1,2,3
)

-- Step 2: Join platform metrics to Rockerbox attribution dataset
SELECT

    -- Dimensions
    a.date,
    a.tier_1,
    a.tier_2,
    a.tier_3,
    a.tier_4,
    a.tier_5,
    a.platform_join_key,

    -- Rockerbox attribution metrics
    b.included_spend,
    b.even,
    b.normalized,
    b.revenue_even,
    b.revenue_normalized,

    -- Platform metrics
    f.clicks,
    f.impressions,
    f.purchase_1d_view,
    f.purchase_7d_click,
    f.purchase_revenue_1d_view,
    f.purchase_revenue_7d_click

FROM aggregate_mta a

-- Join Facebook platform data to Rockerbox attribution
LEFT JOIN facebook_daily_agg f
    ON f.mta_tiers_join_key = a.platform_join_key
    AND f.date = a.date

WHERE
    a.date >= {start_date}
    AND a.date <= {{end_date}}
    AND a.conversion_event_id = {{conversion identifier}}
    AND a.platform ILIKE '%facebook%'

ORDER BY
    1,2,3,4,5,6;