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

  • Is the image in your question relevant? – sfdcfox Nov 8 '16 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 '16 at 21:01
  • This is not SOQL, salesforce object query language. Can you explain your objects further? – Mahmood Nov 8 '16 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? – Eliot Harper Nov 8 '16 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 '16 at 21:54
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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