Platform - Pinterest - Rockerbox Data Foundation Docs

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 (nonad) 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

{
  "purchase": 25,
  "add_to_cart": 40,
  "signup": 12
}

Snowflake - Querying Nested Fields

Extract a Single Event

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

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

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

​Description

​Partition Keys

​Logical Primary Key

​Field Reference

​Nested Fields

​Example Stored Object

​Snowflake - Querying Nested Fields

​Extract a Single Event

​Redshift - Querying Nested Fields (SUPER type)

​Extract a Single Event

​BigQuery - Querying Nested Fields (JSON type)

​Extract a Single Event

Description

Partition Keys

Logical Primary Key

Field Reference

Nested Fields

Example Stored Object

Snowflake - Querying Nested Fields

Extract a Single Event

Redshift - Querying Nested Fields (SUPER type)

Extract a Single Event

BigQuery - Querying Nested Fields (JSON type)

Extract a Single Event