Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

Bounce counts

Avatar

Level 4

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 ?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Ramaswami 

  1.  We cannot have everything in delivery table this is why database normalization exists. Tracking information is in tracking table so you have to join tables and use result.You can make your own cube/report and make month filter and just change it in fronted while join works in background.
  2. You may find it on NmsDeliveryLogStats table or use Broadcast statistics report.
    • iUnknownUser  -> Hard bounce
    • iUnreachable + iMailBoxFull + iInvalidDomain + iDisabled + iNotConnected + iRefused -> Soft bounce

Regards, Milan

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

Hi @Ramaswami 

  1.  We cannot have everything in delivery table this is why database normalization exists. Tracking information is in tracking table so you have to join tables and use result.You can make your own cube/report and make month filter and just change it in fronted while join works in background.
  2. You may find it on NmsDeliveryLogStats table or use Broadcast statistics report.
    • iUnknownUser  -> Hard bounce
    • iUnreachable + iMailBoxFull + iInvalidDomain + iDisabled + iNotConnected + iRefused -> Soft bounce

Regards, Milan

Avatar

Level 4

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. 

Avatar

Level 4

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

Avatar

Community Advisor

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