Skip to main content

​
Description

This analysis enables you to customize attribution logic in the Log Level MTA dataset and rebuild aggregate marketing performance reporting in your warehouse. By adjusting log-level attribution records (for example modifying attribution windows, excluding view-through interactions, or adjusting revenue values), you can generate customized performance metrics such as conversions, revenue, CPA, and ROAS that better reflect your business context. The output reproduces the Buckets Breakdown reporting structure, grouping attributed conversions, revenue, and spend by marketing channel hierarchy.

​
When to Use This Analysis

  • Adjust attribution logic to better reflect your marketing measurement strategy.
  • Shorten or modify attribution windows for specific channels.
  • Remove view-through touchpoints to build click-based attribution metrics.
  • Adjust order-level revenue based on factors not passed to Rockerbox (e.g., margins, discounts, VAT).
  • Apply custom attribution calibration based on experiments or internal heuristics.
  • Rebuild aggregate marketing KPIs (CPA, ROAS) after modifying attribution logic.

​
Source Data

This analysis combines attribution data from the Log Level MTA dataset with spend data from the Buckets Breakdown dataset.
DatasetFieldDescription
log_level_mtadateDate of the attributed touchpoint
log_level_mtatier_1, tier_2, tier_3, tier_4, tier_5Marketing channel hierarchy
log_level_mtaspend_keyIdentifier used to link attribution data to marketing spend
log_level_mtafirst_touch, last_touch, even, normalizedAttributed conversions under different attribution models
log_level_mtarevenue_first_touch, revenue_last_touch, revenue_even, revenue_normalizedAttributed revenue under each attribution model
log_level_mtanew_to_fileIndicator for new customers used to calculate NTF metrics
aggregate_mtaplatform_join_keySpend identifier used to match marketing placements
aggregate_mtaincluded_spendMarketing spend for the placement
aggregate_mtaplatformAdvertising platform associated with the placement
aggregate_mtadateDate associated with the marketing spend

​
Key Metrics

MetricSQL LogicDescription
conversionsSUM(even)Conversions attributed to marketing placements
revenueSUM(revenue_even)Revenue attributed to marketing placements
spendSUM(included_spend)Marketing spend from advertising platforms
CPAspend / conversionsCost per acquisition
ROASrevenue / spendReturn on ad spend
ntf_conversionsSUM(CASE WHEN new_to_file=1 THEN conversions END)Conversions from new customers

​
Example Queries

​
Approach 1: Join Attribution with Spend

This query:
  • aggregates attribution from log_level_mta
  • joins spend from aggregate_mta
  • outputs conversions, revenue, and spend by marketing tier
WITH log_level_mta_pivot AS (

    -- Aggregate attribution from log-level MTA
    SELECT
        date,
        tier_1,
        tier_2,
        tier_3,
        tier_4,
        tier_5,
        spend_key,

        -- First Touch Attribution
        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,

        -- Last Touch Attribution
        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,

        -- Even Attribution
        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,

        -- Modeled Attribution
        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

    WHERE
        date >= {{start_date}}
        AND date <= {{end_date}}

    GROUP BY
        1,2,3,4,5,6,7
),

aggregate_mta_spend AS (

    -- Gather spend from buckets breakdown
    SELECT
        date,
        tier_1,
        tier_2,
        tier_3,
        tier_4,
        tier_5,
        platform,
        platform_join_key,
        included_spend AS spend

    FROM mta_tiers

    WHERE
        date >= {{start_date}}
        AND date <= {{end_date}}
        --choose the same conversion event matching the log level MTA table
        AND conversion_event_id = {{conversion identifier}}
        AND included_spend > 0
)

-- Combine attribution with spend
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.first_touch,0) AS first_touch,
    COALESCE(l.revenue_first_touch,0) AS revenue_first_touch,

    COALESCE(l.last_touch,0) AS last_touch,
    COALESCE(l.revenue_last_touch,0) AS revenue_last_touch,

    COALESCE(l.even,0) AS even,
    COALESCE(l.revenue_even,0) AS revenue_even,

    COALESCE(l.normalized,0) AS normalized,
    COALESCE(l.revenue_normalized,0) AS revenue_normalized

FROM log_level_mta_pivot l

FULL OUTER JOIN aggregate_mta_spend s
    ON s.platform_join_key = l.spend_key
   AND s.date = l.date;

​
Approach 2: Combine Attribution and Spend Using a Union

This approach unions attribution data and spend data into a single dataset. Metrics are then aggregated downstream in a BI layer or additional SQL step. 
SELECT * FROM log_level_mta_pivot
UNION ALL
SELECT * FROM aggregate_mta_spend;
After the union, aggregate metrics by: 
date
tier_1
tier_2
tier_3
tier_4
tier_5
to produce final marketing performance KPIs.