Create power bi report with dynamic CRM marketing insights data

Introduction

In this blog post, we will see the power bi report creation for Dynamic CRM marketing module, and we will show the insights data.

Background

The insights data of marketing module belongs areas shown in below screens:

Insights from marketing email:

Insights from Customer journey :

This data is not stored directly in dynamic CRM tables such as account, contact data stored in dynamic CRM tables. To access this data we need to create a Azure blob storage and after this we need to create a record in dynamic CRM marketing table as described:

Creating of azure blob storage and connection with Microsoft dynamic CRM

1. Go to https://portal.azure.com

2. Search for the storage account as shown below:

3. After selection of storage account click the container present in left side as shown below:

4.Click container and fill in the required details. After this click the create button. It will create container. This container we will use to store the dynamic CRM marketing data.

5. Now download storage explorer provide in below link:

https://azure.microsoft.com/en-in/products/storage/storage-explorer

6. After installation connect with portal container using azure key as shown below:

After select below option:

After clicking next new browser tab will appear. Add credentials and after authentication you will able to see the storage account.

7. Now, select the container and click the below option:

After this copy URL portion and now we will move toward dynamic CRM.

8. Now, we will go to Microsoft dynamic CRM marketing module. Select the setting for change area as shown below:

9. Inside setting, select the analytics configuration and create a record.

The copied URL we need to paste in below highlighted area:

After save the URL will be cleared due to security reason and status will appear as running as shown below:

Now we have done all the setup, and we start will the power BI now.

Creation of Power BI Report:

1.Go to power bi desktop and click the Get data and search for Azure as shown below:

2. Now click connect and the screen below will appear. Here you need to past the URL found in dynamic CRM record creation (i.e. Analytics configuration entity record).

URL From CRM:

3. After clicking on “OK” below screen will appear:

The above screen contains the binary data stored in azure blob storage and these binary files contains details about various insights of Dynamic CRM customer journeys.

Now, we will click transform data and below screen will appears:

Here, we need to select the required binary file. For e.g.  I am selecting “csv/ActivityContactBlocked/0-ffffffffffffffff” binary series.

As you can see we have various binary file with name as “csv/ActivityContactBlocked/XXX-ffffffffffffffff”. Now we need to club this binary files as described below:

1. click on below highlight Icon and add filter as shown below:

2. After filtering, click on below highlighted button:

3. After clicking below screen will appear:

After loading click on OK button and it will merge the binary file into one main file. Now we can you this data set in power BI report.

In next blog, we will combine the data from CRM and Azure blob to have complete report.