Not all records are getting exported from tracking log

[naveenj10058761]

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.

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

[Jonathon_wodnicki]

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

[naveenj10058761]

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

[Jonathon_wodnicki · Accepted solution]

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

[naveenj10058761]

Thanks a lot, Jon.

It was quite helpful.

Cheers...

Naveen