I would like to extract my Opens into a statistics table that contains columns like
Last Opened 7 Days. To achieve this I am using the Data Views to query out the
The target Data Extension stores a Salesforce Account ID which I need to retrieve as my SubscriberKeys in MC are
Account.PersonContactID's so I need to do a LEFT JOIN on my Account_Salesforce synced object as well.
I have created the following query but on runtime it always fails, can anyone advise what the issue is?
select o.subscriberkey as subscriber_key, count(*) as opens_7_days, sforce.Id as salesforce_account_id from _open o left join Account_Salesforce sforce on sforce.PersonContactId = o.SubscriberKey where o.EventDate >= dateadd(hour,-174,cast(GetDate() as date)) group by o.subscriberkey, sforce.Id