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

## Description

The **Platform – Google** dataset contains aggregated performance metrics from Google Ads (formerly AdWords), including spend, impressions, clicks, and conversion data.

***

## 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`
* `campaign_id`
* `ad_group_id`
* `ad_network_type`

***

## Field Reference

| Order | Name | Description |
| - | - | - |
| 1 | advertiser | The name of the Rockerbox account associated with the data export. |
| 2 | type | The dataset type. For this table, the value will be `platform_data`. |
| 3 | platform | The advertising platform name. For this dataset, the value is `Adwords` (Google Ads). |
| 4 | report | The name of the dataset. For this table, the value is `platform_performance_ad`. |
| 5 | identifier | The unique identifier of the advertiser account in Google Ads. |
| 6 | date | The calendar date associated with the reported performance metrics. |
| 7 | utc\_hour | The UTC hour during which the activity occurred. |
| 8 | tier\_1 | Marketing channel categorization level 1, as defined in your Rockerbox configuration. |
| 9 | tier\_2 | Marketing channel categorization level 2, as defined in your Rockerbox configuration. |
| 10 | tier\_3 | Marketing channel categorization level 3, as defined in your Rockerbox configuration. |
| 11 | tier\_4 | Marketing channel categorization level 4, as defined in your Rockerbox configuration. |
| 12 | tier\_5 | Marketing channel categorization level 5, as defined in your Rockerbox configuration. |
| 13 | mta\_tiers\_join\_key | The unique identifier used to join spend data from Google Ads to Rockerbox MTA datasets. This is typically the `ad_id`, but may vary depending on your account setup. |
| 14 | ad\_network\_type | The Google Ads network where the ad was served (e.g., `SEARCH`, `SEARCH_PARTNERS`, `MIXED`, `YOUTUBE_WATCH`). |
| 15 | advertising\_channel\_type | The primary advertising channel type for the campaign (e.g., `SEARCH`, `DISPLAY`, `SHOPPING`). |
| 16 | campaign\_name | The name of the campaign in Google Ads. A campaign may contain one or more ad groups. |
| 17 | campaign\_id | The unique identifier of the campaign in Google Ads. |
| 18 | ad\_group\_type | The type of ad group (e.g., `SEARCH_STANDARD`, `DISPLAY_STANDARD`). |
| 19 | ad\_group\_name | The name of the ad group. An ad group contains one or more ads. |
| 20 | ad\_group\_id | The unique identifier of the ad group in Google Ads. |
| 21 | spend | The total estimated spend in the account’s local currency for the given date and dimension breakdown. |
| 22 | currency\_code | The local currency of the Google Ads account (e.g., `USD`, `EUR`). |
| 23 | spend\_usd | The total estimated spend converted to USD. |
| 24 | clicks | The number of clicks recorded on the ads. |
| 25 | impressions | The number of times ads were served across Google properties or partner networks. |
| 26 | cost\_micros | Ad spend in micro units of the account’s local currency. For example, \$1.23 is represented as 1,230,000 (1.23 × 1,000,000). |
| 27 | all\_conversions\_by\_conversion\_date | The total number of conversions attributed based on the actual conversion date. |
| 28 | all\_conversions | The total number of conversions attributed based on the date of the associated advertising interaction (impression or click). |
| 29 | view\_through\_conversions | The number of conversions attributed to impressions without a click interaction. |
| 30 | all\_conversions\_value\_by\_conversion\_date | The total value of conversions attributed based on the actual conversion date. |
| 31 | all\_conversions\_value | The total value of conversions attributed based on the date of the associated advertising interaction (impression or click). |
| 32 | view\_through\_lookback\_window\_days | The maximum number of days between an impression and a conversion for the conversion to be attributed without an interaction. |
| 33 | click\_through\_lookback\_window\_days | The maximum number of days between a click and a conversion for the conversion to be attributed. |
| 34 | rb\_sync\_id | Internal identifier used by Rockerbox to sync this dataset to your data warehouse. |
| 35 | updated\_at | Timestamp indicating when the record was last updated in Rockerbox. |

***

## Nested Fields

The following fields are nested JSON objects keyed by Facebook conversion event name:

* `all_conversions_by_conversion_date`
* `all_conversions`
* `view_through_conversions`
* `all_conversions_value_by_conversion_date`
* `all_conversions_value`
* `view_through_lookback_window_days`
* `click_through_lookback_window_days`

### Example Stored Object

```json theme={null}
{
  "purchase": 12,
  "add_to_cart": 41
}
```

### Snowflake - Querying Nested Fields

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  all_conversions_by_conversion_date:"purchase"::number as purchase,
  all_conversions_value_by_conversion_date:"purchase"::float as purchase_value
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 all_conversions
from <database>.<schema>.<table_name> t,
  lateral flatten(input => t.all_conversions_by_conversion_date) f;
```

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

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  all_conversions_by_conversion_date['purchase']::int as purchase,
  all_conversions_value_by_conversion_date['purchase']::decimal(18,4) as purchase_value
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
from <database>.<schema>.<table_name> t,
  t.all_conversions_by_conversion_date as kv;
```

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

#### Extract a Single Event

```sql theme={null}
select
  date,
  ad_id,
  cast(json_value(all_conversions_by_conversion_date, '$.purchase') as int64) as purchase,
  cast(json_value(all_conversions_value_by_conversion_date, '$.purchase') as float64) as purchase_value
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.all_conversions_by_conversion_date, concat('$.', k)) as int64) as conversions
from <project_id>.<dataset>.<table_name> t,
unnest(json_keys(t.all_conversions_by_conversion_date)) as k;
```


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