I'm trying to run this as a query activity (writing to a data extension) but am finding that it's timing out. It's not all that 'heavy' of a query but it's not completing. I'm a database developer by trade...but don't know the intricacies of SalesForce query. For example - I've tried to introduce variables and CTEs into this query but I'm getting an error stating that 'select' must be the first word in the query.

    Count(distinct s.subscriberkey) Sends,
    Count(distinct o.subscriberkey) Opens,
    Count(distinct c.subscriberkey) Clicks,
    count(distinct u.subscriberkey) Unsubs,
    Count(distinct b.subscriberkey) Bounces,
    Count(distinct s.subscriberkey) - Count(distinct b.subscriberkey) Delivered
    _Job j 
    join _Sent s on j.JobID = s.JobID
    join [Snapshot - RPI_Standard_Audience 2_14_20] a on s.subscriberkey = a.email_address
    left join _Open o on j.JobID = o.JobID and
        a.email_address = o.subscriberkey
    left join _Click c on j.JobID = c.JobID and
        a.email_address = c.subscriberkey
    left join _Unsubscribe u on j.JobID = u.JobID and
        a.email_address = u.subscriberkey
    left join _Bounce b on j.JobID = b.JobID and
        a.email_address = b.subscriberkey

    j.JobID in ('4142867','4142864','4142863')
group by

A typical "error" leading to timeouts is that there simply are too many JOINs over the tables in the backend (data views). Solution would be to split up your query into several sequential parts. Often, this behavior will "creep" into your project as the data views are very small at the beginning, and with increased sendout volume, problems begin.

So, my advice is to be conservative and split into several rather boring queries to make use of some pre-processing.

So I'll outline a very broad schema, as you likely will have no issue with the queries themselves:

a) SELECT job / JOIN sent JOIN snapshot into stagingDE

b) At the same time you could create your counts in a "countsDE"

c) When a and b are done, run a LEFT JOIN of stagingDE and countsDE

Hope this helps!

  • Thanks Jonas - that's very helpful. It's a bit disappointing as a simple query seems like it will take multiple times the dev effort I was hoping for but this seems like the right solution. appreciate your help. – Matt Morris Feb 17 '20 at 14:22

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.