Customer experience | Sales, Customer Insights,...

Segments based on SUM and AVG of order total amount

(0) Share

Report

Posted on by Mazze90

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.

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

cloflyMao 25,210 on at

Like (0)

Report
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.

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

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.)

Regards,

Clofly

Was this reply helpful? Yes No
Mazze90 5 on at

Like (0)

Report

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

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report

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.)

Regards,

Clofly

Was this reply helpful? Yes No