0

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

Target DE schema Table schema

Improve this question
4
  • Can you send screen shot of target data extension - with field types, nullable/non-nullable and PK info. Your query looks fine, I think you just can't push data to the table due to field requirements.
    – Mateusz Bartkowiak
    Jan 11, 2019 at 13:55
  • Updated original question
    – Deployment Failure
    Jan 11, 2019 at 13:57
  • by 'on runtime it always fails', do you mean that it times out? If so, it is likely because of the size of the two tables you are pulling against. Also, Data Views are notoriously slow in queries.
    – Gortonington
    Jan 11, 2019 at 15:30
  • 1
    @Gortonington Not quite, the query itself just failed. The reason for the issue was explained in the answer below and in my comment to his answer.
    – Deployment Failure
    Jan 11, 2019 at 15:50

1 Answer 1

Reset to default
1

Issue is that you probably have Subscribers (might be tests etc) that are not present in Sales Cloud CRM Account but opened an email. When you left join them sforce.Id can be null and it's required in DE. You can either:

  • Instead of left join do a join - you'll get only Contacts that are present in SFMC and in Sales Cloud
  • Make salesforce_account_id field in Data Extension Nullable and remove Primary Key from that field - you'll get All customers from SFMC, and people with PersonAccountId will have salesforce_account_id populated

EDIT I also think you might need to do select distinct to not violate primary key but I'm not 100% sure about it

Improve this answer
1
  • 1
    Thanks, there was actually two issues (the one above, but also the the date line was incorrect: it should have been where o.EventDate >= DATEADD(HOUR, -174, GETDATE())
    – Deployment Failure
    Jan 11, 2019 at 15:49

You must log in to answer this question.

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