SQL Query valid but Automation to populate Data Extension with engagement data is failing

Question

Attempting to create a 'score' data extension for each email contact, to measure engagement levels with email sends.

Background:

• Aware the contact score function is available on Pardot/CRM, however we're limited to only using Marketing Cloud.
• Do not have access to subscriber / profile management and contacts are imported via data extension. The scores will be using data logged within these various data extensions and using EmailAddress as the uniqueID.

First step is logging all the distinct clicks and opens recorded from sends.

Second step is aggregating them later to each EmailAddress and using the URL type, number of clicks and opens against sends to calculate scores and upsert into a 'score' data extension.

For the first step - Set up this query (Successfully validated)

SELECT distinct
 s.SubscriberKey,
 s.EmailAddress,
 e.JobID,
 e.BatchID,
 c.isunique AS 'Clicks',
 o.isunique AS 'Opens',
 c.URL
FROM [_sent] e
LEFT JOIN [_open] o
 ON e.JobID = o.JobID AND e.ListID = o.ListID AND e.BatchID = o.BatchID AND e.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN [_click] c
 ON e.JobID = c.JobID AND e.ListID = c.ListID AND e.BatchID = c.BatchID AND e.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN [_subscribers] s
 ON e.SubscriberKey = s.SubscriberKey

to get this into a Data Extension - set as so...

but when running the query via automation, got the message

'Automation Failed due to system error'

Start Time and Error Time was within 5mins of each other.

Just trying another test by updating the query to only map on SubscriberKey (Assume SubscriberID would add another layer of unnecessary search and matches with sends based on Data Extension entries):

SELECT distinct
 s.SubscriberKey,
 s.EmailAddress,
 e.JobID,
 c.isunique AS 'Clicks',
 o.isunique AS 'Opens',
 c.URL,
 c.LinkName
FROM [_sent] e
LEFT JOIN [_open] o
ON e.JobID = o.JobID AND e.SubscriberKey = o.SubscriberKey and o.IsUnique = 1
LEFT JOIN [_click] c
ON e.JobID = c.JobID AND e.SubscriberKey = c.SubscriberKey and c.IsUnique = 1
LEFT JOIN [_subscribers] s
ON e.SubscriberKey = s.SubscriberKey

AND updated the Data Extension to have Boolean fields for clicks and opens, with everything but EmailAddress as Nullable:

but when running the query via automation on the above, got the message

'Query Failed: Timeout'

Start Time and Error Time was within 30mins of each other.

Any ideas on what the best query snippet could be on this? With the first query being more specific, guess this helps with timeout issues but can't get it right.

Answer 1

For simple scoring, I usually recommend using the following queries. I have broken this apart into 5 queries to allow for long processing times (to avoid time outs) as well as to allow for easy debugging.

DE:(Subscribers_stats)

SubscriberKey | EmailAddress | Clicks | Opens | Score

Query 1: (Gathering Subscribers)

SELECT  SubscriberKey
        , EmailAddress
FROM _Subscribers

/* Target: Subscribers_stats */
/* Action: Overwrite */

Query 2: (Adding in Click Counts)

SELECT  SubscriberKey
        COUNT(*) as Clicks
FROM _Click
GROUP BY SubscriberKey

/* Target: Subscribers_stats */
/* Action: Update */

Query 3: (adding in Open Counts)

SELECT  SubscriberKey
        COUNT(*) as Opens
FROM _Open
GROUP BY SubscriberKey

/* Target: Subscribers_stats */
/* Action: Update */

Query 4: (setting null to 0)

SELECT  SubscriberKey
        , CASE
            WHEN Clicks IS NULL THEN 0
            WHEN Clicks IS NOT NULL THEN Clicks
        END as Clicks
        , CASE
            WHEN Opens IS NULL THEN 0
            WHEN Opens IS NOT NULL THEN Opens
        END as Opens
FROM Subscriber_stats

/* Target: Subscribers_stats */
/* Action: Update */

Query 5: (calculating score - formula below is example only)

SELECT  SubscriberKey
        , SUM(Clicks * 3, Opens * 1) as Score
FROM Subscriber_stats

/* Target: Subscribers_stats */
/* Action: Update */

This will then provide you with the subkey, email address, total clicks, total opens and overall score of each subscriber inside of your _Subscribers Dataview for the last 6 months.

Please note that the action I have on Query 1 is overwrite to remove any old records prior to the 6 month mark. If this is run to include those beyond 6 months old, there will be more that will need to be added to the Click and Open counting queries and will further increase processing times.