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
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