0

I'm in need of some query help. I'm currently trying to Join multiple tables on Salesforce Marketing Cloud but it keeps timingout. Each table has a field called "SubscriberKey" and "EventDate" for Sent, Opens, Clicks, Unsub which I want to pull all together. This way i can see the subscribers and their actions from an email deployment. Hope that makes sense. Below is my query:

SELECT 
s.SubscriberKey, 

min(s.EventDate) as [EventSent],
min(o.EventDate) as [EventOpens],
min(c.EventDate) as [EventClicks],
min(u.EventDate) as [EventUnsubs]

FROM _Sent s

LEFT JOIN _Open o 
ON s.SubscriberKey = o.SubscriberKey

LEFT JOIN _Click c 
ON s.SubscriberKey = c.SubscriberKey

LEFT JOIN _Unsubscribe u 
ON s.SubscriberKey = u.SubscriberKey

WHERE s.jobid = 695597
GROUP BY s.[SubscriberKey]

sample of how i want the final table to look like

6
  • Is the image in your question relevant?
    – sfdcfox
    Nov 8, 2016 at 20:54
  • Apologies for not explaining the image. The image is suppose to represent my ideal run query. This shows each subscriber and what actions if taken.
    – Allen Chan
    Nov 8, 2016 at 21:01
  • This is not SOQL, salesforce object query language. Can you explain your objects further?
    – Mahmood
    Nov 8, 2016 at 21:45
  • When you say your query is timing out, what do you mean by this? Have you tried putting your query in a Scheduled Automation (in Automation Studio), then click Run Once and view the activity on the Activity tab? Does the Automation error? Nov 8, 2016 at 21:47
  • Hi Eliot, i did create a automation which runs the query by clicking run once, and i get and error saying it timeout.
    – Allen Chan
    Nov 8, 2016 at 21:54

1 Answer 1

1

While valid, it's unlikely this will never run successfully -- even for a single job. The data views are just not that performant, unfortunately.

I'd suggest one query for every activity type.

The first one based on _Sent that does an overwrite and the subsequent three are updates and based on the rows resulting from the first query.

You should also join on SubscriberID as @Chris suggested.

If you don't want to do that, I'd suggest mirroring the data views in your own set of Data Extensions and refreshing them daily. That way you can control the primary keys.

These work-arounds are a ton of unnecessary work and, frankly, a big pain in the butt. Believe me, I know.

You must log in to answer this question.

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