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.

T-SQL Split Queries

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:

Calculate the Last Day of the Month in T-SQL

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:

Stripping Times from T-SQL Dates

When you’re writing queries in Salesforce Marketing Cloud sometimes it’s helpful to truncate the time from date columns — like when you want to compare two date columns, one with a time and one without. You can strip the time off of both to make sure you’re comparing apples to apples.

Here’s an example:

The first column returns the full date and time, the second column cast strips the time portion.

date with timedate without time
2013-04-04 14:37:39.3632013-04-04 00:00:00.000

For further reading:

Geolocation in the Salesforce Marketing Cloud

How about targeting your Salesforce Marketing Cloud subscribers by zip code? All it takes is a zip code data and a query. There’s a few bits and pieces in the SFMC documentation about it (you’ll need to scroll down to the Geo-Targeting section).  I thought it would be helpful to see a use-case. This example calculates the closest store for each subscriber.

You’ll need:

  • A subscriber data extension that includes a zip code column
  • A store data extension that includes a zip code column
  • A zip code data extension that includes X, Y, and Z-axis columns
  • A query that writes to a new data extension

Subscriber_Master_List

EmailAddressFirstNameZip
neo@matrix.comNeo11111
morpheus@matrix.comMorpheus11112
trinity@matrix.comTrinity11113

Store_Location

Store_NumberStore_ZipStore_Phone
55511115555-222-2222
44411116555-333-3333

Zip_Axis

ZipLatitudeLongitudeXAxisYAxisZAxis
1111145.429654-65.9824260.28563807-0.641024400.71238935
1111245.427139-65.9953440.28550626-0.641117350.71235855
1111345.427138-65.9953440.28550627-0.641117360.71235853
I’ve seen people simply store the latitude and longitude for each zip and then calculate the X, Y, and Z values in the SQL query. However, I prefer calculating those columns beforehand. The queries will be faster and cleaner.

Target Data Extension

EmailAddressFirstNameZipStore_ZipStore_PhoneDistance
neo@matrix.comNeo1111111115555-222-22224
You can certainly add more store-related columns to this data extension. For simplicity, I only included a few columns.

Geolocation Query

This query pulls it all together. The round function and case statements do the dirty work of the calculations between the two zip codes. The ranking and partition part picks out the closest store per subscriber.

 

For further reading:
Data Extensions
Query Activities
Import Activities
Microsoft Transact-SQL (T-SQL) Reference
Distances between Zips (for verification)