I am trying to join 4 data views in single query and storing the result in data extension.

o.AccountID, o.JobID, o.ListID, o.SubscriberID, o.BatchID, o.SubscriberKey, o.EventDate, s.EmailAddress, s.Status, s.DateUnsubscribed
FROM _Open  o 
INNER JOIN _subscribers s
   ON o.SubscriberKey= s.SubscriberKey
INNER JOIN _Click  c
   ON  o.SubscriberKey = c.SubscriberKey
INNER JOIN _Unsubscribe  u
   ON  u.SubscriberKey = c.SubscriberKey 

I am not sure can i do in that way. Because when running query is sucessful and no error in actionlog but no data is displaying.

I have tried with 3 data views it worked.

  • 2
    You should break these up into multiple queries/DE regardless of your current issue. As your audience/volume increases, your query will time out and no longer function. This will also help with debugging by letting you see which section is causing the issue. – Gortonington Feb 7 '18 at 18:19
  • But i need common data in all these data views – sarah Feb 7 '18 at 18:20
  • 3
    The major issue is that dataviews (_Open, _Sent, etc) are notoriously slow. So even doing something that hourly pushes these Dataviews into a DE and then using your query on that would lower your chance of timing out. But in general, I would find a way to split these out - don't forget you can 'update' to a single DE, so it gives the appearance of only 1 query, despite running multiple ones. – Gortonington Feb 7 '18 at 18:24
  • you mean for each data view point to one D.E like wise it results in 4 D.Es with 4 data views then to combine all data create one final D.E and in that final query i need to join all 4 D.Es right? – sarah Feb 7 '18 at 19:10
  • That should triage it for a bit - but down the line, I would look to try and reduce the number of joins as these can be heavy resource drains. – Gortonington Feb 7 '18 at 19:16

If you are doing an update on the new aggregated DE make sure you are selecting one record for multiple opens of the same email on the same day for a user with rank or you will have a primary key violation.

| improve this answer | |

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