Attempting to create a 'score' data extension for each email contact, to measure engagement levels with email sends.
- 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
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.