Replicate Cross Channel Report from Rockerbox UI

Updated by Omar Elmaghrabi

This SQL query replicates the Cross-Channel Attribution report. This shows for a given time frame, all marketing spend and conversions mapped back to each marketing placement. This report enables you to see weighted CPA/ROAS against marketing placements for new and existing users.

Sample Query: Even Weight Attribution for All Customers Types (New + Repeat)

SELECT 
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5,    
    sum(even) AS conversions,
    sum(revenue_even) AS revenue,
    sum(spend) AS spend,
    sum(spend) / nullif(sum(even),0) AS cpa,
    sum(revenue_even) / nullif(sum(spend),0) AS roas
FROM 
    buckets_breakdown_table --enter your table name
WHERE 
    date >= current_date - INTERVAL '30 DAYS'
    AND date <= current_date - INTERVAL '1 DAY'
GROUP BY
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5;

This sample query reports even weight attribution for the last 30 days down to tier_5 granularity for both new + repeat customers.

There are several adjustments that you might make to sample query based on your specific reporting needs:
  1. Reporting Granularity / Group By Dimensions: evaluate performance at different levels of granularity (e.g., channel or tactic level down to ad level) by adjusting the set of columns (tier 1-5) in the SELECT and GROUP BY
    1. tier_1, tier_2, tier_3, tier_4, tier_5
  2. Attribution methodology: report attributed conversions and revenue against different attribution methodologies by changing the columns in the SELECT
    1. even weight = even, revenue_even
    2. modeled multi-touch = normalized, revenue_normalized
    3. first touch = first_touch, revenue_first_touch
    4. last touch = last_touch, revenue_last_touch
  3. Customer Type: evaluate attribution for new vs. repeat customers
    1. New Customer attribution
      1. Conversions = ntf_even, ntf_normalized, ntf_first_touch, ntf_last_touch
      2. Revenue = ntf_revenue_even, ntf_revenue_normalized, ntf_revenue_first_touch, ntf_revenue_last_touch
    2. Repeat Customer attribution is computed by deducting new customer metrics from all customer metrics
      1. e.g., even weight repeat customer conversions = even - ntf_even
  4. Time Period Dimension: evaluate performance over different periods of time by adding a time period dimension to the SELECT and GROUP BY statement and converting the date column to different time units (e.g., daily, weekly, monthly, quarterly, yearly)

How did we do?