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

# Evaluate Meta Click vs. View Through Attribution

> This page provides an overview of how to evaluate the impact of view and clicks on Meta attribution.

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

| Field                                             | Description                                                              |
| ------------------------------------------------- | ------------------------------------------------------------------------ |
| `date`                                            | Event date (used for filtering)                                          |
| `marketing_type`                                  | Indicates the type of ad interaction - click or view                     |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5`  | Channel hierarchy dimensions                                             |
| `first_touch`, `last_touch`, `even`, `normalized` | Fractional conversion credit based on the chosen attribution methodology |

The `marketing_type` fields indicates the type of ad interaction

| Marketing Type             | Description                                         |
| -------------------------- | --------------------------------------------------- |
| `onsite`, `facebook_click` | User clicks on an Meta ad on the path to conversion |
| `facebook_view`            | User views an ad on the path to conversion          |

***

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

## Snowflake SQL Example

```sql theme={null}
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

```sql theme={null}
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;
```