0

I have tried basic SQL which is on help document to get last 30 days opens is not working. here is what i have

  Select
j.JobID,
j.EmailName,
j.DeliveredTime as SendTime,
o.EventDate as OpenTime,
s.EmailAddress,
s.SubscriberKey
from [_Job] j
join [_Open] o
on j.JobID = o.JobID
join [_Subscribers] s
on o.SubscriberID = s.SubscriberID
where
o.IsUnique = 1 and
o.EventDate > dateadd(d,-30,getdate()) and

But this does not generate any record within DE that I created as per direction within help site. https://help.salesforce.com/articleView?id=mc_as_query_opens_in_last_30_days_ref.htm&type=5

can anyone help me with this. also, is it possible to get past 1 or 2 year tracking data from sfmc. anybody who clicked or opened in last 1 year. do i just update the query with 365 days instead of 30?

7
  • It works after removing the join [_Subscribers] s on o.SubscriberID = s.SubscriberID
    – API User
    Commented Jan 21, 2020 at 7:27
  • 2
    You can only get 6 months of tracking data from the Data Views. To get more, use Tracking Extract in Automation Studio help.salesforce.com/… or use API to call the OpenEvent object developer.salesforce.com/docs/… and ClickEvent object developer.salesforce.com/docs/…
    – zuzannamj
    Commented Jan 21, 2020 at 7:38
  • Thank you Zuzannamj, I will look into your suggestion. also could you help me understanding the issue with the subscriber key value. when I remove this, value gets populated with remaining fields but when I include it runs fine shows success in automation but does not populate any data within DE.
    – API User
    Commented Jan 21, 2020 at 13:40
  • You can also try that asking to Support to provide the data views with information about the last 1 year or more. But it is only approved in very justified scenarios.
    – fromero
    Commented Jan 21, 2020 at 13:40
  • I heard about tracking extract that provides an option to select 30 days date range, it means I have to create some 24 odd extract activities in order to get the data. Isn't there a simpler option to do that? I'm not aware of any code
    – API User
    Commented Jan 21, 2020 at 13:54

1 Answer 1

5

Because it is noted "It works after removing the join [_Subscribers] s on o.SubscriberID = s.SubscriberID", I believe this is being written in a BU. The _Subscribers data view generally holds subscribers at the enterprise level when in an E2.0 account. Try changing this to ENT.[_Subscribers]

You must log in to answer this question.

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