0

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'

2 Answers 2

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

-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)
)
2
  • I can see you run a similar SQL query in your video - can you post that here? Self promotion is ok if it's called out, but link-only answers are generally removed as they often break in the long term.
    – Matt Lacey
    Commented Mar 24, 2023 at 15:00
  • Sure. In this case the link is a tutorial... Here's the code. 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) ) Commented Mar 26, 2023 at 22:44

You must log in to answer this question.

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