Skip to main content

​
When to Use This Analysis

  • Recreate the Cross-Channel Attribution report from the Rockerbox UI directly in your data warehouse.
  • 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.

​
Source Data

This analysis uses fields from the Aggregate MTA schema, which contains attributed conversions, revenue, and spend aggregated by marketing dimensions.
FieldDescription
dateDate of the attributed conversion
tier_1, tier_2, tier_3, tier_4, tier_5Marketing channel hierarchy used to segment performance
evenEven-weight attributed conversions
revenue_evenEven-weight attributed revenue
included_spendMarketing spend associated with the placement

​
Key Metrics

MetricSQL LogicDescription
conversionsSUM(even)Even-weight attributed conversions
revenueSUM(revenue_even)Even-weight attributed revenue
spendSUM(included_spend)Marketing spend associated with the placement
cpaSUM(spend) / NULLIF(SUM(even),0)Cost per acquisition
roasSUM(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 ModelConversions ColumnRevenue ColumnDescription
Even Weightevenrevenue_evenDistributes conversion credit evenly across all touchpoints in the conversion path
Modeled Multi-Touchnormalizedrevenue_normalizedUses Rockerbox’s modeled attribution weights across touchpoints
First Touchfirst_touchrevenue_first_touchAssigns 100% of conversion credit to the first marketing touchpoint
Last Touchlast_touchrevenue_last_touchAssigns 100% of conversion credit to the last marketing touchpoint
For new customer attribution, use the corresponding new-to-file (NTF) fields:
Attribution ModelConversions ColumnRevenue Column
Even Weightntf_evenntf_revenue_even
Modeled Multi-Touchntf_normalizedntf_revenue_normalized
First Touchntf_first_touchntf_revenue_first_touch
Last Touchntf_last_touchntf_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

AdjustmentHow
Change reporting granularityAdjust the tier_1–tier_5 fields in the SELECT and GROUP BY clauses
Use a different attribution methodologyReplace attribution fields in the query using the columns listed in the Attribution Model Reference
Analyze new customer attributionUse new-to-file fields such as ntf_even, ntf_normalized, ntf_first_touch, ntf_last_touch
Analyze repeat customersCalculate repeat metrics by subtracting new customer metrics from total metrics
Report by time periodConvert date to daily, weekly, monthly, quarterly, or yearly time buckets
Extend analysis windowModify the date filter in the WHERE clause