Advanced Mode of Aggregation Custom Values

The Advanced Mode of Aggregation Custom Values allows you to calculate aggregated metrics—such as Sum, Average, Count, Minimum, or Maximum—when you do not have a Predefined Relation set up between your Data Extensions.

Create an advanced Aggregation Custom Value

In the Output step, under Custom Values click the Add new value button.

Provide a name for the Custom Value and select the Aggregation type.

Click the Advanced tab. Here you will find four sections:

Aggregation function (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 …”.

Relation to results (For each...): In the example above, it’s the field that finishes the sentence “I want to count the number of … for each …”.

Matches with: (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.

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

This example aggregates the total amount of euros purchased for each customer ever.

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.

Select the DE, DESelect_Demo_Orders Data Extension.

Select the Field you are doing the aggregation for: Total Amount

Aggregating Sum of Total amount for each…. CUSTOMER

In the 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

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 this 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. It is the answer to: "… and it’s linked to the main Selection by …..?"

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…

Click Edit Filters and to apply filter/filers to the Custom Value.