0

I am attempting to reliably retrieve data from both the Data Views and from the Sendlog. The reason is that I need to be able to add two attributes that were added to the SendLog to the DataView data. The below represents the _Click data view and the Sendlog with additional attributes. Essentially, they act as composite keys for each record outside of SFMC

_CLICK CLK       SENDLOG SLG
------          --------
JOBID|SUBID    JOBID|SUBID|OtherAttrbA|OttherAttrbB
12|111            12|111|AAAA|1A2B
12|111            12|222|BBBB|1C2B
13|111            12|333|CCCC|3E4R
14|111            12|444|DDDD|4D2W
14|111            12|555|EEEE|1G4T
14|111            13|111|FFFF|5C2E
12|222            13|222|GGGG|0P5V
12|222            13|333|HHHH|4Z9E
15|333            13|444|IIII|9I1D
                  13|555|JJJJ|7V8E
                  14|111|KKKK|5V3W
                  14|222|LLLL|7V3G
                  14|333|MMMM|2E2E
                  14|444|NNNN|0F8C
                  14|555|OOOO|6Y4G
                  15|111|PPPP|5B8V
                  15|222|QQQQ|0e5G
                  15|333|RRRR|3J8W
                  15|444|SSSS|8X8Z
                  15|555|TTTT|9N8Z



 Desired Result
 CLK.JOBID|CLK.SUBID|SLG.OtherAttribA|SLG.OttherAttrbB
    12|111|AAAA|1A2B
    12|111|AAAA|1A2B
    13|111|FFFF|5C2E
    14|111|KKKK|5V3W
    14|111|KKKK|5V3W
    14|111|KKKK|5V3W
    12|222|BBBB|1C2B
    12|222|BBBB|1C2B
    15|333|RRRR|3J8W

The issue that I'm running in to, I think, is in the Joining of these two tables. As you might expect, I'm getting duplicates somewhere. For example, if I run:

SELECT SubscriberID
FROM _Click

I will get 519 records (as of now)

However, when I run:

SELECT
  a.JobID,
  a.SubscriberID,
  b.OtherAttribA,
  b.OtherAttribB
FROM _Click a
LEFT JOIN SendLog b on a.SubscriberID = b.SubID AND a.JobID = b.JobID

I get 552 records. I had considered using GROUP BY, but that feels like it would be covering up an issue, right?

  • If the LEFT JOIN on SendLog returns more rows than COUNT(*) on _Click, this means you have multiple records in SendLog for the same SubID and JobID. I would recommend to look for BatchID as well. Or look at your SendLog – shd.lux Apr 26 at 12:09
  • Thank you @shd.lux - you were spot on. This is a brand new BU, so I suspect that there was some testing done and perhaps the SendLog has been taken out of sync with the data views. Would the best way to correct this be to simply clear the SendLog, and export the contents of _Sent to it? Also, please feel free to move your comment to an answer so that I can select it as the correct answer :) – Stephen Boyette Apr 26 at 12:14
0

As per comment:

The SendLog is not necessarily in sync with the _DataViews. It depends on creation time, data retention settings and more.

If a LEFT JOIN on SendLog retrieves more records than a COUNT(*) on _Click, this means you have multiple records in SendLog for the same SubID and JobID. You can either try to join it on BatchID or have a look at the SendLog first to see how the data looks like.

With only 519 clicks this should be fairly easy and straight forward.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.