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

Not all records are getting exported from tracking log

Avatar

Level 4

Hi,

I am trying to export information from trackinglog with email ID of recipients.

But the during export the information for few of the recipients is not getting exported.

As you can see in the screenshot that the number of rows processed is 316140 which is fewer than the number of rows exported.

1813247_pastedImage_3.png

1813168_pastedImage_2.png

When I remove any linked table like recipient table and export only the trackinglog fields then all the rows are getting exported.

I have compared the email IDs in the delivery list with that of recipient table, and all the email IDs exists in recipient table.

Can you please check what is the root cause of this problem.

Thanks

Naveen

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi,

Recipient id's, not email addresses. From the small number it's likely some recipients were naturally deleted and later recreated, though you should still check your ETL jobs to make sure they're upserting recipients and not doing anything off-spec. This will sound unorthodox, but to fix the existing issue all you have to do is update the recipient id's in nms:recipient to match what's in nms:trackingLogRcp, i.e. `update nmsrecipient n set n.irecipientid=t.irecipientid from (select irecipientid from nmstrackinglogrcp where semail=n.semail and noyolo) t`. NB I wrote this in the comment box here to help clarify, don't copy-paste this to your prod db, dogs and cats living together, etc.

Thanks,

-Jon

View solution in original post

4 Replies

Avatar

Community Advisor

Hi,

The discrepancy is due to the inner join there, with trackinglog having recipient id's that aren't in recipient.

If you remove the first name and email fields the result should be 316140.

Thanks,

-Jon

Avatar

Level 4

Thanks for the response, Jon.

Can you please elaborate what could have caused this join issue and how to fix it.

I can see the email address in the recipient table but it is not getting joined with tracking log.

Cheers

Naveen

Avatar

Correct answer by
Community Advisor

Hi,

Recipient id's, not email addresses. From the small number it's likely some recipients were naturally deleted and later recreated, though you should still check your ETL jobs to make sure they're upserting recipients and not doing anything off-spec. This will sound unorthodox, but to fix the existing issue all you have to do is update the recipient id's in nms:recipient to match what's in nms:trackingLogRcp, i.e. `update nmsrecipient n set n.irecipientid=t.irecipientid from (select irecipientid from nmstrackinglogrcp where semail=n.semail and noyolo) t`. NB I wrote this in the comment box here to help clarify, don't copy-paste this to your prod db, dogs and cats living together, etc.

Thanks,

-Jon

Avatar

Level 4

Thanks a lot, Jon.

It was quite helpful.

Cheers...

Naveen