Skip to main content

​
Description

The Platform - Facebook dataset contains delivery, spend, and conversion metrics at an hourly, ad level from Microsoft Advertising (Bing Ads).

​
Partition Keys

  • identifier
  • date
💡 Note: Leverage partition keys when querying the table to improve query efficiency.

​
Primary Key

The concatenation of the following fields can be used as a primary key:
  • identifier
  • date
  • utc_hour
  • ad_id

​
Field Reference

OrderFieldDescriptionType
1advertiserRockerbox account ID.str
2typeDataset type (e.g., platform_data).str
3platformName of the advertising platform (e.g., Bing).str
4reportDataset name (e.g., platform_performance_bing).str
5identifierUnique identifier of the account in the advertising platform.str
6dateDate the performance metrics occurred.date
7utc_hourUTC hour the performance metrics occurred.int
8tier_1Marketing channel categorization level 1.str
9tier_2Marketing channel categorization level 2.str
10tier_3Marketing channel categorization level 3.str
11tier_4Marketing channel categorization level 4.str
12tier_5Marketing channel categorization level 5.str
13mta_tiers_join_keyIdentifier used to pull spend from an advertising platform. Typically ad_id, but may differ based on account setup.str
14campaign_nameCampaign name.str
15campaign_idMicrosoft Advertising–assigned unique identifier of a campaign.str
16ad_group_nameAd group name.str
17ad_group_idMicrosoft Advertising–assigned unique identifier of an ad group.str
18ad_titleAd title.str
19ad_idMicrosoft Advertising–assigned unique identifier of an ad.str
20spendEstimated total spend in the ad account’s local currency.float
21currency_codeISO currency code of the ad account (e.g., USD, EUR).str
22spend_usdEstimated total spend in USD.float
23clicksNumber of clicks on an ad.int
24impressionsNumber of times an ad was displayed on search results pages.int
25conversionsConversion metrics object (includes qualified, all, and view-through conversion counts across conversion goals).dict
26rb_sync_idIdentifier used by Rockerbox to sync the dataset to your warehouse.str
27updated_atTimestamp of the most recent row update.timestamp

​
Nested Fields

The following fields are nested JSON objects keyed by Facebook conversion event name:
  • view_1d
  • click_1d
  • click_7d
  • view_1d_value
  • click_1d_value
  • click_7d_value
  • view_1d_value_usd
  • click_1d_value_usd
  • click_7d_value_usd

​
Example Stored Object

{
  "purchase": 12,
  "add_to_cart": 41
}

​
Snowflake - Querying Nested Fields

​
Extract a Single Event

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

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

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

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

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

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;