I have a couple of questions about the open,email clicks, opt-out and email bounces. :
question 1 : through delivery parameters we can see the metrics like opens, email clicks, opt-out, reactivity rate etc., for a delivery. So these metrics are for whole delivery
for example : if i have a delivery - "delivery A" which was been running through a campaign from Jan - May 2020 is there a way through which i can filter the counts of open, email clicks, opt-outs by months like "delivery A" for month of Jan how many opens/clicks/opt-outs, for the month of Feb how many opens/clicks/opt-outs and so on... i didn't found from and to dates on delivery table. So alternatively i am pulling all the tracking logs and filtering by month ( is it the only way to do it>)
question 2: i found open, opt-outs, email clicks parameters on delivery table but there is no bounce rate or bounce count on delivery table. Where can i see the bounce count on a delivery ?
Solved! Go to Solution.
i have checked "NmsDeliveryLogStats" which is under campaign management and i can see the number of occurences and type of failure there and created date, modified date.
How can i query BroadlogRCP or TrackinglogRCP or Delivery table with NmsDeliveryLogStats to see the count of bounces per month or for particular date. Can you please provide an example query with joins.
I have queried through the query activity by taking the schema as 'broadlogRCP' where "NmsBroadLogMsg " is the linked table and i have queired like below with event date and failure type = soft. Is the right way to do.
SELECT B0.iBroadLogId, B0.iMsgId, B1.iBroadLogMsgId FROM NmsBroadLogRcp B0 JOIN NmsBroadLogMsg B1 ON (B1.iBroadLogMsgId = B0.iMsgId) WHERE (B1.iFailureType = 1) AND (B0.tsEvent >= TIMESTAMP WITH TIME ZONE '2020-07-01 00:00:00.000-05') AND (B0.tsEvent < TIMESTAMP WITH TIME ZONE '2020-07-31 00:00:00.000-05') LIMIT 10001
extend this query in WHERE clause to identify you specific delivery or domain.
You have enough for start just adjust it per your needs.
SELECT sum((D0.iUnknownUser)) as HARD_BOUNCES, sum((D0.iUnreachable + D0.iMailBoxFull + D0.iInvalidDomain + D0.iDisabled + D0.iNotConnected + D0.iRefused)) as SOFT_BOUNCES,
sum((D0.iPrepared + D0.iError + D0.iSuccess)) as PROCESSED,
sum(D0.iSent) as SENT,
FROM NmsDeliveryLogStats D0
JOIN NmsDelivery D1
ON (D1.iDeliveryId = D0.iDeliveryId)
JOIN NmsOperation O2
ON (O2.iOperationId = D1.iOperationId)
WHERE (D1.iMessageType = 0)
GROUP BY D0.sDomain, O2.sLabel;