> ## 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. # Platform - Facebook ## Description The **Platform - Facebook** dataset contains Facebook ad platform performance metrics and conversion reporting at the hourly, ad-level granularity. *** ## Partition Keys * `identifier` * `date` 💡 **Note:** Leverage partition keys when querying the table to improve query efficiency. *** ## Logical Primary Key These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table. * `identifier` * `date` * `utc_hour` * `ad_id` *** ## Field Reference | Order | Name | Description | Type | | ----- | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- | --------- | | 1 | advertiser | Rockerbox Account ID | str | | 2 | type | Report type (e.g., `platform_data`, `attribution`) | str | | 3 | platform | Name of platform (e.g., `Facebook`) | str | | 4 | report | The name of the report (only visible in Snowflake integrations) | str | | 5 | identifier | Ad platform account identifier | str | | 6 | date | Date when the platform metrics occurred | date | | 7 | utc\_hour | UTC hour when the platform metrics occurred | int | | 8 | tier\_1 | Five-level categorization tiers aligned to UI taxonomy (most broad) | str | | 9 | tier\_2 | Five-level categorization tiers aligned to UI taxonomy | str | | 10 | tier\_3 | Five-level categorization tiers aligned to UI taxonomy | str | | 11 | tier\_4 | Five-level categorization tiers aligned to UI taxonomy | str | | 12 | tier\_5 | Five-level categorization tiers aligned to UI taxonomy (most granular) | str | | 13 | mta\_tiers\_join\_key | Platform spend identifier (usually Ad ID or composite key) | str | | 14 | campaign\_name | The name of the ad campaign. A campaign contains ad sets and ads | str | | 15 | campaign\_id | The unique ID of the ad campaign | str | | 16 | adset\_name | The name of the ad set | str | | 17 | adset\_id | The unique ID of the ad set | str | | 18 | ad\_name | The name of the ad | str | | 19 | ad\_id | The unique ID of the ad | str | | 20 | spend | The estimated total amount spent in the ad platform account’s local currency | float | | 21 | currency\_code | Reporting currency for revenue and spend | str | | 22 | spend\_usd | The estimated total amount spent in USD | float | | 23 | clicks | The number of clicks on the ad | int | | 24 | impressions | The number of times the ads were shown on screen | int | | 25 | inline\_link\_clicks | The number of clicks on links to select destinations or experiences, on or off Facebook-owned properties (fixed 1-day-click attribution window) | int | | 26 | outbound\_clicks | The number of clicks on links that take users off Facebook-owned properties | int | | 27 | view\_1d | The total number of view-through conversions within a 1-day lookback window | dict | | 28 | click\_1d | The total number of click-through conversions within a 1-day lookback window | dict | | 29 | click\_7d | The total number of click-through conversions within a 7-day lookback window | dict | | 30 | view\_1d\_value | The total value of view-through conversions (local currency) within a 1-day lookback window | dict | | 31 | click\_1d\_value | The total value of click-through conversions (local currency) within a 1-day lookback window | dict | | 32 | click\_7d\_value | The total value of click-through conversions (local currency) within a 7-day lookback window | dict | | 33 | view\_1d\_value\_usd | The total value of view-through conversions converted to USD within a 1-day lookback window | dict | | 34 | click\_1d\_value\_usd | The total value of click-through conversions converted to USD within a 1-day lookback window | dict | | 35 | click\_7d\_value\_usd | The total value of click-through conversions converted to USD within a 7-day lookback window | dict | | 36 | rb\_sync\_id | Rockerbox internal sync identifier | str | | 37 | updated\_at | Timestamp when the record was last updated | timestamp | *** ## Nested Fields The following fields are nested JSON objects keyed by Facebook conversion event name: * `view_1d` * `click_1d` * `click_7d` * `view_1d_value` * `click_1d_value` * `click_7d_value` * `view_1d_value_usd` * `click_1d_value_usd` * `click_7d_value_usd` ### Example Stored Object ```json theme={null} { "purchase": 12, "add_to_cart": 41 } ``` ### Snowflake - Querying Nested Fields #### Extract a Single Event ```sql theme={null} select date, ad_id, click_1d:"purchase"::number as purchase_click_1d, click_1d_value:"purchase"::float as purchase_value_click_1d from ..; ``` #### Flatten All Events Into Rows ```sql theme={null} select t.date, t.ad_id, f.key as conversion_event, f.value::number as conversions_click_1d from .. t, lateral flatten(input => t.click_1d) f; ``` ### Redshift - Querying Nested Fields (SUPER type) #### Extract a Single Event ```sql theme={null} select date, ad_id, click_1d['purchase']::int as purchase_click_1d, click_1d_value['purchase']::decimal(18,4) as purchase_value_click_1d from ..; ``` #### Flatten All Events Into Rows ```sql theme={null} select select t.date, t.ad_id, kv.key as conversion_event, kv.value::int as conversions_click_1d from .. t, t.click_1d as kv; ``` ### BigQuery - Querying Nested Fields (JSON type) #### Extract a Single Event ```sql theme={null} select date, ad_id, cast(json_value(click_1d, '$.purchase') as int64) as purchase_click_1d, cast(json_value(click_1d_value, '$.purchase') as float64) as purchase_value_click_1d from ..; ``` #### Flatten All Events Into Rows ```sql theme={null} select t.date, t.ad_id, k as conversion_event, cast(json_value(t.click_1d, concat('$.', k)) as int64) as conversions_click_1d from .. t, unnest(json_keys(t.click_1d)) as k; ```