We all love ampscript as it’s so incredibly versatile and sometimes we need to use it as part of an automation when a SQL query won’t do. In Automation Studio the Script Activity allows you to add coding functions to automations, but unfortunately the only language that can be used is SSJS.

A way around this limitation is to use the SSJS ContentBlockByKey platform function: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_platformContentContentBlockByKey.html

In this blog post we’ll walk you through the steps to leverage ampscript in your SSJS script inside of an Automation Studio Script Activity.

Here are the steps:
(1) Determine when it makes sense to use Ampscript in an automation script
(2) Create a code snippet content block
(3) Test and validate the content block in an email
(4) Create a script activity using the ContentBlockByKey platform function
(5) Test and validate the script in Automation Studio
(6) Deploy

Step I. Determine when it makes sense to use Ampscript in an automation script
SQL is very powerful and should be the go to code when creating automation logic. What SQL doesn’t do well is looping through data. It also can’t update fields in Salesforce. Let’s take a look at the following use case and determine if ampscript is a viable solution.

Use Case: This use case is based on an actual lead nurture program we are conducting here at Cervello.

Marketing wants to send a drip campaign to leads each week. In Salesforce Leads are loaded into a campaign where they need to receive a series of emails. After each send they want to update the lead and campaign member status in salesforce in two ways:
a.) They want to update the Acquisition Program field on the lead to “PrivacyCenterDrip”.
b.) They want to update the status of the campaign member from “Added” to “Email 1 Sent”.

What SQL can do: Our SQL query can populate a Salesforce Send data extension of leads in the campaign to send the emails.

What SQL can’t do:
(1) It can’t loop through each campaign member in the campaign.
(2) It can’t update the lead field or the campaign member field.

We could utilize SSJS in the script activity to accomplish the above, but the team is more comfortable in ampscript and would prefer using that code.

So let’s build out a content block in Ampscript that we can leverage in an Automation Studio Script Activity.

Step II. Create a code snippet content block

(1) In your content folder.
(2) Select the “Create” button in the upper right hand corner.
(3) Select “Content Blocks”.
(4) Select “Code Snippet”

(1) Always open your snippet with %%[ and close it (line 33) with ]%%.
(2) It’s always best practice to declare the variables that you will be using in your ampscript code.
(3) These are the key variables we will be using in the lookuprows loop code below.
(4) To loop through all leads sent in the automation step before this activity we’ll want to use LookupRows. Here’s a walkthrough of how to set up a standard Lookuprows syntax featured here: https://handsonsfmc.com/part-3-lookuprows-function/
(5) In each loop we are determining the lead ID and campaign member ID for our update record function.
(6) Utilize the UpdateSingleSalesforceobject ampscript function outlined here: https://ampscript.guide/updatesinglesalesforceobject/. We are updating the status of the campaign member to “Email 1 Sent” and the lead field “Acquisition Program” field to “PrivacyCenterDrip”.
(7) To validate the functionality in an email we’ll use the CONCAT function: https://ampscript.guide/concat/ to test output each update in an email in our test validation step.

Don’t forget when saving your content block to name the key accordingly:

(1) When saving/naming your content block.
(2) You can name the Customer Key the same name as the block or leave it blank and Marketing Cloud will fill in a generic name which will be a series of numbers.

For visibility into the code above, here’s the data extension that will be populated with leads after they are sent the email in the automation.

(1) The name of the data extension.
(2) The lead ID named “SubscriberKey”
(3) The campaign member ID.
(4) And the flag for the loop to find all records in the DE with “Sent” in the Sent field.

Step III. Test and validate the content block in an email
Let’s leverage this content block in our email to validate before creating the script.

(1) In our email content.
(2) Our best practice is to have a code content block at the top of our email to capture the ampscript coding vs inline.
(3) That code block is always a Code Snippet block.
(4) All we need to do is call the block using the contentblockbykey ampscript function: https://ampscript.guide/contentblockbykey/. Make sure you enter in the right name of the content block or use the selector which will pop up if you type contentblockbykey( and wait a second.
(5) And output the variable set in the content block above to validate lead and campaign member ids.

(1) Doing a preview and test.
(2) Select the data extension navigating by clicking on the blue folder.
(3) Validate in the email content the lead and campaign member ids. You can see two records outputted here thanks to the Concat function.

Step IV. Create a script activity using the ContentBlockByKey platform function
Now that the ampscript is validated let’s create a script activity so we can add it to the automation process.

(1) Create a Script Activity.
(2) Open the script with the standard syntax.
(3) Declare a variable with the name of the content block.
(4) Use the ContentBlockByKey platform function: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_platformContentContentBlockByKey.html. You can use ID as well if you prefer.
(5) Close out the script. And that’s it!

Step V. Test and validate the script in Automation Studio

(1) Create an automation.
(2) Add the script to your flow.
(3) Click Run Once and select the script.
(4) You can refresh the automation while it runs.
(5) If all goes well the script activity will turn green.

Let’s check in Salesforce CRM to validate the fields have been updated, first stop the lead field.

(1) For test lead number 1.
(2) The Acquisition Program field was blank.
(3) And it’s not populated as expected.

(1) And on the campaign member statuses.
(2) Both leads started with a status of “Added”.
(3) Which are now “Email 1 Sent”.

Note: For this use case you could simply include the UpdateSingleSalesforceobject in the code snippet block for each email vs creating a separate script. We thought it was a viable example to use as a use case.

For those who either aren’t as comfortable in SSJS or find ampscript has more flexibility as a code language all you need to do is drop your ampscript into a code snippet block and call it via the platform function!

Please share any ideas or use cases where you’ve leveraged ampscript in an automation.