How to Take Advantage of SQL Query in Salesforce Marketing Cloud

Publicado por Santiago Tabuenca

29 / June / 2023

Temática

In a previous article we talked about the possibilities of the Filtered Data Extension, a very useful tool for segmentation.

What we must pursue with our Email Marketing strategy must be in line with the widely used phrase of sending the right message to the right person at the right time.

In order to send the right message at the right time, in addition to planning your shipments well (which Marketing Cloud can also help you with and, above all, with the new Einstein features) and creating a good message, it is important that you also send it to the person who may be interested in said information at that time. This is where a good SEGMENTATION comes into play, and you can do this if you have a good data structure and you know how to use the Marketing Cloud tools to segment, as we have mentioned, for some basic filters you can use the “filtered” ones. , but for more advanced stuff, you already need SQL Query.

What is SQL Query?

It is a tool that Marketing Cloud has and that you can find under the heading of Journey Builder -> Automation Studio -> Activities. Once there, you will find a section called SQL Query:

To create a QUERY SQL we will need the source on which we will query the information using SQL (Structured Query Language), a highly standardized language for making database queries. Some databases use a “dialect” of SQL, but basically they are all very similar.

In the case of Marketing Cloud, it is based on T-SQL for SQL Server with which you can search for references on the internet at the same time, it is widely used and you will find everything.

Another piece of the puzzle is that “engine” in SQL that we will create and that is the one that will extract the information that we need and with the structure that we need so that once the data is extracted, (and now comes the third piece) we will store it in the DE of destination.

Several important issues regarding SQL:

The only operation you will be able to use is SELECT, it does not accept the use of UPDATE or DELETE.
SQL statements are “case-insensitive” for values and column names, but good practices say that sql statements are capitalized, at least I do it like this, Example: SELECT customer_id AS id FROM example_table.
Unfortunately the SQLs are fixed, they cannot receive parameters (which would be tremendously useful), for that you already have to use api’s or with ssjs something can also be done.
The database always operates for time functions with the “Central Standard Time” CST, keep that in mind.
The type of language used is the usual Microsoft SQL Server.
An advice, always call the fields in the same way, so you won’t have to add extra code in your SQL, if the source and destination field is called ID, calling it like this is enough, if not, you’ll have to use AS, if in origin is called idCustomer and in destination ID, you will have to put SELECT idCustomer AS ID FROM table1, etc.

An example that you also could not do with a filter (if not, what an example)

Imagine that you want to create a DE with information from a user table and a table of user purchase topics to make an email with dynamic content based on those categories.

STEP 1. DE origin and destination

Given a user DE with the data of user_id, email, name, lastname1 and another with the historical purchase data, we are going to select the last purchase category to be able to make a personalized email with Dynamic content highlighting that category.

User table:

Purchase table:

Destination table:

Once we have the source and destination tables well created, we create the SQL QUERY, in Activities we choose SQL QUERY.

STEP 2. We create the SQL QUERY

We fill in the properties of the SQL QUERY, it is always good to give it a very descriptive name and order them by folders, in the end you will have a lot of sql queries and it is good to have an order.

In this example, as we do not want to complicate, we select the last category that the user has purchased.

For that, we introduce the query, we validate the syntax (very useful, because it checks that everything is correct, including field and table names).

In the last step of the SQL query, we select the destination Data Extension and choose how we want it to treat the data.

Appending. Add only the new records found in the query.
Updating. It updates the data of those that it finds by its PRIMARY KEY and adds those that it cannot locate.
Overwriting. In this case, directly leave the DE blank if it had data and enter the new data.

STEP 3. Manual execution or automation

Once we have everything ready, the moment of truth arrives, execute the SQL QUERY and see that everything works.

In many cases, it will have to be like this if you want it to be an SQL QUERY that refreshes a Data Extension, for example, if you want to send an email and first the SQL QUERY is executed to bring records to a table of users to send that email to. e-mail. Also, with an automation it is true that you have everything more controlled and you can see if it fails or not, and even receive an email if the process fails.

Here you can see an example of an Automation with SQL QUERY that finally connects with a JOURNEY to send a Welcome email:

However, the SQL QUERY itself has a tab with an error log.

Debugging

Within the activity there is an essential tab, if when you finally execute the query, the results do not arrive, there is a tab within the activity called LOG in which you will find a fairly descriptive message of what could have happened, many times it is PRIMARY KEY VIOLATION, which is usually resolved by trying not to cast duplicates with the SQL using distinct, group by, etc.

Including null values in fields that don’t support them, either change the field to Nullable=True or modify the source or sql.

Conclusion:

As you have seen, it is a very useful tool and is constantly used in Marketing Cloud, both for specific queries and integrated into Automations. There are people who recommend always putting these queries in automations to have them under control, however, from our experience, Marketing Cloud resolves the issues first. queries if you directly execute the SQL QUERY.

If you liked this article, you might also be interested: