How does the Advanced Mode of Aggregation Custom Values work?

Creating an advanced Aggregation Custom Value

In the Output step, under Custom Values:

1. Click on the ‘Add new value’ button
2. Give the Custom Value a name 
3. Select the Aggregation type 
4. Click on the Advanced tab

Once you are on the Advanced tab of the Aggregation Custom Value, there are 4 sections, as you can see in the below screenshot: 

1. I want to aggregate …

After selecting the function you want to apply, you’d need to select the field that you want to aggregate.

If for example, you are doing a “count” aggregation type, you’d need to populate the field that finishes the sentence when saying “I want to count the number of …”

2. … For each …

In the example above, it’s the field that finishes the sentence “I want to count the number of … for each …” 

3. … and it’s linked to the main Selection by …

Finally, you’d need to define how to relate the aggregation you’ve just built back to the Selection, by specifying the matching column between the aggregation and your Selection

4. Additional Filters (Optional)

In the aggregation you’ve built, you’ll be taking into account every record from your Data Extension, but you can narrow down your results to specific conditions. It’s equivalent to the condition you’d set in a COUNTIF function in Excel, for example.

Example: We are aiming to Aggregate the total amount of euros purchased for each customer ever

Now, let’s take it to step by step:

1- I want to aggregate… TOTAL AMOUNT OF EUROS PURCHASED

Choose the Aggregation function which is needed for your use case from the dropdown list. 

Once you select the function from the drop-down, you will need to specify the field you are doing the sum for. In our case, we have the order information in DESelect_Demo_Orders, and the amount of each order in the Total Amount field.

1. Select the DE, DESelect_Demo_Orders Data Extension. 
2. Select the Field you are doing the aggregation for: Total Amount 

2- Aggregating Sum of Total amount for each…. CUSTOMER

In our example above, it’s the field that finishes the sentence “I want to count the total amount of orders… for each Customer, and each Customer is represented by the CustomerId field

3- How it relates to the main Selection. 

When selecting the Aggregation function you will need to specify the matching field with the DE in the Selection. (This is why it is recommended to use the Basic mode, as this is already set based on the Predefined Relation).

In our example, the relation is that the field CustomerId in the DESelect_Demo_Orders Data Extension is matching with the field Id in the DESelect_Demo_Customers Data Extension, which is included in the Selected Data Extensions section of this Selection. 

Simply it is the answer to: "… and it’s linked to the main Selection by …..?"

4- Additional Filters (Optional)

You will be able to apply filters on the Data Extension you are aggregating on, for example only orders in the last month, etc… 

You are able to click on Edit Filters and to apply filter/filers to the Custom Value. 

Related Articles: 

Custom Values: Aggregating on two Fields