Data Dictionary

• Table campaign
• Table category
• Table country (deprecated)
• Table dimension
• Table experience
• Table goal_completion_attribution
• Table goal_completion_attribution_promoted_item
• Table goal_completion_line_item
• Table location (deprecated)
• Table order_line_item
• Table order_line_item_attribute
• Table product
• Table product_attribute
• Table product_category
• Table product_dimension
• Table segment
• Table segment_membership
• Table survey_question (deprecated)
• Table survey_response (deprecated)
• Table user
• Table user_alias
• Table user_attribute
• Table user_campaign_state
• Table user_click
• Table user_daily_stat
• Table user_daily_stat_action
• Table user_daily_stat_item_stat_category
• Table user_daily_stat_item_stat_dimension
• Table user_daily_stat_item_stat_product
• Table user_daily_stat_item_stat_promotion
• Table user_dismissal
• Table user_engagement_history
• Table user_goal_completion
• Table user_impression
• Table user_order
• Table user_order_attribute
• Table user_search
• Table user_user_visit_lookup
• Table user_visit
• Table user_visit_detail

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

• PRIMARY KEY campaign_pkey ON campaign USING btree (id)

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

Back to top

In a hierarchical catalog, items belong to a category. This table stores the category IDs and names. In Personalization, there’s 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. You can use the parent ID in cases where the hierarchy maybe unavailable, but the short, non-hierarchical ID remains consistent across channels and systems that reference categories.

• PRIMARY KEY category_pkey ON category USING btree (id)

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

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.

• PRIMARY KEY country_pkey ON country USING btree (id)

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.

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

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

Back to top

Campaigns contain experiences. You can create different personalization results within the same campaign using experiences. Suppose you want to create a campaign for first-time visitors to your site. However, you want to show a different message to visitors from Boston and San Francisco where you have upcoming events. In this scenario, you would create 3 experiences in the same campaign: one campaign each for visitors from Boston and San Francisco, and one for everyone else. You can then use rules to restrict each experience's visibility to its respective target group.

• PRIMARY KEY experience_pkey ON experience USING btree (id)

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

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.

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

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.

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

Back to top

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

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

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.

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

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

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

• PRIMARY KEY product_pkey ON product USING btree (id)

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

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

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

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

Back to top

A 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 one time 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.

• PRIMARY KEY segment_pkey ON segment USING btree (id)

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

• 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

• 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

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

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

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

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

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

Back to top

This table provides summary information about a user or campaign. It mirrors the current state of an operational table for real-time reporting or other application. Although this table is less useful in a warehouse context, it does provide the state at batch time. You can use this table to quickly confirm membership or perform other on-demand tasks that can later be migrated to fully fleshed-out queries.

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

• 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

• 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

• 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

• 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

• 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

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

Back to top

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

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

• 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

• 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

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

• 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

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

• 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

• 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 and tokenization is performed)

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

• 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

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

• 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

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

Back to top

• Salesforce Help: Engagement Score
• External Link: IBAN Alpha-2 and Alpha-3 Country Codes