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

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

⚠️ **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.

***

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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

| 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  |
| parent\_platform | The name of the ad platform (e.g., Facebook)                                                                                                                                                                                                      | str  |
| platform         | The 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\_key        | The 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\_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 (most granular)                                                                                                                                                                                          | str  |