Join Rockerbox Attribution with Platform Data
This page provides an overview for how you can join aggregate Rockerbox performance data (i.e., attribution) with aggregate performance data pulled directly from major ad platforms.
Primary Use Case:
Evaluate ad platform reported performance alongside Rockerbox deduplicated attribution.
Schemas:
- Rockerbox Dataset: Buckets Breakdown / MTA Tiers
- Platform Dataset(s): Platform Performance
Join Key:
- Rockerbox dataset = transform_table_join_key
- Platform dataset = mta_tiers_join_key
The datasets that we are joining are at different levels of granularity - hourly grain size for platform data (for all platforms except LinkedIn) versus daily grain for Rockerbox data.
We must aggregate the platform data to report on a daily granularity before joining it to the Rockerbox data.
Join Type:
We recommend performing a LEFT JOIN to append Facebook data onto Rockerbox attribution because Rockerbox attribution will often include additional records beyond what is reported by the ad platform. This happens because Rockerbox has the ability to apply a longer attribution window than what is available in the ad platform; as a result, Rockerbox may report attributed conversions and revenue for ads after the brand has stopped spending on the ad and the ad has fallen out of the ad platform reporting.
The LEFT JOIN is a safe operation because the Rockerbox buckets breakdown report will always include all records that are present in the platform dataset by virtue of Rockerbox incorporating the platform reported spend in the buckets breakdown report.
Query Steps:
- Aggregate hourly platform performance to be daily granularity
- Join against Rockerbox data on the transform_table_join_key = mta_tiers_join_key
Sample Query to Join Against Facebook Platform Data (Snowflake SQL Syntax):
WITH facebook_daily_agg as (
SELECT
identifier,
date,
mta_tiers_join_key,
sum(clicks) as clicks,
sum(impressions) as impressions,
sum(coalesce(view_1d:offsite_conversion_fb_pixel_purchase,0)) purchase_1d_view,
sum(coalesce(click_7d:offsite_conversion_fb_pixel_purchase,0)) purchase_7d_click,
sum(coalesce(view_value_usd_1d:offsite_conversion_fb_pixel_purchase,0)) purchase_revenue_1d_view,
sum(coalesce(click_value_usd_7d:offsite_conversion_fb_pixel_purchase,0)) purchase_revenue_7d_click
FROM {platform_facebook_table}
WHERE
date >= {start_date}
AND date <= {end_date}
GROUP BY 1,2,3
)
SELECT
b.date,
b.tier_1,
b.tier_2,
b.tier_3,
b.tier_4,
b.tier_5,
b.transform_table_join_key,
b.spend,
b.even,
b.normalized,
b.revenue_even,
b.revenue_normalized,
f.clicks,
f.impressions,
f.purchase_1d_view,
f.purchase_7d_click,
f.purchase_revenue_1d_view,
f.purchase_revenue_7d_click
FROM {buckets_breakdown_table}
LEFT JOIN facebook_daily_agg f
ON f.mta_tiers_join_key = m.transform_table_join_key
WHERE
b.date >= {start_date}
AND b.date <= {{end_date}}
AND b.platform ilike '%facebook%'
ORDER BY 1,2,3,4,5,6;