Dynamics Community Forum Thread Details

Customer Segments based on Aggregated vale (Sum) of Related Data

Hi all,

We have the following scenario:

The Contacts entity has a 1:N relationship with Orders.

Each Order has a "Value" field (currency amount).

Question:

In Customer Insights - Journeys (Real-Time Journeys), is it possible to create a segment of Contacts where the total aggregated value of all their orders exceeds a set amount?

Example: Filter for Contacts where the sum of all Order Values is greater than $1,000.

Key Considerations:

Does the segment builder in Journeys allow us to aggregate data (e.g., sum the "Value" field across all related orders for a contact)?

If not, is there an OOB way to pre-aggregate the data in Dynamics 365 CRM or Customer Insights - Data and then use it in Journeys?

Thoughts So Far:

Option 1: Add a rollup field on the Contacts entity in CRM to sum the total Order Value, sync it to Customer Insights, and filter based on it in Journeys

From what I’ve seen, Journeys doesn’t natively aggregate related entity data in the segment builder—am I missing something?

I came across these posts but they are related to Outbound Marketing

https://community.dynamics.com/forums/thread/details/?threadid=6bf3adf1-1254-49ec-8ecb-b181b095032e
https://learn.microsoft.com/en-us/dynamics365/customer-insights/journeys/developer/segment-query-definition

Looking for Insights:
Has anyone tackled this before? Can this be done fully OOB, or would custom work be needed? Any best practices or recommendations would be greatly appreciated!

Thanks in advance,

Abhishek C

Categories:

Dynamics 365 Customer Insights - Journeys

Hello Abhishek,

You've accurately pinpointed the core challenge: Customer Insights - Journeys (Real-Time Marketing) does not offer native, real-time aggregation of related entity data directly within its segment builder.

Let's break down your scenario and explore the most effective solutions:

Understanding the Constraints:

Journeys Segment Builder Limitations:
- The segment builder in Real-Time Journeys is designed for direct filtering on the Contact entity and its immediate related attributes.
- It lacks the ability to perform complex aggregations (SUM, AVG, COUNT) across related entities like Orders.

Outbound Marketing vs. Real-Time Marketing:
- As you've noted, the Outbound Marketing segment builder has more advanced querying capabilities, but these don't directly translate to Real-Time Journeys.

Recommended Solution: Rollup Field on Contact Entity

Your "Option 1" is indeed the most practical and recommended approach for achieving your goal:

Create a Rollup Field on the Contact Entity (Dynamics 365 CRM):
- Create a new currency field on the Contact entity (e.g., "Total Order Value").
- Configure this field as a rollup field.
- Set the rollup to calculate the SUM of the "Value" field from related Order records (1:N relationship).
- This field will automatically calculate and update the total order value for each contact.

Ensure Data Sync to Customer Insights - Data:
- Verify that the "Total Order Value" rollup field is included in the data that is synchronized from Dynamics 365 CRM to Customer Insights - Data.
- Verify that the Contact entity is being used in the Customer Insights - Data.

Use the Rollup Field in Journeys Segment Builder:
- In the Real-Time Journeys segment builder, you can now filter Contacts based on the "Total Order Value" rollup field.
- Create a segment with the condition: "Total Order Value" > $1,000.

Advantages of This Approach:

OOB Functionality: Rollup fields are a standard Dynamics 365 CRM feature.
Performance: Rollup fields are calculated asynchronously, minimizing impact on real-time operations.
Simplicity: The segment builder in Journeys can easily filter on a single, pre-aggregated field.
Accuracy: Roll up fields are very accurate.

Additional Considerations:

Rollup Field Updates: Rollup fields are not updated in real-time. They are calculated periodically (e.g., every hour). You can manually trigger a recalculation if needed.
Data Latency: There might be a slight delay between changes in Order values and updates to the rollup field.
Customer Insights - Data: Ensure that the data refresh from Dynamics 365 to Customer Insights - Data is frequent enough to meet your requirements.

Why Not Other Options?

Custom Code (Plugins/Workflows): While possible, custom code adds complexity and maintenance overhead. Rollup fields are a more sustainable OOB solution.
Power Automate: While Power Automate could be used to calculate the sums, it would have to be triggered on every order change, and that could cause performance issues, and it would then have to update the contact record. Roll up fields are much better for this scenario.

In summary:

Using a rollup field on the Contact entity is the most efficient and recommended approach for aggregating order values and using them in Real-Time Journeys segments. It leverages OOB functionality and minimizes complexity.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak

Quick Links