Query delivery information

March 6, 2025

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:

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

  2. 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.

  3. 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.

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

  5. 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.

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

  7. 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:

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

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

  3. Select Filtering conditions then click Next.

  4. Click the Add button and select Tracking logs.

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

  6. Add another expression. Select Type in the URL category.

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

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

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

  10. 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 should be set to Tracking logs (Recipients).