SELECT
    tier_1,
    tier_2,
    tier_3,
    sum(CASE WHEN marketing_type != 'facebook_view' THEN even END) AS even_click,
    sum(CASE WHEN marketing_type = 'facebook_view' THEN even END) AS even_view,
    sum(even) AS even_total,
    sum(CASE WHEN marketing_type != 'facebook_view' THEN revenue_even END) AS revenue_even_click,
     sum(CASE WHEN marketing_type = 'facebook_view' THEN revenue_even END) AS revenue_even_view,
     sum(revenue_even) AS revenue_even_total
FROM
    <log_level_mta_table> --replace with your mta table
where 
    date >= dateadd(day, -30, current_date)
    --edit this with your customized Facebook reporting hierarchy to filter down to FB campaigns
    AND tier_1 = 'Paid Social'
    AND tier_2 = 'facebook'
GROUP BY 
    tier_1,
    tier_2,
    tier_3
ORDER BY
    tier_1,
    tier_2,
    tier_3;

WITH meta_view_vs_click AS (

    -- STEP 1: GATHER VIEW VS CLICK ATTRIBUTION PER META AD ID    
    SELECT 
        date,
        spend_key,

        SUM(CASE WHEN marketing_type != 'facebook_view' THEN even END) AS even_click,
        SUM(CASE WHEN marketing_type = 'facebook_view' THEN even END) AS even_view,
        SUM(even) AS even_total,

        SUM(CASE WHEN marketing_type != 'facebook_view' THEN revenue_even END) AS revenue_even_click,
        SUM(CASE WHEN marketing_type = 'facebook_view' THEN revenue_even END) AS revenue_even_view,
        SUM(revenue_even) AS revenue_even_total

    FROM {log_level_mta} --replace with your table name

    WHERE date >= dateadd(day, -30, current_date)

    GROUP BY 
        date,
        spend_key

),  

meta_view_vs_click_spend AS (

    -- STEP 2: GATHER SPEND PER META AD ID AND JOIN WITH VIEW VS CLICK ATTRIBUTION
    SELECT 
        a.date,
        a.tier_1,
        a.tier_2,
        a.tier_3,
        a.tier_4,
        a.tier_5,
        a.platform_join_key,
        a.platform,
        a.included_spend,

        m.even_click,
        m.even_view,
        m.even_total,
        m.revenue_even_click,
        m.revenue_even_view,
        m.revenue_even_total

    FROM aggregate_mta a

    LEFT JOIN meta_view_vs_click m 
        ON m.date = a.date
        AND m.spend_key = a.platform_join_key 

    WHERE 
        a.date >= dateadd(day, -30, current_date)
        AND a.platform ILIKE '%facebook%'
        AND a.included_spend > 0
        AND a.conversion_event_id = <conversion_event_id> --identifier of the conversion you are evaluating

) 

-- STEP 3: COMPUTE CPA/ROAS AND AGGREGATE AT DESIRED REPORTING DIMENSIONS
SELECT 
    tier_1, 
    tier_2,
    tier_3,
    
    SUM(included_spend) AS spend,
    SUM(even_click) AS click_conversions,
    SUM(even_total) AS all_conversions,

    SUM(revenue_even_click) AS click_revenue,
    SUM(revenue_even_total) AS all_revenue,
    
    SUM(spend) / NULLIF(SUM(even_click), 0) AS click_cpa,
    SUM(spend) / NULLIF(SUM(even_total), 0) AS all_cpa,
    SUM(revenue_even_click) / NULLIF(SUM(spend), 0) AS click_roas,
    SUM(revenue_even_total) / NULLIF(SUM(spend), 0) AS all_roas
    
FROM meta_view_vs_click_spend

GROUP BY 
    tier_1,
    tier_2,
    tier_3

ORDER BY
    tier_1,
    tier_2,
    tier_3;