Evaluate Meta Click vs. View Attribution
This page provides an overview for how you can evaluate the impact of view and clicks on Meta attribution using Rockerbox's log level MTA dataset.
Use Cases:
- Understand the contribution of views vs clicks on your Meta CPA / ROAS
- Evaluate the incremental impact of Meta viewthrough
Schema: Log Level MTA
Leverage the marketing_type field in log level MTA to distinguish clickthrough versus viewthrough attribution.
The type value of facebook_view indicates any a facebook view-based touchpoint that is synthetically modeled.
The type value of facebook_view indicates any a facebook view-based touchpoint that is synthetically modeled.
Sample Query: View vs. Click Based Attributed Conversions and Revenue
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 >= current_date - INTERVAL '30 DAYS'
--isolate Facebook campaigns
--edit this with your customized Facebook reporting hierarchy
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;Sample Query: 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.
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 >= current_date - INTERVAL '30 DAYS'
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
b.date,
b.tier_1,
b.tier_2,
b.tier_3,
b.tier_4,
b.tier_5,
b.platform_join_key,
b.platform,
b.spend,
m.even_click,
m.even_view,
m.even_total,
m.revenue_even_click,
m.revenue_even_view,
m.revenue_even_total
FROM
{buckets_breakdown_table} b --replace with your table name
LEFT JOIN
meta_view_vs_click m
ON
m.date = b.date
AND m.spend_key = b.platform_join_key
WHERE
b.date >= current_date - INTERVAL '30 DAYS'
and b.platform ilike '%facebook%'
)
--STEP 3: COMPUTE CPA/ROAS AND AGGREGATE AT DESIRED REPORTING DIMENSIONS
SELECT
tier_1,
tier_2,
tier_3,
sum(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;