BigQuery Reporting & Dataset Information
Introduction
Adhese Gateway (GW) logs all actions taken while receiving a request from a device and returning a response milliseconds later. This data is made available to the Gateway users through BigQuery (BQ) tables that contain results per day and are updated at midnight.
The BQ tables are subject to change. Therefore, if you implement against them, you must be prepared to configure changes to the column structure. We will communicate planned changes prior to any deployments. However, we will not wait for every implementer to update.
Access your Adhese data
As a customer, you can access your data in three different ways, each suitable for a specific use case. These are briefly described below.
- Through a predefined Google Data Studio report
Standard Data Studio templates are available to connect to your data source. This gives you immediate insight without any additional development.
- Access to the Data Studio data source(s)
This allows you to create your own custom dashboards, which are useful for reporting on specific cases not covered by the standard dashboards.
- Direct access to the BigQuery table(s)
The more advanced option is to query the dataset(s) or export them directly. This requires some technical knowledge.
If you would like to access your data in any of these ways, please contact Adhese support and provide a (Google) email address with which we can share access.
Through a Google Data Studio template
At Adhese, we offer a variety of predefined dashboards that provide immediate insights into your data. An example is shown below.
Access to the Data Studio data source(s)
Another option is to get direct access to the underlying data sources. This method makes it possible to create any custom report or dashboard. When working with the data, it is important to understand the different columns and how to interpret them.
Direct access to the BigQuery table(s)
This is the more advanced option for your data team. It allows them to query the data sources directly or export the data to their own data solution.
The following steps are required to access the BigQuery source:
- Contact Adhese support and provide a Google email address connected to a Google Cloud account. We'll give the necessary permissions (bigQuery dataViewer, see role details here).
- Once access is provided, you can query the different tables.
Ensure you are within your own Google Cloud project; otherwise, errors will be returned.
Ensure you use the correct data location; by default, this is europe-west2.
The table names are structured as follows: adhese-cloud.${customer}.${table_name}
Where ${customer} is your Adhese account name, and ${table_name} is the table you are trying to query. See details about the different tables below.
You can find your Adhese account name in the URL used to log in to the Adhese platform (e.g. https://demo.adhese.org/)
A test query could look as follows:
If this works, you should be able to use any BigQuery functionality to process your data further.
If you still have any questions, please don't hesitate to contact us at Adhese support.
Overview of Collected Data
Two sets of data are collected during the lifetime of a single GW request/response: the Media set and the Market set.
The Media set contains all the information about the request, including which device it came from, what content is associated with it, which user is associated with it (if any), and so on. The exact attributes of the media set are different for each GW account, depending on the available data and the context that an account has configured.
The Market set contains the communication log between GW and the active markets for a given GW account. Market standards determine the attributes available, although some attributes are not available in all markets. The Market set contains all incoming bid responses from all active markets with advertiser and price information.
Table descriptions
slots_extended
adhese-cloud.${customer}.slots_extended
This table contains the Market data set. Each row contains the number of bid responses per day, aggregated in latency buckets of 10ms.
Show table
| Field name | Type | Mode | Description |
| date | DATE | NULLABLE | The date on which these bid requests were received. |
| slot | RECORD | NULLABLE | |
| slot. id | INTEGER | NULLABLE | The ID of the slot the bid request was made from. |
| slot. code | STRING | NULLABLE | The code of the slot the bid request was made from. |
| publication | RECORD | NULLABLE | |
| publication. name | STRING | NULLABLE | The publication name in the Adhese dbase. |
| publication. url | STRING | NULLABLE | The publication URL in the Adhese dbase. |
| format | RECORD | NULLABLE | |
| format. name | STRING | NULLABLE | The format name in the Adhese database. |
| format. width | INTEGER | NULLABLE | The format width in pixels for this Bid Response. |
| format. height | INTEGER | NULLABLE | The format height in pixels for this Bid Response. |
| publisher | RECORD | NULLABLE | |
| publisher. name | STRING | NULLABLE | The name of this location in the Adhese dbase. |
| market | RECORD | NULLABLE | |
| market. name | STRING | NULLABLE | The market instance name as known in the Adhese configuration. |
| market. currency | STRING | NULLABLE | The currency this market uses for bid responses. |
| bid | RECORD | NULLABLE | |
| bid. count | INTEGER | NULLABLE | The number of bid responses for this record. |
| bid. amount | FLOAT | NULLABLE | The total value of the bid responses for this record in market.currency. |
| bid. winning | RECORD | NULLABLE | |
| bid.winning. count | INTEGER | NULLABLE | The number of winning bid responses for this record. |
| bid.winning. amount | FLOAT | NULLABLE | The total value of the winning bid responses for this record in market.currency. |
| bid. tracked | RECORD | NULLABLE | |
| bid.tracked. count | INTEGER | NULLABLE | The number of rendered bid responses for this record. (aka paid impressions) |
| bid.tracked. amount | FLOAT | NULLABLE | The total value of the rendered bid responses for this record in market.currency. |
| seatId | STRING | NULLABLE | The seat ID as known by this market. |
| domain | STRING | NULLABLE | The "OpenRTB domain" field, containing the domain of the advertising party or brand for this Bid Response. |
| dealId | STRING | NULLABLE | The deal ID as known by this market. |
| crId | STRING | NULLABLE | The creative ID as known by this market. |
| discountRate | FLOAT | NULLABLE | The value of the multiplier applied to this bid response. |
| exchangeRate | FLOAT | NULLABLE | The value of the exchange rate at the moment of the bid between market.currency and EUR. |
| latency | INTEGER | NULLABLE | The value of the latency bucket for these bids. |
| deviceType | STRING | NULLABLE | The label of the device that generated the bid request (phone, desktop, tablet, tv). |
| consent | BOOLEAN | NULLABLE | Indication of consent for the bid requests to this market. |
reduced_access_log
adhese-cloud.${customer}.reduced_access_log
This table contains direct ad server and campaign data. It can be joined with the metadata tables below.
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| creative_id | INTEGER | NULLABLE | |
| slot_id | INTEGER | NULLABLE | |
| booking_id | INTEGER | NULLABLE | |
| campaign_id | INTEGER | NULLABLE | |
| impression_count | INTEGER | NULLABLE | Each time an ad is requested, one impression is counted. This does not correspond to an IAB paid impression. |
| track_count | INTEGER | NULLABLE | When an ad is rendered on a page, 1 track is counted. This corresponds to an IAB paid impression. |
| click_count | INTEGER | NULLABLE | Each time a user clicks on an ad, 1 click is counted |
| actions | STRING | REPEATED | A viewable impression is counted when a ‘Adhese_IABview’ or 'mrc_viewable' action is present. |
Metadata tables
Updated daily with metadata for primary keys in the reduced_access_log table.
meta_slots
adhese-cloud.${customer}.meta_slots
Extra information regarding slots (= positions) can be found here
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| id | INTEGER | NULLABLE | |
| name | STRING | NULLABLE | |
| url | STRING | NULLABLE | |
| position_type | STRING | NULLABLE | position type as defined when creating a slot |
| template_file | STRING | NULLABLE | |
| location_id | INTEGER | NULLABLE | |
| location_name | STRING | NULLABLE | |
| location_url | STRING | NULLABLE | |
| location_code | STRING | NULLABLE | |
| publication_id | INTEGER | NULLABLE | |
| publication_name | STRING | NULLABLE | |
| publication_url | STRING | NULLABLE | |
| publisher_id | INTEGER | NULLABLE | |
| publisher_name | STRING | NULLABLE |
meta_format
adhese-cloud.${customer}.meta_format
Extra information regarding formats can be found here
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| id | INTEGER | NULLABLE | |
| name | STRING | NULLABLE | |
| width | INTEGER | NULLABLE | |
| height | INTEGER | NULLABLE | |
| code_tag | STRING | NULLABLE | Code used to request the format |
| code_book | STRING | NULLABLE | Code used to link formats (subformat setup) |
| code_import | STRING | NULLABLE |
meta_creatives
adhese-cloud.${customer}.meta_creatives
Extra information regarding creatives can be found here
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| id | INTEGER | NULLABLE | |
| name | STRING | NULLABLE | |
| type | STRING | NULLABLE | |
| format_name | STRING | NULLABLE | |
| format_width | INTEGER | NULLABLE | |
| format_height | INTEGER | NULLABLE | |
| creative_width | INTEGER | NULLABLE | |
| creative_height | INTEGER | NULLABLE | |
| creative_url | STRING | NULLABLE | |
| size_kb | INTEGER | NULLABLE | |
| advar_fields | RECORD | REPEATED | advar template fields |
| advar_file | STRING | NULLABLE |
meta_booking
adhese-cloud.${customer}.meta_booking
Extra information regarding bookings can be found here
Show table
| meta_booking | Description | ||
|---|---|---|---|
| Field name | Type | Mode | |
| date | DAT | NULLABLE | |
| id | INTEGER | NULLABLE | |
| campaign_id | INTEGER | NULLABLE | |
| slot_id | INTEGER | NULLABLE | |
| start | TIMESTAMP | NULLABLE | |
| end | TIMESTAMP | NULLABLE | |
| delivery_method | STRING | NULLABLE | |
| to_reach_unit | STRING | NULLABLE | e.g. impressions/clicks |
| to_reach_volume | INTEGER | NULLABLE | amount of units to reach |
| priority | STRING | NULLABLE | |
| capping | STRING | NULLABLE | |
| delivery_limit | STRING | NULLABLE | |
| pricing_type | STRING | NULLABLE | |
| unit_price | FLOAT | NULLABLE | |
| external_key | STRING | NULLABLE | |
| comment | STRING | NULLABLE | |
| daily_start | INTEGER | NULLABLE | |
| daily_end | INTEGER | NULLABLE | |
| exclusive | BOOLEAN | NULLABLE | |
| compete_with_rtb | BOOLEAN | NULLABLE | |
| together_with | INTEGER | REPEATED | |
| active | RECORD | NULLABLE | |
| active.monday | BOOLEAN | NULLABLE | |
| active.tuesday | BOOLEAN | NULLABLE | |
| active.wednesday | BOOLEAN | NULLABLE | |
| active.thursday | BOOLEAN | NULLABLE | |
| active.friday | BOOLEAN | NULLABLE | |
| active.saturday | BOOLEAN | NULLABLE | |
| active.sunday | BOOLEAN | NULLABLE |
meta_campaign
adhese-cloud.${customer}.meta_campaign
Extra information regarding campaigns can be found here
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| id | INTEGER | NULLABLE | |
| name | STRING | NULLABLE | |
| priority | STRING | NULLABLE | |
| created_by | STRING | NULLABLE | |
| to_reach_unit | STRING | NULLABLE | e.g. impressions/clicks |
| to_reach_volume | INTEGER | NULLABLE | amount of units to reach |
| advertiser_company | STRING | NULLABLE | |
| invoice_company | STRING | NULLABLE | |
| media_company | STRING | NULLABLE | |
| intermediary_company | STRING | NULLABLE | |
| client_contacts | STRING | NULLABLE | |
| internal_id | STRING | NULLABLE | |
| external_id | STRING | NULLABLE | |
| account_managers | STRING | NULLABLE | |
| public_comment | STRING | NULLABLE | |
| internal_comment | STRING | NULLABLE |
meta_booking_targets
adhese-cloud.${customer}.meta_booking_targets
Contains for each booking the target group, target label and target code that is used in that booking.
Extra information regarding targeting can be found here
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| booking_id | INTEGER | NULLABLE | |
| type | STRING | NULLABLE | |
| name | STRING | NULLABLE | |
| code | STRING | NULLABLE |
meta_dooh_player
adhese-cloud.${customer}.meta_dooh_player
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| id | INTEGER | NULLABLE | |
| date | DATE | NULLABLE | The date on which these bid requests were received. |
| store_city | STRING | NULLABLE | |
| store_id | STRING | NULLABLE | |
| store_formula | STRING | NULLABLE | Type of store. |
| store_channel | STRING | NULLABLE | |
| store_category | STRING | NULLABLE | |
| store_latlong | STRING | NULLABLE | Latitudinal and longitudinal coordinates of the store. |
| store_adress | STRING | NULLABLE | |
| store_postal_code | STRING | NULLABLE |
Other
error_log
adhese-cloud.${customer}.error_log
This is a generic table in which errors are stored. For instance, it can be employed to identify unmatched slot requests.
Show table
| Field name | Type | Mode | Description |
|---|---|---|---|
| date | DATE | NULLABLE | |
| error_type | STRING | NULLABLE | |
| message | STRING | NULLABLE | |
| count | INTEGER | NULLABLE |
No Comments