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

Impressions from retargeting and prospecting campaigns

cli

Click

Clicks from retargeting and prospecting campaigns

ct2

Click Through Conversion (CTC) v2

Click through attributed conversion for retargeting, prospecting, email campaigns

vt2

View Through Conversion (VTC) v2

View through attributed conversion for retargeting, prospecting, email campaigns

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