Skip to main content

Documentation Index

Fetch the complete documentation index at: https://data-foundation.rockerbox.com/llms.txt

Use this file to discover all available pages before exploring further.

​
When to Use This Analysis

  • Understand the contribution of views vs clicks on your Meta CPA / ROAS
  • Evaluate the incremental impact of Meta view-through —

​
Source Data

This analysis primarily uses the following fields from the Log Level MTA schema:
FieldDescription
dateEvent date (used for filtering)
marketing_typeIndicates the type of ad interaction - click or view
tier_1, tier_2, tier_3, tier_4, tier_5Channel hierarchy dimensions
first_touch, last_touch, even, normalizedFractional conversion credit based on the chosen attribution methodology
The marketing_type fields indicates the type of ad interaction
Marketing TypeDescription
onsite, facebook_clickUser clicks on an Meta ad on the path to conversion
facebook_viewUser views an ad on the path to conversion

​
Example Query #1: View Through vs. Click Through Attributed Conversions and Revenue

​
Snowflake SQL Example

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;

​
How to customize

  • Add tier_2, tier_3, campaign, or other dimensions to increase granularity
  • Change day to hour if you want higher precision
  • Expand the date filter to analyze longer time windows

​
Example Query #2: Meta View vs. Click Based ROAS/CPA

Query Steps:
  • Gather view vs. click attribution per Meta AD ID
  • Gather spend per Meta AD ID and join with view vs. click attribution,
  • Compute CPA/ROAS and aggregate at desired reporting dimensions.

​
Snowflake SQL Example

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;