Aggregate MTA - Rockerbox Data Foundation Docs

New Product Update

Aggregate MTA is replacing the Buckets Breakdown schema. Rockerbox will reach out to you directly when you are required to migrate to this new schema.

Description

The Aggregate MTA schema offers granular marketing performance reporting including spend, attributed conversions, and revenue for all conversion events tracked in Rockerbox.
This dataset is aggregated for each date down to the lowest level of granularity that ad performance is tracked in Rockerbox (e.g., ad group level for Google Ads, ad level for Meta).

Table Creation

This table is automatically created upon connecting Rockerbox with your supported warehouse provider.

Partition Keys

aggregate_mta is an external table. There are two partition keys relevant for data access:

conversion_event_id
date

💡 Note: Leverage partition keys when querying the table to improve query efficiency.

Usage Notes

Aggregate (SUM) attributed conversions and revenue and spend across relevant dimension columns (e.g., date, tier_1, tier_2, etc.) to compute KPIs such as CPA and ROAS.
To only extract spend apply WHERE included_spend > 0

Sample SQL (compatible with Snowflake, BigQuery, and Redshift)

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

Primary Key

There is no logical primary key in this table. Each row represents metrics (spend OR conversions/revenue) aggregated by the following dimensions:

date
platform_join_key
tier_1
tier_2
tier_3
tier_4
tier_5

Measures (spend, attributed_conversions, attributed_revenue) should be aggregated across this full set (or subset) of dimensions to avoid double counting.

Field Reference

Name	Description	Type
report	Report name	str
version	Schema version	str
advertiser	Rockerbox Account ID. Note: this static column is only visible in Snowflake integrations	str
currency_code	The reporting currency for both revenue and spend	str
date	Date when the conversion event happened and when the ad spend was incurred	date
even	Conversions attributed via even weight attribution methodology	float
first_touch	Conversions attributed via first touch attribution methodology	int
fx_rate_to_usd	The exchange rate from the local currency to USD for the date. If currency_code is USD, this value will be 1.	float
conversion_event_id	Unique ID of the conversion event being tracked in Rockerbox	int
conversion_event_name	Name of the conversion event being tracked in Rockerbox (e.g., Purchase)	str
last_touch	Conversions attributed via last touch attribution methodology	int
normalized	Conversions attributed via multi-touch attribution model	float
ntf_even	Conversions for new customers attributed via even weight attribution methodology	float
ntf_first_touch	Conversions for new customers attributed via first touch attribution methodology	int
ntf_last_touch	Conversions for new customers attributed via last touch attribution methodology	int
ntf_normalized	Conversions for new customers attributed via multi-touch attribution model	float
ntf_revenue_even	Conversion revenue for new customers attributed via even weight attribution methodology	float
ntf_revenue_first_touch	Conversion revenue for new customers attributed via first touch attribution methodology	float
ntf_revenue_last_touch	Conversion revenue for new customers attributed via last touch attribution methodology	float
ntf_revenue_normalized	Conversion revenue for new customers attributed via multi-touch attribution model	float
platform	The name of the ad platform (e.g., Facebook). This is only populated for platforms with spend tracking in Rockerbox.	str
platform_join_key	The unique ID used to record spend for an advertising platform. This is typically the AD ID, or a composite identifier if the campaign types within a platform support different reporting granularities. This is only populated for platforms with spend tracking in Rockerbox.	str
rb_sync_id	The unique ID of a particular instance of a dataset; this is internal to Rockerbox	int
revenue_even	Conversion revenue attributed via even weight attribution methodology	float
revenue_first_touch	Conversion revenue attributed via first touch attribution methodology	float
revenue_last_touch	Conversion revenue attributed via last touch attribution methodology	float
revenue_normalized	Conversion revenue attributed via multi-touch attribution model	float
included_spend	Total spend for a given line item	float
tier_1	Marketing channel categorization level 1 (most broad), as defined in your mapping rules / reporting taxonomy. For example, a link where referrer url = Google and utm_campaign = cpc may be mapped as tier_1 = Paid Search and tier_2 = Google	str
tier_2	Marketing channel categorization level 2	str
tier_3	Marketing channel categorization level 3	str
tier_4	Marketing channel categorization level 4	str
tier_5	Marketing channel categorization level 5	str
updated_at	Updated timestamp. Note: Rockerbox processes and publishes datasets on a conversion_event_id + date basis; therefore, all records data from the full date are replaced and the updated_at timestamp will be identical for all rows for a given conversion_event_id and date	timestamp

​New Product Update

​Description

​Table Creation

​Partition Keys

​Usage Notes

​Sample SQL (compatible with Snowflake, BigQuery, and Redshift)

​Primary Key

​Field Reference