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:

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

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

     

<table

Field name	Type	Mode	Description
date	DATE	NULLABLE
id
id INTEGER	INTEGER NULLABLE	NULLABLE
name	STRING
name NULLABLE	STRING
url	NULLABLE STRING	NULLABLE
url position\_type	STRING	NULLABLE
position_type	STRING	NULLABLE	position type as defined when creating a slot
template_file template\_file	STRING	NULLABLE
location\_id
location_id INTEGER	INTEGER NULLABLE	NULLABLE
location\_name	STRING
location_name NULLABLE	STRING
location\_url	NULLABLE STRING	NULLABLE
location_url location\_code	STRING	NULLABLE
publication\_id
location_code INTEGER	STRING NULLABLE	NULLABLE
publication\_name	STRING
publication_id NULLABLE	INTEGER
publication\_url	NULLABLE STRING	NULLABLE
publication_name publisher\_id	STRING INTEGER	NULLABLE
publisher\_name
publication_url STRING	STRING NULLABLE	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
id INTEGER	INTEGER NULLABLE	NULLABLE
name	STRING
name NULLABLE	STRING
width	NULLABLE INTEGER	NULLABLE
width height	INTEGER	NULLABLE
code\_tag
height STRING	INTEGER NULLABLE	NULLABLE
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[here](https://documentation.adhese.org/books/campaign-management/page/creatives)

Show table

     

<table

Field name	Type	Mode	Description
date	DATE	NULLABLE
id
id INTEGER	INTEGER NULLABLE	NULLABLE
name	STRING
name NULLABLE	STRING
type	NULLABLE STRING	NULLABLE
type format\_name	STRING	NULLABLE
format\_width
format_name INTEGER	STRING NULLABLE	NULLABLE
format\_height	INTEGER
format_width NULLABLE	INTEGER
creative\_width	NULLABLE INTEGER	NULLABLE
format_height creative\_height	INTEGER	NULLABLE
creative\_url
creative_width STRING	INTEGER NULLABLE	NULLABLE
size\_kb	INTEGER
creative_height NULLABLE	INTEGER
advar\_fields	NULLABLE RECORD	REPEATED
creative_url	STRING	NULLABLE
size_kb	INTEGER	NULLABLE
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[here](https://documentation.adhese.org/books/campaign-management/page/bookings)

Show table

     

meta_bookingmeta\_booking			Description
Field name	Type	Mode
date
date DATE	DATE NULLABLE	NULLABLE
id	INTEGER
id NULLABLE	INTEGER
campaign\_id	NULLABLE INTEGER	NULLABLE
campaign_id slot\_id	INTEGER	NULLABLE
start
slot_id TIMESTAMP	INTEGER NULLABLE	NULLABLE
end	TIMESTAMP
start NULLABLE	TIMESTAMP
delivery\_method	NULLABLE STRING	NULLABLE
end to\_reach\_unit	TIMESTAMP STRING	NULLABLE
delivery_method	STRING	NULLABLE
to_reach_unit	STRING	NULLABLE	e.g. impressions/clicks
to_reach_volume to\_reach\_volume	INTEGER	NULLABLE	amount of units to reach
priority	STRING	NULLABLE
capping
capping STRING	STRING NULLABLE	NULLABLE
delivery\_limit	STRING
delivery_limit NULLABLE	STRING
pricing\_type	NULLABLE STRING	NULLABLE
pricing_type unit\_price	STRING FLOAT	NULLABLE
external\_key
unit_price STRING	FLOAT NULLABLE	NULLABLE
comment	STRING
external_key NULLABLE	STRING
daily\_start	NULLABLE INTEGER	NULLABLE
comment daily\_end	STRING INTEGER	NULLABLE
exclusive
daily_start BOOLEAN	INTEGER NULLABLE	NULLABLE
compete\_with\_rtb	BOOLEAN
daily_end NULLABLE	INTEGER
together\_with	NULLABLE INTEGER	REPEATED
exclusive active	BOOLEAN RECORD	NULLABLE
active.monday
compete_with_rtb BOOLEAN	BOOLEAN NULLABLE	NULLABLE
active.tuesday	BOOLEAN
together_with NULLABLE	INTEGER
active.wednesday	REPEATED BOOLEAN	NULLABLE
active active.thursday	RECORD BOOLEAN	NULLABLE
active.friday
active.monday BOOLEAN	BOOLEAN NULLABLE	NULLABLE
active.saturday	BOOLEAN
active.tuesday NULLABLE	BOOLEAN
active.sunday	NULLABLE 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
id INTEGER	INTEGER NULLABLE	NULLABLE
name	STRING
name NULLABLE	STRING
priority	NULLABLE STRING	NULLABLE
priority created\_by	STRING	NULLABLE
to\_reach\_unit
created_by STRING	STRING NULLABLE	NULLABLE
to_reach_unit	STRING	NULLABLE	e.g. impressions/clicks
to_reach_volume to\_reach\_volume	INTEGER	NULLABLE	amount of units to reach
advertiser_company advertiser\_company	STRING	NULLABLE
invoice\_company
invoice_company STRING	STRING NULLABLE	NULLABLE
media\_company	STRING
media_company NULLABLE	STRING
intermediary\_company	NULLABLE STRING	NULLABLE
intermediary_company client\_contacts	STRING	NULLABLE
internal\_id
client_contacts STRING	STRING NULLABLE	NULLABLE
external\_id	STRING
internal_id NULLABLE	STRING
account\_managers	NULLABLE STRING	NULLABLE
external_id public\_comment	STRING	NULLABLE
internal\_comment
account_managers STRING	STRING NULLABLE	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
booking_id INTEGER	INTEGER NULLABLE	NULLABLE
type	STRING
type NULLABLE	STRING
name	NULLABLE STRING	NULLABLE
name code	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
error_type STRING	STRING NULLABLE	NULLABLE
message	STRING
message NULLABLE	STRING
count	NULLABLE INTEGER	NULLABLE
count	INTEGER	NULLABLE