Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Suggested answer

Segments based on SUM and AVG of order total amount

(0) ShareShare
ReportReport
Posted on by 5

Hello!

I would like to create dynamic segments based on what customers spent in a timeframe that is going to be specified in the query. For example a segment that shows all contacts that have spent more than 500 € in online transactions in the last 6 months.

The information about the transactions comes from a connected ERP system and is shown in the order entity in CDS. I've added this entity to the marketing app and made it available for queries. The records contain a total amount and a submit date.

Simply aggregating the total value of the orders in an additional attribute on the contact can be done, but it's not sufficient for flexible timeframes that are defined in the segment query. Is there any way to achieve this?

Thank you for your help.

  • cloflyMao Profile Picture
    cloflyMao 25,202 on at
    RE: Segments based on SUM and AVG of order total amount

    Hi Mazee,

    Have you tried using relative date function when selecting date and time field?(Click the switch mode icon to switch to relative timeframe.)

    dd.JPG

    Regards,

    Clofly

  • Mazze90 Profile Picture
    Mazze90 5 on at
    RE: Segments based on SUM and AVG of order total amount

    Hello Clofly,

    thanks for your answer. While this approach might be useful in some scenarios, it doesn't quite fit the requirement. It's less about the exact sum or average of the total amount, but more about the list of contacts that are the result of my dynamic segment.

    The difficult part for me is the dynamic timeframe. When I just want to show all contacts where the sum of the orders is greater than let's say 1.000 €, I just add an attribute to the contact where I calculate the sum and query my segment on this attribute.

    We could get some time dimension if we calculate the same for the past 6 months, 12 month, 24 month, so we would have 4 attributes on the contact (or 8 if we also include the average). But in this case you're not fully free when selecting a timeframe on creation of a segment.

    Another idea is to do the calculation in PowerBI and create a static segment when importing the results of a report in D365 Marketing.

    Regards,

    Mazze

  • Suggested answer
    cloflyMao Profile Picture
    cloflyMao 25,202 on at
    RE: Segments based on SUM and AVG of order total amount

    Hi Mazee,

    You could insert the following web resource to your segment form to check whether it could meet your requirement.

    1. Retrieve all members of the segment with segment API.

    https://docs.microsoft.com/en-us/dynamics365/marketing/developer/extend-segments#addremove-contacts-to-static-segments

    2. Sum total value of associated orders of single contact.

    3. Sum total value of orders of all contacts.

    
    
    
        
        
        
        
        
    
    
    
        

    Total value of orders

    Average value of orders

    Test 

    There are 3 orders, and they belong to two customers.

    pastedimage1605164405431v1.png

    I create a simple segment to query contacts who have orders.

    pastedimage1605164487761v2.png

    pastedimage1605164501011v3.png

    The SUM and AVG value of total amount will display in the web resource:

    (Length of segment members is 2, so AVG value is 3174/2.)

    pastedimage1605164577174v4.png

    Regards,

    Clofly

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans