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

# Aggregate MTA Migration

## 🚨 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](https://app.rockerbox.com/v3/data/exports/data_warehouse).
* 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:

```sql theme={null}
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

<Accordion title="Snowflake Create View Statement">
  ```sql theme={null}
  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;
  ```
</Accordion>

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

<Accordion title="BigQuery Create View Statement">
  ```sql theme={null}
  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;
  ```
</Accordion>

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

<Accordion title="Redshift Create View Statement">
  ```sql theme={null}
  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;
  ```
</Accordion>

### 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):**

```sql theme={null}
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. 


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.