> ## 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.

# Customize Attribution Windows

> Apply custom attribution window filters to the log-level MTA dataset and rebuild attribution credit across remaining touchpoints.

# 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**.

| Field                                             | Description                                             |
| ------------------------------------------------- | ------------------------------------------------------- |
| `conversion_hash_id`                              | Identifier for a unique conversion event                |
| `conversion_key`                                  | Conversion identifier used for joins                    |
| `timestamp_conv`                                  | Timestamp of the conversion event                       |
| `timestamp_events`                                | Timestamp of the marketing touchpoint                   |
| `sequence_number`                                 | Position of the touchpoint in the conversion path       |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5`  | Marketing channel hierarchy                             |
| `spend_key`                                       | Identifier linking attribution events to platform spend |
| `normalized`, `even`, `first_touch`, `last_touch` | Attribution credit values                               |
| `revenue_even`                                    | Revenue attributed under the even attribution model     |
| `new_to_file`                                     | Indicator for new customer conversions                  |

***

# Key Metrics

| Metric                     | Description                                             |
| -------------------------- | ------------------------------------------------------- |
| `filtered_first_touch`     | First-touch attribution after filtering touchpoints     |
| `filtered_last_touch`      | Last-touch attribution after filtering touchpoints      |
| `filtered_even`            | Even attribution weight across remaining touchpoints    |
| `filtered_normalized`      | Modeled attribution weight recalculated after filtering |
| `filtered_sequence_number` | Order of touchpoints after filtering                    |
| `filtered_total_events`    | Number 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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;
```