Category: AMPScript

In a triggered send scenario, there’s no chance to leverage query activities to prepare auxiliary data for display in an email. I recently had this request from a client:

Display a random set 6 of the products in a specific data extension in the body of this email.

That’s tricky with AMPscript since you can only retrieve ordered rows based on fields and values already in the data extension (e.g you can’t order by GUID(), random() or _customObjectKey with the lookup functions). There are also no arrays or sorting outside of the rowset lookup functions in AMPscript.

Here’s a solution that worked for me:

DataExtensionTest

EmailAddress	SubscriberKey	FirstName	LookupKey
doug@limedash.com	8473	Doug	1
suzy@limedash.com	5497	Suzy	1
dale@limedash.com	7114	Dale	1
barb@limedash.com	5767	Barb	1
curt@limedash.com	5152	Curt	1
nora@limedash.com	7014	Nora	1
leon@limedash.com	8225	Leon	1
lily@limedash.com	9496	Lily	1

(NOTE: I added a lookupKey column to my data extension with a default value to facilitate retrieving all of the rows)

Output 1


arr(1): {2}{6}{7}{4}{8}{3}{5}{1}
arr(2): 5497|7014|8225|5767|9496|7114|5152|8473
subscriberkey1: 5497, firstName: Suzy
subscriberkey2: 7014, firstName: Nora
subscriberkey3: 8225, firstName: Leon
subscriberkey4: 5767, firstName: Barb
subscriberkey5: 9496, firstName: Lily
subscriberkey6: 7114, firstName: Dale
subscriberkey7: 5152, firstName: Curt
subscriberkey8: 8473, firstName: Doug


Output2


arr(1): {7}{3}{5}{6}{8}{1}{2}{4}
arr(2): 8225|7114|5152|7014|9496|8473|5497|5767
subscriberkey1: 8225, firstName: Leon
subscriberkey2: 7114, firstName: Dale
subscriberkey3: 5152, firstName: Curt
subscriberkey4: 7014, firstName: Nora
subscriberkey5: 9496, firstName: Lily
subscriberkey6: 8473, firstName: Doug
subscriberkey7: 5497, firstName: Suzy
subscriberkey8: 5767, firstName: Barb


I’m sure there are other ways of approaching this. Know of a simpler way or an alternate approach? Please share!

Reference

• lookupRows
• multiply
• random
• replace
• buildRowSetFromString

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.)

Email/Template Code:

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:

JobID	DataSourceName	emailaddr
66172	DataExtension1	aspriggs@degdigital.com
66172	DataExtension2	aspriggs@degdigital.com

(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.
Reference:

• Personalization Strings
• Send Logging
• AttributeValue() function

If you need to calculate the first and last day of the current month in AMPscript, you can do it like this:

Output:

@today: 7/30/2017 8:25:22 PM
@monthOfThisMonth: 07
@yearOfThisMonth: 2017
@firstDayOfThisMonth: 7/1/2017 12:00:00 AM
@nextMonth: 8/30/2017 8:25:22 PM
@monthOfNextMonth: 08
@yearOfNextMonth: 2017
@firstDayOfNextMonth: 8/1/2017 12:00:00 AM
@lastDayOfThisMonth: 7/31/2017 12:00:00 AM


Reference:

• Now()
• DateAdd()
• DateParse()
• Concat()