Automation Studio - Query Activity: _Subscribers.EmailAddress not populated for any record

Question

I am trying to run an SQL query (below) to get a list of all Clicks for all Jobs that were started this year, but I'm having some difficulty querying the Email Address associated with each click. _Subscribers.EmailAddress seems to be blank for every single record in the _Subscribers Data View and I'm not sure if this is expected behaviour and I am supposed to acquire a Subscriber's email using another method, but any help would be greatly appreciated.

SELECT 
     c.SubscriberKey
    ,c.URL
    ,CAST(c.EventDate AS smalldatetime) ClickTime
    ,s.EmailAddress EmailAddress
    ,c.JobID
FROM 
    _click as c LEFT JOIN 
    _subscribers as s 
        ON c.SubscriberID = s.SubscriberID LEFT JOIN
    _job j
        ON j.JobID = c.JobID
WHERE 
    j.CreatedDate > '2018-01-01'

Answer 1

    SELECT 
     c.SubscriberKey,
     c.URL,
     CAST(c.EventDate AS smalldatetime) ClickTime,
     s.EmailAddress AS EmailAddress,
     c.JobID
FROM 
    _click as c 
    LEFT JOIN _subscribers s ON c.SubscriberID = s.SubscriberID
    LEFT JOIN _job j ON j.JobID = c.JobID
WHERE 
    j.CreatedDate > '2018-01-01'

Try this. Your query might have been having an issue parsing your double emailaddress without the defining fuction AS

Answer 2

I've created an explainer video to show you how to do an engaged user segment within Salesforce Marketing Cloud using SQL. The engaged user segment is designed to refresh on an hourly basis, as configured through Automation Studio. This segment will comprise individuals who have either clicked or opened an email within the past seven days. In addition, an unengaged user segment has been established for those who have not opened or clicked an email over the past 30 days.

Here's the link: https://youtu.be/Gy-GMftUhXM

And here's the query used:

select
EmailAddress AS EMAIL,
SubscriberKey AS SUBSCRIBER_KEY
from _Subscribers s
where exists (
  select top 1 c.*
  from _Click c 
  where c.subscriberId = s.subscriberId
  and c.eventDate >= convert(date,getDate()-7)
)
and exists (
  select top 1 o.*
  from _Open o
  where o.subscriberId = s.subscriberId
  and o.eventDate >= convert(date,getDate()-7)
)