Skip to main content

SFMC SQL Basics

SELECT your data FROM Salesforce Marketing Cloud WHERE drag-and-drop Filters are not enough.

How to segment data in Marketing Cloud

Salesforce Marketing Cloud offers a few ways of segmenting data for your marketing needs. Out of the box, you can use Filters and SQL Query Activities.

Filters are a straightforward drag-and-drop solution for one-time (Filtered Data Extensions) or refreshed on-schedule (Data Filters with Automation) segmentation. Unfortunately, they are constrained, as they allow you to copy a single Data Extension with all its columns and filtered-down rows. That filtering can leverage source Data Extension values, another Data Extension values (through Data Relationship) or Measures. That's it. For real Marketing Automation, it's not enough.

This is why you are looking to learn about SFMC SQL. Along with Automation Studio Query Activity, it will allow you to deploy any segmentation scenario you want with complete control over the output columns, names and values. However, with great power comes some learning required.

There is also a third way - paid AppExchange solutions that offer you a way to eat a cookie and have a cookie: the full power of SQL with a drag-and-drop interface. For a price.

You can check my comparison of all the above options in my here. This article will focus only on the most powerful and free option - SFMC SQL.

SQL in Salesforce Marketing Cloud

SQL (Structured Query Language) is one of the best ways to work with relational databases. And Marketing Cloud's Data Extensions, with the help of Data Designer, offer precisely that. A relational database. The same is valid for system Data Views containing tracking data. Because of this, SQL is used in SFMC whenever powerful segmentation is needed (mainly using Automation Studio).

The main difference from standard SQL (SFMC uses more or less SQL Server 2019) is that SFMC supports only the SELECT statement. Neither INSERT, UPDATE or DELETE are officially supported. It is because the queries are executed as a part of the backend query and are limited to provide non-breakable functionality.

Another limiting characteristic of SFMC SQL is visible in the data accessible by queries. Only data stored in data extensions or system data views is available. Moreover, you can save the results only to the data extensions. The reason is other data structures of SFMC are not relational databases.

Finally, the significant limitation you might hit as your Marketing Cloud grows is AutoKill. It will automatically stop your query if the execution time exceeds the 30 minutes limit. Think about the query with the future in mind. It is vital for queries that are supposed to be the backbones of your data management flow in Marketing Cloud. Even if it works great now, consider how it will behave once you have more data in the system. One of the great ways to not trigger AutoKill is to implement retention periods for the Data Extensions used in the query to limit input data.

Where to learn SFMC SQL

Before we start with the real deal, let's cover where to learn to write SQL. I recommend getting a Query Studio. It's a free AppExchange add-on to Marketing Cloud developed by Salesforce. While it has cons, it's perfect for quickly learning how to query.

If you cannot install this app in your Marketing Cloud, you still can learn using Query Activities in Automation Studio, but it will be much less user-friendly. For each change in the query, you will have to update the target Data Extension manually and go through multiple steps across the platform to see the results.

My approach is to always start in Query Studio, and only once my SQL Query is complete and validated do I move it to Automation Studio.

How to learn SFMC SQL

SFMC SQL is very limited compared to the full power of Structured Query Language, but there are still many topics to cover. I split them into steps that slowly add more complex concepts to make it easier. Each should provide knowledge that you can actively use in your day-to-day work.

In each level, you will see:

  1. Learning materials to read.
  2. Exercise you can do in your Marketing Cloud platform to test your skills.
  3. Hint that can help you with the exercise if you are stuck.
  4. Example solution to that exercise. Keep in mind that there might be more than one correct approach.

Level 1 - SELECT, FROM, WHERE

Learn the three essential SQL statements for all queries. They will give you similar power to what Filters can do - with the bonus of choosing the columns you need for your final segment.

Learning materials

  • SELECT - just the first part, before the aliasing section.
  • FROM - whole article.
  • WHERE - the first part, before the Basic Operators, plus Equality Operators and Logical Operators. Don't worry about Comparison operators or shorthands for now. Don't worry about the examples with dates and unknown functions. We will get there. For now, think about how you can filter based on simple values like status.
  • System Data Views - the basics and _Subscribers Data View.

Check your skills

Write a query that will find Subscriber Key for all Active (opted-in) Subscribers in your All Subscribers list.


Level 2 - JOIN, Aliasing, NULL

Learn how to leverage a second Data Extension or Data View, change the output's column name, and deal with basic NULL cases.

Learning materials

Check your skills

Write a query that will find Subscriber Key for all Active (opted-in) Subscribers in your All Subscribers list that were sent an email in the last 6 months.


Level 3 - LIKE, CASE, Strings

Learn how to deal with string values, how to implement SQL version of if/else logic and how to make complex conditions calculating your data. With this, you are no longer limited to the data already available in the tables.

Learning materials

Check your skills

Write a query that will find Subscriber Key for all Subscribers in your All Subscribers list and add a new column called SubscriberKeyType. In that new column, calculate whether the Subscriber Key is using an email address, Salesforce ID (and in this case - source object) or other. Possible values should be: Email, Account, Contact, User, Lead, and Other.

You Should Know

For our need here, if the Subscriber Key has 18 characters, no @, and starts with three specific characters, it probably is a Salesforce ID. Those first three characters point to a specific Salesforce Object being a source of that record:

  • 001 = Account
  • 003 = Contact
  • 005 = User
  • 00Q = Lead

Level 4 - Dates, WHERE operators, Conversion

Learn how to use the most important filter of all - date filtering. Read about essential date functions and what to do when you don't have the correct data type for your use case.

Learning materials

Check your skills

Write a query that will find Subscriber Key for all Subscribers in your All Subscribers list that joined the list within the last year and had a bounce on Google or Microsoft email between the first day of the current month (write that date manually, as a string) and today.

For this exercise, we will assume that Google users have emails like [email protected], Microsoft users - [email protected] and Yahoo - [email protected].


Level 5 - Multiple JOINS, UNION, INTERSECT and EXCEPT

Learn to leverage data from multiple tables - the only limitations will be creativity and autokill function crashing queries after 30 minutes of execution ;)

Learning materials

  • JOINs - yup, everything. The Cheat Sheet at the end should be useful to visualise multi-JOIN fun better.
  • Journey Builder Data Views - both _Journey and _JourneyActivity.

Check your skills

Find some active Journey from the last quarter that already had some email sends with engagement. Note the name of the Journey and Email Activity.

Write a query that will find Subscriber Key and Email Address for all Subscribers in your All Subscribers list that clicked a link in the first email activity in the Journey you selected.

Append a proof record with Subscriber Key equal to level5exercise and your email address.


Level 6 - GROUP, COUNT, TOP and HAVING

Graduate from just playing with rows and start calculating the data to see patterns, outliers and problems. It is handy for SFMC Administrators and Consultants performing analysis on the platform data.

Learning materials

Check your skills

Find the number of sends, unique bounces, and the bounce rate (percent of bounces vs sends) per domain from deliveries attempted in the last month. Filter the records to only those Domains with at least 100 sends. Order by the bounce rate descending and limit to the top 20 records.

If you send huge volumes, feel free to change the timeframe from last month to last week for the exercise.


Level 7 - coming soon-ish

I will add more levels once I finish writing supporting learning materials :)


Writing with style

Knowing the available functions is one thing. Writing good and readable queries - another. Salesforce Marketing Cloud is always a team sport, so be sure to leverage the power of a Style Guide to improve the quality of your SQL.

Order of operations

One of the key things to remember when building more complex queries is the order of operations, as it will decide what is in the output:

FROM > WHERE > GROUP BY > HAVING > DISTINCT > ORDER BY

It means that rows filtered with WHERE will not be considered for GROUP BY and that DISTINCT deduplication will be applied to results grouped and filtered by HAVING - not the source data.

All SQL Guides