0

I want to pull my email data from MC into Market Intelligence (Datorama) by subscriber ID and include enterprise attributes. The best way I have found thus far is to create a DE with the needed info and use the MC Data Extension API in Datorama to ingest. I need a query that will create a daily file of email activity in MC. I have tried the following, but it does not pull all the information. For testing purposes I limited the query to two emailnames as seen in the Where statement. If I run this query for just one of the emailnames at a time I get the correct numbers of Opens, Sends and clicks. (comparing it to a report pulled from MC). When I run the query with both email names one email has the correct number of opens, send, clicks, but the other has a much lower number than it should. Why is the query not pulling the correct numbers for both emails?

Select
s.SubscriberID, 
s.SubscriberKey,
s.jobid,
s.eventdate as SentDate,
j.emailname,
o.eventdate as OpenDate,
c.eventdate as ClickDate,
c.linkname as LinkName
From 
_sent s
Left Join _job j on s.jobid = j.jobid
Left Join _Open o on s.jobid =o.jobid
            AND  s.subscriberid = o.subscriberid
             AND s.ListID = o.ListID 
             and s.BatchID = o.BatchID 
             AND o.ISUnique = 1
Left Join _click c on s.jobid = c.jobid
             AND s.ListID = o.ListID 
             and s.BatchID = o.BatchID 
             and s.subscriberid = c.subscriberid
             AND c.ISUnique = 1
Where emailname IN ('IC_InsiderToday_Employee_28-Oct-2022_Null_Null_Null','IC_InsiderToday_Employee_28-Nov-2022_Null_Null_Null')
Improve this question
3
  • Maybe create the individual DEs for Send, Open and Click and check the counts before joining them all together?
    – travelonly-minyoung
    Commented Jan 22, 2023 at 3:51
  • @Karen when you said it was incorrect, Which one were you comparing to the records in 2 SQL queries or the metric (opens, sends, clicks) from the MC report?
    – Duc Le
    Commented Jan 23, 2023 at 14:26
  • Thank you all for replying, I ended up creating the send, open and click data in different queries and them combining with a separate query, It works.
    – Karen McRight
    Commented Jan 24, 2023 at 22:00

1 Answer 1

Reset to default
0

The join condition for _Click seems not correct: ListID and BatchID

Select
s.SubscriberID, 
s.SubscriberKey,
s.jobid,
s.eventdate as SentDate,
j.emailname,
o.eventdate as OpenDate,
c.eventdate as ClickDate,
c.linkname as LinkName
From 
_sent s
Left Join _job j on s.jobid = j.jobid
Left Join _Open o on s.jobid =o.jobid
            AND  s.subscriberid = o.subscriberid
             AND s.ListID = o.ListID 
             and s.BatchID = o.BatchID 
             AND o.ISUnique = 1
Left Join _click c on s.jobid = c.jobid
             AND s.ListID = c.ListID 
             and s.BatchID = c.BatchID 
             and s.subscriberid = c.subscriberid
             AND c.ISUnique = 1
Where emailname IN ('IC_InsiderToday_Employee_28-Oct-2022_Null_Null_Null','IC_InsiderToday_Employee_28-Nov-2022_Null_Null_Null')
Improve this answer
2
  • What do you think is wrong with it?
    – Karen McRight
    Commented Jan 22, 2023 at 21:45
  • @KarenMcRight The alias for Click data view was wrong. It was o.ListID and o.BatchID. I changed them to c.ListID and c.BatchID. Can you run and check if the counts are correct?
    – travelonly-minyoung
    Commented Jan 24, 2023 at 9:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .