0

I am running below SQL Query to get the details from SFMC data views but I am getting NULL values on the Email address, journey name and journey version.

Please help me to get these details Below is my SQL query:

select
a.CampaignName,
a.EmailSubject,
CONVERT(date, a.SendDate) as SendDate,
sum(b.OpenCount) as [OpenCount],
sum(c.BounceCount) as [BounceCount],
sum(d.ClickCount) as [ClickCount],
a.JobID,
sum(e.UnsubCount) as [UnsubCount],
a.EmailAddress,
a.JourneyName,
a.Source,
sum(a.SendVolume) as [SendVolume],
a.VersionNumber
from  (Select
            c.Email as EmailAddress,
            s.EmailName as [CampaignName],
            s.EmailSubject,
            j.JourneyName,
            a.EventDate as [SendDate],
            1 as [SendVolume],
            s.JobID,
            a.SubscriberID,
            s.FromName as [Source],
       j.VersionNumber
        From _Sent a
            left join _Job s on a.JobID = s.JobID
       left join _JourneyActivity ja ON a.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
        left join _Journey j ON ja.VersionID = j.VersionID
       left join _Subscribers sb on a.SubscriberID = sb.subscriberID
            left join ent.Contact_Salesforce c on sb.subscriberkey = c.id
         where lower(s.EmailName) like '%AnyEmailName%'  
        group by
            c.Email,
            s.EmailName ,
            s.EmailSubject,
            j.JourneyName,
            a.EventDate,
            s.JobID,
            a.SubscriberID,
            s.fromname,
   j.VersionNumber) a

Left join (
        Select
            1 as [OpenCount],
            s.JobID,
            s.SubscriberID
        From _Open s
        group by
            s.JobID,
            SubscriberID) b on a.JobID = b.JobID and b.SubscriberID = a.SubscriberID

Left join (
        Select
            1 as [BounceCount],
            s.JobID,
            s.SubscriberID
        From _Bounce s
        group by
            s.JobID,
            s.SubscriberID) c on a.JobID = c.JobID and c.SubscriberID = a.SubscriberID

Left join (
        Select
            1 as [ClickCount],
            s.JobID,
            s.SubscriberID
        From _Click s
        group by
            s.JobID ,
            s.SubscriberID) d on a.JobID = d.JobID and d.SubscriberID = a.SubscriberID
           
Left join (
        Select
            1 as [UnsubCount],
            s.JobID,
            s.SubscriberID
        From _Unsubscribe s
        group by
            s.JobID,
            s.SubscriberID) e on a.JobID = e.JobID and e.SubscriberID = a.SubscriberID
group by
a.CampaignName,
a.EmailSubject,
CONVERT(date, a.SendDate) ,
a.JobID,
a.EmailAddress,
a.JourneyName,
a.Source,
a.VersionNumber 

Please help me with this.

Thanks Adam

1 Answer 1

1

Here are some observations:

  • You've several collisions with your aliases. I'd recommend being more specific with them. Using a, b, c, etc., is not a good practice.
  • The join to ent.Contact_Salesforce appears unnecessary. You weren't using any of the fields.
  • I removed the where lower(s.EmailName) like '%AnyEmailName%' so I could test it.
  • I switched your LEFT JOINs for OUTER APPLYs. I like to use those since it's easier to aggregate when the join criteria is inside.
select
  x.CampaignName
, x.EmailSubject
, x.JobID
, x.EmailAddress
, x.SubscriberID
, x.JourneyName
, x.Source
, x.VersionNumber
, convert(date, x.SendDate) as SendDate
, x.sendCount as SendCount
, [open].count as OpenCount
, click.count as ClickCount
, bounce.count as BounceCount
, unsub.count as UnsubCount
from (
    select 
      sub.EmailAddress as EmailAddress
    , j.EmailName as CampaignName
    , j.EmailSubject
    , jn.JourneyName
    , s.EventDate as SendDate
    , j.JobID
    , s.SubscriberID
    , j.FromName as Source
    , jn.VersionNumber 
    , count(s.SubscriberID) as sendCount
    From _Sent as s 
    inner join _Subscribers as sub on sub.subscriberID = s.SubscriberID
    inner join _Job as j on j.JobID = s.JobID
    left join _JourneyActivity as ja ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID 
    left join _Journey as jn ON jn.VersionID = ja.VersionID 
    where 1=1
    group by 
      sub.EmailAddress
    , j.EmailName
    , j.EmailSubject
    , jn.JourneyName
    , s.EventDate
    , j.JobID
    , s.SubscriberID
    , j.fromname
    , jn.VersionNumber
) as x
outer apply (

    select 
    count(a.subscriberid) as Count
    from _Open as a
    where a.JobID = x.JobID and a.SubscriberID = x.SubscriberID

) as [open]
outer apply (

    select 
    count(a.subscriberid) as Count
    from _Click as a
    where a.JobID = x.JobID and a.SubscriberID = x.SubscriberID

) as click
outer apply (

    select 
    count(a.subscriberid) as Count
    from _Bounce as a
    where a.JobID = x.JobID and a.SubscriberID = x.SubscriberID

) as bounce
outer apply (

    select 
    count(a.subscriberid) as Count
    from _Unsubscribe as a
    where a.JobID = x.JobID and a.SubscriberID = x.SubscriberID
    
) as unsub

run example

1
  • Thank you so much @Adam Spriggs
    – Adam Coder
    Mar 25 at 9:29

You must log in to answer this question.

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