Skip to main content

​
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

  • Attributed conversions and revenue are recorded in a different row from spend for a given ad object.
  • Attributed conversions and revenue are in a row where partition=mta. Spend is in a separate row where partition=included_spend.
  • 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.

​
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 single primary key. To create a unique composite key, concatenate the following fields:
  • date
  • platform_join_key
  • tier_1
  • tier_2
  • tier_3
  • tier_4
  • tier_5

​
Field Reference

NameDescriptionType
reportReport namestr
versionSchema versionstr
advertiserRockerbox Account ID. Note: this static column is only visible in Snowflake integrationsstr
currency_codeThe reporting currency for both revenue and spendstr
dateDate when the conversion event happened and when the ad spend was incurreddate
evenConversions attributed via even weight attribution methodologyfloat
first_touchConversions attributed via first touch attribution methodologyint
fx_rate_to_usdThe exchange rate from the local currency to USD for the date. If currency_code is USD, this value will be 1.float
conversion_event_idUnique ID of the conversion event being tracked in Rockerboxint
conversion_event_nameName of the conversion event being tracked in Rockerbox (e.g., Purchase)str
last_touchConversions attributed via last touch attribution methodologyint
normalizedConversions attributed via multi-touch attribution modelfloat
ntf_evenConversions for new customers attributed via even weight attribution methodologyfloat
ntf_first_touchConversions for new customers attributed via first touch attribution methodologyint
ntf_last_touchConversions for new customers attributed via last touch attribution methodologyint
ntf_normalizedConversions for new customers attributed via multi-touch attribution modelfloat
ntf_revenue_evenConversion revenue for new customers attributed via even weight attribution methodologyfloat
ntf_revenue_first_touchConversion revenue for new customers attributed via first touch attribution methodologyfloat
ntf_revenue_last_touchConversion revenue for new customers attributed via last touch attribution methodologyfloat
ntf_revenue_normalizedConversion revenue for new customers attributed via multi-touch attribution modelfloat
platformThe name of the ad platform (e.g., Facebook). This is only populated for platforms with spend tracking in Rockerbox.str
platform_join_keyThe 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_idThe unique ID of a particular instance of a dataset; this is internal to Rockerboxint
revenue_evenConversion revenue attributed via even weight attribution methodologyfloat
revenue_first_touchConversion revenue attributed via first touch attribution methodologyfloat
revenue_last_touchConversion revenue attributed via last touch attribution methodologyfloat
revenue_normalizedConversion revenue attributed via multi-touch attribution modelfloat
included_spendTotal spend for a given line itemfloat
tier_1Marketing 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 = Googlestr
tier_2Marketing channel categorization level 2str
tier_3Marketing channel categorization level 3str
tier_4Marketing channel categorization level 4str
tier_5Marketing channel categorization level 5str
partitionIndicates whether the row records attributed conversions and revenue (partition=mta) or spend (partition=spend)str
updated_atUpdated 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 datetimestamp