Data Warehouse

The Marketing Cloud Personalization Data Warehouse enables business analysts to connect BI tools to Personalization, and data scientists to analyze and enhance Personalization data as well as use the power of machine learning.

The Data Warehouse is available only to customers who have already purchased the Marketing Cloud Personalization - Data Warehouse add-on product.

This article discusses the following topics related to the Personalization Data Warehouse:

  • What data is accessible?
  • Physical Data Model Entity Relationship Diagram (ERD)
  • Data Warehouse Dimensions
  • Data Warehouse Activity (Fact) Tables

For every prospect, customer, and account who engages with your organization, Personalization maintains a unified profile that stores engagement details and a contextual understanding of the individual’s behavior. Profiles also include attribute data ingested from external sources or captured with each web visit. This attribute data includes things like referring sources, geolocation, and browser.

Personalization tracks detailed engagement statistics at the product level, as well as transactional data such as registrations, downloads, purchases, adds-to-cart. Using the Data Warehouse, business analysts can access this information to identify trends and patterns related to their product or content catalogs.

The Personalization Data Warehouse currently doesn’t support data related to User Owned Objects and Article, Blog, and Promotion catalog objects.

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.

The _dw_created_ts field present in all Personalization Data Warehouse tables is a system field for tracking when a record was created. We advise against building dependencies on it as its name or the logic behind it can change without notice.

Instead, we recommend using the created_ts or update_ts fields, or other fields documented in the Data Dictionary that are more suited to your use case.

Due to the size of the full data warehouse model, we've provided ERD Detail sections at the end of each dimension and activity table sections for clarity. These sections present just the portion of the data warehouse ERD discussed. These 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 (deprecated)
  • User Segment
  • Customer Survey (deprecated)

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

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

  • user
  • user_attribute
  • user_alias

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

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

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

  • campaign
  • experience

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

  • location (deprecated)
  • country (deprecated)

The location and country dimension tables are now deprecated and no longer in use.

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

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

The survey_question dimension table is now deprecated and no longer in use.

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

Dimension Top

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 subsection presents corresponding table details for the listed tables.

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

Activity Top

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

Activity Middle-Left

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

  • user_daily_stat_item_stat_promotion
  • user_goal_completion

Activity Middle-Right

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 (depracated)

The survey_response dimension table is now deprecated and no longer in use.

Activity Bottom-Left

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

Activity Bottom-Left