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: June 2022

    Summer is finally here along with powerful new features from our team. Happy June! It’s Garrett again with big product updates for you as we start the summer. 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 […]
  • What’s new in DESelect: April 2022

    Get more time back in your day with DESelect. That’s not an April Fools’ joke! Welcome to April! It’s Garrett, from DESelect, here to share this month’s Product updates. It’s no secret that our no-code products already help thousands of marketers unlock the full power of SQL for more effective marketing campaigns. Today we’re excited […]
  • DESelect February ’22 Release: Scheduling of waterfall selections and moving around selected data extensions

    It is the first release of the year so let’s make it interesting! As you may already know, DESelect loves to bring customer’s ideas to life. And so, we are introducing 2 new powerful functionalities that come by popular demand: scheduling of waterfall selections and moving around selected data extensions.   Let’s get started! Scheduling […]
  • DESelect Dec ’21 Release: Folders for available data extensions, selection templates and descriptions, and improved date filters

    It’s that cold snowflakes season and DESelect is introducing some new cool features: folders for Available Data Extensions, selection templates, and descriptions, and improved date filters. Let’s dive in! Folders for Available Data Extensions Available for: DESelect Segment Enable, DESelect Segment Plus, DESelect Segment Advanced Being able to search for the data extensions that you […]

Stay Connected

Join our newsletter to receive updates and helpful SFMC guides

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

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