1

I have a query that's assigns engagement definition to subscribers based on their interaction with the email. The dataViewSubscribers DE contains 16million users. However, the query times out now every time it's ran. Is there a way it can be optimised?

    SELECT DISTINCT ds.emailaddress, 
                    'New' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) >= 
           CONVERT(VARCHAR(10), Getdate() - 14, 102)

    UNION

    SELECT DISTINCT ds.emailaddress, 
                    'Active' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           INNER JOIN _open o 
                   ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 14, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) >= 
               CONVERT(VARCHAR(10), Getdate() - 90, 102)

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'Defining' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 14, 102) 
           AND CONVERT(VARCHAR(10), ds.datejoined, 102) >= 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND o.subscriberkey IS NULL 

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'At Risk' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND o.subscriberkey IS NULL 

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'Inactive' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON ds.emailaddress = o.subscriberkey 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) < 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) !< 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 
  • Why don't you split it on multiple queries instead of using Union? – Rachid Mamai Nov 22 at 10:55
  • I tried that but because there are some segments that overlap, the last query overrides the previous definition already assigned to some users. – Faithful Adia Nov 22 at 12:25
8

I'd recommend combining this into a single query. Also converting to the date data type will is less costly than converting all of the dates to strings. Something like this:

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when ds.dateJoined >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) 
         and o.eventDate IS NULL then 'Active'
    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) then 'Inactive'
    else
      'Unclassified'
  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 

Loop through all of the DataViewSubscribers records and check the criteria in a CASE statement. The outer apply reduces all of the opens for a subscriber to a single value that you can use in your CASE statement.

Update: If it still times out, you can go through your subscribers in 4 queries by adding a where-clauses using the mod operator like this:

  • Query 1: where ds._customobjectKey % 4 = 0
  • Query 2: where ds._customobjectKey % 4 = 1
  • Query 3: where ds._customobjectKey % 4 = 2
  • Query 4: where ds._customobjectKey % 4 = 3

The first query would be an overwrite, then subsequent ones would be updates.

  • Hi Adam. Thank you for taking out the time to do this. I just tried running the query and it timed out also. Do you think there's anything further that can be done to this? – Faithful Adia Nov 22 at 16:17
  • updated my answer above – Adam Spriggs Nov 22 at 17:54

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.