Data Dictionary

Campaigns contain experiences you design to personalize the interaction a visitor has with your website or application.

Field NameData TypeDescriptionExample values
idChar(5)Unique identifier for a campaign
"type"Varchar(32)Campaign type implies marketing channelWeb, MobileData, TriggeredEmail
created_tsTimestamp without time zoneUTC date and time the campaign was created
updated_tsTimestamp without time zoneUTC date and time the campaign was most recently updated
last_published_tsTimestamp without time zoneUTC date and time the campaign was most recently published
nameVarchar(100)Name of the campaign
stateVarchar(100)Campaign statePublished, Testing, Disabled

Indexes

  • PRIMARY KEY campaign_pkey ON campaign USING btree (id)

Referenced by

  • TABLE "experience" CONSTRAINT "experience_campaign_id_fkey" FOREIGN KEY (campaign_id) REFERENCES campaign(id)
  • TABLE "user_campaign_state" CONSTRAINT "user_campaign_state_campaign_id_fkey" FOREIGN KEY (campaign_id) REFERENCES campaign(id)

Example Query

back to top


In a hierarchical catalog, items belong to a category. This table stores the category IDs and names. In Personalization, there is more than one way to represent a hierarchy. First, categories can be fully specified by the category ID itself (for example, womensapparel|denim|highrise). A hierarchy can be derived from the path. Second, you can use the parent_id field in the category. Products can belong to many categories. How you build categories depends on your integration, but the data structure supports both approaches.

Encoding the hierarchy in the ID is simpler. The parent ID can be used to handle the case when the hierarchy is sometimes not available but the short, non-hierarchical ID is consistently available in the various channels and systems that refer to the categories.

Field NameData TypeDescriptionExample values
idVarchar(200)Unique identifier for the category
nameVarchar(200)Name of the category
is_departmentBooleantrue when the category is a department (a higher-level entity in the category hierarchy)
parent_idVarchar(200)Parent category ID of this category
created_tsTimestamp without time zoneDate and time the category was created in the operational store, UTC
updated_tsTimestamp without time zoneDate and time of the most recent update in the operation store, UTC (as of the recent DW import)

Indexes

  • PRIMARY KEY category_pkey ON category USING btree (id)

Referenced by

  • TABLE "product_category" CONSTRAINT "product_category_category_id_fkey" FOREIGN KEY (category_id) REFERENCES category(id)
  • TABLE "user_daily_stat_item_stat_category" CONSTRAINT "user_daily_stat_item_stat_category_category_id_fkey" FOREIGN KEY (category_id) REFERENCES category(id)

Example Query

back to top


Country lookup table. Provides iso_2 and iso_3 codes and continent information. For more information, refer to IBAN's list of country ISO codes.

Field NameData TypeDescriptionExample values
idVarchar(200)Unique identifier for the country
nameVarchar(100)Name of the country
updated_tsTimestamp without time zoneDate and time of the most recent update to the table
iso_2_codeChar(2)2-character country codeAW, GB, US
iso_3_codeChar(3)3-character country codeABW, GBR
has_regionsBooleanTrue if the country has regions
continent_codeVarchar(100)Numeric continent code0, 1, 2, 3, 4, 5, 6, 7
continent_iso_2_codeChar(2)ISO-2 standard code for a continentNA, AS, EU

Indexes

  • PRIMARY KEY country_pkey ON country USING btree (id)

Example Query

back to top


Dimensions are name-value pairs associated with items (usually products). Typical defaults are brand, color, style, and gender. Custom dimensions are also available. The combination of (id, dimension_type) is unique.

As of September 7th, 2021, there is no distinction between items and dimensions. They are both now known and treated as catalog objects. For more information on catalog objects, refer to the Catalog Objects documentation.

Field NameData TypeDescriptionExample values
idVarchar(200)Identifier for this dimension. The combination of id and dimension_type uniquely identifies a dimension.
dimension_typeVarchar(200)Built-in and custom types are supported. Dimension types are capitalized.Brand, Gender, Color, Style
nameVarchar(200)The label used when displaying the dimensionGucci, Mens
created_tsTimestamp without time zoneDate and time the dimension was created
updated_tsTimestamp without time zoneDate and time this dimension was most recently updated

Indexes

  • UNIQUE INDEX dimension_id_key ON dimension USING btree (id, dimension_type)

Referenced by

  • TABLE "product_dimension" CONSTRAINT "product_dimension_dimension_id_fkey" FOREIGN KEY (dimension_id, dimension_type) REFERENCES dimension(id, dimension_type)

Example Query

back to top


Campaigns contain experiences. You can create different personalization results within the same campaign using experiences. For example, suppose you wish to create a campaign for first-time visitors to your site, but you want to show a different message to visitors from Boston and San Francisco because you have events coming up in those two cities. You would create 3 experiences in the same campaign: one for visitors from Boston, one for visitors from San Francisco, and one for visitors from everywhere else. Then, you can use rules to restrict the visibility of these experiences to the target group of visitors.

Field NameData TypeDescriptionExample values
idChar(5)Unique identifier for this experience
nameVarchar(100)Name of the experience
display_modeVarchar(100)Presentation detail about the experience.Personalize, Redirect
campaign_idChar(5)Associated Campaign ID

Indexes

  • PRIMARY KEY experience_pkey ON experience USING btree (id)

Referenced by

  • TABLE "goal_completion_attribution" CONSTRAINT "goal_completion_attribution_experience_id_fkey" FOREIGN KEY (experience_id) REFERENCES experience(id)
  • TABLE "goal_completion_attribution_promoted_item" CONSTRAINT "goal_completion_attribution_promoted_item_experience_id_fkey" FOREIGN KEY (experience_id) REFERENCES experience(id)
  • TABLE "user_click" CONSTRAINT "user_click_experience_id_fkey" FOREIGN KEY (experience_id) REFERENCES experience(id)
  • TABLE "user_dismissal" CONSTRAINT "user_dismissal_experience_id_fkey" FOREIGN KEY (experience_id) REFERENCES experience(id)
  • TABLE "user_impression" CONSTRAINT "user_impression_experience_id_fkey" FOREIGN KEY (experience_id) REFERENCES experience(id)

Example Query

back to top


Stores the timestamp for the campaign goal in UTC. This table is important when reconstructing or analyzing campaign performance. Use the last_age_seconds field to decide whether to attribute a goal completion to a campaign.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User who satisfied the goal criteria
goal_tsTimestamp without time zoneTimestamp of the goal completion in UTC
goal_idVarchar(256)One of: segment id or purchase (p)p
experience_idVarchar(256)The experience associated with the goal completion
user_groupChar(1)Per experience test (D) and control (C)C, D
promotion_idVarchar(200)The promotion associated with the goal completion
direct_revenueNumeric(18,4)Revenue associated with the promoted item of the goal
event_typeVarchar(50)The event typeclick, send, impression
first_age_secondsIntegerFirst age is the time between the first attributable event and the goal time.
first_time_tsTimestamp without time zoneTimestamp for the first attributable event, UTC
last_age_secondsIntegerLast age is the time between the most recent attributable event and the goal time.
last_time_tsTimestamp without time zoneTimestamp for the most recent attributable event, UTC
browserVarchar(256)Client browser applicationSafari, Chrome, Firefox, IE
deviceVarchar(256)Client hardware devicemobile, computer, tablet, other
engagementVarchar(256)Engagement for the associated userlow, medium, high
has_purchasedBooleanSet when the user makes a purchase
is_anonymousBooleantrue when the user is not named (identified)
is_firsttimeBooleantrue when the completion represents a first encounter with a visitor
osVarchar(256)Operating system - x is "macOS X"x, windows, linux, ios, android, other
platformVarchar(256)Platform used in the goal completion (email, web)Email, Web
sourceVarchar(256)For web events, source is Web. For customers who use the mobile SDK, each mobile app would be a separate source.

Indexes

  • UNIQUE INDEX goal_completion_attribution_user_id_key ON goal_completion_attribution USING btree (user_id, goal_id, goal_ts, experience_id, user_group, event_type)

Example Query

back to top


If a goal completion has an associated promoted item, you can find it in this table. Use (user_id, goal_id, goal_ts) to join back to the other goal tables.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User associated with the goal completion
goal_tsTimestamp without time zoneTimestamp of the goal completion in UTC
goal_idVarchar(256)The goal id stores different value types; for segment goals, the goal id is the segment id; for purchase goals, the goal id is the character p.p
experience_idVarchar(256)Experience associated with the goal
user_groupChar(1)Per experience control (C) and test (D)C, D
item_idVarchar(256)Unique identifier for the promoted item
item_typeVarchar(256)For example, p to indicate productp
attributesVarchar(256)A list of attributes for the promoted item. The underlying data is JSON, stored here as a string.
updated_tsTimestamp without time zoneUTC Date and time of the most recent update of this promoted item attribution
revenueNumeric(18,4)Revenue resulting from the goal completion

Indexes

  • UNIQUE INDEX goal_completion_attribution_promoted_item_user_id_key ON goal_completion_attribution_promoted_item USING btree (user_id, goal_id, goal_ts, experience_id, user_group, item_type, item_id)

Example Query

back to top


For goal completions that are associated with a purchase, this table stores the line items associated with that purchase.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User associated with the order
goal_tsTimestamp without time zoneTimestamp of the goal completion in UTC
goal_idVarchar(256)p for purchase or a segment idp
line_item_numSmallintData warehouse internal enumeration (does not have an analog in the operational store)
item_idVarchar(256)Line item
item_typeVarchar(256)Typically p for productp
priceNumeric(18,4)Item price
quantitySmallintItem quantity

Indexes

  • UNIQUE INDEX goal_completion_line_item_user_id_key ON goal_completion_line_item USING btree (user_id, goal_id, goal_ts, line_item_num)

Example Query

back to top


Use this table to de-reference geographical information stored at the visit or user level (for example, city_code, metro_code, postal_code, latitude, or longitude) and convert it to human-readable labels.

Field NameData TypeDescriptionExample values
idVarchar(200)Unique identifier for the location
latitudeNumeric(9,6)Latitude associated with the location40.7134, 37.0547
longitudeNumeric(9,6)Longitude associated with the location-111.889, -91.5281
postal_codeVarchar(10)Postal code associated with the location02143
city_codeIntegerCode for the city associated with the location
city_nameVarchar(100)Name of the city associated with the location
metro_codeIntegerMetro code associated with the location. Metro codes for US are Nielsen DMA codes
region_codeVarchar(10)Region code associated with the locationUT, WI
country_codeVarchar(100)2-letter Country code associated with the locationUS, IT, KR
continent_codeVarchar(100)2-letter continent code associated with the locationNA, SA
updated_tsTimestamp without time zoneUTC date and time of the most recent update to the location

Indexes

  • PRIMARY KEY location_pkey ON "location" USING btree (id)

back to top


Stores the line items for a given order. Use (user_id, order_id) when joining back to order.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User id associated with the order
order_idVarchar(256)Unique identifier for the order associated with the line item
line_item_numSmallintData warehouse internal enumeration (does not have an analog in the operational store)
item_typeVarchar(256)Type of item. Usually product.
item_idVarchar(256)The item (usually product) associated with the order
priceNumeric(18,4)Unit price
quantitySmallintItem quantity for this line item
currencyVarchar(256)Currency associated with the order
skuVarchar(256)Stock Keeping Unit associated with the line item
order_created_tsTimestamp without time zoneUTC date and time when the order was created

Indexes

  • PRIMARY KEY order_line_item_pkey ON order_line_item USING btree (user_id, order_id, item_id, order_created_ts, line_item_num)

back to top


Provides attribute name-value pairs associated with line items, as well as provenance.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User associated with the order
order_idVarchar(256)Unique order identifier
line_item_numSmallintGenerated in DW, a 1-based sequence value for this line item
item_typeVarchar(256)Type type of item
item_idVarchar(256)Unique identifier for the associated item
attribute_nameVarchar(256)The name of the attributequantity_shipped
attribute_valueVarchar(256)The value of the attribute
value_metadata_originVarchar(256)The name of the entity that issues or creates the initial attribute value.CRM, PointOfSaleSystem, FulfillmentSystem, LoyaltySystem
value_metadata_providerVarchar(256)The name of the entity that is providing the attribute.CsvUserEtlJob:user-20190528.csv.gz
value_metadata_gear_idVarchar(256)ID of the Personalization gear that updated the attribute, or null if it was not updated by a gear
value_metadata_last_updated_tsVarchar(256)UTC date and time when the attribute was last updated
value_metadata_last_verified_tsVarchar(256)UTC date and time when the attribute value was last verified as being true and belonging to the specified individual
value_metadata_classificationVarchar(256)Metadata relevant or pertaining to the security classification of a given attribute's value

Indexes

  • PRIMARY KEY order_line_item_attribute_pkey ON order_line_item_attribute USING btree (user_id, order_id, item_type, item_id, line_item_num, attribute_name)

back to top


Stores product name and price.

Field NameData TypeDescriptionExample values
idVarchar(200)Unique identifier for the product
nameVarchar(200)Name of the product
descriptionVarchar(500)Description of the product
list_priceRealThe list price of the product
priceRealUnit price displayed to an anonymous user when promoting the product
created_tsTimestamp without time zoneUTC date and time the product created in Personalization system
updated_tsTimestamp without time zoneUTC date and time the product was most recently updated in the Personalization system
promotion_stateVarchar(50)Metadata concerning product eligibility and prioritizationExcluded, Prioritized
urlVarchar(2048)URL for the product
image_urlVarchar(2048)URL for the product image

Indexes

  • PRIMARY KEY product_pkey ON product USING btree (id)

Referenced by

  • TABLE "product_attribute" CONSTRAINT "product_attribute_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)
  • TABLE "product_category" CONSTRAINT "product_category_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)
  • TABLE "product_dimension" CONSTRAINT "product_dimension_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)
  • TABLE "user_daily_stat_item_stat_product" CONSTRAINT "user_daily_stat_item_stat_product_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)

back to top


Stores attribute data about products, as well as provenance for the attributed value.

Field NameData TypeDescriptionExample values
product_idVarchar(200)Unique identifier for the product
attribute_nameVarchar(256)The name of the attribute
attribute_valueVarchar(256)The value of the attribute
value_metadata_originVarchar(256)The name of the entity that issues or creates the initial attribute valueCRM, PointOfSaleSystem, FulfillmentSystem, LoyaltySystem
value_metadata_providerVarchar(256)The name of the entity that is providing the attributeCsvUserEtlJob:user-20190528.csv.gz
value_metadata_gear_idVarchar(256)ID of the Personalization gear that updated the attribute, or null if it was not updated by a gear
value_metadata_last_updated_tsVarchar(256)UTC date and time when the attribute was last updated
value_metadata_last_verified_tsVarchar(256)UTC date and time when the attribute value was last verified as being true and belonging to the specified individual
value_metadata_classificationVarchar(256)Metadata relevant or pertaining to the security classification of a given attribute's value

Indexes

  • PRIMARY KEY product_attribute_pkey ON product_attribute USING btree (product_id, attribute_name)

back to top


A product can belong to more than one (fully qualified) category. This table stores product-category relationships.

Field NameData TypeDescriptionExample values
product_idVarchar(256)Unique identifier for the product
category_idVarchar(256)Unique identifier for the category

Indexes

  • UNIQUE INDEX product_category_product_id_key ON product_category USING btree (product_id, category_id)

back to top


Logically the (dimension_type, dimension_id) are a name-value pair associated with a product. Common dimensions include gender, brand, and style. Custom dimensions are also available.

Field NameData TypeDescriptionExample values
product_idVarchar(200)Unique identifier for a product
dimension_idVarchar(200)The value of a dimensionGucci, Red
dimension_typeVarchar(200)The name of a dimension. Dimension names are capitalizedBrand, Gender, Style

Indexes

  • UNIQUE INDEX product_dimension_product_id_key ON product_dimension USING btree (product_id, dimension_type, dimension_id)

back to top


An Personalization segment is a real-time grouping of accounts or individuals based on a set of criteria you define. Segment updates in the operational store happen in real time, so any membership changes occur immediately, even during the same visit. Exports to this table, if done at all, are done once per day. Segment export is enabled for individual segments by request, on the condition that the segment is small enough to export in this manner.

Field NameData TypeDescriptionExample values
idVarchar(10)Unique identifier for the segment
nameVarchar(200)Name of the segment
is_goalBooleantrue when segment is associated with a goal.
entity_typeVarchar(200)The type of entity associated with this segmentAccount, User
created_tsTimestamp without time zoneUTC date and time of creation
updated_tsTimestamp without time zoneUTC date and time of recent update

Indexes

  • PRIMARY KEY segment_pkey ON segment USING btree (id)

Referenced by

  • TABLE "segment_membership" CONSTRAINT "segment_membership_segment_id_fkey" FOREIGN KEY (segment_id) REFERENCES segment(id)

back to top


User-segment associations. For B2C, segments are associated with users.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Segment member
segment_idVarchar(200)Unique identifier for the segment
joined_tsTimestamp without time zoneUTC date and time of segment join event
left_tsTimestamp without time zoneUTC date and time of segment leave event
is_activeBooleanTrue if user is currently an active member of the segment
updated_dtTimestamp without time zoneUTC date and time of most recent update

Indexes

  • UNIQUE INDEX segment_membership_user_id_key ON segment_membership USING btree (user_id, segment_id, joined_ts)

back to top


Survey questions and associated metadata

Field NameData TypeDescriptionExample values
survey_idVarchar(6)Unique identifier for the survey
survey_nameVarchar(200)Name of the survey
survey_updated_tsTimestamp without time zoneUTC date and time of the most recent update to the question
page_nameVarchar(200)Page name on which the question appears
page_numIntegerInternal sequence number for the page
element_nameVarchar(200)Name of the element in which the question appears. Pages have many elements.
element_titleVarchar(200)Title of the element in which the question appears
element_typeVarchar(200)The type of element - text, radio button, and so on.
element_idVarchar(200)Unique identifier for the element in which the question appears
element_numIntegerInternal sequence enumerating the elements on a page
question_idVarchar(200)Unique identifier for the question

Indexes

  • UNIQUE INDEX survey_question_survey_id_key ON survey_question USING btree (survey_id, question_id, element_id, page_num, element_num)

back to top


Field NameData TypeDescriptionExample values
survey_idVarchar(6)Unique identifier for the survey
started_tsTimestamp without time zoneUTC date and time the survey was started
user_idVarchar(200)Unique identifier for the user taking the survey
question_idVarchar(200)Unique identifier for the survey question
survey_sessionVarchar(200)hash of survey_id, started_ts, user_id, which can help group questions into a session
response_tsTimestamp without time zoneUTC date and time the response was made
answerVarchar(2048)Long text field that captures the survey answer

Indexes

  • UNIQUE INDEX survey_response_user_id_key ON survey_response USING btree (user_id, survey_id, question_id, response_ts)

back to top


User is the center of the Personalization data model. Much of the data about users is stored in tables other than this one; this table stores top-level information associated with a user. Fields prefixed with loc_ refer to location information. Location codes can be looked up in the Location table. Fields prefixed with or_ refer to the original referring event for this user.

Field NameData TypeDescriptionExample values
idVarchar(200)Unique id for the user
id_mod_100IntegerAn offset from 0 to 99 used to create random partitions in the audience
first_activity_tsTimestamp without time zoneUTC date and time the user was first observed
last_activity_tsTimestamp without time zoneUTC date and time the user was most recently observed
engagement_scoreIntegerThe engagement score is an index that tracks how engaged your site visitors and application users are based on several different factors including visit information, actions, key performance indicators (KPIs), and segments. Higher values indicate deeper engagement.
engagement_dtDateThe time the engagement score was computed.
created_tsTimestamp without time zoneUTC date and time this record was created
loc_device_providedBooleantrue if the location (loc_ fields) was provided by a device (for example, obtained via a GPS retrieval), or false if this location is an approximate determined via geocoding of an IP address
loc_ip_addressVarchar(100)IP Address associated with the user location
loc_continent_keyVarchar(100)2-letter continent code associated with the user locationNA, AS
loc_country_codeChar(2)An ISO 3166-1 alpha-2 country code (for example, US)US
loc_country_numeric_codeSmallintISO-3166-1 numeric country code (for example, 840 for United States)
loc_region_codeIntegerLocation's region code. Note - does not correspond to location(region_code)
loc_metro_codeIntegerLocation's metro code, corresponds to location(metro_code). For the US, the metro code is the Nielsen Designated Market Area (DMA) code
loc_city_codeIntegerLocation's city code, corresponds to location(city_code)
loc_postal_codeVarchar(10)Location's postal code
loc_ispVarchar(100)Internet Service Provider associated with a locationTelstra Internet, Comcast, Verizon
loc_organizationVarchar(100)Organization associated with a location (can be null)
loc_naics_codeIntegerNAICS industry code
or_mediumVarchar(50)Original referrer medium - how the user first arrivedSEARCH, EMAIL, SOCIAL, DIRECT
or_referrer_sourceVarchar(50)Source of the original referrerGoogle, Bing, Yahoo!, Facebook, Pinterest
or_query_termsVarchar(512)Search terms for user who first arrived via search
or_referrer_domainVarchar(100)Domain of the original referrergoogle.com, bing.com, google.de, facebook.com
or_referrer_reverse_subdomainVarchar(200)Full host associated with the original referrer, reversed Used operationallymx.com.google.www
is_anonymousBooleantrue when this user is not identifiable
lifetime_valueRealTotal amount this user has spent
loc_cityVarchar(256)City name associated with the user's most recent location
loc_latitudeRealLatitude associated with the user's most recent location
loc_longitudeRealLongitude associated with the user's most recent location
loc_timezoneVarchar(256)Timezone associated with the user's most recent location
updated_tsTimestamp without time zoneUTC date and time the user was most recently updated
loc_state_province_codeVarchar(256)Alphanumeric code associated with the user's most recent locationKY, PAC
or_referring_urlVarchar(2048)URL associated with the original referrerhttps://www.google.com/
or_landing_urlVarchar(2048)URL of the first page the user saw
email_addressVarchar(100)Email address associated with the user, if available

Indexes

  • PRIMARY KEY user_pkey ON "user" USING btree (id)

Referenced by

  • TABLE "goal_completion_attribution_promoted_item" CONSTRAINT "goal_completion_attribution_promoted_item_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "goal_completion_attribution" CONSTRAINT "goal_completion_attribution_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "goal_completion_line_item" CONSTRAINT "goal_completion_line_item_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "order_line_item_attribute" CONSTRAINT "order_line_item_attribute_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "order_line_item" CONSTRAINT "order_line_item_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "segment_membership" CONSTRAINT "segment_membership_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_alias" CONSTRAINT "user_alias_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_attribute" CONSTRAINT "user_attribute_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_campaign_state" CONSTRAINT "user_campaign_state_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_click" CONSTRAINT "user_click_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat_action" CONSTRAINT "user_daily_stat_action_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat_item_stat_category" CONSTRAINT "user_daily_stat_item_stat_category_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat_item_stat_dimension" CONSTRAINT "user_daily_stat_item_stat_dimension_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat_item_stat_product" CONSTRAINT "user_daily_stat_item_stat_product_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat_item_stat_promotion" CONSTRAINT "user_daily_stat_item_stat_promotion_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_daily_stat" CONSTRAINT "user_daily_stat_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_dismissal" CONSTRAINT "user_dismissal_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_engagement_history" CONSTRAINT "user_engagement_history_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_goal_completion" CONSTRAINT "user_goal_completion_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_impression" CONSTRAINT "user_impression_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_order_attribute" CONSTRAINT "user_order_attribute_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_order" CONSTRAINT "user_order_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_search" CONSTRAINT "user_search_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
  • TABLE "user_visit" CONSTRAINT "user_visit_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)

back to top


This table contains the IDs of any anonymous users that have been merged into a given user. It also contains any non-anonymous IDs they used to have. This occurs when userIDs are changed to a new format, or simply a new ID.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for a user
anon_aliasVarchar(200)User Id associated with this user

Indexes

  • UNIQUE INDEX user_alias_user_id_key ON user_alias USING btree (user_id, anon_alias)

back to top


Name-value pairs associated with a user (many different use cases - ids, segment support, and so on)

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for the user
updated_tsTimestamp without time zoneUTC date and time this attribute was most recently updated
nameVarchar(256)Name of the attribute
valueVarchar(256)Value of the attribute

Indexes

  • PRIMARY KEY user_attribute_pkey ON user_attribute USING btree (user_id, name, updated_ts)

back to top


Summary information about a user or campaign. This table is a mirror of an operational table that provides current state to the platform for up-to-the-minute reporting or other applications; less useful in a warehouse context, but does provide the state at batch time. Can be used to quickly confirm membership or perform other on demand tasks that are later migrated to fully fleshed-out queries.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for the user
campaign_idVarchar(256)Unique identifier for the campaign
last_reset_time_tsTimestamp without time zoneUTC date and time of the most recent campaign reset
experience_idVarchar(256)Unique identifier for the experience
user_groupVarchar(256)The user group (control or test) associated with this user's experience
num_clicksIntegerTotal number of clicks
first_click_tsTimestamp without time zoneUTC date and time of the least recent click
last_click_tsTimestamp without time zoneUTC date and time of the most recent click
num_impressionsIntegerTotal number of impressions
first_impression_tsTimestamp without time zoneUTC date and time of the least recent impression
last_impression_tsTimestamp without time zoneUTC date and time of the most recent impression

Indexes

  • UNIQUE INDEX user_campaign_state_user_id_key ON user_campaign_state USING btree (user_id, campaign_id, experience_id, user_group)

back to top


Stores click events associated with a campaign at the visit level. Event_ts is associated with the most recent event.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for the user
experience_idVarchar(256)Unique identifier for the experience
event_tsTimestamp without time zoneUTC date and time of the most recent click in a session
user_groupChar(1)The user group associated with the user and experience - control (C) or test (D)
browserVarchar(256)Client browser applicationSafari, Chrome, Firefox, IE, other
deviceVarchar(256)Client hardware devicemobile, computer, tablet, other
engagementVarchar(256)Engagement for the associated userlow, medium, high
has_purchasedBooleanSet when the user makes a purchase
click_countSmallintTotal number of clicks for the session
is_anonymousBooleantrue when the user is not named (identified)
is_firsttimeBooleantrue when this visit is a user's first
osVarchar(256)Operating system - x is "macOS X"x, windows, linux, ios, android, other
platformVarchar(256)Platform associated with the clicksEmail, Web
sourceVarchar(256)For customers who use the mobile SDK, each mobile app would be listed as a separate source

Indexes

  • PRIMARY KEY user_click_pkey ON user_click USING btree (user_id, experience_id, event_ts)

back to top


Aggregation of events at the (user, day) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate of the stat aggregation for this user
user_idVarchar(200)Unique identifier for the user
visit_countIntegerNumber of times user visited on the stat_date
visit_millisBigintTotal visit duration, in milliseconds
total_actionsIntegerNumber of actions performed by the user
num_pagesIntegerNumber of pages the user viewed

Indexes

  • UNIQUE INDEX user_daily_stat_stat_date_key ON user_daily_stat USING btree (stat_date, user_id)

back to top


Aggregation of events at the (user, day, action) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate of the stat aggregation for this user
user_idVarchar(200)Unique identifier for the user
action_idVarchar(200)Identifier for an action performed by the userHome, Search, View Category, View Product
action_countIntegerTotal count of actions for this action on this day by the associated user

Indexes

  • UNIQUE INDEX user_daily_stat_action_stat_date_key ON user_daily_stat_action USING btree (stat_date, user_id, action_id)

back to top


Aggregation of events at the (user, day, product category) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate associated with the stat
user_idVarchar(200)Unique identifier for the user
category_idVarchar(200)Unique identifier for the category
cart_valueNumeric(18,4)Total value of items in the cart for this day, category, user
num_cart_itemsIntegerCount of items in the cart
num_purchasesIntegerCount of purchases
out_of_stock_viewsIntegerCount of views of products that were out of stock
purchase_valueNumeric(18,4)Total value of purchased made for this user, category, date
recommended_countIntegerNumber of items recommended to the user for this category
view_time_millisIntegerTotal view time for the category in milliseconds
view_valueNumeric(18,4)The sum of the value of items viewed in this category
viewsIntegerNumber of views for this category, day, user

Indexes

  • UNIQUE INDEX user_daily_stat_item_stat_category_stat_date_key ON user_daily_stat_item_stat_category USING btree (stat_date, user_id, category_id)

back to top


Aggregation of events at the (user, day, product dimension) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate associated with the stat
user_idVarchar(200)Unique identifier for the user
dimension_typeVarchar(256)The name of a dimension. Dimension names are capitalized.Brand, Gender, Style, ItemClass
dimension_idVarchar(256)Unique identifier for the dimension
cart_valueNumeric(18,4)Total value of items in the cart for this day, dimension, user
num_cart_itemsIntegerCount of items in the cart
num_purchasesIntegerCount of purchases
out_of_stock_viewsIntegerCount of views of products that were out of stock
purchase_valueNumeric(18,4)Total value of purchased made for this user, dimension, date
recommended_countIntegerNumber of items recommended to the user for this dimension
view_time_millisIntegerTotal view time for the dimension in milliseconds
view_valueNumeric(18,4)The sum of the value of items viewed in this dimension
viewsIntegerNumber of views for this dimension, day, user

Indexes

  • UNIQUE INDEX user_daily_stat_item_stat_dimension_stat_date_key ON user_daily_stat_item_stat_dimension USING btree (stat_date, user_id, dimension_type, dimension_id)

back to top


Aggregation of events at the (user, day, product) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate associated with the stat
user_idVarchar(200)Unique identifier for the user
product_idVarchar(200)Unique identifier for the product
cart_valueNumeric(18,4)Total value of items in the cart for this day, product, user
num_cart_itemsIntegerCount of items in the cart
num_purchasesIntegerCount of purchases
out_of_stock_viewsIntegerCount of views of products that were out of stock
purchase_valueNumeric(18,4)Total value of purchased made for this user, product, date
recommended_countIntegerNumber of items recommended to the user for this product
view_time_millisIntegerTotal view time for the product in milliseconds
view_valueNumeric(18,4)The sum of the value of items viewed in this product
viewsIntegerNumber of views for this product, day, user

Indexes

  • UNIQUE INDEX user_daily_stat_item_stat_product_stat_date_key ON user_daily_stat_item_stat_product USING btree (stat_date, user_id, product_id)

Example Query

back to top


Aggregation of events at the (user, day, promotion) grain

Field NameData TypeDescriptionExample values
stat_dateDateDate for the stat
user_idVarchar(200)Unique identifier for the user
promotion_idVarchar(200)Unique identifier for the promotion
cart_valueNumeric(18,4)Total value of items in the cart for this day, promotion, user
num_cart_itemsIntegerCount of items in the cart
num_purchasesIntegerCount of purchases
out_of_stock_viewsIntegerCount of views of promotions associated with an out of stock product
purchase_valueNumeric(18,4)Total value of purchases made for this user, promotion, date
recommended_countIntegerNumber of items recommended to the user for this promotion
view_time_millisIntegerTotal view time for the promotion in milliseconds
view_valueNumeric(18,4)The sum of the value of promotions viewed
viewsIntegerNumber of views by this user for this promotion and day
eligible_for_servingIntegerNumber of times this promotion was eligible to be served
requested_for_servingIntegerNumber of times this promotion was requested to be served
servedIntegerNumber of times this promotion was served

Indexes

  • UNIQUE INDEX user_daily_stat_item_stat_promotion_stat_date_key ON user_daily_stat_item_stat_promotion USING btree (stat_date, user_id, promotion_id)

back to top


Stores dismissal events associated with a campaign at the visit level. Event_ts is associated with the most recent event.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for the user
experience_idVarchar(256)Unique identifier for the experience
event_tsTimestamp without time zoneUTC date and time of the most recent dismissal in a session.
user_groupChar(1)The user group associated with the user and experience - control (C) or (D)
browserVarchar(256)Client browser applicationSafari, Chrome, Firefox, IE, other
deviceVarchar(256)Client hardware devicemobile, computer, tablet, other
engagementVarchar(256)Engagement for the associated userlow, medium, high
has_purchasedBooleanSet when the user makes a purchase
dismissal_countSmallintTotal number of dismissals for the session
is_anonymousBooleantrue when the user is not named (identified)
is_firsttimeBooleantrue when this visit is a user's first
osVarchar(256)Operating system - x is "macOS X"x, windows, linux, ios, android, other
platformVarchar(256)Platform associated with the dismissalsEmail, Web
sourceVarchar(256)For customers who use the mobile SDK, each mobile app would be a separate source.

Indexes

  • PRIMARY KEY user_dismissal_pkey ON user_dismissal USING btree (user_id, experience_id, event_ts)

back to top


Stores historical engagement values at the user grain

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for a user
"day"DateDate of the day tracked for engagement
scoreIntegerEngagement score is an integer reflecting a percentage from 0 to 125 and the value depends on account configuration. For more information, see Engagement Score.

Indexes

  • UNIQUE INDEX user_engagement_history_user_id_key ON user_engagement_history USING btree (user_id, "day")

back to top


Since goal completions often involve a purchase, purchase data is stored at the top level in this table and is empty for non-purchase goal completions

Field NameData TypeDescriptionExample values
user_idVarchar(200)User who satisfied the goal criteria
goal_tsTimestamp without time zoneTimestamp of the goal completion in UTC
goal_idVarchar(256)One of: segment id or purchase (p)p
order_idVarchar(256)Unique identifier for the order. This ID comes from the customer.
order_currencyVarchar(256)Currency used to purchase
order_line_item_countSmallintCount of line items in the order
order_total_valueNumeric(18,4)Sum of quantity * price for all line items
order_units_countSmallintCount of units in the order
order_visit_age_minutesDouble precisionAge of the visit at the time of the purchase goal completion
order_visit_timeTimestamp without time zoneUTC date and time of the visit during which this order was made
browserVarchar(256)Client browser applicationSafari, Chrome, Firefox, IE, other
deviceVarchar(256)Client hardware devicemobile, computer, tablet, other
engagementVarchar(256)Engagement for the associated userlow, medium, high
has_purchasedBooleanSet when the user makes a purchase
is_anonymousBooleantrue when the user is not named (identified)
is_first_timeBooleantrue when the completion represents a first encounter with a visitor
osVarchar(256)Operating system - x is "macOS X"x, windows, linux, ios, android, other
platformVarchar(256)Platform used in the goal completion (email, web)Email, Web
sourceVarchar(256)For customers who use the mobile SDK, each mobile app would be a separate source.

Indexes

  • UNIQUE INDEX user_goal_completion_user_id_key ON user_goal_completion USING btree (user_id, goal_id, goal_ts)

back to top


Stores impression events associated with a campaign at the visit level, with the most recent event associated with event_ts.

Field NameData TypeDescriptionExample values
user_idVarchar(200)Unique identifier for the user
experience_idVarchar(256)Unique identifier for the experience
event_tsTimestamp without time zoneUTC date and time of the most recent impression in a session.
user_groupChar(1)The user group associated with the user and experience - control (C) or test (D)
browserVarchar(256)Client browser applicationSafari, Chrome, Firefox, IE, other
deviceVarchar(256)Client hardware devicemobile, computer, tablet, other
engagementVarchar(256)Engagement for the associated userlow, medium, high
has_purchasedBooleanSet when the user makes a purchase
impression_countSmallintTotal number of impressions for the session
is_anonymousBooleantrue when the user is not named (identified)
is_firsttimeBooleantrue when this visit is a user's first
osVarchar(256)Operating system - x is "macOS X"x, windows, linux, ios, android, other
platformVarchar(256)Platform associated with the impressionsEmail, Web
sourceVarchar(256)For customers who use the mobile SDK, each mobile app would be a separate source.

Indexes

  • PRIMARY KEY user_impression_pkey ON user_impression USING btree (user_id, experience_id, event_ts)

back to top


Stores purchases. Only purchased orders are kept in the Data Warehouse, nothing open

Field NameData TypeDescriptionExample values
idVarchar(100)Unique identifier for the order. This ID comes from the customer.
user_idVarchar(200)Unique identifier for the user
order_numSmallint1-based sequence of orders for this user. Based on the array order in the operational store. (Not set by the customer)
visit_start_tsTimestamp without time zoneStart time of the visit in which this order was placed
visit_numIntegerVisit in which order create time falls w/in visit start/end.
created_tsTimestamp without time zoneUTC date and time at which the order was created
updated_tsTimestamp without time zoneUTC date and time the order was most recently updated
purchased_tsTimestamp without time zoneUTC date and time the purchase was made
visit_age_at_purchase_msIntegerAge of the session, in milliseconds, at purchase time
num_itemsIntegerNumber of items in the order
num_unitsIntegerNumber of units (distinct items) in the order
total_valueNumeric(18,4)Total value of the purchase
currencyVarchar(200)Currency used to make the purchase
statusVarchar(200)Purchased only, other states not stored in the warehouse. (Open, Purchased, Canceled).

Indexes

  • PRIMARY KEY user_order_pkey ON user_order USING btree (id)
  • UNIQUE INDEX user_order_user_id_key ON user_order USING btree (user_id, created_ts)

Referenced by

  • TABLE "order_line_item_attribute" CONSTRAINT "order_line_item_attribute_order_id_fkey" FOREIGN KEY (order_id) REFERENCES user_order(id)
  • TABLE "order_line_item" CONSTRAINT "order_line_item_order_id_fkey" FOREIGN KEY (order_id) REFERENCES user_order(id)
  • TABLE "user_order_attribute" CONSTRAINT "user_order_attribute_order_id_fkey" FOREIGN KEY (order_id) REFERENCES user_order(id)

back to top


Name-value pairs associated with an order

Field NameData TypeDescriptionExample values
order_idVarchar(100)Unique identifier for the order
user_idVarchar(200)User who made the purchase
order_numSmallintInternally generated 1-based sequence indicating which order this is
attribute_nameVarchar(256)The name of the attribute
attribute_valueVarchar(256)The value of the attribute
value_metadata_originVarchar(256)The name of the entity that issues or creates the initial attribute valueCRM, PointOfSaleSystem, FulfillmentSystem, LoyaltySystem
value_metadata_providerVarchar(256)The name of the entity that is providing the attributeCsvUserEtlJob:user-20190528.csv.gz
value_metadata_gear_idVarchar(256)ID of the Personalization gear that updated the attribute, or null if it was not updated by a gear
value_metadata_last_updated_tsVarchar(256)UTC date and time when the attribute was last updated
value_metadata_last_verified_tsVarchar(256)UTC date and time when the attribute value was last verified as being true and belonging to the specified individual
value_metadata_classificationVarchar(256)Metadata relevant or pertaining to the security classification of a given attribute's value

Indexes

  • PRIMARY KEY user_order_attribute_pkey ON user_order_attribute USING btree (user_id, order_id, attribute_name)

back to top


User search terms (some normalization/tokenization is performed)

Field NameData TypeDescriptionExample values
user_idVarchar(200)User who performed the search
termVarchar(200)Search term (tokenized)
most_recent_searchTimestamp without time zoneUTC date and time of the most recent search for this term
product_clicksIntegerCount of product clicks associated with the search
purchase_valueNumeric(18,4)Total purchase value of orders associated with this search
search_countIntegerTotal number of searches for this term

Indexes

  • PRIMARY KEY user_search_pkey ON user_search USING btree (user_id, term)

back to top


Use this table to join the user_visit_detail with other tables containing a user_id. The user_id in this table corresponds to the general notion of user_id in the database, and user(id). The user_visit_key is the user_id in the user_visit_detail table.

Field NameData TypeDescriptionExample values
user_idVarchar(200)User id from the user table
user_id_hashedVarchar(200)Hashed user id from the user_visit_detail table

Indexes

  • UNIQUE INDEX user_user_visit_lookup_user_id_key ON user_user_visit_lookup USING btree (user_id, user_id_hashed)

back to top


Stores information about the user visit

Fields that only appear in user visit: referring_campaign_id, weather, previous_path, temperature, referrer, num_pageviews, num_events, last_event_ts, visit_num

Fields that appear in both user visit and user visit detail: is_anonymous, browser, engagement, source, utm_parameters, device, is_firsttime, has_purchased, platform, start_ts, _dw_created_ts, os, user_id

The user_id user_visit_detail is a hash value of the user ID in this table; use user_user_lookup to join

Field NameData TypeDescriptionExample values
user_idVarchar(200)User id associated with the visit
visit_numSmallintInternally generated sequence value in the visit history - which visit this was.
num_eventsIntegerCount of events for the visit
previous_pathVarchar(100)The most recent page view action for this visit (operation field)
num_pageviewsIntegerCount of pages viewed in the visit
referring_campaign_idVarchar(32)External campaign that drove the visit
deviceVarchar(100)Client hardware devicemobile, computer, tablet, other
engagementVarchar(100)Engagement for the associated userlow, medium, high
osVarchar(100)Operating system - x is "macOS X"x, windows, linux, ios, android, other
browserVarchar(100)Client browser applicationSafari, Chrome, Firefox, IE, other
platformVarchar(100)Platform used in the goal completion (email, web)Email, Web
temperatureVarchar(100)Recorded temperature at the physical location of the user during the visit time
weatherVarchar(100)Recorded weather at the physical location of the user during the visit time
referrerVarchar(2048)HTTP referrer for the visit
start_tsTimestamp without time zoneUTC date and time the visit began
last_event_tsTimestamp without time zoneUTC date and time of the most recent event in the visit
utm_parametersVarchar(100)Urchin Tracking Module (UTM) parameters identify the marketing campaign that refers traffic to a specific website and are associated with the visit's referrer
has_purchasedBooleanSet when the user makes a purchase
is_anonymousBooleantrue when the user is not named (identified)
is_first_timeBooleantrue when this visit is the user's first
sourceVarchar(10)For customers who use the mobile SDK, each mobile app would be a separate source

Indexes

  • PRIMARY KEY user_visit_pkey ON user_visit USING btree (user_id, start_ts)
  • UNIQUE INDEX user_visit_user_id_key ON user_visit USING btree (user_id, visit_num)

Referenced by

  • TABLE "user_order" CONSTRAINT "user_order_user_id_visit_num_fkey" FOREIGN KEY (user_id, visit_num) REFERENCES user_visit(user_id, visit_num)
  • TABLE "user_order" CONSTRAINT "user_order_user_id_visit_start_ts_fkey" FOREIGN KEY (user_id, visit_start_ts) REFERENCES user_visit(user_id, start_ts)

back to top


Stores additional information about the user visit - location and referrer are usually of interest.

The following fields appear only in the visit detail, not the visit: region_code, referrer_domain, revenue, city_code, metro_code, longitude, industry_code, referring_campaign, country_code, action_count, page_view_count, referrer_subdomain, referrer_url, referrer_terms, referrer_source, latitude, duration_minutes, referrer_landing_url, referrer_medium

Fields that appear in both this table and user_visit: is_anonymous, browser, engagement, source, utm_parameters, device, is_firsttime, has_purchased, platform, start_ts, _dw_created_ts, os, user_id

The user_id in this table is a hash value on the user_id in user_visit

Field NameData TypeDescriptionExample values
user_id_hashedVarchar(200)Hash code of the user_id (id from the user table) associated with this visit
start_tsTimestamp without time zoneUTC date and time the visit started
referrer_mediumVarchar(100)How the user first arrivedSEARCH, EMAIL, SOCIAL, DIRECT
referrer_sourceVarchar(100)Source of the visitGoogle, Bing, Yahoo!, Facebook, Pinterest
referrer_termsVarchar(255)Search terms for associated with the referral
referrer_domainVarchar(100)Domain of the visit referrergoogle.com, bing.com, google.de, facebook.com
referrer_subdomainVarchar(100)Referrer host with subdomainwww.google.com.mx
referrer_urlVarchar(2048)URL associated with the visit referrer
referrer_landing_urlVarchar(2048)URL of the first page the user saw
utm_parametersVarchar(100)Urchin Tracking Module (UTM) parameters identify the marketing campaign that refers traffic to a specific website and are associated with the visit's referrer
latitudeNumeric(9,6)Latitude associated with the visitor's physical location
longitudeNumeric(9,6)Longitude associated with the visitor's physical location
country_codeVarchar(100)2-letter Country code associated with the visit
region_codeVarchar(100)Region code associated with the visit
metro_codeIntegerMetro code associated with the visit. Metro codes for US are Nielsen DMA codes
city_codeIntegerCode for the city associated with the visit
industry_codeIntegerNAICS industry code
referring_campaignVarchar(32)External campaign that drove the visit
deviceVarchar(100)Client hardware devicemobile, computer, tablet, other
engagementVarchar(100)Engagement for the associated userlow, medium, high
osVarchar(100)Operating system - x is "macOS X"x, Windows, Linux, iOS, Android, other
browserVarchar(100)Client browser applicationSafari, Chrome, Firefox, IE, other
platformVarchar(100)Platform (email, web)Email, Web, Mobile
has_purchasedBooleanSet when the user makes a purchase
is_anonymousBooleantrue when the user is not named (identified)
is_first_timeBooleantrue when this visit is the user's first
sourceVarchar(10)For customers who use the mobile SDK, each mobile app would be a separate source
duration_minutesDouble precisionTime spent in the visit, in minutes
revenueNumeric(18,4)Total revenue associated with purchases made during the visit
action_countIntegerCount of actions performed this visit
page_view_countIntegerCount of pages viewed this visit

Indexes

  • PRIMARY KEY user_visit_detail_pkey ON user_visit_detail USING btree (user_id_hashed, start_ts)

back to top