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... enter image description here

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:

enter image description here

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.

up vote 3 down vote accepted

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.

  • This is super handy, thanks @Gortonington. Will try Query 5 with a calculation using the Scores set in a DE, with a query to pull in points set by engagement. E.g. Case WHEN Link LIKE %.com/checkout% add [POINTS value] to Score. – Salvara Oct 11 at 14:42
  • I would also look at adding in amount sent as well. This is an important variable as someone with 4 opens but 123 sends should not be scored greater than someone with 2 opens on 10 sends. – Gortonington Oct 11 at 15:01
  • Aye - Was planning to use JobID to count up number of sends and find a fair calculation to score opens based opens / sends. Haven't reached this hurdle yet. – Salvara Oct 11 at 15:38
  • Turns out the initial set up of this environment doesn't include the subscribers being managed in marketing cloud, only able to populate lists in data extension for sends and these aren't saved into the 'All subscriber' base. Used the Query1 script to retrieve only SubscriberKey and it looks like no contacts from the sends had been updated into 'All Subscribers'. Good to discover this now using that method - Thanks Gortonington! – Salvara Oct 12 at 9:43

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.