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

# Replicate Cross-Channel Attribution Report

> Recreate the Rockerbox Cross-Channel Attribution report in your warehouse to analyze spend, conversions, CPA, and ROAS across marketing placements.

# When to Use This Analysis

* Evaluate **marketing performance across channels, campaigns, and placements** using the Rockerbox tier hierarchy.
* Analyze **CPA and ROAS across marketing placements** using different attribution methodologies.
* Segment attribution performance by **new vs. repeat customers**.
* Customize reporting beyond the UI by modifying **granularity, attribution model, and reporting time periods**.
* If you're looking to onboard onto the `aggregate_mta` schema and want to be able to sanity check your output against the Rockerbox UI.

***

# Source Data

This analysis uses fields from the **Aggregate MTA** schema, which contains attributed conversions, revenue, and spend aggregated by marketing dimensions.

| Field | Description |
| - | - |
| `date` | Date of the attributed conversion |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy used to segment performance |
| `even` | Even-weight attributed conversions |
| `revenue_even` | Even-weight attributed revenue |
| `included_spend` | Marketing spend associated with the placement |

***

# Key Metrics

| Metric | SQL Logic | Description |
| - | - | - |
| `conversions` | `SUM(even)` | Even-weight attributed conversions |
| `revenue` | `SUM(revenue_even)` | Even-weight attributed revenue |
| `spend` | `SUM(included_spend)` | Marketing spend associated with the placement |
| `cpa` | `SUM(spend) / NULLIF(SUM(even),0)` | Cost per acquisition |
| `roas` | `SUM(revenue_even) / NULLIF(SUM(spend),0)` | Return on ad spend |

***

# Attribution Model Reference

Rockerbox supports multiple attribution methodologies. Each method corresponds to a different set of columns in the dataset.

| Attribution Model | Conversions Column | Revenue Column | Description |
| - | - | - | - |
| Even Weight | `even` | `revenue_even` | Distributes conversion credit evenly across all touchpoints in the conversion path |
| Modeled Multi-Touch | `normalized` | `revenue_normalized` | Uses Rockerbox's modeled attribution weights across touchpoints |
| First Touch | `first_touch` | `revenue_first_touch` | Assigns 100% of conversion credit to the first marketing touchpoint |
| Last Touch | `last_touch` | `revenue_last_touch` | Assigns 100% of conversion credit to the last marketing touchpoint |

For **new customer attribution**, use the corresponding **new-to-file (NTF)** fields:

| Attribution Model | Conversions Column | Revenue Column |
| - | - | - |
| Even Weight | `ntf_even` | `ntf_revenue_even` |
| Modeled Multi-Touch | `ntf_normalized` | `ntf_revenue_normalized` |
| First Touch | `ntf_first_touch` | `ntf_revenue_first_touch` |
| Last Touch | `ntf_last_touch` | `ntf_revenue_last_touch` |

Repeat customer metrics can be derived by subtracting **new customer metrics** from **all customer metrics**.

***

# Example Queries (Snowflake)

## Cross-Channel Attribution Performance

This query replicates the **Cross-Channel Attribution report**, showing marketing spend and conversions mapped to marketing placements.

```sql theme={null}
SELECT
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5,
    SUM(even) AS conversions,
    SUM(revenue_even) AS revenue,
    SUM(included_spend) AS spend,
    SUM(included_spend) / NULLIF(SUM(even),0) AS cpa,
    SUM(revenue_even) / NULLIF(SUM(included_spend),0) AS roas
FROM
    aggregate_mta 
WHERE
    date >= dateadd(day, -30, CURRENT_DATE)
    AND date <= dateadd(day, -1, CURRENT_DATE)
    AND conversion_event_id = {{insert conversion id}}
GROUP BY
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5;
```

### How to Customize

| Adjustment | How |
| - | - |
| Change reporting granularity | Adjust the `tier_1`–`tier_5` fields in the `SELECT` and `GROUP BY` clauses |
| Use a different attribution methodology | Replace attribution fields in the query using the columns listed in the Attribution Model Reference |
| Analyze new customer attribution | Use new-to-file fields such as `ntf_even`, `ntf_normalized`, `ntf_first_touch`, `ntf_last_touch` |
| Analyze repeat customers | Calculate repeat metrics by subtracting new customer metrics from total metrics |
| Report by time period | Convert `date` to daily, weekly, monthly, quarterly, or yearly time buckets |
| Extend analysis window | Modify the `date` filter in the `WHERE` clause |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.