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

## Description

The **Platform - Pinterest** dataset contains performance metrics and conversion reporting at the daily, pin-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`
* `ad_id`
* `pin_promotion_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 | tier\_1 | Marketing channel categorization level 1 | str |
| 8 | tier\_2 | Marketing channel categorization level 2 | str |
| 9 | tier\_3 | Marketing channel categorization level 3 | str |
| 10 | tier\_4 | Marketing channel categorization level 4 | str |
| 11 | tier\_5 | Marketing channel categorization level 5 | str |
| 12 | 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 |
| 13 | campaign\_name | The name of the advertising campaign | str |
| 14 | campaign\_id | The unique identifier associated with a given advertising campaign | str |
| 15 | campaign\_status | The status of a given campaign – active, paused, or archived | str |
| 16 | ad\_group\_name | The name of the ad group | str |
| 17 | ad\_group\_id | The unique identifier associated with a given ad group | str |
| 18 | ad\_group\_status | The status of given ad group – running, paused, not started, completed, advertiser disabled, or archived | str |
| 19 | pin\_promotion\_ad\_group\_type | | str |
| 20 | pin\_promotion\_ad\_group\_type\_2 | | str |
| 21 | pin\_promotion\_name | The name of the promoted pin | str |
| 22 | pin\_promotion\_id | The unique identifier of the promoted pin (i.e., ad) | str |
| 23 | pin\_promotion\_status | The status of a pin promotion – active, other, or paused. | str |
| 24 | pin\_id | The unique identifier of the pin | str |
| 25 | ad\_id | The unique identifier associated with a given ad | str |
| 26 | 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 | foat |
| 27 | currency\_code | The local currency of your account in the ad platform (e.g., EUR, USD) | str |
| 28 | spend\_usd | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in USD. | foat |
| 29 | clicks | The total number of clicks on your Pin or ad to content on Pinterest or off of Pinterest (e.g., the advertisers website) | int |
| 30 | clickthrough\_1\_gross | Pin clicks for first order ad events (e.g., clicks on a promoted pin) | int |
| 31 | clickthrough\_2 | Pin clicks for downstream or earned events (e.g., clicks on a saved instance of a promoted pin). Advertisers are not billed for earned activity. | int |
| 32 | impressions | The number of times your Pins or ads were on screen. | int |
| 33 | impression\_1\_gross | Impressions for first order ad events (e.g., promoted pins) | int |
| 34 | impression\_2 | Impressions for downstream or earned events (e.g., impressions of a saved instance of a promoted pin). | int |
| 35 | repin\_1 | The number of times the pin was saved to another user’s board. This metric captures repins of first order ad events (e.g., repins of promoted pins). | int |
| 36 | repin\_2 | The number of times the pin was saved to another user’s board. This metric captures repins of downstream or earned events (e.g., repins of saved instances of promoted pins). | int |
| 37 | outbound\_click\_1 | An outbound click is a click on a pin that lead the user to a destination off of Pinterest (i.e., the advertiser’s websiter). This metric aggregates all outbound clicks for first order ad events (e.g., outbound clicks of promoted pins). | int |
| 38 | outbound\_click\_2 | An outbound click is a click on a pin that lead the user to a destination off of Pinterest (i.e., the advertiser’s websiter). This metric aggregates all outbound clicks for downstream or earned events (e.g., outbound clicks of saved instances of promoted pins). | int |
| 39 | engagement\_1 | Engagements track any interactions with your Pins – this includes saves, Pin clicks, outbound clicks, carousel card swipes, secondary creative (collections) clicks and Idea Pin forward/backward swipes. This metric captures all engagement for first order ad events (e.g., promoted pins). | int |
| 40 | engagement\_2 | Engagements track any interactions with your Pins – this includes saves, Pin clicks, outbound clicks, carousel card swipes, secondary creative (collections) clicks and Idea Pin forward/backward swipes. This metric captures all engagement for secondary or earned events (i.e., saved instances of promoted pins). | int |
| 41 | video\_mrc\_views\_2 | The number of times your video ad played continuously for 2 seconds while at least 50% in view after being saved to another person’s board. Note that metrics ending in \_2 refer to actions taking on an organic (non￾ad) Pin. | int |
| 42 | video\_avg\_watchtime\_in\_second\_2 | The average time people watched your video. This includes people who rewatch your video in the same day. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | foat |
| 43 | video\_p0\_combined\_2 | The number of times the non-ad Pin version of your video played while at least 50% in view, but did not reach 25% played. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 44 | video\_p25\_combined\_2 | The number of times the non-ad version of your Pin played at least a quarter of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 45 | video\_p50\_combined\_2 | The number of times the non-ad version of your Pin played at least half the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 46 | video\_p75\_combined\_2 | The number of times the non-ad version of your Pin played at least three quarters of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 47 | video\_p95\_combined\_2 | The number of times the non-ad version of your Pin played at least 95% of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 48 | video\_p100\_complete\_2 | The number of times the non-ad Pin version of your video played completely while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 49 | total\_conversions | The total number of conversions across all conversion objectives | int |
| 50 | total\_conversions\_quantity | The total order quantity across all conversion objectives | int |
| 51 | 1d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 52 | 1d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting. | dict |
| 53 | 1d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 54 | 7d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 55 | 7d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 56 | 7d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 57 | 14d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 58 | 14d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 59 | 14d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 60 | 30d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 61 | 30d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 62 | 30d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 63 | 60d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 64 | 60d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 65 | 60d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 66 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | str |
| 67 | updated\_at | | timestamp |

***

## Nested Fields

The following fields are structured as dictionaries (`dict` type):

* `1d_view`
* `1d_click`
* `1d_engagement`
* `7d_view`
* `7d_click`
* `7d_engagement`
* `14d_view`
* `14d_click`
* `14d_engagement`
* `30d_view`
* `30d_click`
* `30d_engagement`
* `60d_view`
* `60d_click`
* `60d_engagement`

Each dictionary contains conversion event names as keys and the associated conversion count as values.

### Example Stored Object

```json theme={null}
{
  "purchase": 25,
  "add_to_cart": 40,
  "signup": 12
}
```

### Snowflake - Querying Nested Fields

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  1d_click:"purchase"::number as purchase
from <database>.<schema>.<table_name>;
```

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

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  1d_click['purchase']::int as purchase
from <database>.<schema>.<table_name>;
```

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

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  cast(json_value(1d_click, '$.purchase') as int64) as purchase
from `<project_id>.<dataset>.<table_name>`;
```


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