0

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: enter image description here

Note that SubscriberKey comes with letters and number, all EmailAddress are filled in... But in Data Extension the return is this:

enter image description here

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

2
  • You shouldn't be joining to _ListSubscribers without specifying a ListID to get the EmailAddress. Should probably be ent._Subscribers. _ListSubscribers is 1:M. Oct 18, 2022 at 17:27
  • @Adam Spriggs I don't understand how this works, could you explain better? Oct 18, 2022 at 17:31

1 Answer 1

3

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: Query Studio Results

3
  • I don't understand much about sql, and the query I made works on the orgs I have, it just doesn't work on the first org I created, it's on this first org that I'm having the problem I described at the beginning. @Adam Spriggs Oct 18, 2022 at 18:08
  • I would just like to know the reason for the problem and how to solve it. The queries you created I believe are better but confused me even more haha Oct 18, 2022 at 18:08
  • The way you're aggregating, the second screenshot appears to be accurate. So if you are comparing results between two different instances, the issue isn't Query Studio vs Query Activity. The reason I suggested a different approach is that it's simpler and it'll scale for larger volumes. Oct 18, 2022 at 18:36

You must log in to answer this question.

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