Expand my Community achievements bar.

SOLVED

How do i write a query in workflow such that i get number of click that user has performed on the email sent via adobe campaign?

Avatar

Level 2

How do i write a query in workflow such that i get number of click that user has performed on the email sent via adobe campaign?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Ajit_Tr ,

In query select targeting dimension as 'recipient' and filtering dimension as 'recipient Tracking log'

ParthaSarathy_0-1681886585815.png

Query Condition: [url/@type] equals to Email Click

ParthaSarathy_1-1681886617084.png

Complementary information > add data > Data linked to the filtering dimension > Data of the filtering dimension > add a new expression: count(@id)

ParthaSarathy_2-1681886745952.png

Below click on Advanced parameter > Disable all checkbox and enable 'Group data by targeting dimension element'

ParthaSarathy_3-1681886816219.png

 

Result:

ParthaSarathy_4-1681887028119.png

 

 

 

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

Hi @Ajit_Tr ,

In query select targeting dimension as 'recipient' and filtering dimension as 'recipient Tracking log'

ParthaSarathy_0-1681886585815.png

Query Condition: [url/@type] equals to Email Click

ParthaSarathy_1-1681886617084.png

Complementary information > add data > Data linked to the filtering dimension > Data of the filtering dimension > add a new expression: count(@id)

ParthaSarathy_2-1681886745952.png

Below click on Advanced parameter > Disable all checkbox and enable 'Group data by targeting dimension element'

ParthaSarathy_3-1681886816219.png

 

Result:

ParthaSarathy_4-1681887028119.png

 

 

 

Avatar

Community Advisor

The above query condition will provide Email click counts of all deliveries/campaign in the instance.

To filter email counts on any specific campaign or delivery, then you can add additional conditions like 'deliveryCode of delivery equals to' or 'internal name of delivery equals to' or 'internalName of Campaign equals to' depends on the requirement.

ParthaSarathy_0-1681887375664.png

 

Avatar

Employee Advisor

@Ajit_Tr ,
To retrieve the number of clicks that a user has performed on an email sent via Adobe Campaign, you can use the nms:trackingLogRcp schema in a workflow query activity, like -

select count(*) as Clicks
from nms:trackingLogRcp
where deliveryId = '<deliveryID>'
and linkLabel is not null
and targetMode = 'M'
and isProcessed = 1
and recipientId = '<recipientID>'

You will need to replace the <deliveryID> and <recipientID> placeholders with the appropriate values.

The query filters the tracking log records based on the following criteria:

  • deliveryId: The ID of the delivery you want to retrieve the clicks for. Replace <deliveryID> with the actual delivery ID.
  • linkLabel is not null: Filters only the tracking logs that have a non-null linkLabel, which means a link was clicked.
  • targetMode = 'M': Filters only the tracking logs with a target mode of 'M', which means the click was performed on a link in an email.
  • isProcessed = 1: Filters only the tracking logs that have been processed.
  • recipientId: The ID of the recipient you want to retrieve the clicks for. Replace <recipientID> with the actual recipient ID.

Note that the tracking log records are only available for a limited time after the delivery is sent, so it is important to run this query soon after the delivery has been sent to ensure the tracking data is still available.

Avatar

Administrator

Hi @Ajit_Tr,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!



Sukrity Wadhwa