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 their 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. Here I will show you 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 set-up 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

Pulling 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.
Rikke Hovgaard is a makepositive certified Salesforce (CRM) consultant. Rikke is an expert in helping businesses get the most out of the application whether it being Sales Cloud, Service Cloud, Pardot, Analytic Cloud or a combination of those. Click here to read more of Rikke’s blogs.