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