Query articles and recipient subscription values

Transcript

Hello, in this video, we are going to start a workflow that will alert recipients of any new published articles they are subscribed to via an email delivery, let’s get started. First, we want to navigate to Profiles and Targets, select Jobs followed by selecting Targeting Workflows. Next, within Targeting Workflows, select Create New Workflow and provide a label and description.

Because this workflow is going to alert subscribed recipients of any new articles that were published, let’s call it Newsletter Subscription Workflow.

Now that we have our empty workflow, we are ready to start. Speaking of start, let’s navigate to Flow Control and drag the start component to our workflow. We’re going to use this component to manually run the workflow. And once we have confirmed our workflow works as intended, we will swap this out with a scheduler to automate the workflow.

Next, within the same tab, drag and drop the fork component attaching it to our start component. Because we are querying both our subscribed recipients and our subscriptions table, we need to create two separate queries. Let’s select the Targeting tab and create our first query by dragging and dropping the query component. Upon double-clicking, we are able to provide a label and edit the query. Let’s call this query Find Subscribed Recipients. The purpose of this query will be to output all the recipients with active subscriptions to any of our newsletters. After naming our query, select Edit Query and the targeting and filtering dimension pop-over appears. We want to leave the targeting dimension on recipients because that’s where we want the outbound data to be. However the filtering dimension needs to be changed to the subscription of the recipient. We can select a subscription in the Filtering Dimension dropdown. After setting our dimensions, double-click Filtering Conditions. The filter pop-over appears. Here we are able to define our query.

Within the expression editor, select Edit Expression and select Creation Date. Then within the Operator’s dropdown, select is not empty.

Essentially what this simple query is doing is querying all of our subscribed users. Next, we need to query whether an article has been published in the last 24 hours. Every article that we publish has a tag associated with it. In a previous video, I published a hockey article and subscribed a recipient to the hockey newsletter. Because the subscription service and articles share the same tag, we want to query recipients with this in mind. Drag and drop a second query component, but this time let’s name the query Daily Article Filter. Similar to before, select Edit Query followed by updating our targeting dimension to Articles. Articles is a custom schema that was created in a previous video to upload and store our Arctica data. We do not need to change the filtering dimension. Next, double-click Filtering conditions and our Filter pop-over appears. This time, we want to filter our results to be within the last 24 hours. Because we are already targeting our articles via the targeting dimension, we only need to specify the time. To do this, we need a beginning and an end time. Start by selecting Edit Expression, then select Date. Next, within the Operator dropdown, select before. Now we need to set a value. To do this, select the Edit Expression icon followed by selecting the radio buttons for Process on dates and Current date. After selecting Finish, our query can now look back from the current time and date, filtering all our articles that have been published. However as I’m sure you are aware, we also need to add a second timeframe to prevent the query from surfacing all articles. To do this, select the Add Filtering Condition icon followed by entering the same date expression. Set the operator to after and within the value field, select the Process on Dates function followed by selecting the Current date minus n days radio button. Then set the value to one and select Finish. What this does is add another condition to our query to only look back one day or 24 hours before the current date slash time this was run. This effectively queries all our articles that were published in the last 24 hours since the article also has a date and time attached to it.

Now that we have both our queries, you may be wondering how do they join on one another? Currently, the way we have things structured, we are only going to receive articles that were published in the last 24 hours and all of our subscribed recipients. The problem we need to solve next is matching our recipient subscriptions with the articles that were published, preventing recipients who are subscribed to just hockey from receiving football email delivery notifications if no new articles were published.

To do this, we need to go back to our queries and add data via an output column which we will later intersect on. Start by double-clicking our first query, followed by selecting the Add data hyperlink text. Three options appear, select the first radio button, Data linked to the filtering dimension, followed by selecting Next. Again, select the first radio button, Data of the filtering dimension, then select Next. This populates your subscription fields. Because we want to grab the tags of the service, we need to select Service, followed by double-clicking Internal Name to add it to our output columns.

The service internal name is the same name we use to tag articles meaning the hockey service newsletter internal name is hockey and the tag we use for any hockey articles is hockey.

We can give this output an alias letting us rename it. Let’s call it Service Name, then select Finish.

Next, we want to update our second query. Select Add data and following the same workflow, select Data linked to the filtering dimension, followed by Data of the filtering dimension until we’re at that Data to add screen.

Within our articles schema is a tag field. Double-click Tag to add it to the output columns and give it the same alias name Service Name. However this time we also want to add the article name, date, description, and URL to the output columns as well. The reason we want these to be added to the output columns is because when we intersect our data, we will need to select a primary set. This means aside from the column we join on tags, we won’t have access to the target unless we open them at a higher level. Adding these article fields will make them persist as a top level item through the intersection allowing us to use them in our delivery attributes.

After finishing our update, we are ready to intersect our data. Drag and drop the intersection component and attach our queries to it. Once complete, double-click the component to open the intersection pop-over. The intersection allows us to join based on columns. Because we just added the service name output to both our queries, we can join the results allowing us to filter recipients with our subscription to any articles published in the last 24 hours. To set up our intersection, start by selecting the radio button called A selection of columns. Next, for the primary set, select the name of your first query. The reason we are using the disk query as the primary set is because it contains the emails of our recipients. The intersection will join based on the tags, then output the remaining primary set.

Lastly, we need to select the columns used for the join. Select the Add icon, followed by entering the field name. In our case, this is the Service Name field.

Once complete, select okay. With the intersection in place, we can now run the workflow to see if our results are being exported. After publishing an article and confirming I have recipients subscribed to a service with the same internal name as the tags of the articles, I expect the output to contain two recipients. After running the workflow, I can see my output is as I expected. We can also right click and select Display the target. This allows us to view our recipients, the service they are subscribed to, and the published article’s details.

You should now know how to query data using different targeting and filtering dimensions, as well as join the outputs using an intersection. In the next video, we will continue the workflow and send an automated personalized delivery to our recipients, thanks for watching. -