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 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.

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~~creative_id	INTEGER	NULLABLE
~~slot\_id~~slot_id	INTEGER	NULLABLE
~~booking\_id~~booking_id	INTEGER	NULLABLE
~~campaign\_id~~campaign_id	INTEGER	NULLABLE
~~impression\_count~~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~~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~~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

Field name	Type	Mode	Description
date	DATE	NULLABLE
id	INTEGER	NULLABLE
name	STRING	NULLABLE
url	STRING	NULLABLE
~~position\_type~~position_type	STRING	NULLABLE	position type as defined when creating a slot
~~template\_file~~template_file	STRING	NULLABLE
~~location\_id~~location_id	INTEGER	NULLABLE
~~location\_name~~location_name	STRING	NULLABLE
~~location\_url~~location_url	STRING	NULLABLE
~~location\_code~~location_code	STRING	NULLABLE
~~publication\_id~~publication_id	INTEGER	NULLABLE
~~publication\_name~~publication_name	STRING	NULLABLE
~~publication\_url~~publication_url	STRING	NULLABLE
~~publisher\_id~~publisher_id	INTEGER	NULLABLE
~~publisher\_name~~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~~code_tag	STRING	NULLABLE	Code used to request the format
~~code\_book~~code_book	STRING	NULLABLE	Code used to link formats (subformat setup)
~~code\_import~~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~~format_name	STRING	NULLABLE
~~format\_width~~format_width	INTEGER	NULLABLE
~~format\_height~~format_height	INTEGER	NULLABLE
~~creative\_width~~creative_width	INTEGER	NULLABLE
~~creative\_height~~creative_height	INTEGER	NULLABLE
~~creative\_url~~creative_url	STRING	NULLABLE
~~size\_kb~~size_kb	INTEGER	NULLABLE
~~advar\_fields~~advar_fields	RECORD	REPEATED	advar template fields
~~advar\_file~~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
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
date	DATE	NULLABLE
error_type	STRING	NULLABLE
message	STRING	NULLABLE
count	INTEGER	NULLABLE