Create an export workflow (Part 1) - Find the last modified date for a list of recipients

Last update: 2023-09-22
  • Created for:
  • Beginner
    Experienced
    Admin

In this first part of the Create an Export Workflow tutorial, learn how to create a workflow that finds the last modified date for a list of recipients created from an Experience Platform segment.

 Transcript

In this video, we are going to begin creating the workflow that will be used to export our campaign data to a storage location. Let’s start by creating a workflow in Campaign. To create a new workflow, select “Explore,” followed by selecting “Jobs,” within “Profiles and Targets.” Within “Jobs,” we can select “Targeting workflows” to view a list of our available workflows. In the top right, select the “New” icon and a pop-over appears. Let’s call the workflow “Experience Platform Campaign Logs Export.” To begin, let’s select the “Flow controls” tab, then drag and drop “Start” to our workflow. Next, we want to connect a JavaScript code action. Within the “Actions” tab, drag, and drop JavaScript code to our workflow, attaching it to the “Start.” Once the JavaScript code action is on our workflow, we want to double-click it to open the JavaScript code pop-over editor.

Start by renaming the label. Because this is a JavaScript file, we will be defining date ranges for our workflow. So let’s give it the name “Established Date Ranges for Query.” Note that this file is not a query. Its purpose is to return values used in a subsequent query that we will define later in this video. Using the file provided in the “Asset” section beneath the video, copy and paste the JavaScript code to the script window in “Campaign.” Once you have the JavaScript, you are technically ready to proceed. However, before we select “Ok,” let’s take a closer look at what the code is doing. At the top, we have a variable called “DEFAULT_LOOKBACK_DAYS.” This means that by default, this workflow will look back ninety days, meaning the last ninety days worth of logs will be exported in the initial run of the workflow. It’s important to note that after that initial ninety days, only incremental changes in the log will be exported going forward.

If you wanted to change the “Lookback” window to say sixty days or a hundred and twenty days, you can change the default ninety value. However, it is not recommended that you change any of the other values in this file. This is because Campaign looks for some of the defined values, such as the “Broadlog_CaptureTime” variable, which is written to “Attempt table” in Campaign. Ultimately, we are provided with two persistent values, the lastRunTime, and the nextRunTime. These values are only accessible within this particular workflow that we’re building. Now that we know what the code is for, select “Ok” to continue. It would also be a good time to run the workflow, to see if any errors are present in our JavaScript code. Everything should run and the status should say “Paused.” Next, we want to make use of our variables by querying our data, using the last modified date. To do this, we need to drag and drop “Query” and attach it to our JavaScript code. Upon double-clicking, let’s name the query “Query By Last Modified.” Once complete, within the “Query” pop-over, select “Edit query.” The “Targeting and filtering” dimension pop-over appears. Within the “Targeting” dimension, search for and select “Recipient delivery” logs .

This will change our filtering dimension to “Recipient delivery” logs.

Next select “Filtering conditions.” Then, select “Next” to open the filter pop-over.

We want to query all our data using an SQL statement. To do this, let’s start by selecting the “Expression” button and from the available fields select “Last modified.” After defining our expression, select the “Operator” dropdown and set the operator to “after.” Lastly, we need a value. Select the “Value” button to bring up the “Formula type” pop-over. We want to select the “Expression radio” button because we want to use our last runtime variable from the JavaScript. Enter the following expression in the edit zone exactly as shown on my screen.

Once complete, select “Finish” and the value should populate as “Value of parameter vars/@lastRunTime.” Next, we need to add an additional expression to use next run time. Select “Add again,” followed by selecting “Last modified” and selecting “on or before” as the operator. Similar to the previous value, we want to enter an expression, but this time use “next RunTime” instead of “last RunTime.” Check to see if your query matches, and once complete select “Finish,” followed by selecting “Ok.” If you run your workflow now, you should see results being returned from the query.

Right-click the results, followed by selecting “Display the target,” and the records that were queried are displayed. You should now have a workflow that uses JavaScript and a query to find the last modified date for our list of recipients created from an experience platform segment. In the next video, we will finish this workflow by uploading and saving our logs data to an external account. Thanks for watching. -

Assets

JavaScript to establish date ranges:

 var DEFAULT_LOOKBACK_DAYS = 90;
 vars.OPTION_NAME = "BroadLog_CaptureTime";

 logInfo("=====================");
 logInfo("Starting Execution...");

 // Establish the last and next RunTimes
 var lastRunTime = getOption(vars.OPTION_NAME);
 var nextRunTime = getCurrentDate();

 //To reset and run through DEFAULT_LOOKBACK, uncomment the following line.
 //lastRunTime = null;

 logInfo("NEXT Run Date Set: [" + nextRunTime + "]");
 logInfo("LAST Run Date Retrieved (" + lastRunTime + ")");

 //Check for null so we can default the lastRunTime using the DEFAULT_LOOKBACK
 if (lastRunTime == null || lastRunTime == "null" || lastRunTime == "") {

   logInfo("Empty Date Retrieved, setting to default lookback (-" + DEFAULT_LOOKBACK_DAYS + " days)");
   lastRunTime = new Date();
   lastRunTime.setDate(nextRunTime.getDate() - DEFAULT_LOOKBACK_DAYS);
   logInfo("LAST Run Date Set: [" + lastRunTime + "]");

 }

 //Persist values through execution of this instance of the workflow.
 vars.lastRunTime = lastRunTime;
 vars.nextRunTime = nextRunTime;

 logInfo("Finished Execution.");
 logInfo("===================");

Next video

Create an export workflow (Part 2) - Extract, format, and save data to an external account

On this page