Filtering Touchpoints from MTA
This page provides sample Snowflake queries that enable you to filter certain touch points out of your log level MTA datasets in order to produce performance reporting.
Use Case: Applying a Custom Attribution Window to Log Level MTA
This query filters out certain touchpoints from log level MTA and produces the log level MTA dataset.
This query operates in 2 stages:
- filtered_mta cte = filters touchpoints out of log level MTA dataset that occur >30D before the conversion and re-allocate credit touchpoints for first touch, last touch, and even weight attribution model
- log_level_final cte = apply a right outer join to re-insert any conversions as a "direct attributed" conversion for any conversions that are excluded from filtered_mta because all of the touchpoints were filtered out
Make sure you are joining tables that represent the same Rockerbox conversion segment in this query (e.g., join purchase_mta to purchase_conversions)
SET start_date = '2024-03-30';
SET end_date = '2024-04-02';
WITH filtered_mta AS (
--CTE to filter events out of log level MTA
SELECT
m.conversion_hash_id,
m.conversion_key,
m.date,
m.timestamp_conv,
m.timestamp_events,
m.marketing_type,
m.new_to_file,
m.tier_1,
m.tier_2,
m.tier_3,
m.tier_4,
m.tier_5,
m.spend_key,
CASE WHEN min(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN 1 ELSE 0 END AS filtered_first_touch,
CASE WHEN min(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN m.total_events * m.revenue_even ELSE 0 END AS filtered_first_touch_revenue,
CASE WHEN max(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN 1 ELSE 0 END AS filtered_last_touch,
CASE WHEN max(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN m.total_events * m.revenue_even ELSE 0 END AS filtered_last_touch_revenue,
div0(1, count(*) over (partition BY m.date, m.conversion_hash_id)) AS filtered_even,
div0(1,count(*) over (partition BY m.date, m.conversion_hash_id)) * m.total_events * m.revenue_even AS filtered_even_revenue,
rank() over (partition BY m.date, m.conversion_hash_id ORDER BY sequence_number ASC) filtered_sequence_number,
count(*) over (partition BY m.date, m.conversion_hash_id) AS filtered_total_events
FROM log_level_mta m --replace with mta table name
WHERE
m.date >= $start_date
and m.date <= $end_date
--filter to apply 30D attribution window across all marketing touchpoints
and timestamp_events >= dateadd(day, -30, timestamp_conv)
), log_level_final as (
--CTE to join the filtered log level MTA back against converions to address edge case where all marketing touches are removed for a conversion
SELECT
coalesce(f.conversion_hash_id, c.conversion_hash_id) AS conversion_hash_id,
coalesce(f.conversion_key, c.conversion_key) AS conversion_key,
coalesce(f.date, c.date) AS date,
coalesce(f.timestamp_conv, c.timestamp_conv) AS timestamp_conv,
f.timestamp_events,
coalesce(f.marketing_type, 'conv_only') AS marketing_type,
coalesce(f.new_to_file, c.new_to_file) AS new_to_file,
coalesce(f.tier_1, 'Direct') AS tier_1,
f.tier_2,
f.tier_3,
f.tier_4,
f.tier_5,
f.spend_key,
coalesce(f.filtered_first_touch, 1) AS filtered_first_touch,
coalesce(f.filtered_first_touch_revenue, c.revenue) AS filtered_first_touch_revenue,
coalesce(f.filtered_last_touch, 1) AS filtered_last_touch,
coalesce(f.filtered_last_touch_revenue, c.revenue) AS filtered_last_touch_revenue,
coalesce(f.filtered_even, 1) AS filtered_even,
coalesce(f.filtered_even_revenue, c.revenue) AS filtered_even_revenue,
coalesce(f.filtered_sequence_number, 1) AS filtered_sequence_number,
coalesce(f.filtered_total_events, 1) AS filtered_total_events
FROM filtered_mta f
RIGHT OUTER JOIN conversions_table c --replace with your conversions table
ON c.date = f.date
AND c.conversion_key = f.conversion_key
WHERE
c.date >= $start_date
and c.date <= $end_date
)
SELECT
*
FROM log_level_final;
Use Case: Filtering log level MTA to only click-based touchpoints
Leverage the marketing_type column in the log level MTA dataset to only include click-based touchpoints.
You can modify the where clause in the filtered_mta cte in the above query to only include certain event types - see event types documentation here :
WHERE marketing_type in ('onsite')Use Case: Applying a Custom Touchpoint Filtering to Buckets Breakdown (MTA Tiers)
This query filters out certain touchpoints from log level MTA and produces the buckets breakdown (MTA Tiers) dataset.
The query operates in 4 stages:
- filtered_mta cte = filters touchpoints out of log level MTA dataset (in this example, touchpoints that occur >30D before the conversion) and re-allocate credit touchpoints for first touch, last touch, and even weight attribution model
- log_level_final cte = apply a right outer join to re-insert any conversions as a "direct attributed" conversion for any conversions that are excluded from filtered_mta because all of the touchpoints were filtered out
- log_level_pivot cte = prepare the log level MTA data to be joined against the spend dataset by grouping the attributed conversions and revenue at the same level of granularity that spend is ingested into Rockerbox on a per-platform basis
- mta_tiers cte = prepare the buckets breakdown data to be joined against log_level_pivot by removing any records where spend = 0
- perform a full outer join between log_level_pivot and mta_tiers to match up all attributed conversions with spend
--Query to reproduce buckets breakdown (MTA Tiers) dataset after filtering out touchpoints from log level MTA
SET start_date = '2024-03-20';
SET end_date = '2024-04-02';
WITH filtered_mta AS (
--CTE to filter events out of log level MTA
SELECT
m.conversion_hash_id,
m.conversion_key,
m.date,
m.timestamp_conv,
m.timestamp_events,
m.marketing_type,
m.new_to_file,
m.tier_1,
m.tier_2,
m.tier_3,
m.tier_4,
m.tier_5,
m.spend_key,
CASE WHEN min(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN 1 ELSE 0 END AS filtered_first_touch,
CASE WHEN min(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN m.total_events * m.revenue_even ELSE 0 END AS filtered_first_touch_revenue,
CASE WHEN max(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN 1 ELSE 0 END AS filtered_last_touch,
CASE WHEN max(m.sequence_number) over (partition by m.date, m.conversion_hash_id) = m.sequence_number THEN m.total_events * m.revenue_even ELSE 0 END AS filtered_last_touch_revenue,
div0(1, count(*) over (partition BY m.date, m.conversion_hash_id)) AS filtered_even,
div0(1, count(*) over (partition BY m.date, m.conversion_hash_id)) * m.total_events * m.revenue_even AS filtered_even_revenue,
rank() over (partition BY m.date, m.conversion_hash_id ORDER BY sequence_number ASC) filtered_sequence_number,
count(*) over (partition BY m.date, m.conversion_hash_id) AS filtered_total_events
FROM log_level_mta_table m --replace with your mta table
WHERE
m.date >= $start_date
and m.date <= $end_date
--filter to apply 30D attribution window across all marketing touchpoints
and timestamp_events >= dateadd(day, -30, timestamp_conv)
), log_level_final as (
--CTE to join the filtered log level MTA back against converions to address edge case where all marketing touches are removed for a conversion
SELECT
coalesce(f.conversion_hash_id, c.conversion_hash_id) AS conversion_hash_id,
coalesce(f.conversion_key, c.conversion_key) AS conversion_key,
coalesce(f.date, c.date) AS date,
coalesce(f.timestamp_conv, c.timestamp_conv) AS timestamp_conv,
f.timestamp_events,
coalesce(f.marketing_type, 'conv_only') AS marketing_type,
coalesce(f.new_to_file, c.new_to_file) AS new_to_file,
coalesce(f.tier_1, 'Direct') AS tier_1,
f.tier_2,
f.tier_3,
f.tier_4,
f.tier_5,
f.spend_key,
coalesce(f.filtered_first_touch, 1) AS filtered_first_touch,
coalesce(f.filtered_first_touch_revenue, c.revenue) AS filtered_first_touch_revenue,
coalesce(f.filtered_last_touch, 1) AS filtered_last_touch,
coalesce(f.filtered_last_touch_revenue, c.revenue) AS filtered_last_touch_revenue,
coalesce(f.filtered_even, 1) AS filtered_even,
coalesce(f.filtered_even_revenue, c.revenue) AS filtered_even_revenue,
coalesce(f.filtered_sequence_number, 1) AS filtered_sequence_number,
coalesce(f.filtered_total_events, 1) AS filtered_total_events
FROM filtered_mta f
RIGHT OUTER JOIN conversions_table c --replace with your conversions table
ON c.date = f.date
AND c.conversion_key = f.conversion_key
WHERE
c.date >= $start_date
and c.date <= $end_date
), log_level_pivot as (
select
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
spend_key,
sum(case when new_to_file = 1 THEN filtered_first_touch ELSE 0 END) as ntf_first_touch,
sum(filtered_first_touch) as first_touch,
sum(case when new_to_file = 1 THEN filtered_first_touch_revenue ELSE 0 END) as ntf_revenue_first_touch,
sum(filtered_first_touch_revenue) as revenue_first_touch,
sum(case when new_to_file = 1 THEN filtered_last_touch ELSE 0 END) as ntf_last_touch,
sum(filtered_last_touch) as last_touch,
sum(case when new_to_file = 1 THEN filtered_last_touch_revenue ELSE 0 END) as ntf_revenue_last_touch,
sum(filtered_last_touch_revenue) as revenue_last_touch,
sum(case when new_to_file = 1 THEN filtered_even ELSE 0 END) as ntf_even,
sum(filtered_even) as even,
sum(case when new_to_file = 1 THEN filtered_even_revenue ELSE 0 END) as ntf_revenue_even,
sum(filtered_even_revenue) as revenue_even
FROM log_level_final
GROUP BY 1,2,3,4,5,6,7
), mta_tiers as (
select
t.date,
t.tier_1,
t.tier_2,
t.tier_3,
t.tier_4,
t.tier_5,
t.platform,
t.platform_join_key,
t.currency_code,
t.fx_rate_to_usd,
spend
from mta_tiers_table t --replace with your buckets breakdown/mta tiers table
where
t.date >= $start_date
and t.date <= $end_date
and t.spend > 0
)
select
coalesce(l.date, t.date) as date,
coalesce(l.tier_1, t.tier_1) as tier_1,
coalesce(l.tier_2, t.tier_2) as tier_2,
coalesce(l.tier_3, t.tier_3) as tier_3,
coalesce(l.tier_4, t.tier_4) as tier_4,
coalesce(l.tier_5, t.tier_5) as tier_5,
coalesce(l.spend_key, t.platform_join_key) as spend_key,
t.platform,
t.currency_code,
t.fx_rate_to_usd,
t.spend,
l.ntf_first_touch,
l.first_touch,
l.ntf_revenue_first_touch,
l.revenue_first_touch,
l.ntf_last_touch,
l.last_touch,
l.ntf_revenue_last_touch,
l.revenue_last_touch,
l.ntf_even,
l.even,
l.ntf_revenue_even,
l.revenue_even
from log_level_pivot l
full outer join mta_tiers t
on t.platform_join_key = l.spend_key
and t.date = l.date;