Query for number of deliveries sent/opened/clicked per recipient per delivery | Community
Skip to main content
C
cjcunniff2
Level 2
August 9, 2018
Solved

Query for number of deliveries sent/opened/clicked per recipient per delivery

  • August 9, 2018
  • 1 reply
  • 2848 views

I have been able to figure out how to get opened and clicked per recipient per delivery but that only shows engagement when the user interacts with an email.   However, I want to get a ratio of opened/clicked to successfully delivered/sent.   The problem is that it seems like the "indicators" table is where we can see sent by delivery but it is aggregated by delivery so I see the same 80K sends for a delivery for recipient A and for recipient B.   What I'd like to see is 1 send for each delivery sent to recipient A, and 1 send for each delivery sent to recipient B.   Does anyone know how I can get to this level of detail.  The table would look something like this:

Recipient ID
Delivery IDSent
DeliveredClickedOpens
Hard Bounce
1111210
1211110
1311001
2110000
2211310
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by james_icf

You are correct - if you just look at the indicators they are aggregated at the delivery level and can't be broken down to the recipient level.

You will want to refer to the nms: broadLogRcp (recipient delivery log) which has a field, "status". You can do a distinctCount on the number of broadLogIDs grouped by delivery to get a count of the number being sent.

The next thing you would want to look at is the nms: trackingLogRcp (recipient tracking log). If you break out the URL types into opens, and clicks and do distinct and total counts you can get the number of opens, clicks, optouts, etc... grouped by delivery.

Looking at your table, though, you'd want to group it by deliveryID and broadLogID instead. The broadLogID should be specific to a recipient, so you could definitely add recipientID to the schema as well.

Additionally, I think its easiest to query each table on its own, add data or enrich to create your matching schemas, then union all the values you need, and then group as necessary.

1 reply

james_icf
james_icfAccepted solution
Level 2
August 9, 2018

You are correct - if you just look at the indicators they are aggregated at the delivery level and can't be broken down to the recipient level.

You will want to refer to the nms: broadLogRcp (recipient delivery log) which has a field, "status". You can do a distinctCount on the number of broadLogIDs grouped by delivery to get a count of the number being sent.

The next thing you would want to look at is the nms: trackingLogRcp (recipient tracking log). If you break out the URL types into opens, and clicks and do distinct and total counts you can get the number of opens, clicks, optouts, etc... grouped by delivery.

Looking at your table, though, you'd want to group it by deliveryID and broadLogID instead. The broadLogID should be specific to a recipient, so you could definitely add recipientID to the schema as well.

Additionally, I think its easiest to query each table on its own, add data or enrich to create your matching schemas, then union all the values you need, and then group as necessary.

Terms & ConditionsAccessibility statement

Loading footer...