Contact History

Hi Blue,

Here is an idea of what can be done in order to achieve this, by using both Query and Enrichment activities in a workflow. The Query activity will retrieve the last "n" deliveries sent to each recipient, while the Enrichment activity will analyze the tracking of these deliveries, still for each recipient.

1. Create a new workflow.

2. Add a Query activity and configure it this way:

                a. Target recipients by setting filtering and targeting dimensions to Recipients.

                b. Add the last "n" recipient delivery log as additional data from data linked to the filtering dimension. To do that, simply select the "Recipient delivery logs" collection. Select the number of lines you want to retrieve ("n") for each recipient and add a descending sort condition on the event date to be sure you retrieve the last "n" deliveries sent to a recipient. In the last step of the wizard, add the eventDate field to the Data to add. You can find more information about adding additional data in a query here.

3. Save the configuration of the Query activity.

4. Add an Enrichment activity connected to the Query, and configure it as described below. It will count opening and click tracking logs that were recorded after the oldest contact date of the "n" deliveries taken into account.

                a. Add additional data linked to the filtering dimension to add an aggregate on the Tracking logs collection that will retrieve one line.

                b. Add a filter on the logDate field to only take into account tracking events recorded after the oldest delivery taken into account in this use case was sent. It also allows to handle the case where a recipient would have received less than the "n" deliveries you want to track. You can use the following function as value for the condition (for example if "n" = 3) in order to retrieve the first non NULL value for the event date of the delivery : 

Coalesce([mainTargetData/broadLogRcp3/@eventDate] , [mainTargetData/broadLogRcp2/@eventDate] , [mainTargetData/broadLogRcp1/@eventDate])

                c. Use a AND condition to add a filter on the url-id foreign key in order to track openings (using url-id=1). To find the url-id foreign key among the available fields, make sure you select the Display advanced fields option

                d. Repeat steps a. to c. and modify only the condition for the url-id foreign key in order to track clicks. Here url-id needs to be "not equal to" 1.

5. You should now have two additional data in the Enrichment activity. Select "Edit additional data" and proceed as follows:

                a. Select the first node and click "Add sub-item".

                b. In the wizard that opens, select "Advanced selection" and specify "Aggregate" > "Count". Select the "Distinct" checkbox.

                c. Click next and select the "Foreign key of the link 'Message' (field 'id') (@broadLog-id)" field. If you don't see this field, select the "Display Advanced field" button.

                d. Click "Finish".

                e. Repeat step a. to d. for the second node of additional data.

                Note: you can find screenshots for these steps in a separate reply below.

6. Save the activity and the workflow.

7. Execute the workflow. You should be able to see the results in the output transition of the Enrichment activity.

Hope this helps,

Florent.