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 _opens
table.
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