How to Ingest Data With Dynamics 365 Customer Insights

In this article, Caroline Mayou, Dynamics 365 Customer Engagement consultant, explains how to ingest data with Dynamics 365 Customer Isights.

Caroline Mayou

Caroline Mayou

Introduction

Modern businesses today struggle with painting a holistic image of their customer. This is because the modern customer exists across so many different platforms, making their journey difficult to contain and measure. Because of this, customer data becomes scattered across many data silos. For example, a customer’s journey could start with them engaging on a business’s social post. The customer could then follow the business on the social platform, leading them to learn about an upcoming event. Perhaps the customer then attends this event and is made aware of a new upcoming product release. Once the product is released, the customer receives an email notification about the product, prompting him or her to purchase it. As you can see, customer journeys can be quite complex and can live across several platforms- each with their own data sets. The question thus becomes- how can a business consolidate these data sets and have a holistic view of their customers?  

Dynamics 365 Customer Insights is a Customer Data Platform (CDP) that allows organizations to centralize customer data stemming from multiple platforms to gain deeper insights. Dynamics 365 Customer Insights, like most CDPs, is only as strong as the data fed into it. In other words, without the ability to connect to all of the platforms a customer could live on, the application is obsolete. This is where Audience Insights and the process of data ingestion comes to play. Within D365 Customer Insights, Audience Insights ingests data from disparate data sources and unifies that data into a single customer profile. It’s from within this customer profile that one can track different markets and identify trends based on the data.  In this article, I will be discussing the different types of data that Dynamics 365 Customer Insights is capable of ingesting, how to connect to it, and how to work the data sets.

Which Data Sources Can Dynamics 365 Customer Insights Connect to?

Gaining a holistic view of your customers starts by connecting the data that you need to work with across your business’s different data silos. These data sources vary by business model and industry but could include:

  • Behavioral sources such as customer service, sales, marketing applications
  • Observational data from a product testing application
  • Transactional data from point-of-sales (POS) systems
  • Any data source where customer-related data is stored

Understanding Extract, Transform and Load (TL)

Before Audience Insights can interpret data, it needs to be able to organize it. For this to happen, the data must be collected and refined into something that can be consumed by Dynamics 365 Customer Insights. The process of extracting data and transforming it is called Extract, Transform and Load- or ETL. The three words in the name ETL stand for the three steps that make up the ETL process and enable data to be integrated from its source to its destination Customer Data Platform (CDP).

 

The ETL process includes the following steps:

  1. Data Extraction: First, the raw data is extracted from a data source. This could be an on-premise system, a cloud environment, a business application such as CRM or ERP, a data lake/warehouse, etc.
  2. Data Transformation: The transformation process is what improves the quality of the data. Data transformation could include data cleansing, sorting, etc.
  3. Data Loading: Lastly, once the data has been extracted and cleansed, it can be loaded into the Customer Data Platform (CDP)

How Data is Stored in Dynamics 365 Insights

When data is brought into Dynamics 365 Insights, it is ingested into Audience Insights and stored in a data set. You can think of a data set as a table that contains the data you want to use. It could be contact specific data stemming from a CRM application like Dynamics 365 Sales or Customer Service, or it could be transactions coming from a Point of Sales (PoS) system. A data set is stored by data source- essentially a unit that organizes data sets and make them easier to group or search. For example, you could define a data source called “CRM” to ingest data from a Customer Relationship Management application, such as Dynamics 365 Sales. Or, you could title a data source ‘eCommerce’ to ingest data from an eCommerce application. Following the CRM example, a sales application likely contains both customer profile (contact information) and customer purchase data (orders). A data set would thus exist for each of those components in the data source.

Dynamics 365 Customer Insights provides three methods for ingesting data in Audience Insights:

  1. Import: You can use this method if you’re looking to connect to data in Dataverse, Azure Blob Storage, OData Sources, etc.
  2. Connect to a Common Data Model Folder: You can use this method when you’re looking to connect a Azure Data Lake Storage Gen 2 Account
  3. Connect to Dataverse: You can use this method when you’re looking to connect to data sets in the Dataverse data lake.

The method you will use for collecting data depends on several factors, including the volume of data being ingested, the amount of data transformation required, and of course, the data source. In the below paragraphs, I will tackle each method in detail and discuss the scenarios and constraints around them.

Using Power Query to Import Data Sources

In a scenario where you have a lot of data transformation that needs to take place, or you are connecting to a non-Common Data Model or Dataverse source, Microsoft recommends using the import method to ingest data. The import method uses Power Query- Microsoft’s Data Connectivity technology that is used to access data from hundreds of data sources. You can think of Power Query like a plugin library, giving you access to 40 of the most common platforms like Excel, Salesforce, Oracle, OData, Azure, and more. Additionally, Power Query acts as a data transformation and data preparation engine, allowing you to perform the ETL processing of data.

A tool like Power Query is incredibly beneficial for organizations looking to connect to and reshape data. In fact, a recent survey exposed that business users spend up to 80 percent of their time on data preparation, which in turn, delays decision-making.

Import Methods in Dynamics 365 Customer Insights

Once data is ingested using the import option, it’s copied into the Audience Insights data lake. For this reason, if you have a large volume of data, you may need to consider extra storage. It’s important to note, that though Power Query offers many different connectors, not all connectors allow you to bring data into Audience Insights.

Below is a table showing which connectors are enabled for Dynamics 365 Customer Insights as dataflows:

Connector

Dynamics 365 Customer Insights Enabled

Access Database

Enabled

Adobe Analytics

Enabled

Amazon Redshift

Enabled

Azure Analysis Services Database

Enabled

Azure Blob Storage

Enabled

Azure Data Explorer (Beta)

Enabled

Azure Data Lake Storage Gen2

Enabled

Azure HDInsight Spark

Enabled

Azure Synapse Analytics (SQL DW)

Enabled

Azure SWL database

Enabled

Azure Table Storage

Enabled

Common Data Service (legacy)

Enabled

Dataflows

Enabled

Dataverse

Enabled

Excel

Enabled

FHIR

Enabled

Folder

Enabled

Google Analytics

Enabled

Google BigQuery

Enabled

IBM DB2 Database

Enabled

Impala

Enabled

JSON

Enabled

Microsoft Exchange

Enabled

MySQL Database

Enabled

OData Feed

Enabled

ODBC

Enabled

OpenSearch Project (Beta)

Enabled

Oracle Database

Enabled

Parquet

Enabled

PDF

Enabled

PostgreSQL Database

Enabled

Salesforce Objects

Enabled

Salesforce Reports

Enabled

SAP Business Warehouse Application Server

Enabled

SAP Business Warehouse Message Server

Enabled

SAP HANA Database

Enabled

SharePoint folder

Enabled

SharePoint list

Enabled

SharePoint Online list

Enabled

Snowflake

Enabled

Spark

Enabled

SQL Server Analysis Services databases

Enabled

SQL Server database

Enabled

Teradata Datbase

Enabled

Text/CSV

Enabled

Web

Enabled

XML

Enabled

The Importance of Data Transformation

Data transformation changes data to the appropriate format so it can be used within Audience Insights. Typically, the data needs to be transformed to ensure it can be used by Dynamics 365 Customer Insights features such as activities and measures. Activities, for example, lean heavily on dates, so the data set must have at least one date field in it. A numerical field may be used in calculations or as measures like calculating total cost of a single purchase or purchase over a timeframe.

When using the import method, you can modify configuration settings like the delimeter used. The preview area in Power Query allows you to modify these settings before the transformation process.

Below are best-practice transformation to apply prior to import:

  • Headers as first row: When importing from a CSV file, you can use the first row of the file as the header information. When selecting Transform Table, you’ll see the option to switch between leveraging the first row as a header or using it as data.
  • Map to standard data format: Within Audience Insights, you can map your data to the Common Data Model. This eases data interoperability and can help you with integrations to other databases down the road. To map to a standard data format, you’ll go to Map to Standard and then map fields from your selected data source to Common Data Model fields.
  • Modify Field Data Types: A datatype should be associated to each field within a dataset. The datatype associated with a field impacts how the field can be used and tracked within Audience Insights. This means that failing to define the data type will prevent you from creating activities or measures. Especially in the case of connecting to a text-based data source, it’s important to ensure that you have reviewed each field and set all data types accordingly.
Data Types in Dynamics 365 Customer Insights

Connecting to a Common Data Model from Audience Insights

Another option for ingesting data is to connect to a Common Data Model. Unlike the import feature with Power Query, common data models are used to support both large and small data volumes and can be used in situations where no ETL is required.

Unlike the import function, connecting to a Common Data Model folder does not copy the data directly into Audience Insights. It’s also important to note that when using Azure Data Lake, the data lake you connect to must be in the same Azure region as the Dynamics 365 Customer Insights environment.

In order to connect to a Common Data Model, you’ll need the Account Name, the Access Key and the Container information. Once you’ve selected the model.json that you want to import data from, you’ll see a list of available entities in the model.json file. You can then select which entities you want to ingest from the data source.

Connecting Audience Insights to Dataverse

In some instances, businesses may be live on Microsoft Dynamics 365 Customer Engagement applications such as Dynamics 365 Sales, Field Service, Customer Service and/or marketing. In this scenario, the business would have existing contact data and activity-related data already sitting in their Dataverse instance. This information can be ingested into Audience Insights using the Common Data Service connector. It’s also important to note that you may want to bring in more than simple customer profile data- for example, you could bring in opportunity or case information based activities.

The Dataverse data source is suitable for small and large data volumes. However, it’s important to handle the ETL functions outside of Audience Insights in this case. Microsoft recommends leveraging tools like Azure Data Factory or Databricks to go through the data cleansing process.

Just like the Common Data Model folder connection, the Dataverse connector doesn’t copy the data into Audience Insights. Upon connecting, the Dataverse connector will prompt you for the server address to your Dataverse organization (environment URL). It’s also important to note that in order to create the connection, you’ll need to have System Administrator privileges.

Once authenticated into Dataverse, the system allows you to select the tables you want to ingest. If certain tables are already selected (Accounts or Contacts, for example), you may be using Dynamics 365 Sales Insights which is prompting that connection and you will not be able to modify the selection based on dependencies.
Once the data source is saved with the appropriate tables, the selected tables will begin syncing into the Dataverse lake. Please note that only a single data source of an instance can use the same Dataverse managed lake.

Refreshing Data Sources in Dynamics 365 Customer Insights

Once your data sources are live, you can manage the data from the Entities tab. However, no matter the data source, it’s important to note that the data will not be refreshed automatically. To gain accurate insights, you will want to ensure that new data from each data source is made available in Audience Insights. To do so, you can pursue one of the following options: 

  • Scheduled Data Refresh: This option will refresh all of the connected data sources on a schedule that you predefine
  • Incremental Refresh: This option refreshes small subsets of data for specific data sources based on incremental refresh settings you configure
  • Near Real-time Data Ingestion: This option will ingest all customer profile and activity-based data in near real-time.

Are these data refresh options also available to the data source that was ingested using the import method?

Manually refreshing data is not an ideal scenario for businesses- especially in a modern world where automation is available. The simplest way to automate the refreshing of data in Audience Insights is to schedule data refreshes. Audience Insights allow you to modify the schedule with option ranging from daily to weekly refreshes. Upon refresh, all configured items like segments, measures, enrichment, data prep, insights and activities will also be refreshed.

Conclusion

Audience Insights, the data hub of Dynamics 365 Customer Insights, ingests transactional, behavioral, and observational data to provide deep insights and a holistic view of your customers. In order to set up these insights, businesses must first configure the different data sources, ensure that the ETL process has been handled, and then map the different data types. Once live, the data can be refreshed through a multitude of options.

Complimentary Consultation

Learn more about the benefits of Microsoft for your business

Start Conversation

Book a free meeting and let us have a look at your opportunities with Microsoft Solutions

Team working on presentation

Request a free Dynamics 365 demo

Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.