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

## Description

The **Platform - Facebook** dataset contains delivery, spend, and conversion metrics at an hourly, ad level from Microsoft Advertising (Bing Ads).

***

## 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 | Field | Description | Type |
| - | - | - | - |
| 1 | advertiser | Rockerbox account ID. | str |
| 2 | type | Dataset type (e.g., `platform_data`). | str |
| 3 | platform | Name of the advertising platform (e.g., Bing). | str |
| 4 | report | Dataset name (e.g., `platform_performance_bing`). | str |
| 5 | identifier | Unique identifier of the account in the advertising platform. | str |
| 6 | date | Date the performance metrics occurred. | date |
| 7 | utc\_hour | UTC hour the performance metrics occurred. | int |
| 8 | tier\_1 | Marketing channel categorization level 1. | str |
| 9 | tier\_2 | Marketing channel categorization level 2. | str |
| 10 | tier\_3 | Marketing channel categorization level 3. | str |
| 11 | tier\_4 | Marketing channel categorization level 4. | str |
| 12 | tier\_5 | Marketing channel categorization level 5. | str |
| 13 | mta\_tiers\_join\_key | Identifier used to pull spend from an advertising platform. Typically `ad_id`, but may differ based on account setup. | str |
| 14 | campaign\_name | Campaign name. | str |
| 15 | campaign\_id | Microsoft Advertising–assigned unique identifier of a campaign. | str |
| 16 | ad\_group\_name | Ad group name. | str |
| 17 | ad\_group\_id | Microsoft Advertising–assigned unique identifier of an ad group. | str |
| 18 | ad\_title | Ad title. | str |
| 19 | ad\_id | Microsoft Advertising–assigned unique identifier of an ad. | str |
| 20 | spend | Estimated total spend in the ad account’s local currency. | float |
| 21 | currency\_code | ISO currency code of the ad account (e.g., `USD`, `EUR`). | str |
| 22 | spend\_usd | Estimated total spend in USD. | float |
| 23 | clicks | Number of clicks on an ad. | int |
| 24 | impressions | Number of times an ad was displayed on search results pages. | int |
| 25 | conversions | Conversion metrics object (includes qualified, all, and view-through conversion counts across conversion goals). | dict |
| 26 | rb\_sync\_id | Identifier used by Rockerbox to sync the dataset to your warehouse. | str |
| 27 | updated\_at | Timestamp of the most recent row update. | 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.