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