Not all records are getting exported from tracking log

Avatar

Avatar

naveenj10058761

Avatar

naveenj10058761

naveenj10058761

14-08-2019

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

15-08-2019

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

Answers (3)

Answers (3)

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

14-08-2019

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

Avatar

naveenj10058761

Avatar

naveenj10058761

naveenj10058761

19-08-2019

Thanks a lot, Jon.

It was quite helpful.

Cheers...

Naveen

Avatar

Avatar

naveenj10058761

Avatar

naveenj10058761

naveenj10058761

15-08-2019

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