I'm trying to create an automation that calls a sql query and saves the return in a data extension, But for some reason the data doesn't arrive in the data extension, not as it appears in Query Studio. This is my query:
SELECT
s.SubscriberKey,
ls.EmailAddress,
j.EmailName,
j.EmailSubject,
j.FromName,
j.FromEmail,
s.EventDate AS SentDate,
o.EventDate AS OpenDate,
op.Opens AS NumberOfOpens,
c.EventDate AS ClickDate,
cl.Clicks AS NumberOfClicks,
url.URLs AS NumberOfLinksClicked,
b.EventDate AS BouncedDate,
b.BounceCategory,
u.EventDate AS UnsubscribedDate
FROM _Sent s
LEFT JOIN _Job j ON s.JobID = j.JobID
LEFT JOIN _Open o ON s.JobID = o.JobID
AND s.ListID = o.ListID
AND s.BatchID = o.BatchID
AND s.SubscriberID = o.SubscriberID
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
LEFT JOIN _Bounce b ON s.JobID = b.JobID
AND s.ListID = b.ListID
AND s.BatchID = b.BatchID
AND s.SubscriberID = b.SubscriberID
AND b.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID
AND s.ListID = u.ListID
AND s.BatchID = u.BatchID
AND s.SubscriberID = u.SubscriberID
AND u.IsUnique = 1
LEFT JOIN (select
jobid,
ListID,
BatchID,
subscriberid,
count(subscriberid) AS Clicks
from _click
group by subscriberid, jobid, ListID, BatchID) cl ON s.JobID = cl.JobID
AND s.ListID = cl.ListID
AND s.BatchID = cl.BatchID
AND s.SubscriberID = cl.SubscriberID
LEFT JOIN (select distinct
jobid,
ListID,
BatchID,
subscriberid,
count(distinct url) AS URLs
from _click
group by subscriberid, jobid, ListID, BatchID) url ON s.JobID = url.JobID
AND s.ListID = url.ListID
AND s.BatchID = url.BatchID
AND s.SubscriberID = url.SubscriberID
LEFT JOIN (select
jobid,
ListID,
BatchID,
subscriberid,
count(subscriberid) AS Opens
from _open
group by subscriberid, jobid, ListID, BatchID) op ON s.JobID = op.JobID
AND s.ListID = op.ListID
AND s.BatchID = op.BatchID
AND s.SubscriberID = op.SubscriberID
LEFT JOIN _ListSubscribers ls ON s.SubscriberID = ls.SubscriberID
This is your return in query studio:
Note that SubscriberKey comes with letters and number, all EmailAddress are filled in... But in Data Extension the return is this:
what makes me think that something is wrong is the SubscriberKey which is only with numbers and the fact that the EmailName/EmailSubject/FromName/FromEmail fields are null, and in Query Estudio everything is filled in. I appreciate that you can give me a north.
obs: SubscriberKey data types in data extension is TEXT as are EmailName/EmailSubject/FromName/FromEmail fields
_ListSubscribers
without specifying aListID
to get theEmailAddress
. Should probably beent._Subscribers
._ListSubscribers
is 1:M.