BigQuery Reporting & Dataset Information
Introduction
Adhese Gateway (GW) logs all actions taken during the process of 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 columns' structure. We will communicate planned changes before any deployments. However, we will not wait for each 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 for connecting to your data source. This will give you immediate insights without any additional development work.
-
Access to the data studio data source(s)
This will allow you to create your custom dashboards, which are useful for reporting on specific cases that are 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 possibility is to get access to the underlying data sources directly. This allows you to create any kind of custom report or dashboard. It is essential to understand the different columns and how they should be interpreted when working with the data.
Direct access to the BigQuery table(s)
This is the more advanced option for your data team, allowing them to directly query the data sources or export the data to their own data solution.
The following steps are required to access the BigQuery source:
Reach out to 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.
Make sure you are within your own Google Cloud project; otherwise, errors will be returned.
Make sure you are using 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 login 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 information on the request, including which device it originated from, what content is associated with it, which user is linked to 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 context 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 available attributes, although some attributes are not available in all markets. The Market set contains all incoming Bid Responses from all active markets, with advertiser and pricing info.
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 dbase.
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.
access_log (deprecated 31/12/2022)
adhese-cloud.${customer}.access_log
Show table
This table contains direct ad server and campaign data.
Field Name
Type
Mode
Description
date
DATE
NULLABLE
The date on which these requests were received.
slot
RECORD
NULLABLE
slot. id
STRING
NULLABLE
The Adhese ID of the slot or placement requested
slot. code
STRING
NULLABLE
The string used in the client application to request this slot.
publication
RECORD
NULLABLE
publication. name
STRING
NULLABLE
The name of the publication in the Adhese dbase.
publication. url
STRING
NULLABLE
The URL of the publication in the adhese dbase.
format
RECORD
NULLABLE
format. name
STRING
NULLABLE
format. width
INTEGER
NULLABLE
Width of the format in pixels.
format. height
INTEGER
NULLABLE
Height of the format in pixels.
publisher
RECORD
NULLABLE
publisher. name
STRING
NULLABLE
The name of the section in Adhese dbase.
bid
RECORD
NULLABLE
bid. amount
INTEGER
NULLABLE
The number of bids.
bid. tracked
RECORD
NULLABLE
bid.tracked. amount
INTEGER
NULLABLE
Number of rendered ads (aka. paid impressions)
bid. clicked
RECORD
NULLABLE
bid.clicked. amount
INTEGER
NULLABLE
The number of clicked ads.
creative
RECORD
NULLABLE
creative. name
STRING
NULLABLE
Adhese creative name.
creative. id
INTEGER
NULLABLE
Adhese creative ID.
campaign
RECORD
NULLABLE
campaign. name
STRING
NULLABLE
Adhese campaign name.
campaign. id
INTEGER
NULLABLE
Adhese campaign ID.
booking
RECORD
NULLABLE
booking. id
INTEGER
NULLABLE
Adhese booking ID.
booking. pricing
RECORD
NULLABLE
booking.pricing. type
STRING
NULLABLE
Adhese booking price type (CPM, CPC, CPD, …).
advertiser
RECORD
NULLABLE
advertiser. id
INTEGER
NULLABLE
Adhese advertiser ID.
advertiser. name
STRING
NULLABLE
Adhese advertiser name.
actions
STRING
REPEATED
An array of events that were triggered for a given rendered ad. Contains, for example, items like "start, mute, skip, …" for video completion events.
targets
RECORD
NULLABLE
Custom list of targets as set up by the Adhese account configuration. Contains data like user information, contextual data, device info…
targets. br
STRING
REPEATED
targets. dm
STRING
REPEATED
targets. dt
STRING
NULLABLE
targets. og
STRING
NULLABLE
targets. tl
STRING
NULLABLE
targets. ge
STRING
NULLABLE
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 notnot** 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’Adhese\_IABview’ or 'mrc_viewable'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
<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[here](https://documentation.adhese.org/books/inventory-management/page/formats)
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[here](https://documentation.adhese.org/books/campaign-management/page/creatives)
Show table
<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[here](https://documentation.adhese.org/books/campaign-management/page/bookings)
Show table
meta_bookingmeta\_booking
Description
Field name
Type
Mode
date
DATE
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[here](https://documentation.adhese.org/books/campaign-management/page/campaigns)
Show table
<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[here](https://documentation.adhese.org/books/inventory-management/page/targeting)
Show table
Field name
Type
Mode
Description
date
DATE
NULLABLE
booking_id
INTEGER
NULLABLE
type
STRING
NULLABLE
name
STRING
NULLABLE
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