Mastering Dynamic Queries in Adobe Campaign
When the time comes, you will find yourself in a situation where you would like to set up query builder condition expressions as dynamic values. We can achieve this and more with Adobe Campaign Classic. But what are those situations?
For example, I had to build a reporting workflow for automated campaigns that was divided by countries and recurrence. I used the very same workflow for all scenarios but had to manually amend the conditions whenever I encountered different countries or recurrences. This led me to make the conditions in various queries dynamically, adding the values of country and recurrence in all necessary queries.
Another use case commonly used is when a workflow template is applied across various marketing campaigns or technical workflows. In such cases, the initial settings are defined as instance variables with values set in a JavaScript activity. These variables can not only dynamically adjust the workflow’s behavior but also set query conditions as needed and where needed.
Additionally, you can utilize data from targeting dimensions and incorporate those values when sending emails. For example, when sending order statistics to customers, you would first select the relevant orders, store them in an array, and then format and display the data as a table within the email.
Another example could involve sending your sales representatives a list of recipients with invalid email addresses, prompting them to contact these recipients and resolve the issue with the broken email addresses.
//DL object initialization in the library loaded loaded to the workflow
//or
//within workflow properties script tab where upi can define global javascript functions
DL = {};//get data from the targeting dimension - pipeline with help of query def DL.data = data
Simply put, use dynamic conditions whenever you feel that centralizing values to one place is beneficial for you in terms of maintenance or replicating functionality.
Simple operators
When employing simple operators such as equal to, not equal to, contains, and starts with, we can utilize our instance or event variables as the value for the query condition.
$(vars/@myVar) $(instance/vars/@myVar)
When all is working fine and when leaving the value input you should see value of parameter 'vars/@myVar' anything else will cause issues.
Included in operators
Now that we understand the use of workflow variables in the query builder, challenges may arise when dealing with the ‘included in’ operator. When we use vars.incond = ‘1,2,’ Adobe Campaign’s query expression will be evaluated as ‘1,2’ instead of ‘1,2,’ thus giving us wrong or no results. Before I found an easier way on the forum, many others and I used the Initialization script of a query activity to access conditions and then create the ‘included in’ expression from the instance variables.
//expr="@firstName IN (1, 2)
vars.incond = "1,2";
activity.where[0].condition[0].condition.expr = "@firstName IN (" + vars.incond + ")";
//condition[0] means that its first conditionBut there is a better and simpler way to do it. We only need to tell Adobe Campaign not to escape quotes by wrapping our variable in the query with the $noescaping function. We can use it directly in query builder and there is no need to access initialization script.
$noescaping(vars/@incond)
$noescaping is a built in function that is used to indicate that the content within the function should not have its quotes or special characters escaped. This is particularly useful when you want to include a variable or value in a query expression without having it modified or escaped. It ensures that the content is treated as-is, without any additional formatting or escaping.
$string(vars/@myVar)casts your variable as string$long(vars/@myVar)casts your variable to be a long$int64(vars/@myVar)casts your variable to be an int64$date(vars/@myVar)casts your variable to be a date$datetime(vars/@myVar)casts your varibale to be a datetime$noescaping(vars/@myVar)content within the function should not have its quotes or special characters escaped.
