Troubleshooting Queries in SFMC

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 things that I’ve found are common things that cause Query Activities to fail.

Primary key violation

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 email address and assigns a ranking to each row. The outermost query selects only the top result per email address.

Inserting a null value into a non-nullable field

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:

Inserting a value too long for the field (truncation)

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:

Timeout

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:

  1. First and foremost, you need to reduce the number of row you’re querying. This can be altering your date range or adding additional criteria to target rows more specifically.
  2. Leverage the primary keys for speed.  While we don’t have any insight in to the indexes that SFMC behind the scenes, it’s a good bet the primary keys are indexes.  So the more utilize their values in the where-clause, the better.
  3. Don’t join multiple System Data Views in your query.  It’s a pain, but it’s a good practice to have a separate query for each activity (sends, opens, clicks, etc.).  Last resort is to make your own copies of the System Data Views with primary keys and refresh them on an interval.  The data extension versions will perform better.
  4. Make sure your where-clause conditionals are sargable to reduce the number of type-conversions and to optimize the selection:
  1. If it’s still timing out, leave a comment below with some details. I have some other tricks to share.

De-duplicate rows in AMPScript

Sometimes using a Query Activity to de-duplicate data before an email send isn’t feasible. You can do it in AMPScript if you don’t mind sorting your external rows.

In this code block, you just need to use a variable to keep track of the previous value that you’ve displayed. When the current row doesn’t match the previous one, output it.

Reference

Exclude an email domain from a send

Sending tons of emails to certain email domains can be tricky, even if your sending IP address reputation is good.  In Salesforce Marketing Cloud, you can utilize an Audience Exclusion Script to isolate domains for special sending treatment — like throttling or sending at a certain time of day.

Here’s an example that utilizes the AMPScript domain() function and the emailaddr personalization string that’s present in every send context.

Email #1 – Exclude emails to comcast.net domains with an exclusion script:

domain(emailaddr) == "comcast.net"

Email #2 – Exclude emails except comcast.net domains with an exclusion script:

domain(emailaddr) != "comcast.net"

Email #2 would be configured with some throttling or be deployed only at a certain time of day.

Code goes here:

This solution will work for sends to Lists, Groups or Data Extensions.

If Exclusion Scripts are not enabled for your User-Initiated or Triggered Send Definitions, SFMC support can enable them for you.

Number Suffixes in AMPScript

Suppose you want to output a day of the month with a proper suffix (e.g. 1st, 13th). There aren’t any formatting functions in AMPScript to do it, but you can do it with a set of if-statements:

Output

1st
2nd
3rd
4th
5th
6th
7th
8th
9th
10th
11th
12th
13th
14th
15th
16th
17th
18th
19th
20th
21st
22nd
23rd
24th
25th
26th
27th
28th
29th
30th
31st

Once you’re satisfied that it works, you can simply remove the for-loop and un-comment the set @day = Format(Now(),"dd")) line

Reference