Marketing Cloud SQL Cheat Sheet
The rules, patterns and data view joins you need for Marketing Cloud Query Activities, with short sample queries you can adapt and test in your own account.
Marketing Cloud Engagement runs SQL through Query Activities in Automation Studio. The dialect is a subset of T-SQL, and the way it runs is different from a normal database: every query is a single SELECT whose results are written into a target data extension. This sheet covers the rules that trip people up and the patterns that come up most often.
The rules that matter
- One SELECT per activity. No
UPDATE,DELETEorINSERTstatements, variables, temporary tables or cursors. The data action on the activity (Overwrite, Update or Append) decides what happens in the target. - Every output column needs a name that matches a field in the target data extension. Alias calculated columns with
AS. - Primary keys in the target are enforced. Duplicate key values in your results cause the activity to fail, so deduplicate before writing.
- Field lengths and types must fit. A 300-character value into a 255-character field fails the run.
- There is a run-time limit. Long-running queries are stopped. Check current limits for your account in Salesforce Help and split heavy logic into staged queries.
- Dates use system time, which is Central Standard Time without daylight saving adjustment.
- Data extension names with spaces or hyphens need square brackets, for example
[Orders - Staging].
System data views give you send and engagement history without building your own tracking extracts. They keep a limited rolling window of history and some are only available in certain business unit contexts, so check current retention and availability in Salesforce Help and copy what you need for long-term reporting into your own data extensions.
System data views at a glance
| Data view | Holds | Useful fields |
|---|---|---|
| _Sent | One row per email sent | SubscriberKey, JobID, EventDate, TriggererSendDefinitionObjectID |
| _Open | Open events | SubscriberKey, JobID, EventDate, IsUnique |
| _Click | Click events | SubscriberKey, JobID, EventDate, URL, IsUnique |
| _Bounce | Bounce events | SubscriberKey, Domain, BounceCategory, SMTPBounceReason |
| _Unsubscribe | Unsubscribe events | SubscriberKey, JobID, EventDate |
| _Job | Send job details | JobID, EmailName, DeliveredTime |
| _Subscribers | All Subscribers list | SubscriberKey, EmailAddress, Status |
| _Journey and _JourneyActivity | Journey versions and their activities | VersionID, JourneyName, JourneyActivityObjectID |
SELECT SubscriberKey, EmailAddress, LastPurchaseDate
FROM (
SELECT
SubscriberKey,
EmailAddress,
LastPurchaseDate,
ROW_NUMBER() OVER (
PARTITION BY SubscriberKey
ORDER BY LastPurchaseDate DESC
) AS rn
FROM Customers_Staging_DE
) x
WHERE x.rn = 1SELECT c.SubscriberKey, c.EmailAddress
FROM Customers_DE c
WHERE NOT EXISTS (
SELECT 1 FROM _Open o
WHERE o.SubscriberKey = c.SubscriberKey
AND o.EventDate >= DATEADD(day, -90, GETDATE())
)
AND NOT EXISTS (
SELECT 1 FROM _Click k
WHERE k.SubscriberKey = c.SubscriberKey
AND k.EventDate >= DATEADD(day, -90, GETDATE())
)Sends by journey
To report on which journey sent which email, join _Sent to _JourneyActivity on the triggered send definition, then to _Journey on the version. The example assumes a target data extension Journey_Sends_DE with matching fields.
SELECT
j.JourneyName,
j.VersionNumber,
ja.ActivityName,
s.SubscriberKey,
s.EventDate AS SentDate
FROM _Sent s
INNER JOIN _JourneyActivity ja
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
INNER JOIN _Journey j
ON ja.VersionID = j.VersionID
WHERE s.EventDate >= DATEADD(day, -7, GETDATE())Quick patterns
| Need | Pattern |
|---|---|
| Join strings safely | ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') AS FullName |
| Default a missing value | COALESCE(PreferredStore, HomeStore, 'Online') AS Store |
| Bucket values | CASE WHEN Points >= 1000 THEN 'Gold' ELSE 'Standard' END AS Tier |
| Date only, no time | CONVERT(date, OrderDate) AS OrderDay |
| Last N days | WHERE EventDate >= DATEADD(day, -30, GETDATE()) |
| Unique opens only | WHERE o.IsUnique = 1 |
| Case-insensitive email match | WHERE LOWER(a.EmailAddress) = LOWER(b.EmailAddress), although the default collation is usually case-insensitive already |
Stage heavy logic
If one query joins several large data views, filters on dates and deduplicates all at once, it is more likely to hit the run-time limit and harder to debug. Split it into steps inside one automation: first copy the last 30 days of _Sent into a staging data extension, then join that smaller set to opens and clicks, then deduplicate into the final target. Each step can be checked on its own, and a failure tells you which stage broke. Give staging extensions a clear naming convention, such as a _STG suffix, and overwrite them on each run so they do not grow without limit.
Diagnosing failures
When a Query Activity fails, check in this order: the target field names and lengths, duplicate primary keys in the results, data types (text compared with numbers or dates), and run time. Validate the query in the activity editor, then run it into a test data extension with no primary key to inspect the raw output.
More detail: Marketing Cloud SQL query activity errors, deduplicating records with ROW_NUMBER, using data views for engagement reporting and our Marketing Cloud SQL development service.
Queries timing out or failing?
We design, tune and document Marketing Cloud SQL and the automations that run it.
