Skip to main content

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.

looker studio.png

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:

afbeelding.png

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 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 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 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 RECORD NULLABLE publisher. name STRING NULLABLE The name of this location in the Adhese dbase. market RECORD NULLABLE   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 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  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  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   DATEcreative_id INTEGER NULLABLE   slot_id INTEGER NULLABLE   booking_id creative_idINTEGER NULLABLE   campaign_id INTEGER NULLABLE   impression_count 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   DATEid INTEGER NULLABLE   name STRING NULLABLE   url idSTRING NULLABLE   INTEGERposition_type STRING NULLABLE name STRING NULLABLE url STRING NULLABLE position_type STRING NULLABLE position type as defined when creating a slot template_file STRING NULLABLE   template_filelocation_id INTEGER NULLABLE   location_name STRING NULLABLE   location_url STRING NULLABLE   location_code location_idSTRING NULLABLE   publication_id INTEGER NULLABLE   publication_name STRING NULLABLE   publication_url location_nameSTRING NULLABLE   publisher_id INTEGER NULLABLE   publisher_name STRING NULLABLE  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   DATEid INTEGER NULLABLE   name STRING NULLABLE   width idINTEGER NULLABLE   height INTEGER NULLABLE   code_tag STRING 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   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   DATEid INTEGER NULLABLE   name STRING NULLABLE   type idSTRING NULLABLE   format_name STRING NULLABLE   format_width INTEGER NULLABLE   format_height INTEGER NULLABLE   creative_width nameINTEGER NULLABLE   creative_height INTEGER NULLABLE   creative_url STRING NULLABLE   size_kb INTEGER 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   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   DATid INTEGER NULLABLE   campaign_id INTEGER NULLABLE   slot_id idINTEGER NULLABLE   INTEGERstart TIMESTAMP NULLABLE   end TIMESTAMP NULLABLE   delivery_method campaign_idSTRING NULLABLE   INTEGERto_reach_unit STRING 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   prioritycapping STRING NULLABLE   delivery_limit STRING NULLABLE   pricing_type STRING NULLABLE   unit_price cappingFLOAT NULLABLE   external_key STRING NULLABLE   comment STRING NULLABLE   daily_start delivery_limitINTEGER NULLABLE   STRINGdaily_end INTEGER NULLABLE   exclusive BOOLEAN NULLABLE   compete_with_rtb pricing_typeBOOLEAN NULLABLE   STRINGtogether_with INTEGER REPEATED   active RECORD 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  BOOLEAN NULLABLE active.tuesday BOOLEAN NULLABLE  BOOLEAN NULLABLE active.wednesday BOOLEAN NULLABLE  BOOLEAN NULLABLE active.thursday BOOLEAN NULLABLE  BOOLEAN NULLABLE active.friday BOOLEAN NULLABLE  BOOLEAN NULLABLE active.saturday BOOLEAN NULLABLE  BOOLEAN NULLABLE active.sunday BOOLEAN NULLABLE  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   DATEid INTEGER NULLABLE   name STRING NULLABLE   priority idSTRING NULLABLE   INTEGERcreated_by STRING NULLABLE   to_reach_unit STRING 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   advertiser_companyinvoice_company STRING NULLABLE   media_company STRING NULLABLE   intermediary_company STRING NULLABLE   client_contacts invoice_companySTRING NULLABLE   internal_id STRING NULLABLE   external_id STRING NULLABLE   account_managers media_companySTRING NULLABLE   public_comment STRING NULLABLE   internal_comment 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   DATEbooking_id INTEGER NULLABLE   type STRING NULLABLE   name booking_idSTRING NULLABLE   INTEGERcode STRING NULLABLE  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   DATEerror_type STRING NULLABLE   message STRING NULLABLE   count error_typeINTEGER NULLABLE  STRING NULLABLE message STRING NULLABLE count INTEGER NULLABLE
Back to top