November was another busy month for the DESelect product team. Besides several smaller improvements, we enabled the use of aggregations in subqueries, opening up a whole new set of possibilities with campaign selections. Let us tell you all about it!
Aggregations
Available for: DESelect Plus, DESelect Advanced
Let’s say you want to send a voucher to contacts that have bought for more than 100 dollars in the last 2 months.
To start, we select the data extension we want to get the details of – Contacts – and drag that to the Selected Data Extensions section.
Next, we’ll add a filter, as we only want to get the contacts that have bought in the last 2 months for at least 100 dollars.
Drag Contact field Id to the Selected Filters section. We want to limit the Contacts based on the Orders they placed, so we add In Results as the criteria for the filter. We then drop the Orders data extension on our filter, and select Contact Id as the field to compare with, as that’s how the Contacts and Orders table relate to each other.
Currently, our filter selects all contacts that have an order, but we want to drill down further. Click on the funnel icon of the filter, so we can add filters for our Orders collection.
Drag-drop the Order Date field to the Selected filters section, and set the date to: greater than: 2, months, Before Now. Note we’re using a dynamic date filter here, so the date will be updated whenever this query is run again.
We’ll want to add one more filter, as we’re only interested in the contacts that have placed at least 100 dollars worth of orders.
Drag-drop the Amount field, and click on the formula icon. Now you can choose Sum as an aggregation formula. The formula button turns green to indicate you’re currently using an aggregation formula.
And that’s it! When you run this query, you’ll only get the contacts that have bought at least 100 dollars worth in the last 2 months!
We support the following aggregations:
- count: count the number of records where there is a value for a field
- sum: sums up the values of a field over multiple records
- minimum: take the lowest value of a field over multiple records
- maximum: take the highest value of a field over multiple records
- average: take the average value of a field over multiple records
Here are a few other examples of queries that are supported by aggregations:
- Select the contacts that placed orders with a total order value over all orders of a maximum of 100 dollar
- Select the accounts that have more than 3 contacts
- Select the orders with the average order line value above 50 euro
Aggregations is part of the DESelect Plus and DESelect Advanced tiers.
Additional Updates
Besides the big new aggregations functionality, we have made the following improvements:
- In the screen to automatically create a new target data extension, you can now add all fields of a source data extensions at once.
- We added the ability to set a default value for a date field when creating a new target data extension.
- We’ve optimised the design of the filters and navigation bar to better use the available space and display better on smaller screens.
- Date and number formats automatically adapt to the locale settings of the user in SFMC.
- We clarified several warning messages.
- We’ll let you know if you try to save a selection with a name that already exists, so it’s easier to find your selections back.
