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