Not all records are getting exported from tracking log | Community
Skip to main content
naveenj10058761
naveenj10058761
Level 3
August 14, 2019
Solved

Not all records are getting exported from tracking log

  • August 14, 2019
  • 4 replies
  • 3305 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Jonathon_wodnicki

    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

    4 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    August 14, 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

      naveenj10058761
      naveenj10058761Author
      Level 3
      August 15, 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

      Jonathon_wodnicki
      Community Advisor
      Jonathon_wodnickiCommunity AdvisorAccepted solution
      Community Advisor
      August 15, 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

        naveenj10058761
        naveenj10058761Author
        Level 3
        August 19, 2019

        Thanks a lot, Jon.

        It was quite helpful.

        Cheers...

        Naveen

        Terms & ConditionsAccessibility statement

        Loading footer...