Say you wanted parse some field values:
…and output something like this in Marketing Cloud:
Since you can’t use any of the newer T-SQL functions for parsing in Marketing Cloud, you can use Cross Apply to determine the positions of the delimiters and then simple sub-strings to extract each one.
Need to custom one-click unsubscribe landing page in AMPscript? Here’s how I generally code it:
This leverages the AMPscript API functions and mirrors the process you’d take if you were doing this with the Marketing Cloud SOAP API.
In Marketing Cloud, there’s not really a good way of finding the original data source for a subscriber in a send — especially if you’re sending to multiple data sources for every send.
There is, however, a personalization string called _DataSourceName, outlined on the Data About the Recipient section of the Personalization Strings page in the documentation.
If you’d like to record this data, you can simply add an AMPScript variable in your email or template and set it to _DataSourceName. If your Send Log Data Extension has a column matching the variable name, it’ll get recorded for all of your sends. (Fields that start with an underscore aren’t allowed in Data Extensions, so you can’t just name it the same as the personalization string in order to record it.)
Send Log Data Extension:
If I do a single User-Initiated Send to multiple Data Extensions (DataExtension1 and DataExtension2), the following is written to the Send Log:
(I removed ListID, BatchID, SubID, TriggeredSendID, ErrorCode and SendDate columns from the above table for clarity)
Once you have this data in your Send Log Data Extension, you can correlate it with other data using a Query Activity or export for use outside Marketing Cloud.
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.
MSDN – Using Cross Joins
If you need to calculate the first and last day of the current month in AMPScript, you can do it like this:
@today: 7/30/2017 8:25:22 PM
@firstDayOfThisMonth: 7/1/2017 12:00:00 AM
@nextMonth: 8/30/2017 8:25:22 PM
@firstDayOfNextMonth: 8/1/2017 12:00:00 AM
@lastDayOfThisMonth: 7/31/2017 12:00:00 AM
If you only have the full name for a subscriber and you’d like to use only the first name for a greeting, you can do something like this.
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 subscriber key and assigns a
ranking to each row. The outermost query selects only the oldest result per subscriber key.
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:
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:
- 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.
- 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.
- 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.
- Make sure your where-clause conditionals are sargable to reduce the number of type-conversions and to optimize the selection:
- If it’s still timing out, leave a comment below with some details. I have some other tricks to share.