DESelect December ’19 Release: Aggregations and more

DESelect December ’19 Release: Aggregations and more

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.

Conclusion

There are many possible scenarios where you may want to filter your selection using aggregations. Think about filtering on total order value of a customer, counting the amount of orders or getting the average order value. Building this kind of query in SQL would be really complex – luckily through DESelect that’s a thing of the past!
Get in touch if you want to try this great functionality for yourself and see how much time DESelect can save you.
  • What’s new in DESelect: December 2022

    Happy Holidays! It’s time to reveal what’s inside the last DESelect release of 2022. Our December release is packed with upgrades like access to more data, serious workflow enhancements, and helpful improvements that admin users will love.   Shared Custom Values Expedite your process and remove redundant tasks. *Available for: the Advanced plan. Previously, when […]
  • What’s new in DESelect: October 2022

    Happy October! We’re excited to announce a bunch of new features focused on increasing your efficiency and effectiveness as marketers. Many of the new features in this release have been highly requested and eagerly awaited by our customers so let’s jump right in. Introducing: Filter Sets Drive efficiency with Filter Sets*Available for: Plus and Advanced […]
  • What’s new in DESelect: August 2022

        The DESelect team has been looking forward to this release with many eagerly requested features finally here and ready for you! This August we’re introducing several features and UIUX enhancements designed to keep your workflow smooth, simplify data management, and empower Admin users. Building, revising and managing Waterfall Selections is now easier, and […]
  • What’s new in DESelect: June 2022

    Summer is finally here along with powerful new features from our team. DESelect proudly opened the door to non-technical marketers by ending the technical dependencies around SQL. We also take pride in equipping technical marketers with tools that increase work efficiency and campaign precision in Marketing Cloud. Today we’re taking this even further: Introducing: Data […]

Stay Connected

Join our newsletter to receive updates and helpful SFMC guides

Subscribe to the Newsletter
Receive Salesforce Marketing Cloud tips,
tricks, and DESelect updates

By clicking the “Subscribe now” button, you agree to the DESelect Terms of Use and Privacy Policy.