Custom Scoring with Marketing Cloud and Salesforce
As you may know, I work in the domain of Pardot and Marketing Cloud. Pardot has a default scoring functionality and model that allows for different prospect activities to be scored. Now, what happens if you are not using Pardot but Marketing Cloud? Well, by default nothing; Marketing Cloud does not come with a scoring model. But that doesn’t mean you cannot build it yourself. I have met several companies that were looking for a way to score there leads and contacts based on email opens and clicks, so I have built this using Marketing Cloud’s Journey Builder and Salesforce’s Process Builder. I’ll here show how you can do a simple but still effective version.
Custom Fields and Process Builder
The first thing to do is build the actual scoring mechanism within Salesforce. On your lead and contact object create three fields:
- Type: Checkbox, Label: Email Opened
- Type: Checkbox, Label: Email Clicked
- Type: Number, Label: Score
Make sure that when you create these fields on your lead and contact that you also map them in the lead conversion, so especially the score is accurate.
We will use the two checkboxes to trigger the scoring mechanism so you can leave these fields of the page layout – or leave them on if you want to do some testing!
For the scoring mechanism, we will use Salesforce’s Process Builder; so head to setup and find the builder and create the following:
Name: Lead Scoring
Object: Lead
- Start the process when a record is created or edited
- Check “YES” to Recursion – Allow process to evaluate a record multiple times in a single transaction?
Criteria: Email Opened?
- Criteria for Executing Actions: Conditions are met
- Set Conditions: [Lead].Email_Opened__c Equals Boolean True
- Conditions: All of the conditions are met (AND)
Immediate Actions: Email Open Score
- Update a Record: Lead
- Filter the records you update based on these conditions:[Lead].Email_Opened__c Equals Boolean True
- Set Field Values: Email Opened Boolean False
- Set Field Values: Score Formula [Lead].Score__c +1
Criteria: Email Opened?
- Criteria for Executing Actions: Conditions are met
- Set Conditions: [Lead].Email_Clicked__c Equals Boolean True
- Conditions: All of the conditions are met (AND)
Immediate Actions: Email Click Score
- Update a Record: Lead
- Filter the records you update based on these conditions:[Lead].Email_Clicked__c Equals Boolean True
- Set Field Values: Email Clicked Boolean False
- Set Field Values: Score Formula [Lead].Score__c +5
Now create another process following the exact same steps but for the Contact object.
DE’s, SQL and Automation Studio
With the core Salesforce setup done let’s move to Marketing Cloud. In order for us to trigger the scoring in core Salesforce we first need to find those leads and contacts that have open or clicked an email and add them to a data extension.
From the Email Studio create a data extension.
Name: Opens and Clicks
- Used for sending: Yes
- Used for testing: No
- Subscriber relationship: SubscriberKey relates to Subscribers on Subscriber Key
Fields:
- SubscriberKey (Text – 254)
- SubscriberID (Number)
- IsUnique (Boolean)
- EventDateClick (Date) – Is Nullable
- EventDateOpen (Date) – Is Nullable
- EmailAddress (EmailAddress – 254) – Is Nullable
- IsContact (Boolean) – Is Nullable
- IsLead (Boolean) – Is Nullable
Next, we need to create the query to find the contacts and leads that have opened an email.
Name: Email Open
Selected Active Data Extension: Opens and Clicks
Update Type: Append
Query:
SELECT T1.SubscriberKey, T1.SubscriberID, T1.IsUnique, T1.EventDate AS EventDateOpen, T2.EmailAddress, CASE WHEN T1.SubscriberKey LIKE '003%' THEN 'TRUE' ELSE 'FALSE' END 'IsContact', CASE WHEN T1.SubscriberKey LIKE '00Q%' THEN 'TRUE' ELSE 'FALSE' END 'IsLead' FROM _Open T1 INNER JOIN _Subscribers T2 ON T1.SubscriberKey = T2.SubscriberKey WHERE T1.EventDate > DATEADD(day,-1,GETDATE()) AND IsUnique = 'True' AND (T1.SubscriberKey IN (SELECT Id From [Contact_Salesforce_1]) OR T1.SubscriberKey IN (SELECT Id From [Lead_Salesforce_1]))
NOTE: Make sure that your reference to the Salesforce synchronized DE has the correct naming.
Name: Email Click
Selected Active Data Extension: Opens and Clicks
Update Type: Append
Query:
SELECT T1.SubscriberKey, T1.SubscriberID, T1.IsUnique, T1.EventDate AS EventDateClick, T2.EmailAddress, CASE WHEN T1.SubscriberKey LIKE '003%' THEN 'TRUE' ELSE 'FALSE' END 'IsContact', CASE WHEN T1.SubscriberKey LIKE '00Q%' THEN 'TRUE' ELSE 'FALSE' END 'IsLead' FROM _Click T1 INNER JOIN _Subscribers T2 ON T1.SubscriberKey = T2.SubscriberKey WHERE T1.EventDate > DATEADD(day,-1,GETDATE()) AND IsUnique = 'True' AND (T1.SubscriberKey IN (SELECT Id From [Contact_Salesforce_1]) OR T1.SubscriberKey IN (SELECT Id From [Lead_Salesforce_1]))
NOTE: Make sure that your reference to the Salesforce synchronized DE has the correct naming.
With the SQL queries created we need to schedule them in an automation in Automation Studio.
Name: Lead and Contact Scoring
Step 1.1.: Query Activity: Email Open
Step 2.1.: Query Activity: Email Click
Schedule the automation to run daily
Tieing it all together with Journey Builder
Okay, so we now need to tie it all together, which we will do with Journey Builder. We will create a journey that is triggered by our automation in Automation Studio and the DE that we have created. The journey will make sure to update our checkboxes in Salesforce and the Salesforce Process Builder will do the rest.
So head to Journey Builder and create a new journey.
Name: Lead and Contact Scoring
Entry Audience: Opens and Clicks
Logic (see picture below):
- Decision split: Evaluate if the lead/contact has opened or clicked an email – I use: EventDateClicked is not Null
- Decision split: Evaluate if it’s a lead or a contact – I use: IsLead equal True
- Update lead activity.
- Salesforce Lookup Fields for Lead: Lead ID
- Journey Data – Entry – SubscriberKey
- When multiple matching records are found: Update last modified record
- When no records are found: Do not update
- Email Clicked = true
- Update contact activity.
- Salesforce Lookup Fields for Contact: Contact ID
- Journey Data – Entry – SubscriberKey
- When multiple matching records are found: Update last modified record
- When no records are found: Do not update
- Email Clicked = true
- Decision split: Evaluate if it’s a lead or a contact – I use: IsLead equal True
- Update lead activity.
- Salesforce Lookup Fields for Lead: Lead ID
- Journey Data – Entry – SubscriberKey
- When multiple matching records are found: Update last modified record
- When no records are found: Do not update
- Email Opened = true
- Update contact activity.
- Salesforce Lookup Fields for Contact: Contact ID
- Journey Data – Entry – SubscriberKey
- When multiple matching records are found: Update last modified record
- When no records are found: Do not update
- Email Opened = true
All there is left to do is activate it all, test it all, and that is it! You now have a simple custom scoring model using the best of Marketing Cloud and Salesforce. You can, of course, expand on this taking in other scoring scenarios. Just remember you need to collect the data and be able to relate it back to your lead or contact.
Written by
I think this solution is complicated using journey builder for just tracking email behaviour scoring.
If there is Marketing Cloud Connect integration, then every email send from marketing cloud connect can be synced to salesforce com “Individual Email Results”. Whether the contact / lead opened or clicked email will be sent to this object using connector. Can’t we just use process builder to update contact or lead object score based on the tracking details in Individual Email Results.
It’s a very good point! The reason why I have done it this way is that not every type of email will be synched to Salesforce. It’s only if your DE has been created by the connector the results will be available in Salesforce. Unless with any of the newer releases this has been changed.
Yeah, it’s a grey area if we look at documentation. If you create / store any DE within marketing cloud with 18 digit salesforce lead or contact id and select “Send Tracking Data to Sales Cloud ” option during email send , then the email will get populated in sales cloud under Individual Email Results.
There are multiple things to consider about this, in terms of what gets synced back. To my knowledge, the Data Extension still has to be physically located in the Salesforce Data Extension folder – and then it will sync, no matter if you select checkbox or not. But again, also depends on send type.
You mean if the Salesforce DE is updated then it synch’s back? That’s news to me, it was always one-way synch. How do you manage what’s master data then? But hey great if that is possible. I guess there are multiple ways of solving the scoring, however, I find this one gives some control though it has other downsides.
It’s a One-way sync, but i mean the individual tracking results. If you send to a data extension using content builder, where the data extension is located in regular DE folder – e-mail results Will not sync, regardless og whether or not you check the box. Similarly, standard triggered sends does not sync tracking results back. My test is 8 months old, so it May have changed, but the conclusion was back then – unless you use JB, the sendable data extension must be located in the SF Data Extension folder.
Ah! Got ya! Plus if you use multiple BU’s and want to segment on the BU level this can very well be the case. I know you can have a connector for each BU but back when I looked at it – probably also 8 months ago – that wasn’t the best solution it had limitations.
Great Topic,
Depending on the use case you can also leverage AMPScript to update the object on send which means no dependency on Process Builder or Automation Studio.
Very true! But that would mean your need to put it in all your emails – or in the footer of course. I guess there are many ways to solve this – it all comes down to what you said use case!
So this is a pretty awesome solution, that solves for some pretty common requests to be honest – really nice way of doing this! 🙂
One question though – have you tested this for scalability?
If i Open and Click e.g. 2 emails on the same day, I could potentially insert 4 rows into Journey Builder, pushing 4 updates towards same object in core. Have you tested if this breaks it?
Also, I was thinking… Given the limitation of the system logs / data view, I would argue that you could expand this “Build” with a send log Data Extension, where you register what you must – but one key thing would be to register email type. At a minimum, this would allow you to distinguish between transactional and commercial emails, which would allow you to classify your emails in more liberal ways, to weigh in on scoring. (e.g. do not score for opening an e.g. event confirmation, or score it higher a method of boosting the score)
I haven’t tested with very high volumes. But where I used this solution it would never have been an issue as it was more a B2B evaluation of leads and contacts. Now for the same email being clicked multiple times that shouldn’t be an issue as the SQL is looking for unique clicks in the past day. So for one email, you will only ever have one open and one click per subscriber, which was a deliberate evaluation of the lead/contact. But should you send out millions of emails every day, then I would agree with you to test if the volume breaks the flow.
As for the add-on I think that would be a brilliant idea. But that would make the SQL a bit more complex. I did a quick check and that information looks to be found in Job data view so you would need to Join on jobID to get that detail and perhaps extend the journey and process builder if you want to give a higher/lower score based on the job type and not just ignore the transactional emails. But hey the concept would remain the same and I will let it be up to whoever needs the solution to come up with the exact modifications 🙂
Interesting, and agree… with the Unique opens / clicks, you get part of the way. I would hope / assume that you would never have more than 2 different emails received and opened / clicked in a given day. So in that way it would hopefully scale.
I would simplify the latter part. In your emails / template (or however you set up your send log), at send time, you can write JobId + Email type into the Send Log DE using AMPscript (that’s the way your can customize what your emails are). Your SQL would, instead of using the Job View, would simply join the Send Log based on Job Id, if you only want to select e.g. Commercial Emails —> or, if you want the select everything, you simply select the email type field from Send Log DE and handle rest in journey.
But still – this is such a wonderful cross-cloud build, truly 🙂
I am struggeling with receiving correct scores for my contacts and leads. By opening and clicking one mail i get wierd scorevalues of 4 and 14 amongst other. Have you got any idea what might be happening and how to troubleshoot?
I have been able to fire all the process including the Journey Builder. However the update contact activity is failing to update Salesforce data. Any idea why this might be?