Expand my Community achievements bar.

SOLVED

Contact History

Avatar

Level 2

Hi folks, need some help with a query/report in Campaign.

I am trying to find out "of the last n emails received by a recipient a count of how many they have opened and clicked."

Thanks,

blue

1 Accepted Solution

Avatar

Correct answer by
Level 10

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.

View solution in original post

18 Replies

Avatar

Level 10

Hi Blue,

I am doing some tests and I'll get back to you as soon as I get some feedback from our data management experts.

 

Florent.

Avatar

Level 2

Thank you Florent. Appreciate the help.

Avatar

Correct answer by
Level 10

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.

Avatar

Level 10

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.

Avatar

Level 2

Hi Florent,

This is great. I am encountering an error along the way though and its probably something straight forward I am not doing. I cannot see a field for @eventDate in the broadlogRcp1 etc...

Thanks,

Neil

Avatar

Level 2

Hi Florent,

This is great. I am encountering an error along the way though and its probably something straight forward I am not doing. I cannot see a field for @eventDate in the broadlogRcp1 etc...

Thanks,

Neil

Avatar

Level 10

Hi Neil,

I just noticed I forgot to mention one part: When adding additional data to the query, you have a last step in the wizard right after you select the sort order. This step is used to select a field that you want to add to the additional data and pass to the output transition of the workflow.

This should resolve the issue you have.

I'll edit my previous post to add this information.

Thanks,

Florent.

Avatar

Level 10

Hi Neil,

I just noticed I forgot to mention one part: When adding additional data to the query, you have a last step in the wizard right after you select the sort order. This step is used to select a field that you want to add to the additional data and pass to the output transition of the workflow.

This should resolve the issue you have.

I'll edit my previous post to add this information.

Thanks,

Florent.

Avatar

Level 2

Thanks, I am now getting a different error:

17/10/2016 14:17:31    504734_3_1_G W3 JOIN wkf369504734_3_1 W2 ON (W2.iRowId = W3.iRowId) WHERE (((T1.tsLog > Coalesce(W2.tsBroadLogRcp3Event , W2.tsBroadLogRcp2Event , W2.tsBroadLogRcp1Event)) AND (T1.iUrlId <> 1)) AND (W2.iRowId = W.iRowId)) AND ((T1.iTrackingLogId > 0 OR T
17/10/2016 14:17:31    1.iTrackingLogId < 0)) GROUP BY T1.iRecipientId, W2.iRowId' could not be executed (error in position 96: 'T1.iTrackingLogId,').
17/10/2016 14:17:31    Oracle error: 'ORA-00979: not a GROUP BY expression'
17/10/2016 14:17:31    SQL statement 'INSERT INTO wkf369504734_3_2 (iTracking_logs__Click_Id,iKeysPKey0,iKeysPKey1) SELECT DISTINCT   T1.iTrackingLogId, T1.iRecipientId, W2.iRowId FROM  NmsTrackingLogRcp T1  JOIN wkf369504734_3_1 W ON (W.iLinksKey1_0=T1.iRecipientId) ,  wkf369

Avatar

Level 2

Thanks, I am now getting a different error:

17/10/2016 14:17:31    504734_3_1_G W3 JOIN wkf369504734_3_1 W2 ON (W2.iRowId = W3.iRowId) WHERE (((T1.tsLog > Coalesce(W2.tsBroadLogRcp3Event , W2.tsBroadLogRcp2Event , W2.tsBroadLogRcp1Event)) AND (T1.iUrlId <> 1)) AND (W2.iRowId = W.iRowId)) AND ((T1.iTrackingLogId > 0 OR T
17/10/2016 14:17:31    1.iTrackingLogId < 0)) GROUP BY T1.iRecipientId, W2.iRowId' could not be executed (error in position 96: 'T1.iTrackingLogId,').
17/10/2016 14:17:31    Oracle error: 'ORA-00979: not a GROUP BY expression'
17/10/2016 14:17:31    SQL statement 'INSERT INTO wkf369504734_3_2 (iTracking_logs__Click_Id,iKeysPKey0,iKeysPKey1) SELECT DISTINCT   T1.iTrackingLogId, T1.iRecipientId, W2.iRowId FROM  NmsTrackingLogRcp T1  JOIN wkf369504734_3_1 W ON (W.iLinksKey1_0=T1.iRecipientId) ,  wkf369

Avatar

Level 10

Hi Neil,

Can you share more details ? For example, in the "Execution" tab of the workflow properties, you can check "Log SQL queries in the journal" and run the workflow again. Some more information should display in the "SQL logs" tab of the workflow.

Also, can you share the XML code of the Enrichment activity ? To do that, simply select it (without opening it) and hit ctrl + F4. This should display the source XML of the workflow and we'll be able to have a look at what could be missing.

Thanks,

Florent.

Avatar

Level 10

Hi Neil,

Can you share more details ? For example, in the "Execution" tab of the workflow properties, you can check "Log SQL queries in the journal" and run the workflow again. Some more information should display in the "SQL logs" tab of the workflow.

Also, can you share the XML code of the Enrichment activity ? To do that, simply select it (without opening it) and hit ctrl + F4. This should display the source XML of the workflow and we'll be able to have a look at what could be missing.

Thanks,

Florent.

Avatar

Level 2

Hi Florent,

I sent the details in a PM.

Thanks,

Neil

Avatar

Level 2

Hi Florent,

I sent the details in a PM.

Thanks,

Neil

Avatar

Level 10

Ok,

I'm editing the procedure again as a substep is missing.

What you can do is "Edit additional data" in your enrichment activity, and proceed as follows:

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

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

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

4. Click "Finish".

5. Repeat step 1 to 4 for the second node of additional data.

 

This should correct the issue you have. Sorry I forgot to add this part in the first place.

Florent.

Avatar

Level 10

Ok,

I'm editing the procedure again as a substep is missing.

What you can do is "Edit additional data" in your enrichment activity, and proceed as follows:

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

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

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

4. Click "Finish".

5. Repeat step 1 to 4 for the second node of additional data.

 

This should correct the issue you have. Sorry I forgot to add this part in the first place.

Florent.

Avatar

Level 2

Thank you Florent.

Works perfectly. Appreciate your help and patience.

Cheers,

Neil

Avatar

Level 2

Thank you Florent.

Works perfectly. Appreciate your help and patience.

Cheers,

Neil