Why should a company choose customer insights data? This is a question I often get, especially when talking to people in the analytics department or with that type of background. “We already have customer analytical cubes in our warehouse/lakehouse, why build it again?”. With this article I will try to shed some light on this subject. I short, it is about making the business more versatile, faster to react and enabling use of large amounts of data in a way that is hard otherwise. It also enables advanced AI features that otherwise would take a lot of time to develop and the UI enables people in the business to experiment and try out things to see what works.
Customer Insights Data (CI-D) is Microsofts Customer Data Platform (CDP). CDP:s are a family of software that aggregate and measure customer data. It is in essence a datalake with some spark engines and a UI to control everything. The important thing to understand is that it is meant, not for DBA:s but for people working with customer data in the business. Some people with deeper skills in how to get data into CI-D are needed but once the data is there, measures, segments etc are meant to be created by “business” people.
Why not just use a data warehouse to solve this? Well, technically, the difference isn’t that large if you look at it from a strictly semantic datamodel perspective. However, the difference lies in who is supposed to be using it. Let’s take the scenario when you are in a B2C business trying to sell computers to customers. If the business wants to have a new measure called “Rate of gaming” where the products on the orderline are categorized as “gaming” or “business”. By calculating the number of order lines that are flagged as gaming compared to the total number of orderlines, for each customer, we will get a “rate of gaming”, a % that tells how many orders are gaming related. This can then be used in email marketing to send newsletters with more gaming oriented content.
With a classical data warehouse setup, the marketing department would order the measure to be calculated by registering at ticket in Jira/Azure Devops or similar. It would go into the backlog and then a few sprints (several months) later, it would be delivered. However, if the marketing department now realized that they forgot to limit this to just the orders placed in the last 24 months, then they will have to register a new ticket and wait several more sprints.
Using CI-D they could create the measures themselves (you have to create 3 to solve this), and then, after the next system synchronization, the measures would be there and working, typically around 24h or less, depending on how fast and often system refreshes are done. If they needed to change anything, like changing the filter, it can easily be done and after the next refresh, it would be live.
This is important for many reasons, the most important being that the marketing departments have to change with the world, and sometimes the world changes very fast. In those cases it can be very important to be able to act fast, to retain customers or to be able to exploit a marketing window that has appeared. Enabling this capability is key in the world of today
Why not use just dataverse? Why not just mirror all the data into dataverse and create a few rollups in dataverse? This is technically possible, not really a good idea for the following reasons;
Dataverse capacity is rather expensive. It is an operational database and is supposed to store data that is top be used operationally, not for analytical purposes like a CDP uses data. (with operationally I mean that you work with the data, like working a case in customer service to close it or working with an opportunity to close that)
Dataverse isn’t built to handle really large amounts of data while CI-D is as it is based on Delta lake data storage with Spark engines to do the heavy lifting.
Dataverse doesn’t really have a good way of doing measures. Yes, you can use rollup fields but these are very limited in functionality and you are limited to a few.
Experimentation is also a key way of working for a well functioning marketing department. As someone who like the scientific method being a MSc EE, I like this data driven approach of making hypothesis and trying them out to see what actually works and not. This does, however, require systems that enable the marketing department to experiment and measure outcomes. With experimentation times being weeks with traditional data warehousing and 24 h with CI-D, having CI-D is a true enabler to make a well functioning marketing department that can experiment as they see fit.
Machine learning and using AI in general is also something that is key today. For example CI-D has built in functionality to find similar customers to specific segments. If you start with a segment of all customers who bough gaming products in a computer sales, then, finding other customers that are similar from a data perspectiv who might also be interested in gaming products, can be a very valuable segment to approach with personalized offers.
Exporting selected segments to external systems for activation in these, is also a key functionality in CI-D. This unlocks some of the most low-hanging fruits like not marketing to some groups of people as they would never buy a particular product. With ad banners costing both actual money but also trust from customers when they are irrelevant, this is easy money to save. For instance, not marketing a gaming PC to a customer that just bought a gaming PC despite Google seeing that they visited that gaming PC:s product page.
Using measures and insights from CI-D outside of marketing can also be very powerful. This can be in scenarios like customer service when a customer calls and the customer service rep immediatly sees the total purchasing amount for business IT, consumer IT and gaming IT as well as the time between PC purchases, age of current PC and more. This can help the customer service rep not only be a better help to the customer but also be more relevant in recommendations.
There are many scenarios where CI-D give true value, but it is a bit more of a strategic product than CI-J which is a bit more tactical in giving faster wins. However, the long term wins of using a competent CDP like Dynamics 365 CI-D can be very large and I hope I have made the case for that clear.
There are scenarios in CI-D when it is integrated with Dataverse where you may run into a bug with the platform. This is when one of the COLA (Contact, Opportunity, Lead or Account) table records has the lookup Customer Profile pointing at a customer profile that isn’t there any more. In other words an orphan link, something that typically should not exists. I will start by describing a scenario where this can occur, what problems it can cause and how to fix it, both short term by you and long term by Microsoft.
When does it occur?
This problem typically occurs if you have chosen to have more contacts in dataverse than you have in CI-D. The most common reason for this is that each 100k of unified profiles in CI-D incur an non-trivial licensing cost per month. Hence skipping the least valuable customers could be a way to reduce this cost. The problem, on a more technical note, is of course that you will have to filter the customers before they are ingested into CI-D. The new filtering functionality in CI-D that is about to be released will probably help with this.
So, let’s say for the sake of an example that we are only keeping customers in CI-D that placed an order with us during the last 24 months. This means that if you have a customer that yesterday was included, it could pass the threshold and be excluded today. Hence it was synchronized to dataverse yesterday with the rehydration functionality and the corresponding contact record was linked to the customerprofileid with the COLA Backstamping functionality. However, today, when the synchronization happened, this contact is no longer part of the unified profiles and hence is not synchronized to dataverse. However, the COLA backstamping functionality does not clean up after itself properly and hence the customerprofileid lookup on the contact record that was set yesterday, is still set BUT as there is no customer profile that corresponds to this anymore, there is an orphan link.
What problems can it cause?
Let’s say you have an integration that might not as specific in its data processing as is best, and hence it reads all fields from dataverse, changes some and then writes all fields back, instead of just the ones that were changed. If this happens to a record with an orphan lookup link then when you try to save the contact to dataverse, the platform will throw an exception saying that the customerprofileid is incorrect/not pointing at a real record.
How to fix it yourself
The easiest fix you can do is probably to write a Power Automate flow that loops through all contact records and verifies that the customerprofileid is set to something that actually exists.
Another fix is to rebuild the integration so that it only changes fields it actually has a change for. This, does require some extra coding, but that code could be rather generic so that you can use it more. This change will also make your code execute faster. It does, however, not actually fix the problem, just removes the symptom.
For field and table reference, below is a FetchXml that will return the contacts that have the lookup field set and one for all customer profiles. I have tried combining these in an outer left join to get the ones that are incorrectly linked, but there is a limitation in FetchXml that is stopping this.
My suggestions to Microsoft on how they should fix it
There are two different ways to solve this from a Microsoft side, one is on the CI-D integration side and the other on the dataverse elastic table side.
First of all, I think this is an incorrect implementation by the CI-D team. Elastic tables currently do not respect cascade rules like “remove link” and this should be a known fact. Hence they should implement a function that after the backstamping is done for all existing records, it should clean up any customerprofileid:s that do not have a corresponding record.
The second alternative solution is for the dataverse team to implement functionality to respect some of the cascade rules, in essence making sure the dataverse platform cleans up any links to records that do not exist. The positive side of this would be that this fix would help any lookup pointing to an elastic table, as this is most certainly not limited to just the CI-D integration, but the negative side is that this would affect the performance of elastic tables, especially during deletes.
When working in CI-D and replacing a datasource with another, for example replacing Power Query Datasource with Delta, then finding any dependencies for this is important. This is a tip that I got from Microsoft Fast Track Architect Ashwini Puranik, so credit should go to her. You can use the API testing functionality that is part of CI-D to query the ListAllMeasuresMetadata. Keep on reading to get some more details.
As the general recommendation for CI-D is to move to Delta-based data sources from Power Query due to performance. However, doing this shift will require you to reroute all dependencies to the new table before you can remove the data source.
Below is a way to find the dependencies based on Measures and Segments defined in CI-D.
Start by going into the API testing tool that is available for CI-D using the Permissions-area and the tab “API”.
Choose the only available API, Customer insights – v1.
Now search for ListAll and select “ListAllMeasuresMetadata”.
This will show a more detailed description of the endpoint. Now click “Try it!” in the green button on the right hand side.
This will show a panel on the right hand side where you have to input some required data. For an easy test, just select “implicit” in the Authroization drop down and put the instanceid into the field for it. In case you don’t know where to find the instanceid, you can grab it from the URL when you are using CI-D using the normal UI as it is the only guid in the URL.
That is all that is required, so scroll down to the bottom and press “Send”
You will now see a long text (json) which is the response from the api call. You can use the built in search in the browser to find all occurances of a datasource. You can use either the Display name or the schema name as both are in the JSON. As you can see below, the Measure “Total unique clicks last month” is one of the measures that has a dependency on the Datasource named “FreshRelevanceAZblob_FreshrelevanceAZBlobInbox”.
Now there are a few options, either remove the measure. This is a good option if you want to fix this quickly or the measure isn’t being used, if it is used there can on the other hand be many dependencies on the measure making it a lot more complex to remove. The other option is hence to just change all dependencies in it to another (Delta) datasource. You will then have to refresh the data source to remove the dependency fully. You can rerun the query by resending it using the button at the very end.
Once you have removed all dependencies on the data source from measures, there might still be dependencies from segments. You can use a similar method as above, but the endpoint you want to use in this case is “ListAllSegments”.
You can still have dependencies left on the datasource, for instance from things externally using the table from the CI-D API. This doesn’t actually block you from removing the data source but whatever you are doing externally will stop working once you have removed the data source. Naturally.
I hope this has helped and thanks a lot to Microsoft Fast Track Architect Ashwini Puranik for pointing me in this direction.
When measures are created in CI-D to be used in CI-J there are dependencies created which will stop you from removing the measure. CI-D is not very helpful in telling you how to remove it. I will try to be a bit more helpful.
When trying to remove the Measure this is the error I got:
Error: Detected DataVerse dependencies in Measure: ListMemberships. Please delete these dependencies and merge again.
Request ID: 14056846-b9c1-4ec8-98c2-88778e518b88
Time: 2024-12-05, 11:12:10
My investigation below with the help of Microsoft Support lead me to the understanding according to the image above. As you can see dependency is actually sort of circular, or at least one part of the dependency (dependent component) is at the entityanalytics on the record which points to msdynci_listmemberships, on the other hand (required component) on the table msdynci_listmemberships.
The fix, is to remove the record in entityanalytics. Easiest way in this case, is to just use a browser console where you are logged into the right Dynamics.
To remove the entityanalytics record that is blocking this:
After this I was able to remove the measure in CI-D.
Below I will detail the steps I did to understand this.
The first step is to look in the table “entityanalyticsconfig” searching for the name of the Measure. Its name is “msdynci_” + name of the measure. Check out the metadata if you are unsure.
Hence, from this I generated the image above. Since it is something of a circular reference, Microsoft Support suggested that I remove the entity analytics record with id: e46e52db-46a7-4585-8a6a-6ba888a5bd1f. However, not entirely sure what this is and hence I tried the one I had gotten in the first steps above, and that worked.
Generally I think it is a good idea to investigate what the dependencies point to, before removing something. In this case the dependency record in itself wasn’t removed, only the dependent part.
Thanks to Microsoft support for helping out with this and I hope it might shed some insights if you are having similar issues. I also think breaking down the dependency table in a tool in for instance XrmToolBox would be a great idea. I have a bit of a bad conscience for BulkDeleteManager that I own and I am not giving enough love, so feel free to build it based on this and I guess some more stuff. I will be happy to help out the the investigations if you are willing to build it.
Recent Comments