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.

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

Warehousing

Replicate Cross-Channel Attribution Report

When to Use This Analysis

Source Data

Key Metrics

Attribution Model Reference

Example Queries (Snowflake)

Cross-Channel Attribution Performance

How to Customize

​When to Use This Analysis

​Source Data

​Key Metrics

​Attribution Model Reference

​Example Queries (Snowflake)

​Cross-Channel Attribution Performance

​How to Customize

When to Use This Analysis

Source Data

Key Metrics

Attribution Model Reference

Example Queries (Snowflake)

Cross-Channel Attribution Performance

How to Customize