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

## Description

The **Platform - Snapchat** dataset contains delivery, spend, engagement, video, and conversion performance metrics from Snapchat Ads. Data is available at the ad-level by date and hour.

***

## 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 | Advertising platform name (`Snapchat`). | str |
| 4 | report | Dataset name (`platform_performance_snapchat`). | str |
| 5 | identifier | Unique identifier of the Snapchat ad account. | 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 join platform spend to MTA datasets. Typically `ad_id`. | str |
| 14 | campaign\_name | Campaign name in Snapchat. | str |
| 15 | campaign\_id | Unique campaign ID in Snapchat. | str |
| 16 | ad\_squad\_name | Ad squad name. | str |
| 17 | ad\_squad\_id | Unique ad squad ID. | str |
| 18 | ad\_name | Ad name. | str |
| 19 | ad\_id | Unique ad ID. | str |
| 20 | ad\_type | Snap ad format (e.g., `SNAP_AD`, `LONGFORM_VIDEO`). | str |
| 21 | spend | Estimated total spend in the account’s local currency. | float |
| 22 | currency\_code | ISO currency code of the ad account (e.g., `USD`, `EUR`). | str |
| 23 | spend\_usd | Estimated total spend in USD. | float |
| 24 | swipes | Swipe-up count. | int |
| 25 | impressions | Total impressions. | int |
| 26 | earned\_impressions | Impressions generated after being shared via Chat or Stories. | int |
| 27 | paid\_impressions | Impressions served via paid delivery. | int |
| 28 | total\_impressions | Combined paid and earned impressions. | int |
| 29 | screen\_time\_millis | Total time spent viewing Top Snap ads (milliseconds). | float |
| 30 | avg\_screen\_time\_millis | Average Top Snap view time (milliseconds). | float |
| 31 | quartile\_1 | Video views to 25%. | int |
| 32 | quartile\_2 | Video views to 50%. | int |
| 33 | quartile\_3 | Video views to 75%. | int |
| 34 | view\_completion | Video views to completion. | int |
| 35 | video\_views | Impressions meeting qualifying video view criteria (≥2 seconds consecutive watch time or swipe-up). | int |
| 36 | video\_views\_15s | Impressions meeting ≥15 seconds watched (or 97% completion if shorter) or swipe-up. | int |
| 37 | video\_views\_time\_based | Impressions meeting ≥2 seconds consecutive watch time (excluding swipe-ups). | int |
| 38 | saves | Number of times a lens/filter was saved to Memories. | int |
| 39 | shares | Number of times a lens/filter was shared via Chat or Stories. | int |
| 40 | attachment\_impressions | Impression count from attachments. | int |
| 41 | attachment\_quartile\_1 | Long-form video views to 25%. | int |
| 42 | attachment\_quartile\_2 | Long-form video views to 50%. | int |
| 43 | attachment\_quartile\_3 | Long-form video views to 75%. | int |
| 44 | attachment\_view\_completion | Long-form video views to completion. | int |
| 45 | attachment\_avg\_view\_time\_millis | Average attachment view time (milliseconds). | float |
| 46 | attachment\_total\_view\_time\_millis | Total attachment view time (milliseconds). | int |
| 47 | view\_1\_day | View-through conversions within a 1-day lookback window. | dict |
| 48 | view\_7\_day | View-through conversions within a 7-day lookback window. | dict |
| 49 | swipe\_1\_day | Click-through (swipe) conversions within a 1-day lookback window. | dict |
| 50 | swipe\_7\_day | Click-through (swipe) conversions within a 7-day lookback window. | dict |
| 51 | swipe\_28\_day | Click-through (swipe) conversions within a 28-day lookback window. | dict |
| 52 | view\_1\_day\_value | Total value of 1-day view-through conversions (local currency). | dict |
| 53 | view\_7\_day\_value | Total value of 7-day view-through conversions (local currency). | dict |
| 54 | swipe\_1\_day\_value | Total value of 1-day click-through conversions (local currency). | dict |
| 55 | swipe\_7\_day\_value | Total value of 7-day click-through conversions (local currency). | dict |
| 56 | swipe\_28\_day\_value | Total value of 28-day click-through conversions (local currency). | dict |
| 57 | view\_1\_day\_value\_usd | USD value of 1-day view-through conversions. | dict |
| 58 | view\_7\_day\_value\_usd | USD value of 7-day view-through conversions. | dict |
| 59 | swipe\_1\_day\_value\_usd | USD value of 1-day click-through conversions. | dict |
| 60 | swipe\_7\_day\_value\_usd | USD value of 7-day click-through conversions. | dict |
| 61 | swipe\_28\_day\_value\_usd | USD value of 28-day click-through conversions. | dict |
| 62 | rb\_sync\_id | Rockerbox-generated identifier used to sync the dataset to your warehouse. | str |
| 63 | updated\_at | Timestamp of the most recent row update. | timestamp |

***

## Nested Fields

The following fields are nested JSON objects keyed by Snapchat conversion event name:

`view_1_day`
`view_7_day`
`swipe_1_day`
`swipe_7_day`
`swipe_28_day`
`view_1_day_value`
`view_7_day_value`
`swipe_1_day_value`
`swipe_7_day_value`
`swipe_28_day_value`
`view_1_day_value_usd`
`view_7_day_value_usd`
`swipe_1_day_value_usd`
`swipe_7_day_value_usd`
`swipe_28_day_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,
  view_1_day:"purchase"::number as purchase,
  view_1_day_value:"purchase"::float as purchase_value
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_view_1_day
from <database>.<schema>.<table_name> t,
  lateral flatten(input => t.view_1_day) f;
```

### Redshift - Querying Nested Fields (SUPER type)

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  view_1_day['purchase']::int as purchase,
  view_1_day_value['purchase']::decimal(18,4) as purchase_value
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
from <database>.<schema>.<table_name> t,
  t.view_1_day as kv;
```

### BigQuery - Querying Nested Fields (JSON type)

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  cast(json_value(view_1_day, '$.purchase') as int64) as purchase,
  cast(json_value(view_1_day_value, '$.purchase') as float64) as purchase_value
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.view_1_day, concat('$.', k)) as int64) as conversions
from <project_id>.<dataset>.<table_name> t,
unnest(json_keys(t.view_1_day)) as k;
```


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