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

[christopher1544]

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 ID	Sent	Delivered	Clicked	Opens	Hard Bounce
1	1	1	1	2	1	0
1	2	1	1	1	1	0
1	3	1	1	0	0	1
2	1	1	0	0	0	0
2	2	1	1	3	1	0

[james_icf · Accepted solution]

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.