DESelect January ’21 Release: SQL functions

DESelect January ’21 Release: use SQL functions from Salesforce Marketing Cloud in DESelect

SQL functions

Available in: DESelect Advanced

Without DESelect, you need to write SQL queries for segmentation in Salesforce Marketing Cloud as soon as you want to use data from multiple sources. SQL has the benefit of being very powerful and flexible, but it’s technical and not easy to learn.
SQL has a lot of built-in functions you can use, for example, LEFT() to take the first characters of a string, or ROUND() to round up a number. DESelect now provides a very easy way of using all of these supported functions as part of your drag-and-drop selections. You can use these functions in a similar way as functions in Excel. The UI provides full flexibility, you can combine as many functions you want, providing the same flexibility as if you were writing SQL code.

To use SQL functions, on the Target Definition screen in DESelect, create a new custom value by clicking the Add New Value button in the section on the left under Custom Values. Next, choose Apply Formula to a Field followed by Apply Any Function

Let’s look at some examples.

Example 1: Lowercase email addresses

We have a data extension with contacts. Each contact has an email. The contact data comes from a form users fill out on a website. Sometimes users write their email in uppercase, sometimes in lowercase, sometimes as a combination. We want to convert all email addresses to lowercase, which can be done using the LOWER function.

We create a new custom value that we give the name Lowercase Email, and choose type Apply formula to a field > Apply any function.

First, we set the Field Type to Email Address, as the result here will be an email.

Next, we click on the dropdown in the Insert Function section, where we can find the overview of all supported functions, nicely grouped by output type. Whenever we choose a function, we can see what the syntax is, a description, an example of how the function is used, and a link to more documentation on each function. Here, we choose LOWER and then click the Insert Formula button.

Lastly, under the Insert Field section, we choose data extension Contacts, and field Email. Note that the data extensions shown here, are the data extensions that were chosen in the Selected Data Extensions section. We put the cursor in between the brackets of the LOWER function and click the Insert Field button.

We have now built the following formula:

LOWER(“Contacts”.”Email”)

Note the Check syntax button on this screen, which allows you to check if the function used is correct. A lot of validation is done here to make it as easy as possible for you. For example, we validate if the number of parameters passed to a function is correct, if they are of the right type, if the number of brackets is correct, etc.

This syntax validation happens when the user presses the Check syntax button or the Save button.

Now we can save this custom value, and drag and drop it to a field in our target data extension.

Example 2: Get the month of an order date

We have a data extension with orders. Each order contains an order date. We’re interested in getting the month from the order date. So for example, if the order date is ‘2020/12/14’, we want to get ‘December’.

We create a new custom value that we give the name Month, and choose type Apply formula to a field > Apply any function.

First, we set the Field Type to text.

Then, from the Insert Function section, we choose function DATENAME from the Date category. We confirm using the Insert Formula button.

We now have:

DATENAME(date_part, date)

Next, we replace date_part with month.

That gives us:

DATENAME(month, date)

Lastly, we select date in our formula and under the Insert Field section, we choose data extension Orders, and field Order Date.

We now have the following:

DATENAME(month, “Orders”.”Date”)

We can click Check syntax or just Save to validate this syntax is correct. Once we map this custom value to a field and run preview, we’ll see it returns the name of the month for each order date.

Example 3: Combining a contact’s first and last name

We have a data extension with contacts. Each contact has a first name and a last name. We want to write the first name and last name of contact into one field in the target data extension.

We can do this using either the CONCAT function or just using the + operator.

We create a new custom value that we give the name Full Name, and choose type Apply formula to a field > Apply any function.

First, we set the Field Type to text.

We have 2 options here:

Option 1: using CONCAT function

From the Insert Function section, we choose function CONCAT from the Text category. We confirm using the Insert Formula button.

We now have:

CONCAT(string1, string2, …., string_n)

Next, we need to replace string1 with the first name. We select string1 in our formula, and under the Insert Field section, we choose data extension Contacts, and field First Name.

That gives us:

CONCAT(“Contacts”.”First Name”, string2, …., string_n)

Next, we’ll replace string2 with a space, so first name and last name are separated by a space, giving us:

CONCAT(“Contacts”.”First Name”, ‘ ’, …., string_n)

Note we use single quotes here for using a string.

Lastly, we can replace the last part with the last name, chosen in the Insert Field section, so we get:

CONCAT(“Contacts”.”First Name”, ‘ ‘, “Contacts”.”Last Name”)

Option 2: using + operator

Note that operators are also fully supported, so you make calculations with *, – and +, or use + operator to append strings.

Using the + we would do the following:

“Contacts”.”First Name” + ‘ ‘ + “Contacts”.”Last Name”

Conclusion

With the introduction of support for SQL functions, DESelect enables users to do advanced operations, in the most simple way possible. With a categorized overview of all supported functions, the built-in descriptions, syntax, descriptions, links to help documentation, and smart validation, users get maximum guidance while using SQL functions.

The examples above give some indication of what’s possible, but much more advanced use cases, like combined and nested usage of these functions or use of operators are possible as well.

Interested in giving this powerful feature a try yourself? Book a demo today! 

  • DESelect Launches ‘Engage’ Platform to Combat Marketing Fatigue

    AUSTIN, Texas, March 28, 2023 — Amid a macro environment where marketing teams must increasingly scrutinize spend, DESelect, the leading Salesforce Marketing Cloud partner, announced the launch of its Engage platform, designed to maximize the impact of every communication sent to each individual contact and defend against marketing fatigue. DESelect Engage gives marketers the tools to control […]
  • DESelect Segment – February ’23 Release

    It’s already February and it’s already time for our next Segment release! You asked, and we listened. In response to some of our customers’ most upvoted feature requests, today we’re releasing brand new capabilities, workflow upgrades, and UI/UX design enhancements for DESelect Segment. Cross Joins A new join type to combine Data Extensions with DESelect […]
  • 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 […]

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.