Custom Scoring with Marketing Cloud and Salesforce

Rikke Hovgaard 26. October 2017 Marketing, Winter 18 14
5
(3)

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

  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.

 

How useful was this post?

Click on a star to rate useful the post is!

Written by


14 thoughts on “Custom Scoring with Marketing Cloud and Salesforce”

  • 1
    Unais Komath on October 29, 2017 Reply

    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.

    • 2
      Rikke on October 29, 2017 Reply

      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.

  • 3
    Unais Komath on October 29, 2017 Reply

    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.

    • 4
      Kenneth Wagner on February 3, 2018 Reply

      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.

      • 5
        Rikke on February 4, 2018 Reply

        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.

        • 6
          Kenneth Wagner on February 4, 2018 Reply

          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.

          • 7
            Rikke on February 4, 2018

            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.

  • 8
    Oscar mccall on November 5, 2017 Reply

    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.

    • 9
      Rikke on November 5, 2017 Reply

      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!

  • 10
    Kenneth Wagner on February 3, 2018 Reply

    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)

    • 11
      Rikke on February 4, 2018 Reply

      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 🙂

      • 12
        Kenneth Wagner on February 4, 2018 Reply

        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 🙂

  • 13
    Petter Pettersen on July 17, 2019 Reply

    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?

  • 14
    DT on July 15, 2021 Reply

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.