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!
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:
Here are a few other examples of queries that are supported by aggregations:
Aggregations is part of the DESelect Plus and DESelect Advanced tiers.
Besides the big new aggregations functionality, we have made the following improvements:
Salesforce Marketing Cloud best practices
and DESelect updates