If you ever need an automated way to clear a data extension, you can simply use a SQL query that does an overwrite. The query doesn’t need a from clause if you specify any required columns in the target.
For example, if EmailAddress is required in the target data extension, simply set the value to null and add an alias:
I’m constantly faced with situations with data in Salesforce Marketing Cloud where I need to select or identify rows based on some ordered criteria. The row_number() over (partition by x order by y) as ranking is my go-to method.
This particular query finds a random row for each bounce category in the _bounce data view:
Recently, found this shorter method:
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