> ## 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 <database>.<schema>.<table_name>;
```

#### 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 <database>.<schema>.<table_name> 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 <database>.<schema>.<table_name>;
```

#### 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 <database>.<schema>.<table_name> 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 <project_id>.<dataset>.<table_name>;
```

#### 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 <project_id>.<dataset>.<table_name> t,
unnest(json_keys(t.click_1d)) as k;
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.