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