Another month, another exciting release full of features that make a marketers life easier. This month we’re allowing faster and more advanced selections via date filters that update themselves depending on the moment the query executes, new ways of comparing with data in other data extensions, and leveraging the powerful data in data views.
Dynamic date filters
Available for: DESelect Enable, DESelect Plus, DESelect Advanced
At DESelect we have always supported filtering results by dates. Until now we only supported filtering by exact date, through the kind of date picker most people are familiar with.
But what do you do when you want to send an email to the people that bought from you for the first time in the last week, every week? You don’t want to update the query / selection every time.
To solve for this, we introduce Dynamic Date Filters: use a time indication relative to the moment the query executes and you never have to update your selection.
Examples of Dynamic Date Filters would be:
- OrderDate Greater Than 7 Days Before Now: to get the orders of the last 7 days
- CreatedDate Smaller Than 10 Business Days Before Now: to get contacts created more than 10 business days ago
- InvoiceDueDate 12 Hours After Now: to get the invoices that are more than 12 hours overdue
- ContractEndDate 1 Week After Now: to get the contracts that will end 1 week from now
We support comparing years, quarters, months, weeks, days, weekdays, hours, minutes and seconds.
Subqueries
Available for: DESelect Enable, DESelect Plus, DESelect Advanced
Sometimes you want to limit your result set without JOINing with another data extension.
Some use cases are:
– get the products for which an order exists
– get the subscribers that have not received an email in the last 5 days
– get the accounts that have an active contract
Let’s zoom into this last example: getting the Accounts that have an active Contract. You could do an INNER JOIN between the Accounts and Contract table, but if Accounts have multiple Contracts, your result set will contain multiple lines for each Account.
Filtering your results based on values in another data extension is something you could do with a subquery in SQL. And DESelect now provides a simple UI to support this as well.
To our filters criteria dropdown, we have added ‘In results’ and ‘Not in results’, which allows you to select a data extension and a field in that data extension you want to compare your field value with.
In the screenshot below, we only want to get Accounts with an Account Id that can be found in the Customer Id column of data extension MD-Orders.
Clicking the filter icon allows you to filter the values of that Customer Id column, for example when you only care about the contracts that are currently still active.
Data views
Available for: DESelect Enable, DESelect Plus, DESelect Advanced
In SFMC, we have data extensions, which are custom made data tables, and data views, which are system tables. In data views you’ll find things like subscribers, events (like email/sms sends, link clicks) and other data Marketing Cloud tracks.
Until now, all data extensions and a few data views were available in DESelect to build queries with. We have now extended this to fully support all 23 data views available in Marketing Cloud.
Data views can be easily recognised as their name starts with an underscore, and we style them a little differently as data extensions.
Conclusion
What do you think of these new features? Do you have segmentations for which you write difficult SQL queries that could easily be constructed with these easy to use functionalities?
