> ## 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 - TikTok ## Description The **Platform - TikTok** dataset contains TikTok ad platform performance metrics 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 | The dataset type (e.g., `platform_data`) | str | | 3 | platform | The name of the ad platform (e.g., Adwords) | str | | 4 | report | The name of the dataset (e.g., `platform_performance_adwords`) | str | | 5 | identifier | The unique identifier of the account in the advertising platform | str | | 6 | date | The date that the action occured | date | | 7 | utc\_hour | The UTC hour that the action occured | 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 | The unique identifier used to pull spend from an advertisting platform. This is typically the ad\_id, but may differ based on your account setup | str | | 14 | campaign\_name | The name of the campaign. A campaign is made up of a series of ad groups, each with its own unique targeting settings, optimization goals, budgets, and ads | str | | 15 | campaign\_id | The unique identifier of the campaign | str | | 16 | adgroup\_name | The name of the ad group. An ad group relates to one campaign and contains a set of similar ads | str | | 17 | adgroup\_id | The unique identifier of the ad group | str | | 18 | ad\_name | The name of the ad | str | | 19 | ad\_id | The unique identifier of the ad | str | | 20 | spend | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in the ad platform account’s local currency | float | | 21 | currency\_code | The local currency of your account in the ad platform (e.g., EUR, USD) | str | | 22 | spend\_usd | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in USD. | float | | 23 | clicks | Clicks recorded to CTA button, ad caption, nickname, profile picture, and swipe-left | int | | 24 | impressions | The number of times your ads were on screen | int | | 25 | follows | The number of new followers that were gained within 1 day of a user seeing a paid ad | int | | 26 | likes | The number of likes the video creative received within 1 day of a user seeing a paid ad | int | | 27 | comments | The number of comments your video creative received within 1 day of a user seeing a paid ad | int | | 28 | reach | The number of unique users who saw your ads at least once. This metric is estimated | int | | 29 | frequency | The average number of times each person saw your ad | float | | 30 | shares | The number of times your video creative was shared within 1 day of a user seeing a paid ad | int | | 31 | profile\_visits | The number of profile visits the paid ad drove during the campaign | int | | 32 | secondary\_goal\_result | The number of times your ad achieved an outcome, based on the secondary goal you selected. | int | | 33 | video\_play\_actions | The number of times your video starts to play. Replays will not be counted | int | | 34 | average\_video\_play | The average time your video was played per single video view, including any time spent replaying the video | float | | 35 | average\_video\_play\_per\_user | The average amount of time your video ads played per person. Including anytime spent replaying the video | float | | 36 | video\_watched\_2s | Number of times your video was played for at least 2 seconds. Replays will not be counted | int | | 37 | video\_watched\_6s | Number of times your video was played for at least 6 seconds. Replays will not be counted | int | | 38 | video\_views\_p25 | The number of times your video was played at 25% of its length. Replays will not be counted | int | | 39 | video\_views\_p50 | The number of times your video was played at 50% of its length. Replays will not be counted | int | | 40 | video\_views\_p75 | The number of times your video was played at 75% of its length. Replays will not be counted | int | | 41 | video\_views\_p100 | The number of times your video was played at 100% of its length. Replays will not be counted | int | | 42 | clicks\_on\_music\_disc | The number of clicks recorded to Music Disc icon and Music title | int | | 43 | conversions | The number of times your ad achieved an outcome based on the objective and settings you selected | dict | | 44 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | str | | 45 | updated\_at | | timestamp | *** ## Nested Fields The following fields are nested JSON objects keyed by Facebook conversion event name: * `conversions` ### Example Stored Object ```json theme={null} { "conversion": 5, "real_time_conversion": 3, "real_time_result": 13, "result": 20 } ``` ### Nested Field Definitions * **conversion** — attributed conversion events based on the ad group’s configured attribution window (finalized reporting metric). * **real\_time\_conversion** — near real-time conversion events reported before the full attribution window has matured; primarily used for in-flight pacing, rapid performance checks, and short-term bid/budget adjustments. * **result** — Attributed events tied specifically to the campaign’s selected optimization objective (the primary KPI metric). * **real\_time\_result** — near real-time version of the objective-based result metric; used by marketers to monitor live optimization performance and make same-day creative or budget decisions. ### 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 ..; ``` #### 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 .. 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 ..; ``` #### 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 .. 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 ..; ``` #### 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 .. t, unnest(json_keys(t.click_1d)) as k; ```