AdRoll SQL Schema

Available Tables:

Data Types

The AdRoll SQL schema depends on the the underlying wharehouse type. Use this table to map between the AdRoll generic type name to a wharehouse-specific type.

Generic Name Amazon Redshift PostgreSQL MySQL
timestamp TIMESTAMP WITHOUT TIME ZONE TIMESTAMP WITHOUT TIME ZONE TIMESTAMP
varchar VARCHAR(65535) VARCHAR(65535) TEXT
text TEXT TEXT TEXT
integer INTEGER INTEGER INT
double DOUBLE PRECISION DOUBLE PRECISION DOUBLE
bigint BIGINT BIGINT BIGINT

Log Lines

Table name: log_lines

Indexes:

  • timestamp_index btree (timestamp)
  • type_index btree (type)

Schema:

Column name Data Type
type varchar
adgroup_eid varchar
ad_eid varchar
width varchar
height varchar
site_url varchar
timestamp timestamp
referrer varchar
user_agent varchar
cookie varchar
ip varchar
segment_eid varchar
conversion_value varchar
external_data varchar
product varchar
campaign_eid varchar
currency varchar
language varchar
domain varchar
device_id_type varchar
device_id varchar
event varchar
event_source varchar
event_source_version varchar
stable_user_identifier varchar
product_group varchar
product_action varchar
domain_normalized varchar
geo2_continent_code varchar
geo2_country_code varchar
geo2_country_code3 varchar
geo2_country_name varchar
geo2_country_eid varchar
geo2_region_name varchar
geo2_region_eid varchar
geo2_metro_regions varchar
geo2_metro_name varchar
geo2_metro_eid varchar
geo2_city_name varchar
geo2_city_eid varchar
geo2_latitude varchar
geo2_longitude varchar
geo2_company varchar
geo2_demographics varchar
geo2_postal_code_name varchar
geo2_postal_code_eid varchar
email_id varchar
ad_name varchar
adgroup_name varchar
campaign_name varchar

Log Lines Types

There are several types of log lines, each with their own meaning. You can filter by log type using the type column.

Log Line Types
Type Name Description
imp Impression Web and Facebook impressions
cli Click Web and Facebook clicks
ct2 Click Through Conversion (CTC) v2 Retargeting click through attributed conversion
vt2 View Through Conversion (VTC) v2 Retargeting view through attributed conversion

Attribution

Table name: public.attributions

Indexes:

  • attributions_ad_index btree (ad_eid)
  • attributions_adgroup_index btree (adgroup_eid)
  • attributions_segment_index btree (segment_eid)
  • attributions_timestamp_index btree (timestamp)

Schema:

Column name Data Type
timestamp timestamp
advertisable_eid text
segment_eid text
adgroup_eid text
ad_eid text
view_throughs integer
click_throughs integer
view_revenue bigint
click_revenue bigint

Segments

Table name: public.segments

Indexes:

  • segments_segment_index btree (segment_eid)
  • segments_timestamp_index btree (timestamp)

Schema:

Column name Data Type
timestamp timestamp
advertisable_eid text
segment_eid text
visitors integer
revenue bigint

Deliveries

Table name: public.deliveries

Indexes:

  • deliveries_ad_index btree (ad_eid)
  • deliveries_adgroup_index btree (adgroup_eid)
  • deliveries_timestamp_index btree (timestamp)

Schema:

Column name Data Type
timestamp timestamp
advertisable_eid varchar
adgroup_eid varchar
ad_eid varchar
impressions integer
clicks integer
cost bigint

Segmentation Intent

Table name: public.segmentation_intent

Indexes:

  • segmentation_intent_cookie_index btree (cookie)
  • segmentation_intent_timestamp_index btree (timestamp)

Schema:

Column name Data Type
cookie text
advertisable_eid text
score double
timestamp timestamp
email_id text