Taxonomy Lookup

​

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.

​

Table Creation

​

Logical 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