Refine targets by combining query results

Last update: 2023-09-22
  • Created for:
  • Beginner
    User

Learn how to refine your target by using intersection or union activities to combine query results in a workflow.

 Transcript

In this module, we’ll learn how to refine targets by combining the results of multiple queries using intersection and union activities. Adobe Campaign provides three activities through which we can combine the results of multiple queries. These are intersections, unions, and exclusions. Exclusion activities will be covered in a separate module. Using multiple queries enables us to perform more advanced targeting logic and query against different tables in our database. With multiple queries, we can also view subpopulation counts and interim results during the execution of a workflow. The way we choose to design our workflow will influence where and how we split criteria into different queries. Setting up multiple queries and then having the ability to combine their results in the workflow provides a simple way to visualize, construct, and debug our targeting logic. The union, intersection, and exclusion activities are found within the targeting palette of the workflow designer. The intersection activity keeps only the overlapping results of the incoming populations. In other words, profiles belonging to both populations are included, while any profiles that are only found in one of the populations are not. In this example, the first query returns people that live in either Los Angeles or New York City and the second query returns men under 30. When we combine the results of these two queries with an intersection, we get the overlapping population containing men under 30 that live in either Los Angeles or New York. The intersection result contains 14 profiles. However, an intersection could be equal to the total size of the smallest incoming population if every profile in one query was also part of the second. In other words, the intersection could have included a maximum of 95 profiles as there were 95 profiles returned in the smaller of the two query results. On the other hand, the union combines the full results of two populations or two queries, including recipients that belong to one or both groups. The total number of records from the union will be the sum of the two populations, unless there are duplicate records that exist in both groups. This is because the results of the union are deduplicated, to ensure that any results that exist in both populations will only appear once in the final result. For this exercise, we’ll refine targets within our campaign workflow using the Autumn Sales Offers campaign. On the Target and Workflows tab, select Add. We’ll keep the default template and set the label to Apparel Sale for Men, then select OK. Next, we’ll save the workflow. If the name of the workflow is not showing, we can hit refresh to ensure everything is appearing correctly. Now choose Properties and set the internal name. Once again, we can save our workflow. Next, drag our query result to the workflow and double-click on the query to edit it. Select Edit Query and keep the targeting and filtering dimensions as the recipients. Then select Filtering Conditions and Next. In the Expression, choose Edit Expression. For the first expression, double-click on Gender. Keep the operation as Equal To and set the value to Male, then select Add. For the second expression, double-click Age. In the Operator column, select Less Than and in the value, we’ll set that to 80. This query is now looking for men under the age of 80. To preview the results of the query, click on the Preview tab, then right-click on the list and select Configure List. Add to Age and Gender columns, then select OK. We can see that the ages in the preview appear to be under 80 so far. If we sort the results by Age Descending, we can see that the maximum age is 79. Keep in mind that the preview is not showing all of the query results, but we appear to be capturing the recipients we’re looking for. Now we can hit Finish and then set the label to Men Less Than 80 and select OK. Next, we’re going to add a second query to the workflow. Double-click the query to edit it and then select Query. We can double-click on the Filtering Conditions and then choose to edit the expression. For this expression, we’re going to set the location. Expand the location and then select the city by double-clicking on it. For the operator, we’re going to use Included In. For the Value column, we’re going to select Distribution of Values. This functionality displays the distribution of values for the selected field for the recipients. In this case, the selected field is City, so we can see a count and the percentage of recipients by city. We can use this feature to help us select a target city. For example, we may want to target cities with the highest distribution values only. In this case, we would target Chicago and Houston. In our case, we’re going to select Los Angeles and New York. We can do this by first selecting Los Angeles, then using the Ctrl key to multi-select New York. Once our cities are selected, choose OK, then Finish. We can change the label of our query to City of Los Angeles or New York. Now we can execute the workflow and see the results of each query. However, before we do that, we’re going to right-click on the location query and select Do Not Enable. Then we can choose Start. Note that when we select Start, our workflow will be automatically saved, so we don’t need to click separately to save the workflow. After the workflow executes, we can see that the age and gender query results in 7,589 records. By setting the location query to Do Not Enable, it did not exclude location, even though the workflow did. This feature is helpful when configuring and testing a workflow so that we can focus on certain activities, such as a single query. After changing the location query back to normal execution, we can restart the workflow by clicking Restart. We can see that our workflow is finished executing, and that we now have 95 results for the Los Angeles or New York filtering conditions and that we still have the same results for the age and gender query. Now we’re going to add our intersection activity. In the targeting palette, select Intersection and drag the activity over the workflow, attaching it to either of these queries. We can then drag the transition for the second query over to the intersection. After confirming that the intersection is connected to both of our queries, we can restart our workflow. After the workflow executes, we can see that our results are down to 40 recipients. These 40 records represent the recipients that are part of both the age and gender and location queries. We could also experiment using a union activity here, by right-clicking on the intersection and deleting it. Then from the targeting palette, we can drag and drop a union activity into the workflow, once again making sure to attach it to both queries. When we restart the workflow, we can see from the results that we are adding the query results together, generating a high total number of recipients. We have successfully refined our targets by combining the results of multiple queries using intersection and union activities.

On this page