Data Warehouse

The Personalization data warehouse makes it possible for business analysts to connect BI tools to Personalization, and for data scientists to analyze and enhance Personalization data as well as leverage the power of machine learning.

This article discusses the following topics related to the Personalization data warehouse:

  • Physical Data Model Entity Relationship Diagram (ERD)
  • Data Warehouse Dimensions
  • Data Warehouse Activity (Fact) Tables

The following ERD shows the relationships between the Dimension and Activity (Fact) tables of the Personalization data warehouse. The Data Dictionary article provides the complete data specifications on these tables in alphabetical order.

ERD Details

Because of the size of the full Data Warehouse model, "ERD Detail" sections are provided at the end of each of the dimension and activity table sections for clarity. These sections present just the portion of the Data Warehouse ERD discussed. These "detail" sections show the following information for the tables in the ERD:

  • Table name
  • Field names
  • Field data types (d=date, #=number, t=text/string, b=boolean, c=code)
  • Primary Keys (indicated by key icon)
  • Foreign Keys (indicated by "up-and-out" arrow)
  • Fields used as Foreign Key by other tables (indicated by "down-and-in" arrow)

ER Diagram

The dimension tables in the default Personalization data warehouse provide the context for data analysis in the following dimensions:

  • User
  • Product
  • Campaign
  • Location
  • User Segment
  • Customer Survey

Tables by Dimension

The following sections present the data warehouse tables used to store data for each dimension.

User

The dimension tables that store data associated with the User dimension include the following:

  • user
  • user_attribute
  • user_alias

Product

The dimension tables that store data associated with the Product dimension include the following:

  • product
  • product_category
  • category
  • product_attribute
  • product_dimension
  • dimension

Campaign

The dimension tables that store data associated with the Campaign dimension include the following:

  • campaign
  • experience

Location

The dimension tables that store data associated with the Location (affiliate) dimension include the following:

  • location
  • country

User Segment

The segment dimension table stores data associated with the specific segment for which a user has qualified.

Customer Survey

The survey_question dimension table stores data associated with the customer survey questions that exist in the current system.

ERD Detail - Dimensions

The following sections present the tables in the Dimension side of the data warehouse ERD broken into top and bottom sections.

ERD Detail - Dimension Top

Dimension Top

ERD Detail - Dimension Bottom

Dimension Bottom

The following sections list the activity (fact) tables in the default Personalization data warehouse that collect and store the values used with dimension tables analysis. The tables are presented in the order in which they appear in the Activity side of the ERD shown in this article (left-to-right, top-to-bottom). The ERD Detail section at the end of each of the following subsections presents the ERD sections with the table details corresponding to those listed in that subsection.

Activity Tables - ERD Top

The activity tables in the top portion of the ERD Activity side include:

  • user_visit_detail
  • user_visit_detail_utm_parameters
  • path (affiliate)
  • user_search
  • user_send
  • user_send_activity

ERD Detail - Activity Top

Activity Top

Activity Tables - ERD Middle-Left

The activity tables in the middle-left portion of the ERD Activity side include:

  • segment_membership
  • user_campaign_state
  • user_click
  • user_order_attribute
  • order_line_item_attribute
  • user_order
  • user_user_visit_lookup
  • user_impression

ERD Detail - Activity Middle-Left

Activity Middle-Left

Activity Tables - ERD Middle-Right

The activity tables in the middle-right portion of the ERD Activity side include:

  • user_daily_stat_item_stat_promotion
  • user_goal_completion

ERD Detail - Activity Middle-Right

Activity Middle-Right

Activity Tables - ERD Bottom-Left

The activity tables in the bottom-left portion of the ERD Activity side include:

  • order_line_item
  • goal_completion_attribution
  • user_visit
  • user_daily_stat_item_stat_category
  • user_daily_stat_item_stat_product
  • goal_completion_line_item
  • user_dismissal
  • goal_completion_attribution_promoted_item
  • survey_response

ERD Detail - Activity Bottom-Left

Activity Bottom-Left

Activity Tables - ERD Bottom-Right

The activity tables in the bottom-right portion of the ERD Activity side include:

  • user_daily_stat_item_stat_product
  • user_daily_stat
  • user_daily_stat_action
  • user_engagement_history
  • user_daily_stat_item_stat_dimension

ERD Detail - Activity Bottom-Right

Activity Bottom-Left