How to Use Salesforce Marketing Cloud (SFMC) Query Studio

Query studio is probably the AppExchange App that I use the most in Salesforce Marketing Cloud. It’s a free app that allows you to quickly query from data extensions using SQL, without having to go through all of the additional steps of creating a SQL Activity in Automation Studio and specifying a data extension to store the results

Unsupported SQL Functions in SFMC Query Studio

However, this free tool does come with some quirks that required getting used. Keep in mind that the following SQL functions are not supported or require adjustments in SFMC Query Studio. 

WITH Clause

SFMC Query Studio only accepts SELECT statements, meaning that WITH clauses, which are one level above, are not supported.

Instead, use subqueries such as the below.

SELECT TOP 10 a.[Subscriber Key],
(
    SELECT TOP 1 COUNT(b.[Subscriber Key]) as ct 
    FROM [testDE] 
    WHERE b.[Subscriber Key] = a.[Subscriber Key]
) as Cnt
FROM [testDE] a

This query should validate and run with no issues, returning the expected results. Even if an error persists in SFMC Query Studio ("Unable to create temporary data extension: Field name cannot be blank."), it will definitely work in Automation Studio.

ORDER BY

Anyone who has tried to use ORDER BY in SFMC Query Studio has probably encountered this error before.

An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

To get around this, simply add TOP 100 PERCENT or use a subquery to rank fields by subscriber.

LIMIT

Similarly, queries using the LIMIT expression will need to be written using TOP, as SFMC query studio uses T-SQL syntax.

The query below will return an error:

SELECT SubscriberKey, EmailAddress 
    FROM MASTER_IMPORT
        WHERE EmailAddress LIKE "%gmail.com" LIMIT 300

Rewrite it using the TOP expression:

SELECT TOP 300 SubscriberKey, EmailAddress 
FROM MASTER_IMPORT
WHERE EmailAddress LIKE "%gmail.com"

SELECT *

Unfortunately, SFMC Query Studio does not support SELECT *. Instead, you'll need to write out all of the column names that you need. If a column name contains a space, hyphen or is a SQL's reserved keyword, you need to capture it with squared brackets.

SELECT
	ContactKey
	, EmailAddress
	, [External Contact Id]

Single Line Comments

SFMC Query Studio generally does not support the single-line SQL comment using two dashes (–). I've gotten away with very short (one or two words) single line SQL comments, but anything longer and I'm usually met with a prompt to use multi-line comments start instead (even if the comment is single-line).

Date Functions

SFMC Query Studio does not support user-defined functions, but there are multiple date functions that are useful on a day-to-day basis for marketing automation purposes. In many cases, GETDATE and DATEADD are all I need.

SELECT subscriberkey
FROM testDE
WHERE createdate < DATEADD(year, -1, GETDATE())

You can also simply hard-code a date in yyyy-mm-dd format.

SELECT subscriberkey
FROM testDE
WHERE createdate < '2024-01-01'

GETDATE gets server time, which for SFMC is Central Standard Time (UTC -6), without changes between standard and daylight savings time. This remains the case even if you update your timezone settings in Setup. To get date data in your desired timezone, use the SYSDATETIMEOFFSET() function.

---

Although SFMC query studio can be a bit buggy at times, it has still been a huge time saver for me. Overall, I’m satisfied with this app, especially since it’s completely free. If you haven’t already, download SFMC query studio for free and give it a try. Below is a tutorial from Salesforce Labs Day.