Hi Team,
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.
Views
Replies
Total Likes
Hi @Ramaswami
Regards, Milan
Views
Replies
Total Likes
Hi @Ramaswami
Regards, Milan
Views
Replies
Total Likes
Hi @milan,
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.
Views
Replies
Total Likes
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.
query :
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
Views
Replies
Total Likes
Hi @Ramaswami,
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,
D0.sDomain,
O2.sLabel
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;
Regards,
Milan
Views
Replies
Total Likes