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

# Build CPA/ROAS From Log Level MTA

> Customize attribution logic in the log-level MTA dataset and rebuild aggregate marketing performance reporting such as CPA and ROAS.

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

| Dataset         | Field                                                                             | Description                                                 |
| --------------- | --------------------------------------------------------------------------------- | ----------------------------------------------------------- |
| `log_level_mta` | `date`                                                                            | Date of the attributed touchpoint                           |
| `log_level_mta` | `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5`                                  | Marketing channel hierarchy                                 |
| `log_level_mta` | `spend_key`                                                                       | Identifier used to link attribution data to marketing spend |
| `log_level_mta` | `first_touch`, `last_touch`, `even`, `normalized`                                 | Attributed conversions under different attribution models   |
| `log_level_mta` | `revenue_first_touch`, `revenue_last_touch`, `revenue_even`, `revenue_normalized` | Attributed revenue under each attribution model             |
| `log_level_mta` | `new_to_file`                                                                     | Indicator for new customers used to calculate NTF metrics   |
| `aggregate_mta` | `platform_join_key`                                                               | Spend identifier used to match marketing placements         |
| `aggregate_mta` | `included_spend`                                                                  | Marketing spend for the placement                           |
| `aggregate_mta` | `platform`                                                                        | Advertising platform associated with the placement          |
| `aggregate_mta` | `date`                                                                            | Date associated with the marketing spend                    |

***

# Key Metrics

| Metric            | SQL Logic                                           | Description                                    |
| ----------------- | --------------------------------------------------- | ---------------------------------------------- |
| `conversions`     | `SUM(even)`                                         | Conversions attributed to marketing placements |
| `revenue`         | `SUM(revenue_even)`                                 | Revenue attributed to marketing placements     |
| `spend`           | `SUM(included_spend)`                               | Marketing spend from advertising platforms     |
| `CPA`             | `spend / conversions`                               | Cost per acquisition                           |
| `ROAS`            | `revenue / spend`                                   | Return on ad spend                             |
| `ntf_conversions` | `SUM(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

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

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