If you’d like to get the total number of rows in two Data Extensions (or whatever tables) and do it in a single query, you can leverage CROSS JOINs like this:
Doing it this way will get you past the no-ANSI-JOINs restriction in SFMC.
Reference:
the personal blog of adam spriggs
Scenarios and samples of T-SQL Query Activities in the Salesforce Marketing Cloud platform.
If you’d like to get the total number of rows in two Data Extensions (or whatever tables) and do it in a single query, you can leverage CROSS JOINs like this:
Doing it this way will get you past the no-ANSI-JOINs restriction in SFMC.
Reference:
So your Query Activity failed. What next?
SFMC Support can certainly tell you exactly what the error was, and now you can (sometimes) see some error details in the Activity tab of Automation Studio.
Here are a few common things that cause Query Activities to fail.
If your query results in duplicate rows not allowed by the primary key, then you’ll need to change your primary key or de-duplicate the rows that result from your query. Here’s my go-to method for deduplication. The innermost query sorts by insertDate
, groups by subscriber key and assigns a ranking
to each row. The outermost query selects only the oldest result per subscriber key.
If you have fields in your target Data Extension that are non-nullable, you need to ensure none of the columns in your query return a null value. You can utilize the isnull()
SQL function to handle that situation:
If any of your source data extension columns are larger than the target, then you can adjust the length of the target column or use the left()
SQL function to trim the value:
Probably the most frustrating error is the the 30 minute timeout. If your query won’t run in that amount of time, then it’ll error out. There are a bunch of different ways to address query timeouts. Here are some tips:
If it’s still timing out, leave a comment below with some details. I have some other tricks to share.
Easy fix for this one. Just re-select the target data extension in your Query Definition.
You can’t, for example, insert a string value of $12.34
into a Decimal field. What I typically do in this scenario is to check the data types of the source data extension(s) and ensure that they match the target data extension column data types. If they don’t, then I’ll either recreate the target column with the correct data type or cast/convert/fix the source column value in the query.
Testing content is a good thing in marketing. It’s especially appropriate for email.
Find what works best and go with it.
One way to go about that is to segment your sending audience into groups and send varying content to each group.
In the Salesforce Marketing Cloud platform, you can create those groups with queries. Here’s an example of a 20-20-80 split — two groups of 20% and the rest in the last group.
(Select the first 20% randomly)
(select 25% more of those not already selected in the first group)
(select the rest of the rows that are not in either of the first two groups)
You may be wondering if the second query is correct — 25% is not 20%. Here’s the math:
If you have a 100 subscribers and you subtract 20% — or 20 — you have 80 left.
If we want two equal groups of 20, then 20% of 80 is not 20, it’s 16. To get the correct count by percentage we’ll need to use 25% to get 20 from 80, since 80 * .25 = 20.
Also, ordering by NewID() will randomize the selection each run of the query.
For further reading:
Suppose you’d like to select rows in an Salesforce Marketing Cloud data extension created on the last day of the month. Here’s what I use:
The last portion of the where clause finds the first day of the next month and then subtracts one day.
Normally queries like this run in a daily automation.
For further reading:
Suppose you’d like to select subscribers from an Salesforce Marketing Cloud data extension that were created in the last thirty days. Here’s what I use:
or between two static dates:
For further reading: