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!
