Aggregate MTA Migration - Rockerbox Data Foundation Docs

🚨 Required Action

All usage of the Buckets Breakdown schema must be migrated to the aggregate_mta table by April 30, 2026. After this date, Buckets Breakdown tables will stop receiving updates (historical data will remain queryable).

What’s Changing

New table: aggregate_mta replaces all Buckets Breakdown tables in your data share.
Rolled out alongside existing tables so you can build and test without impacting production.
aggregate_mta is fully backfilled to your first clean reporting date in Rockerbox.

Key Benefits

Significantly faster and more reliable delivery of aggregate attribution and spend — KPIs are available before your stakeholders start their day.
Fewer tables to query and manage with all aggregate attribution data across all conversion events available in one streamlined reporting table.

Migration Timeline

Deprecation date: April 30, 2026 — Buckets Breakdown tables stop receiving updates.

📜 Historical Access: Historical data in Buckets Breakdown tables will still be available after the deprecation date. We do not intend to remove these tables at this point in time.

Migration Guidance

General Notes

Rockerbox will not drop or rename existing Buckets Breakdown tables so as to not break your production reporting.
Create views to replicate Buckets Breakdown tables, suffixed with _v2.
Change the table reference in all queries to point to the view.
If you don’t want to create backwards compatible views, then review the Schema Differences section below for a full accounting of the changes.

⚠️ Important: If merging aggregate_mta with historical data sourced from Buckets Breakdown schema, then lowercase all platform_join_key and taxonomy columns (tier_1–tier_5) in the historical data from the Buckets Breakdown source table to ensure consistency. Example:

LOWER(tier_1) AS tier_1

Snowflake Instructions

Pre-Requisites

Local database and schema where you can define views.

Note: Rockerbox shares data via Secure Data Sharing. The shared database is read-only.

SQL Statement

Snowflake Create View Statement

CREATE OR REPLACE VIEW <TARGET_DB>.<TARGET_SCHEMA>.<VIEW_NAME> AS
SELECT
    advertiser,
    currency_code,
    date,
    SUM(even)                      AS even,
    SUM(first_touch)               AS first_touch,
    fx_rate_to_usd,
    conversion_event_id            AS identifier,
    SUM(last_touch)                AS last_touch,
    SUM(normalized)                AS normalized,
    SUM(ntf_even)                  AS ntf_even,
    SUM(ntf_first_touch)           AS ntf_first_touch,
    SUM(ntf_last_touch)            AS ntf_last_touch,
    SUM(ntf_normalized)            AS ntf_normalized,
    SUM(ntf_revenue_even)          AS ntf_revenue_even,
    SUM(ntf_revenue_first_touch)   AS ntf_revenue_first_touch,
    SUM(ntf_revenue_last_touch)    AS ntf_revenue_last_touch,
    SUM(ntf_revenue_normalized)    AS ntf_revenue_normalized,
    platform,
    LOWER(platform_join_key)       AS platform_join_key,
    MAX(rb_sync_id)                AS rb_sync_id,
    report,
    SUM(revenue_even)              AS revenue_even,
    SUM(revenue_first_touch)       AS revenue_first_touch,
    SUM(revenue_last_touch)        AS revenue_last_touch,
    SUM(revenue_normalized)        AS revenue_normalized,
    SUM(included_spend)            AS spend,
    LOWER(tier_1)                  AS tier_1,
    LOWER(tier_2)                  AS tier_2,
    LOWER(tier_3)                  AS tier_3,
    LOWER(tier_4)                  AS tier_4,
    LOWER(tier_5)                  AS tier_5,
    'attribution'                  AS type,
    MAX(updated_at)                AS updated_at
FROM <SOURCE_DB>.<SOURCE_SCHEMA>.<SOURCE_TABLE>
WHERE conversion_event_id = <12345>
GROUP BY
    advertiser,
    currency_code,
    date,
    fx_rate_to_usd,
    conversion_event_id,
    platform,
    platform_join_key,
    report,
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5,
    type;

SQL Parameters

Parameter	Description
`TARGET_DB`	Database where the view will reside
`TARGET_SCHEMA`	Schema where the view will reside
`VIEW_NAME`	Name of the view (match source table name + suffix, e.g. `_v2`)
`CONVERSION_EVENT_ID`	Conversion ID to filter on (match Buckets Breakdown table)
`SOURCE_DB`	Rockerbox-shared database
`SOURCE_SCHEMA`	Rockerbox-shared schema
`SOURCE_TABLE`	Rockerbox Buckets Breakdown source table

BigQuery Instructions

Pre-Requisites

A project created with BigQuery resource enabled.
A dataset within the aforementioned project.

Note: these are the same pre-requisites for connecting Rockerbox with BigQuery, so there is no need to create a new project and for the specific purpose of creating these compatibility views.

SQL Statement

BigQuery Create View Statement

CREATE OR REPLACE VIEW `<TARGET_PROJECT>.<TARGET_DATASET>.<VIEW_NAME>` AS
SELECT
    LOWER(tier_1)                 AS tier_1,
    LOWER(tier_2)                 AS tier_2,
    LOWER(tier_3)                 AS tier_3,
    LOWER(tier_4)                 AS tier_4,
    LOWER(tier_5)                 AS tier_5,
    platform,
    LOWER(platform_join_key)      AS platform_join_key,
    SUM(first_touch)              AS first_touch,
    SUM(ntf_first_touch)          AS ntf_first_touch,
    SUM(revenue_first_touch)      AS revenue_first_touch,
    SUM(ntf_revenue_first_touch)  AS ntf_revenue_first_touch,
    SUM(last_touch)               AS last_touch,
    SUM(ntf_last_touch)           AS ntf_last_touch,
    SUM(revenue_last_touch)       AS revenue_last_touch,
    SUM(ntf_revenue_last_touch)   AS ntf_revenue_last_touch,
    SUM(even)                     AS even,
    SUM(ntf_even)                 AS ntf_even,
    SUM(revenue_even)             AS revenue_even,
    SUM(ntf_revenue_even)         AS ntf_revenue_even,
    SUM(normalized)               AS normalized,
    SUM(ntf_normalized)           AS ntf_normalized,
    SUM(revenue_normalized)       AS revenue_normalized,
    SUM(ntf_revenue_normalized)   AS ntf_revenue_normalized,
    SUM(included_spend)           AS spend,
    currency_code,
    fx_rate_to_usd,
    MAX(rb_sync_id)               AS rb_sync_id,
    MAX(updated_at)               AS updated_at,
    date
FROM `<SOURCE_PROJECT>.<SOURCE_DATASET>.<SOURCE_TABLE>`
WHERE conversion_event_id = <12345>
GROUP BY
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5,
    platform,
    platform_join_key,
    currency_code,
    fx_rate_to_usd,
    date;

SQL Parameters

Parameter	Description
`TARGET_PROJECT`	Project where the view will reside
`TARGET_DATASET`	Dataset where the view will reside
`VIEW_NAME`	Name of the view (match source table name + suffix, e.g. `_v2`)
`CONVERSION_EVENT_ID`	Conversion ID to filter on (match Buckets Breakdown table)
`SOURCE_PROJECT`	Rockerbox-shared project
`SOURCE_DATASET`	Rockerbox-shared dataset
`SOURCE_TABLE`	Rockerbox Buckets Breakdown source table

Redshift Instructions

Pre-Requisites

Local database and schema where you can define views.
USAGE permissions on Rockerbox external schema (Glue + S3).

Note: External schema used to share data is read-only.

SQL Statement

Redshift Create View Statement

CREATE OR REPLACE VIEW <target_db>.<target_schema>.<view_name> AS
SELECT
    LOWER(tier_1)                AS tier_1,
    LOWER(tier_2)                AS tier_2,
    LOWER(tier_3)                AS tier_3,
    LOWER(tier_4)                AS tier_4,
    LOWER(tier_5)                AS tier_5,
    platform,
    LOWER(platform_join_key)     AS platform_join_key,
    SUM(first_touch)             AS first_touch,
    SUM(ntf_first_touch)         AS ntf_first_touch,
    SUM(revenue_first_touch)     AS revenue_first_touch,
    SUM(ntf_revenue_first_touch) AS ntf_revenue_first_touch,
    SUM(last_touch)              AS last_touch,
    SUM(ntf_last_touch)          AS ntf_last_touch,
    SUM(revenue_last_touch)      AS revenue_last_touch,
    SUM(ntf_revenue_last_touch)  AS ntf_revenue_last_touch,
    SUM(even)                    AS even,
    SUM(ntf_even)                AS ntf_even,
    SUM(revenue_even)            AS revenue_even,
    SUM(ntf_revenue_even)        AS ntf_revenue_even,
    SUM(normalized)              AS normalized,
    SUM(ntf_normalized)          AS ntf_normalized,
    SUM(revenue_normalized)      AS revenue_normalized,
    SUM(ntf_revenue_normalized)  AS ntf_revenue_normalized,
    SUM(included_spend)          AS spend,
    currency_code,
    fx_rate_to_usd,
    MAX(rb_sync_id)              AS rb_sync_id,
    MAX(updated_at)              AS updated_at,
    "date"                       AS date
FROM <source_db>.<source_schema>.<source_table>
WHERE conversion_event_id = <12345>
GROUP BY
    tier_1,
    tier_2,
    tier_3,
    tier_4,
    tier_5,
    platform,
    platform_join_key,
    currency_code,
    fx_rate_to_usd,
    "date"
WITH NO SCHEMA BINDING;

SQL Parameters

Parameter	Description
`TARGET_DB`	Database where the view will reside
`TARGET_SCHEMA`	Schema where the view will reside
`VIEW_NAME`	Name of the view (match source table name + suffix, e.g. `_v2`)
`CONVERSION_EVENT_ID`	Conversion ID to filter on (match Buckets Breakdown table)
`SOURCE_DB`	Rockerbox external database
`SOURCE_SCHEMA`	Rockerbox external schema
`SOURCE_TABLE`	Rockerbox Buckets Breakdown source table

Schema Differences

Full Schema Diff

Change	Legacy Schema (Buckets Breakdown)	Legacy Type	New Schema (Aggregate MTA)	New Type	Notes
Removed	type	str	—	—	Only visible in Snowflake
	report	str	report	str	Only visible in Snowflake
Added	—	—	version	str	Only visible in Snowflake
Added	—	—	partition	str
	advertiser	str	advertiser	str	Only visible in Snowflake
Added/Renamed	identifier	int	conversion_event_id	int	Redshift/BigQuery: new column; Snowflake: renamed identifier
	date	date	date	date
	tier_1	str	tier_1	str
	tier_2	str	tier_2	str
	tier_3	str	tier_3	str
	tier_4	str	tier_4	str
	tier_5	str	tier_5	str
	platform	str	platform	str
	platform_join_key	str	platform_join_key	str
Added	—	—	conversion_event_name	str
	first_touch	int	first_touch	int
	ntf_first_touch	int	ntf_first_touch	int
	revenue_first_touch	float	revenue_first_touch	float
	ntf_revenue_first_touch	float	ntf_revenue_first_touch	float
	last_touch	int	last_touch	int
	ntf_last_touch	int	ntf_last_touch	int
	revenue_last_touch	float	revenue_last_touch	float
	ntf_revenue_last_touch	float	ntf_revenue_last_touch	float
	even	float	even	float
	ntf_even	float	ntf_even	float
	revenue_even	float	revenue_even	float
	ntf_revenue_even	float	ntf_revenue_even	float
	normalized	float	revenue_even	float
	ntf_normalized	float	ntf_normalized	float
	revenue_normalized	float	revenue_normalized	float
	ntf_revenue_normalized	float	ntf_revenue_normalized	float
Renamed	spend	float	included_spend	float
	currency_code	str	currency_code	str
	fx_rate_to_usd	float	fx_rate_to_usd	float
	rb_sync_id	int	rb_sync_id	int
	updated_at	timestamp	updated_at	timestamp

Many Tables > One Table

Before: One table per conversion event.
- N tables: buckets_breakdown_add_to_cart, buckets_breakdown_purchase
After: One unified table includes conversion_event_id and conversion_event_name for segmentation.
- One new table: aggregate_mta
Guidance: Add a filter to replicate current table behavior.
- WHERE conversion_event_name = <name>

Partitioned Rows

Before: Spend and attributed conversions/revenue in the same row.
After: Attribute conversions/revenue in row where partition=mta; spend in separate row where partition=included_spend.
Guidance: Aggregate (SUM) metrics across relevant dimension columns (date, tier_1, tier_2, etc.) to compute KPIs such as CPA and ROAS.

🚨 Rows may look duplicated compared to the Buckets Breakdown schema because attribution and spend data are now split across separate rows. This is expected behavior.

Sample SQL (works in Redshift, BigQuery, Snowflake):

SELECT
    date,
    tier_1,
    tier_2,
    SUM(even)           AS even,
    SUM(included_spend) AS spend
FROM aggregate_mta
GROUP BY date, tier_1, tier_2;

Case Differences

Issue: platform_join_key and tier_1 through tier_5 may have inconsistent case vs. legacy tables in certain instances.
Guidance: Apply LOWER() in queries before joining or merging data from aggregate_mta with ANY of the legacy schemas.

​🚨 Required Action

​What’s Changing

​Key Benefits

​Migration Timeline

​Migration Guidance

​General Notes

​Snowflake Instructions

​Pre-Requisites

​SQL Statement

​SQL Parameters

​BigQuery Instructions

​Pre-Requisites

​SQL Statement

​SQL Parameters

​Redshift Instructions

​Pre-Requisites

​SQL Statement

​SQL Parameters

​Schema Differences

​Full Schema Diff

​Many Tables > One Table

​Partitioned Rows

​Case Differences

🚨 Required Action

What’s Changing

Key Benefits

Migration Timeline

Migration Guidance

General Notes

Snowflake Instructions

Pre-Requisites

SQL Statement

SQL Parameters

BigQuery Instructions

Pre-Requisites

SQL Statement

SQL Parameters

Redshift Instructions

Pre-Requisites

SQL Statement

SQL Parameters

Schema Differences

Full Schema Diff

Many Tables > One Table

Partitioned Rows

Case Differences