Build Aggregate Reporting Based on Adjustments to Log Level MTA
Objective:
Deliver aggregate reporting KPIs (e.g., CPA, ROAS) based on in-warehouse adjustments/customizations to log level MTA.
Use Cases:
Here are a few common use cases for adjusting log level MTA based on your specific business context:
- Shorten attribution windows to user path to conversion for a subset of channels
- Adjust order level revenue based on context not passed to Rockerbox (e.g., product profit margins, adjustments for discounts or VAT)
- Calibrate / adjust credit based on heuristics and/or test results external to Rockerbox
- Exclude view through attribution in the path to conversion to build a click-based KPI
Query Overview
Schemas:
Output: Reproduce the buckets breakdown report --> attributed conversions, revenue, and spend grouped by date, platform_join_key, tier 1-5 columns
Steps:
- Adjust / customize log level MTA to your business context (e.g., adjust attribution window for Meta)
- Prepare log level MTA to be merged with spend (pivot log level MTA to have the same group by dimensions as spend reporting)
- Merge aggregate MTA data (from step #2) with spend from the buckets breakdown schema. This can be implemented via a SQL join or union statement.
Technical Approach #1: SQL Join
Steps from example query:
- Roll up the log level MTA to have the same group by dimensions as spend reporting in the buckets breakdown schema
- Select records with non-zero spend out of buckets breakdown report
- Implement an OUTER JOIN to join the spend from step #2 against the aggregated attribution from step #1
This must be an OUTER JOIN because there may be:
- attribution for ads for a given date that occurs after you stopped spending on an ad
- ads that you're spending on that did not drive to any conversions on a given date
- attribution for ads for a given date that occurs after you stopped spending on an ad
- ads that you're spending on that did not drive to any conversions on a given date
WITH log_level_mta_pivot AS (
--Roll up the log level MTA to have the same group by dimensions as spend reporting in the buckets breakdown schema
SELECT
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
spend_key,
sum(case when new_to_file = 1 THEN first_touch ELSE 0 END) as ntf_first_touch,
sum(first_touch) as first_touch,
sum(case when new_to_file = 1 THEN revenue_first_touch ELSE 0 END) as ntf_revenue_first_touch,
sum(revenue_first_touch) as revenue_first_touch,
sum(case when new_to_file = 1 THEN last_touch ELSE 0 END) as ntf_last_touch,
sum(last_touch) as last_touch,
sum(case when new_to_file = 1 THEN revenue_last_touch ELSE 0 END) as ntf_revenue_last_touch,
sum(revenue_last_touch) as revenue_last_touch,
sum(case when new_to_file = 1 THEN even ELSE 0 END) as ntf_even,
sum(even) as even,
sum(case when new_to_file = 1 THEN revenue_even ELSE 0 END) as ntf_revenue_even,
sum(revenue_even) as revenue_even,
sum(case when new_to_file = 1 THEN normalized ELSE 0 END) as ntf_normalized,
sum(normalized) as normalized,
sum(case when new_to_file = 1 THEN revenue_normalized ELSE 0 END) as ntf_revenue_normalized,
sum(revenue_normalized) as revenue_normalized
FROM log_level_mta --replace with log level MTA table
WHERE
date >= {{start_date}} --replace with start date
and date <= {{end_date}} --replace with end date
GROUP BY 1,2,3,4,5,6,7
), mta_tiers_spend as (
--Gather all spend from the buckets breakdown report
select
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
platform,
platform_join_key,
currency_code,
fx_rate_to_usd,
spend
from mta_tiers --replace with buckets breakdown table
where
date >= {{start_date}}
and date <= {{end_date}}
and spend > 0
)
--Join spend with the attribution data
select
coalesce(l.date, s.date) as date,
coalesce(l.tier_1, s.tier_1) as tier_1,
coalesce(l.tier_2, s.tier_2) as tier_2,
coalesce(l.tier_3, s.tier_3) as tier_3,
coalesce(l.tier_4, s.tier_4) as tier_4,
coalesce(l.tier_5, s.tier_5) as tier_5,
coalesce(l.spend_key, s.platform_join_key) as spend_key,
s.platform,
coalesce(s.spend, 0) as spend,
coalesce(l.ntf_first_touch, 0) as ntf_first_touch,
coalesce(l.first_touch, 0) as first_touch,
coalesce(l.ntf_revenue_first_touch, 0) as ntf_revenue_first_touch,
coalesce(l.revenue_first_touch, 0) as revenue_first_touch,
coalesce(l.ntf_last_touch, 0) as ntf_last_touch,
coalesce(l.last_touch, 0) as last_touch,
coalesce(l.ntf_revenue_last_touch, 0) as ntf_revenue_last_touch,
coalesce(l.revenue_last_touch, 0) as revenue_last_touch,
coalesce(l.ntf_even, 0) as ntf_even,
coalesce(l.even, 0) as even,
coalesce(l.ntf_revenue_even, 0) as ntf_revenue_even,
coalesce(l.revenue_even, 0) as revenue_even,
coalesce(l.ntf_normalized, 0) as ntf_normalized,
coalesce(l.normalized, 0) as normalized,
coalesce(l.ntf_revenue_normalized, 0) as ntf_revenue_normalized,
coalesce(l.revenue_normalized, 0) as revenue_normalized
from log_level_mta_pivot l
full outer join mta_tiers_spend s
on s.platform_join_key = l.spend_key
and s.date = l.date;
Technical Approach #2: SQL Union
Steps from example query:
- Roll up the log level MTA to have the same group by dimensions as spend reporting in the buckets breakdown schema and to standardize all of the columns in preparation of the union statement
- Select records with non-zero spend out of buckets breakdown report and set the column headers in the select to match step #1 in preparation of the union statement
- Union datasets from CTE #1 and CTE #2
Data Access Pattern for Final Output
As a result of the union in the example query, the spend and attribution metrics will appear on different records. The access pattern for the final output is to sum the measures/metrics based on the relevant group by dimensions.
- Measures: attributred conversions, attributred revenue, spend
- Group By Dimensions: date, tier_1, tier_2, tier_3, tier_4, tier_5
You can also add another CTE to the end of this query to group sum all metrics by the group by dimensions so that all metrics are on the same record. This step is not necessary if you are going to connect this output dataset to a BI layer (e.g., Looker) that will have predefined metrics and group by dimensions.
As a result of the union in the example query, the spend and attribution metrics will appear on different records. The access pattern for the final output is to sum the measures/metrics based on the relevant group by dimensions.
- Measures: attributred conversions, attributred revenue, spend
- Group By Dimensions: date, tier_1, tier_2, tier_3, tier_4, tier_5
You can also add another CTE to the end of this query to group sum all metrics by the group by dimensions so that all metrics are on the same record. This step is not necessary if you are going to connect this output dataset to a BI layer (e.g., Looker) that will have predefined metrics and group by dimensions.
WITH log_level_mta_pivot AS (
--Roll up the log level MTA to have the same group by dimensions as spend reporting
--Add spend column in preparation of union
SELECT
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
spend_key,
0 as spend,
sum(case when new_to_file = 1 THEN first_touch ELSE 0 END) as ntf_first_touch,
sum(first_touch) as first_touch,
sum(case when new_to_file = 1 THEN revenue_first_touch ELSE 0 END) as ntf_revenue_first_touch,
sum(revenue_first_touch) as revenue_first_touch,
sum(case when new_to_file = 1 THEN last_touch ELSE 0 END) as ntf_last_touch,
sum(last_touch) as last_touch,
sum(case when new_to_file = 1 THEN revenue_last_touch ELSE 0 END) as ntf_revenue_last_touch,
sum(revenue_last_touch) as revenue_last_touch,
sum(case when new_to_file = 1 THEN even ELSE 0 END) as ntf_even,
sum(even) as even,
sum(case when new_to_file = 1 THEN revenue_even ELSE 0 END) as ntf_revenue_even,
sum(revenue_even) as revenue_even,
sum(case when new_to_file = 1 THEN normalized ELSE 0 END) as ntf_normalized,
sum(normalized) as normalized,
sum(case when new_to_file = 1 THEN revenue_normalized ELSE 0 END) as ntf_revenue_normalized,
sum(revenue_normalized) as revenue_normalized
FROM log_level_mta --replace with log level MTA table
WHERE
date >= {{start_date}} --replace with start date
and date <= {{end_date}} --replace with end date
GROUP BY 1,2,3,4,5,6,7
), mta_tiers_spend as (
--select out non-zero spend records
--add attributed conversions/revenue columns in preparation of the union
select
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
platform_join_key as spend_key,
spend,
0 AS ntf_first_touch,
0 AS first_touch,
0 AS ntf_revenue_first_touch,
0 AS revenue_first_touch,
0 AS ntf_last_touch,
0 AS last_touch,
0 AS ntf_revenue_last_touch,
0 AS revenue_last_touch,
0 AS ntf_even,
0 AS even,
0 AS ntf_revenue_even,
0 AS revenue_even,
0 AS ntf_normalized,
0 AS normalized,
0 AS ntf_revenue_normalized,
0 AS revenue_normalized
FROM mta_tiers --replace with buckets breakdown table
WHERE
date >= {{start_date}}
AND date <= {{end_date}}
AND spend > 0
)
SELECT * FROM log_level_mta_pivot
UNION ALL
SELECT * FROM mta_tiers_spend;