-- CTE: Filter events out of log-level MTA
WITH filtered_mta AS (
SELECT
-- Identity fields
m.action,
m.base_id,
m.uid,
m.hash_ip_events,
m.user_agent_events,
-- Conversion context
m.new_to_file,
m.currency_code,
m.fx_rate_to_usd,
m.conversion_hash_id,
m.conversion_key,
-- Event timestamps
m.date,
m.timestamp_conv,
m.timestamp_events,
-- Session context
m.onsite_count,
m.marketing_type,
m.event_id,
m.matches,
-- URL context
m.original_url,
m.url_parameters,
m.utm_parameters,
m.request_referrer,
-- Marketing hierarchy
m.tier_1,
m.tier_2,
m.tier_3,
m.tier_4,
m.tier_5,
-- Spend identifiers
m.spend_key,
m.platform,
-- First-touch attribution recalculation
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,
-- Last-touch attribution recalculation
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,
-- Even attribution recalculation
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_even,
-- Even attribution revenue allocation
(
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id)
) * m.total_events * m.revenue_even AS filtered_even_revenue,
-- Modeled attribution recalculation
COALESCE(
m.normalized / SUM(m.normalized) OVER (PARTITION BY m.date, m.conversion_hash_id),
0
) AS filtered_normalized,
-- Sequence tracking after filtering
RANK() OVER (
PARTITION BY m.date, m.conversion_hash_id
ORDER BY sequence_number ASC
) AS filtered_sequence_number,
-- Count of remaining events
COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_total_events,
m.rb_sync_id,
m.updated_at
FROM log_level_mta m
WHERE
m.date >= {start_date}
AND m.date <= {end_date}
-- Apply custom attribution window
AND timestamp_events >= DATEADD(day, -30, timestamp_conv)
),
-- CTE: Reinsert conversions when all marketing touchpoints were removed
log_level_final AS (
SELECT
COALESCE(f.identifier, m2.identifier) AS identifier,
COALESCE(f.action, m2.action) AS action,
COALESCE(f.base_id, m2.base_id) AS base_id,
COALESCE(f.uid, m2.uid) AS uid,
COALESCE(f.hash_ip_events, m2.hash_ip_events) AS hash_ip_events,
COALESCE(f.user_agent_events, m2.user_agent_events) AS user_agent_events,
COALESCE(f.new_to_file, m2.new_to_file) AS new_to_file,
COALESCE(f.conversion_hash_id, m2.conversion_hash_id) AS conversion_hash_id,
COALESCE(f.conversion_key, m2.conversion_key) AS conversion_key,
COALESCE(f.date, m2.date) AS date,
COALESCE(f.timestamp_conv, m2.timestamp_conv) AS timestamp_conv,
COALESCE(f.timestamp_events, m2.timestamp_conv) AS timestamp_events,
COALESCE(f.marketing_type, 'conv_only') AS marketing_type,
f.event_id,
COALESCE(f.tier_1, 'Direct') AS tier_1,
f.tier_2,
f.tier_3,
f.tier_4,
f.tier_5,
f.spend_key,
-- Attribution metrics after filtering
COALESCE(f.filtered_first_touch, 1) AS filtered_first_touch,
COALESCE(f.filtered_last_touch, 1) AS filtered_last_touch,
COALESCE(f.filtered_even, 1) AS filtered_even,
COALESCE(f.filtered_normalized, 1) AS filtered_normalized,
COALESCE(f.filtered_sequence_number, 1) AS filtered_sequence_number,
COALESCE(f.filtered_total_events, 1) AS filtered_total_events,
COALESCE(f.rb_sync_id, m2.rb_sync_id) AS rb_sync_id,
COALESCE(f.updated_at, m2.updated_at) AS updated_at
FROM filtered_mta f
RIGHT OUTER JOIN (
-- Ensure each conversion remains represented
SELECT *
FROM log_level_mta m
WHERE
m.first_touch = 1
AND m.date >= {start_date}
AND m.date <= {end_date}
) AS m2
ON m2.date = f.date
AND m2.conversion_key = f.conversion_key
)
SELECT *
FROM log_level_final;
