SQL Query return is different in Data Extension and Query Studio return - Marketing Cloud

Question

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

Answer 1

If you're trying to summarize subscriber activity, I'd recommend using separate queries. If you need additional data points, simply update an existing query or add another one.

Step 0 - Create a Subscriber_Activity_Summary Data Extension

SubscriberId, Number (Primary Key)
EmailAddress, EmailAddress
Subscriberkey, Text(254)
Status, Text(15)
SendCount, Number
LastSend, Date
OpenCount, Number
LastOpen, Date
ClickCount, Number
LastClick, Date

Step 1 - Get all Subscribers

select
  s.subscriberid
, s.subscriberkey
, s.emailAddress
, s.status
from ent._Subscribers s
/* name: Subscriber_Activity_Summary_Status */
/* target: Subscriber_Activity_Summary */
/* action: update */

Step 2 - Populate Send Data

select
  x.subscriberid
, x.subscriberkey
, isnull(sas.SendCount,0) + x.SendCount SendCount
, x.LastSend
from (

    select
      y.subscriberid
    , y.subscriberkey
    , count(*) SendCount
    , max(y.eventDate) LastSend
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        from _Sent a
        where a.EventDate >= convert(date, getDate()-1)
        and a.EventDate < convert(date, getDate())
    ) y
    group by
    y.subscriberid
    , y.subscriberkey

) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastSend > isnull(sas.lastSend,'1980-01-01')
/* name: Subscriber_Activity_Summary_Sends */
/* target: Subscriber_Activity_Summary */
/* action: update */

Step 3 - Populate Open Data

select
x.subscriberid
, x.subscriberkey
, isnull(sas.OpenCount,0) + x.OpenCount OpenCount
, x.LastOpen
from (

    select
      y.subscriberid
    , y.subscriberkey
    , count(*) OpenCount
    , max(y.eventDate) LastOpen
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        from _Open a
        where a.EventDate >= convert(date, getDate()-1)
        and a.EventDate < convert(date, getDate())
    ) y
    group by
    y.subscriberid
    , y.subscriberkey

) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastOpen > isnull(sas.lastOpen,'1980-01-01')
/* name: Subscriber_Activity_Summary_Opens */
/* target: Subscriber_Activity_Summary */
/* action: update */

Step 4 - Populate Click Data

select
x.subscriberid
, x.subscriberkey
, isnull(sas.ClickCount,0) + x.ClickCount ClickCount
, x.LastClick
from (

    select
      y.subscriberid
    , y.subscriberkey
    , count(*) ClickCount
    , max(y.eventDate) LastClick
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        from _Click a
        where a.EventDate >= convert(date, getDate()-1)
        and a.EventDate < convert(date, getDate())
    ) y
    group by
    y.subscriberid
    , y.subscriberkey

) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastClick > isnull(sas.lastClick,'1980-01-01')
/* name: Subscriber_Activity_Summary_Clicks */
/* target: Subscriber_Activity_Summary */
/* action: update */

I'd schedule this in an Automation to run overnight every day.

---

EDIT -- Single query

Here's an updated version of your query. I don't recommend this approach. Joining this many slow data views together will cause a query timeout when you have a moderate send volume.

SELECT
  s.SubscriberKey
, sub.EmailAddress
, j.EmailName
, j.EmailSubject
, j.FromName
, j.FromEmail
, s.EventDate AS SentDate
, o.lastOpen AS OpenDate
, coalesce(o.OpenCount,0) AS NumberOfOpens
, c.lastClick AS ClickDate
, coalesce(c.clickCount,0) AS NumberOfClicks
, coalesce(c.URLClickCount,0) AS NumberOfLinksClicked
, b.lastBounce AS BouncedDate
, b.lastBounceCategory as BounceCategory
, u.lastUnsubscribe AS UnsubscribedDate
FROM _Job j
outer apply (
    select
      s0.subscriberid
    , s0.subscriberkey
    , s0.eventDate
    from _Sent s0
    where s0.jobid = j.jobid
) s
inner join ent._Subscribers sub ON s.SubscriberID = sub.SubscriberID 
outer apply (

  select
      y.subscriberid
    , y.subscriberkey
    , count(*) OpenCount
    , max(y.eventDate) LastOpen
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        from _Open a
        where a.jobid = j.jobid 
        and a.subscriberid = s.subscriberid
    ) y
    group by
      y.subscriberid
    , y.subscriberkey

) o
outer apply (

  select
      y.subscriberid
    , y.subscriberkey
    , count(*) ClickCount
    , max(y.eventDate) LastClick
    , count(distinct y.url) URLClickCount
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        , a.url
        from _Click a
        where a.jobid = j.jobid 
        and a.subscriberid = s.subscriberid
    ) y
    group by
      y.subscriberid
    , y.subscriberkey
) c
outer apply (

  select
      y.subscriberid
    , y.subscriberkey
    , count(*) BounceCount
    , max(y.eventDate) LastBounce
    , max(y.bounceCategory) lastBounceCategory
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        , a.BounceCategory
        from _Bounce a
        where a.jobid = j.jobid 
        and a.subscriberid = s.subscriberid
    ) y
    group by
      y.subscriberid
    , y.subscriberkey

) b
outer apply (

  select
      y.subscriberid
    , y.subscriberkey
    , count(*) UnsubscribeCount
    , max(y.eventDate) LastUnsubscribe
    from (
        select
          a.subscriberid
        , a.subscriberkey
        , a.eventDate
        from _Unsubscribe a
        where a.jobid = j.jobid 
        and a.subscriberid = s.subscriberid
    ) y
    group by
      y.subscriberid
    , y.subscriberkey

) u

Results: