Skip to main content

​
Description

  • The Taxonomy Lookup schema provides your standardized Rockerbox reporting taxonomy for all channels with spend tracking.
  • It is aggregated to the lowest reporting level per platform/vendor (e.g., ad group for Google Ads, ad for Meta).
  • The table always shows the latest mapping rules, with updates automatically synced.
⚠️ Note: The taxonomy for non-paid media and organic channels is not included in this table.

​
Table Creation

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

​
Primary Key

  • join_key

​
Usage Notes

  • Join this table against these schemas to apply taxonomy updates to historical data:
    • Aggregate MTA
    • Log Level MTA
    • Platform Performance
  • The tier_1 through tier_5 taxonomy columns have inconsistent capitalization in comparison to legacy schemas in certain instances.
    • Apply LOWER() to the tier_1 to tier_5 columns when joining taxonomy_lookup with ANY of those schemas to ensure consistency in aggregate computations when grouping by tier_x dimensions in your taxonomy.
The Aggregate MTA, Log Level MTA, and Platform Performance schemas already include tier_1 through tier_5, but only with the mapping rules that were in place when the data was created. Joining against taxonomy_lookup applies the most up-to-date mappings.

​
Table Join Guidance

  • Use a left join because taxonomy_lookup only contains mappings for channels with spend tracking.
  • Lowercase and tier_1 through tier_5 to ensure consistency in taxonomy capitalization across historical data
  • Lowercase platform_join_key on aggregate_mta before joining onto the taxonomy_lookup table.

​
Example: Aggregate MTA

SELECT
    COALESCE(t.tier_1, a.tier_1, "No mapping") AS tier_1,
    COALESCE(t.tier_2, a.tier_2) AS tier_2,
    COALESCE(t.tier_3, a.tier_3) AS tier_3,
    COALESCE(t.tier_4, a.tier_4) AS tier_4,
    COALESCE(t.tier_5, a.tier_5) AS tier_5
FROM aggregate_mta a
LEFT JOIN taxonomy_lookup t
    ON t.join_key = lower(a.platform_join_key)

​
Example: Log Level MTA

SELECT
    LOWER(COALESCE(t.tier_1, m.tier_1, "No mapping")) AS tier_1,
    LOWER(COALESCE(t.tier_2, m.tier_2)) AS tier_2,
    LOWER(COALESCE(t.tier_3, m.tier_3)) AS tier_3,
    LOWER(COALESCE(t.tier_4, m.tier_4)) AS tier_4,
    LOWER(COALESCE(t.tier_5, m.tier_5)) AS tier_5
FROM <log_level_mta_table> m
LEFT JOIN taxonomy_lookup t
    ON t.join_key = m.spend_key

​
Example: Platform Performance

SELECT
    LOWER(COALESCE(t.tier_1, m.tier_1, "No mapping")) AS tier_1,
    LOWER(COALESCE(t.tier_2, m.tier_2)) AS tier_2,
    LOWER(COALESCE(t.tier_3, m.tier_3)) AS tier_3,
    LOWER(COALESCE(t.tier_4, m.tier_4)) AS tier_4,
    LOWER(COALESCE(t.tier_5, m.tier_5)) AS tier_5
FROM <platform_performance_meta> m
LEFT JOIN taxonomy_lookup t
    ON t.join_key = m.mta_tiers_join_key

​
Field Reference

NameDescriptionType
reportReport namestr
versionSchema versionstr
advertiserRockerbox Account ID Note: this static column is only visible in Snowflake integrationsstr
parent_platformThe name of the ad platform (e.g., Facebook)str
platformThe name of the ad platform overloaded with context of the version of the Rockerbox integration with a particular ad platform (e.g., facebook_v2)str
join_keyThe unique ID used to record spend for an advertising platform. This is typically the AD ID, or a composite identifier if the campaigns types within a platform support different reporting granularities.str
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 5 (most granular)str