Refining targets by combining query results

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

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

 Transcript

In this module you’ll learn to combine the results of multiple queries. Campaign provides functionality that allows you to combine the results of multiple queries. Splitting the criteria out into multiple queries really comes down to how you want to design your workflow.

Setting up multiple queries and then combining their results in the workflow allows you to more easily visualize, construct, and debug your targeting logic. You can perform more advanced targeting logic and query against different tables in the database.

This approach also allows you to view subpopulation counts and end-term results during the execution of the workflow. The three activities that allow you to combine the results of multiple queries are the Intersection, the Union, and the Exclusion activities and they are all found within the targeting palette of the workflow designer. In this module we’ll focus on the intersection in the Union. The exclusion activity will be covered in another module. The intersection activity keeps only the overlapping results of the incoming populations, or in other words, the profiles that belong to both subpopulations. Profiles that are only found in one of the subpopulations are not kept. In this example, the first query returns people that live in either Los Angeles or New York City and the other query returns men under 30. When you combine the results of these two queries with an intersection, you’ll only get the overlapping population, which results in men under 30 that live in either Los Angeles or New York.

The highest number of records that could be returned from the intersection is the size of the smallest population, which would occur if everyone in the smaller population was also part of the larger population. However, oftentimes you’ll get less results because not everyone from the smaller population is part of the larger population.

In this example the most we could get out of this intersection is 95, but we can see that only 14 results were returned. The Union combines the full results of two populations or two queries. Recipients that belong to either group will be included. The results are de-duplicated, so if some profiles exist in both populations, they will only appear once in the final result.

Typically, the total number of records from the union will be the sum of the two populations. However, you’ll get fewer records than that if there are duplicates. For this exercise we’ll use the campaign called Autumn Sales Offers as I show you how to refine targets within your Campaign workflow. On the Targeting and workflows tab, click Add, keep the default template, and set the label to Apparel Sale for Men, click Ok and let’s go ahead and save our workflow.

Now notice over here, if the name of your workflow is not appearing, just do a quick refresh here and we can see that everything is set correctly.

Now click on Properties and let’s go set our internal name.

Click Ok.

And again, go ahead and save your workflow. Now we’re going to drag a query over to the workflow.

Double click.

Click on Edit query, and we’re going to keep our targeting and filtering dimensions as the recipients and we’re going to select Filtering conditions and click Next.

In the Expression we’re going to click Edit expression.

And in the first condition we’re going to double-click on Gender. Equal to we’re going to keep and value we’re going to set to Male.

Click Add.

Click the Edit expression again. We’re going to do Age, so double-click Age.

In the Operator column, select less than, and in the Value, we’re going to set that to 80. So we’re looking for men under the age of 80. Click on the preview tab, right-click in the list and select Configure List. Let’s add the Age and the Gender column and click Ok.

You can see that the ages that are appearing here are less than 80. We can also do some sorting, so we could sort by age. If we do it the other way, we can see that it kind of maxes out at 79. I say kind of, because we’re not seeing all the results here but we do get a sense that the age is kind of maxing out at the 79, which is what we’re looking for. So go ahead and click Finish.

And let’s change here, the label. Set the label to Men less than 80 and go Ok. Now let’s join a second query onto the workflow.

Double-click and let’s go ahead and edit the query.

We’re going to double-click on the filtering conditions.

Edit the Expression.

And now we’re going to set the location. So you scroll down a bit, you can expand the Location and then we’re going to select the city, so just double-click on City.

And we’re going to use the operator that is called is included in. So if you scroll in that list, select is included in, select the Value column and then over on the right there’s this option Distribution of values, so we’re going to select that.

So this functionality displays the distribution of values of the selected field for the recipients. So 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 the target city, for example, 'cause we may want to target cities with the highest distribution values only. For example, we could just target Chicago and Houston since they have the highest percentage here. In our case, we’re going to select Los Angeles and New York, so go ahead and click Los Angeles and then we’re going to do a multi-select, so using your Control key and then we’re going to select New York.

And then click Ok, and Finish.

Here we can change the label to City is Los Angeles or New York.

And click Ok.

Now let’s execute the workflow and see the results of each query, but first we’re going to right-click on this location one, this location query, and go and select Do not enable, and go ahead and click Start. Just a note here that when I click Start, my workflow will actually automatically be saved, so it’s a step I can avoid. I don’t always have to click Save if I’m going to execute. So no errors occurred and I can see that my results are 7,589 for the age and the gender.

By setting this query to do not enable, it didn’t execute, even though the workflow executed. So this feature is helpful when you’re trying to configure your workflows and you only want to execute certain activities that have been already configured in the workflow, you want to maybe focus on one query, you can set the other ones to do not enable. So let’s go and change that, so to bring it back, we just set it to Normal execution, and then what we can do is a Restart, so go ahead and Restart the workflow. And hit Yes, confirm Yes.

And we can see our workflow is finished executing, we now have 95 results for the Los Angeles or New York filtering conditions and then we still have the same results here. Now we’re going to add our intersection activity. So in the Targeting palette, go ahead and select the Intersection, drag it over, and attach it to either of the queries and then just drag the other transition over to the intersection. So just make sure that they’re both connected and then we can go ahead and click Restart.

And now our results are down to 40. So we know that this worked, it’s actually done that intersection of recipients that are in both of these queries. Now, we could also want to do a union instead of these two. In this case, this is probably not the best use case for a union, but I just want to show you the functionality. So you can go ahead and right-click and Delete your activity, your intersection activity and then from the targeting palette again, just drag over a union and make sure it’s attached to both queries.

And that’s all there is to configuring it. Go ahead and restart your workflow. And we can now see that the results from the union are higher than this query here, so we know that we’re adding the amount of results from here as well. So this completes the exercise on refining targets. -

On this page