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