Querying delivery information
Number of clicks for a specific delivery
In this example, we are looking to recover the number of clicks for a specific delivery. These clicks are recorded thanks to recipient tracking logs taken over a given period. The recipient is identified via their email address. This query uses the Recipient tracking logs table.
- Which table needs to be selected?The recipient log tracking table ( nms:trackingLogRcp )
- Fields to be selected for output columns?Primary key (with count) and Email
- What criteria will information be filtered based on?A specific period and an element of the delivery label
To carry out this example, apply the following steps:
- 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.
Recipients who did not open any delivery
In this example, we want to filter recipients who didn't open an email in the last 7 days.
To create this example, apply the following steps:
- 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.
Your outbound transition will contain recipients who didn't open an email in the last 7 days.
If, on the opposite, you want to filter recipients who opened at least one email your query should be as follows. Please note that, in this case, the Filtering dimension shoud be set to Tracking logs (Recipients) .
Recipients who have opened a delivery
The following example shows how to target profiles who have opened a delivery within the last 2 weeks:
- 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.
Filtering recipients' behavior folllowing a delivery
In a workflow, the Query and Split boxes let you select a behavior following a previous delivery. This selection is carried out via the Delivery recipient filter.
- Aim of the exampleIn a delivery workflow, there are several ways of following up a first email communication. This type of operation involves using the Split box.
- ContextA "Summer sports offer" delivery is sent. Four days after the delivery, two other deliveries are sent. One of them is "watersports offer", the other is a follow-up to the first "Summer sports offer" delivery.The "Watersports offer" delivery is sent to recipients who clicked the "watersports" link in the first delivery. These clicks show that the recipient is interested in the topic. It makes sense to steer them towards similar offers. However, recipients who did not click in the "Summer sports offer" will receive the same content again.
The following steps show you how to configure the Split box by integrating two different behaviors:
- 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.
Below is the list of the various components configured by default:
- All recipients
- Recipients of successfully sent messages,
- Recipients who opened or clicked (email),
- Recipients who clicked (email),
- Recipients of a failed message,
- Recipients who didn't open or click (email),
- Recipients who didn't click (email).