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
    Jan 21 '20 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
    Jan 21 '20 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
    Jan 21 '20 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
    Jan 21 '20 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
    Jan 21 '20 at 13:54
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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.