Custom Scoring with Marketing Cloud and Salesforce by Rikke Hovgaard

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]))

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):

1. Decision split: Evaluate if the lead/contact has opened or clicked an email – I use: EventDateClicked is not Null
2. Decision split: Evaluate if it’s a lead or a contact – I use: IsLead equal True
3. Update lead activity.
   1. Salesforce Lookup Fields for Lead: Lead ID
   2. Journey Data – Entry – SubscriberKey
   3. When multiple matching records are found: Update last modified record
   4. When no records are found: Do not update
   5. Email Clicked = true
4. Update contact activity.
   1. Salesforce Lookup Fields for Contact: Contact ID
   2. Journey Data – Entry – SubscriberKey
   3. When multiple matching records are found: Update last modified record
   4. When no records are found: Do not update
   5. Email Clicked = true
5. Decision split: Evaluate if it’s a lead or a contact – I use: IsLead equal True
6. Update lead activity.
   1. Salesforce Lookup Fields for Lead: Lead ID
   2. Journey Data – Entry – SubscriberKey
   3. When multiple matching records are found: Update last modified record
   4. When no records are found: Do not update
   5. Email Opened = true
7. Update contact activity.
   1. Salesforce Lookup Fields for Contact: Contact ID
   2. Journey Data – Entry – SubscriberKey
   3. When multiple matching records are found: Update last modified record
   4. When no records are found: Do not update
   5. 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.