Query delivery information querying-delivery-information

Open the Generic query editor and select the Recipient tracking logs schema.

In the Data to extract window, we want to create an aggregate to collect information. To do this, add the primary key (located above the main Recipient tracking logs element): Tracking log count is carried out on this Primary key field. The edited expression will be x=count(primary key). It links the sum of various tracking logs to a single email address.

To do this:

• Click the Add icon to the right of the Output columns field. In the Formula type window, select the Edit the formula using an expression option and click Next. In the Field to select window, click Advanced selection.

  

• In the Formula type window, run a process on the aggregate function. This process will be a primary key count.

  Select Process on an aggregate function in the Aggregate section and click Count.

  

  Click Next.

• Select the Primary key (@id) field. The count (primary key) output column is configured.

  

Select the other field to be displayed in the output column. In the Available fields column, open the Recipient node and choose Email. Check the Group box to Yes to group the tracking logs by email address: this group links each log to its recipient.

Configure column sorting so that the most active recipients (with the most tracking logs) are displayed first. Check Yes in the Descending sort column.

You must then filter the logs which interest you, i.e. those which are under 2 weeks old and which concern sales-related deliveries.

To do this:

• Configure data filtering. To do this, select Filter conditions then click Next.

  

• Recover tracking logs over a given period for a specific delivery. Three filtering conditions are necessary: two date conditions to set the search period between 2 weeks before the current date and the day before the current date; and another condition to restrict the search to a specific delivery.

  In the Target element window, configure the date starting from which tracking logs will be taken into account. Click Add. A condition line is displayed. Edit the Expression column by clicking the Edit expression function. In the Field to select window, choose Date (@logDate).

  

  Select the greater than operator. In the Value column, click Edit expression, and in the Formula type window, select Process on dates. Finally, in Current date minus n days, enter “15”.

  Click Finish.

  

• To select the tracking log search end date, create a second condition by clicking Add. In the Expression column, choose Date (@logDate) again.

  Select the less than operator. In the Value column, click Edit expression. For date processing, go to the Formula type window, enter “1” in Current date minus n days.

  Click Finish.

  

  Now we want to configure the third filter condition, i.e. the delivery label which our query concerns.

• Click the Add function to create another filtering condition. In the Expression column, click Edit expression. In the Field to select window, choose Label in the Delivery node.

  Click Finish.

  

  Look for a delivery containing the word “sales”. Since you don’t remember its exact label, you can choose the contains operator and enter “sales” in the Value column.

  

Click Next until you get to the Data preview window: no formatting is necessary here.

In the Data preview window, click Start the preview of the data to see the number of tracking logs for each delivery recipient.

The result is displayed in descending order.

The highest number of logs for a user is 6 for this delivery. 5 different users opened the delivery email or clicked one of the links in the email.

Drag and drop a Query activity in a workflow and open the activity.

Click Edit query and set the target and filtering dimensions to Recipients.

Select Filtering conditions then click Next.

Click the Add button and select Tracking logs.

Set the Operator of the Tracking logs expression to Do not exist such as.

Add another expression. Select Type in the URL category.

Then, set its Operator to equal to and its Value to Open.

Add another expression and select Date. Operator should be set to on or after.

To set the value last 7 days, click the Edit expression button in the Value field.

In the Function category, select Current date minus n days and add the number of days you want to target. Here, we want to target the last 7 days.

To target profiles having opened a delivery, you need to use tracking logs. they are stored in a linked table: start by selecting this table in the drop-down list of the Filtering dimension field, as shown below:

Concerning filtering conditions, click the Edit expression icon of the criteria shown in the sub-tree structure of the tracking logs. Select the Date field.

Click Finish to confirm selection.

In order to recover only the tracking logs less than two weeks old, select the Greater than operator.

Then click the Edit expression icon in the Value column to define the calculation formula to be applied. Select the Current date minus n days formula and enter 15 in the related field.

Click the Finish button of the formula window. In the filtering window, click the Preview tab to check targeting criteria.

Insert the Split box into the workflow. This box will break down the recipients of the first delivery into the next two deliveries. Breakdown occurs based on the filtering conditions linked to recipient behavior during the first delivery.

Open the Split box. In the General tab, enter a label: Split based on behavior for instance.

In the Subsets tab, define the first split branch. For example, enter the Clicked label for this branch.

Select the Add a filtering condition on the incoming population option. Click Edit.

In the Targeting and filtering dimension window, double-click the Recipients of a delivery filter.

In the Target element window, select the behavior you want to apply to this branch: Recipients having clicked (email).

Below, select the Delivery specified by the transition option. This functionality will automatically recover the people targeted during the first delivery.

This is the “Watersports offer” delivery.

Define the second branch. This branch will include the follow-up email with the same content as for the first delivery. Go to the Subsets tab and click Add to create it.

Another sub-tab is displayed. Name it “Did not click”.

Click Add a filtering condition for the incoming population. Then click Edit….

Click Delivery recipients in the Targeting and filtering dimension window.

In the Target element window, select the Recipients who did not click (email) behavior. Select the Delivery specified by the transition option as shown for the last branch.

The Split box is now fully configured.