Skip to main content
Dynamics 365 Community / Blogs / Dynamics 365 FastTrack Blog / Unlocking the Power of D365...

Unlocking the Power of D365 Customer Insights: Best Practices for Data Modelling and Data Quality

Ashwini Puranik Profile Picture Ashwini Puranik

Overview

During the design phase of D365 Customer Insights implementation, one of the key aspects is data modelling to ensure that your data is ready to use within D365 Customer Insights and works well to yield the expected value. The other important aspect is to validate the quality of your source data and address the gaps upfront.
In this blog, we will cover the following topics:
  • Conceptual table/data categorization in D365 Customer Insights.
  • Data Modelling recommendations.
  • Data Quality recommendations.
 

Conceptual table/data categorization in D365 Customer Insights

Conceptually, you should be able to categorize all the tables you bring into D365 Customer Insights in one of the following categories:
  • Profile source – Table which is a source for customer profile creation and will result in ‘Unified Profiles’.
  • Activity source – Table that contains the ‘events’/’transactions’ data linked with the profile source.
  • Supporting – Table that doesn’t represent profile or events/transactions but are helpful in grouping (E.g. Membership Type, Product Group).
If your data source/s do not provide the data which could be categorized as above then you will need to model the data to fit these requirements. Please note: The method/tools used to implement this will depend on ETL tools’ availability and the ingestion pattern used in D365 Customer Insights which will not be covered in this blog.
 

Why is this required?

D365 Customer insights has the below high-level processes and the data used for these is expected to be fit for purpose:
  • Profile Unification – Profile Unification is the process where profile source/s are deduplicated, matched and merged based on the user defined rules and preferences, to create unique customer profiles.
The tables participating in the profile unification are expected to have only the data related to customer’s profile and should not contain transactional/event data.
If the source data is normalised, then it should be denormalized  to contain all profile related data in a single row per source.
 
  • Activity Unification – Activity Unification is the process where events/transactions associated with the profile sources, that matched as part of profile unification to create a unique customer profile, are unified (union’ed) together and associated with this unique Customer Insights created profile.
Hence the expectation for all events/transactions it to have a direct or indirect Foreign Key: Primary Key (FK:PK) relation to the profile source table. This relation will be defined in D365 Customer Insights by end users and will be leveraged by Activity Unification process.
The tables participating in this process should contain only the events/transactions data but not the profile information.
The table should have a stable Primary Key to uniquely identify a transaction and a non-null timestamp field to identify the time of event/transaction.
The data is expected to be denormalized  to represent an event/transaction per row. E.g.,depending on the use case, you might want to denormalise Orders and Order LineItems in a single event/transaction row.
 
  • Measures and Segments (on supporting tables) – Measures (that are created at D365 Customer Insights’ unique profile granularity - CustomerId granularity) and Segments can be created from tables that are not profile or activity sources but have a direct or indirect relationship to the unified profile. These tables usually contains ‘grouping’ information.
E.g.1 MembershipType – Silver, Gold, Platinum which can be a direct link with the customer profile.
E.g.2 ProductCategory – home appliances, clothing etc. which can have a direct link with the activity data and in turn indirect link to customer profile.
Such tables are categorized as ‘supporting’ tables.

 

Summary view of conceptual table/data categories and data pre-requisites

 
TypeProfileActivitiesSupporting
DescriptionTable that is a source for profile creation. This will result in ‘Unified Profiles’Table that contains the events/transactions linked with the profile sourceTable that doesn’t represent profile or events/transactions but is helpful in grouping the profiles
Relationship1:n (PK:FK) with ActivitiesDirect- n:1 (FK:PK) with Profile
OR
Indirect- n:1:1 relation via ‘intermediate’ entity to Profile entity
(Activity:IntermediateEntity:CustomerProfile)		Direct or Indirect relation with the ‘Unified Profile’ entity
Tables Must HavesPrimary KeyPrimary Key
Foreign Key linking to the corresponding profile entity (directly or indirectly)
Timestamp Field (no null values)		NA
NormalisationDenormalized to contain all profile related data in a single row per source
B2B: Hierarchy must be included in this source entity and should use the PK column as Source Id in hierarchy definition		Denormalized to contain a single row per source record representing an event/transaction. Example: Orders and Order LineItems to be denormalized into a single row (based on use case requirements)Denormalized
 

Visual representation of Table categories and Relationship



 

Data Modelling Recommendations

 
  • All Profile sources should be at the same granularity level: the one expected in the unified profile.
  • Give a business friendly name to attributes that have technical or non-business friendly names in your data source (e.g. attribute1 refers to Product Name then it model it as ‘ProductName’).
  • Bring only the attributes that can serve a customer data platform use case. (e.g. Ignore unused fields – empty, non-PII data for profile information, key columns linking to other tables).
  • Bring only the relevant events/transactions that will help to serve a customer data platform use case. Filter out the noise. (e.g. Web events capturing each action performed by end-user on the website and all of these might not be relevant for use cases in customer data platform).
  • The data input to D365 Customer Insights should be denormalized  and modelled to ensure that the criteria (as shown in ‘Conceptual table/data categorization’ section) for each table is met.
  • An example of denormalization:


 
  • If your source is denormalized  and contains Profile and Activity data in a single table then as part of data integration pipeline, you would need to split it into two entities : one for Profile and one for Activity with their respective proper granularity and a PK:FK link between the two.
  • For B2B, for the hierarchy to be useful in creating Measures, the hierarchy data has to be modelled into the Profile table by using the PK of the table as ‘Source Id’ while defining the hierarchy. D365 Customer Insights can use only one hierarchy while creating measures.
 

Data Quality Recommendations

  • Evaluate the data quality upfront and address potential gaps to avoid issues during the build phase. This is especially true when using non-relational datasets as input e.g. excel files.
  • Focus on identifying and correcting:
    • Null Primary Key.
    • Null or unmatching relationship between Activities and Profile entities.
    • Missing or null or default (e.g. 01/01/1900) timestamp in Activities source data.
    • If a single column is used in a Match rule then null values in this column.
    • The attributes used in defining relationship do not contain ‘null’ or blank values.

Comments