Skip to main content

Documentation Index

Fetch the complete documentation index at: https://data-foundation.rockerbox.com/llms.txt

Use this file to discover all available pages before exploring further.

​
Description

This analysis demonstrates how to filter marketing touchpoints from the Log Level MTA dataset in order to apply custom attribution logic. For example, you may want to exclude marketing touchpoints that occur outside a defined attribution window (e.g., more than 30 days before a conversion). After filtering the dataset, attribution credit for conversions must be recalculated across the remaining touchpoints. The query performs two main steps:
  1. Filter marketing touchpoints from the log-level MTA dataset based on custom attribution rules.
  2. Reinsert conversion records for cases where all marketing touchpoints were removed by the filter, ensuring conversions remain represented in the output dataset.
The result is a customized log-level attribution dataset that reflects your adjusted attribution logic.

​
When to Use This Analysis

  • Apply a custom attribution window to marketing touchpoints.
  • Remove interactions that occur outside a defined attribution period.
  • Recalculate attribution weights after filtering certain touchpoints.
  • Create a modified log-level attribution dataset aligned with internal measurement rules.

​
Source Data

This analysis uses fields from the Log Level MTA dataset.
FieldDescription
conversion_hash_idIdentifier for a unique conversion event
conversion_keyConversion identifier used for joins
timestamp_convTimestamp of the conversion event
timestamp_eventsTimestamp of the marketing touchpoint
sequence_numberPosition of the touchpoint in the conversion path
tier_1, tier_2, tier_3, tier_4, tier_5Marketing channel hierarchy
spend_keyIdentifier linking attribution events to platform spend
normalized, even, first_touch, last_touchAttribution credit values
revenue_evenRevenue attributed under the even attribution model
new_to_fileIndicator for new customer conversions

​
Key Metrics

MetricDescription
filtered_first_touchFirst-touch attribution after filtering touchpoints
filtered_last_touchLast-touch attribution after filtering touchpoints
filtered_evenEven attribution weight across remaining touchpoints
filtered_normalizedModeled attribution weight recalculated after filtering
filtered_sequence_numberOrder of touchpoints after filtering
filtered_total_eventsNumber of remaining touchpoints for a conversion

​
Example 1: Apply Standard Attribution Window Across All Channels

This query filters marketing touchpoints that occur more than 30 days before a conversion and recalculates attribution credit across the remaining touchpoints. 
-- 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;

​
Example 2: Apply a Variable Attribution Window by Channel

Note: This example assumes channels are defined using tier_1 and tier_2. Depending on your taxonomy, you may also need to define lookback windows at the tier_3 level. If a channel is not included in the lookup table, the query applies a very long fallback window so those touchpoints remain included. 
-- CTE: Define channel specific attribution window in days
-- Config assumes exact match on `tier_#` strings
WITH lookback_config AS (

    SELECT
        column1 AS tier_1,
        column2 AS tier_2,
        column3 AS lookback_window_days
    FROM VALUES
        ('display', 'prospecting', 60),
        ('display', 'prospecting', 7),
        ('display', 'retargeting', 60),

), 

-- CTE: Filter events out of log-level MTA
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

    -- Apply custom attribution window
    LEFT JOIN lookback_config c 
        ON lower(m.tier_1) = lower(c.tier_1)
        AND lower(m.tier_2) = lower(c.tier_2)
    
    WHERE 
        m.date >= {start_date}
        AND m.date <= {end_date}
        -- Apply the channel-specific attribution window
        -- If no configuration exists for a channel, use a large fallback window
        AND timestamp_events >= DATEADD(
            day,
            -COALESCE(c.lookback_window_days, 10000),
            timestamp_conv
        )
)
SELECT * 
FROM filtered_mta;