1

I have a query running in Automation studio that times out. The dataViewSubscribers data extension contains 20 million users. Is there a way this can be optimised?

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when o.eventDate >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) then 'Active'
    when ds.dateJoined < convert(date, getDate()-14)
         and ds.dateJoined >= convert(date, getDate()-90)
         and o.eventdate IS NULL then 'Defining'
    when ds.dateJoined < convert(date, getDate()-90) 
         and o.eventdate IS NULL then 'At Risk'
    when ds.dateJoined < convert(date, getDate()-90)
         and o.eventdate < convert(date, getDate()-90)
         and o.eventdate !< convert(date, getDate()-90) then 'Inactive'
  end AS EngagementDefinition 
FROM DataviewSubscribers ds 
outer apply (
  select 
    max(o0.eventDate) eventDate
  from _Open o0
  where o0.subscriberid = ds.subscriberid
  and o0.isunique = 1
) o where ds._customobjectKey % 6 = 0
  • Can you translate the Inactive conditional in your case statement for us? Seems like the last two conditions will cause it to never be true. – Adam Spriggs May 4 at 13:53
  • You're right. This is what it's meant to be. when ds.dateJoined < convert(date, getDate()-90) and o.eventdate < convert(date, getDate()-90) then 'At Risk' when ds.dateJoined < convert(date, getDate()-90) and o.eventdate IS NULL then 'Inactive' – Faithful Adia May 5 at 7:45
2

Looks like you're utilizing all of the tricks already.

I'd build a new DE for the Opens and revise your query left join to it.

Opens query:

select
o0.subscriberid
, max(o0.eventDate) eventDate
from _Open o0
where o0.isunique = 1
group by o0.subscriberid
/* name: MostRecentOpens */
/* target: MostRecentOpens */
/* action: overwrite */

Then I'd repeat this pattern in your 6 subsequent queries:

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when o.eventDate >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) then 'Active'
    when ds.dateJoined < convert(date, getDate()-14)
         and ds.dateJoined >= convert(date, getDate()-90)
         and o.eventdate IS NULL then 'Defining'
    when ds.dateJoined < convert(date, getDate()-90) 
         and o.eventdate IS NULL then 'At Risk'
    when ds.dateJoined < convert(date, getDate()-90)
         and o.eventdate < convert(date, getDate()-90)
         and o.eventdate !< convert(date, getDate()-90) then 'Inactive'
  end AS EngagementDefinition 
FROM DataviewSubscribers ds 
left join MostRecentOpens o on o.subscriberid = ds.subscriberid
where ds._customobjectKey % 6 = 0

If it still times out, I'd consider adding a query for each EngagementDefinition instead of breaking the DataViewSubscribers into 6 groups.

| improve this answer | |

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.