Velocity Calculation of the Marketing to Sales Funnel … technical insights by Rikke Hovgaard

Last year Salesforce launched “Wave for B2B Marketing” now known as “B2B Marketing Analytics” giving Pardot users insight in their marketing engagement and pipeline with 3 dashboards. I’ve been lucky enough to get access to the tool and I’ve done a blog post as well as two talks for the London Pardot User Group on the topic. Now, most Pardot users would agree that the Pardot reporting is not the best as there is limited ability to customize reports and graphs. So pulling this data through to Salesforce or Einstein Analytics would instantly give more flexibility. Again Pardot users would know that activity and engagement data is not actually stored in Salesforce, hence it is not possible to create Salesforce reports on that data. This is where “B2B Marketing Analytics” comes in handy, it pulls through the engagement data and allows you to view that data in a highly dynamic and flexible way.

How Pardot MQL velocity is calculated

Sounds too good to be true? Well, to be honest, I have one major issue with the App, I cannot customize it. The major issue is that Pardot calculates the funnel velocity for you and MQL is based on lead assignment:

Marketing Qualified Leads (MQL) is “the number of prospects created in the specified time frame (in the Date Range selector for the report) that became assigned. Assignment means that the Marketing team has qualified this lead for their Sales team. Assignments can occur in Pardot manually, via the API, via a CRM sync, or during a Pardot import.”

Read more about the definitions in the Pardot knowledge article.

Most companies using Pardot have a direct assignment in Salesforce as soon as a prospect is created in order to associate them with a Salesforce campaign. However, that means that the Prospect creation time ultimately also will be the time of it being a marketing qualified lead, which in my experience never is the case in reality.

Change your Salesforce data model

For a talk on Pardot reporting, I was addressing the above issue. My solution was to do the calculation in Salesforce using formula fields and process builder to time stamp the prospect at the different stages of their lifecycle. Hereafter I could pull all the fields into Wave using the data flow and even combine it with existing Pardot datasets using the functionality of recipes. Though the solution worked I wasn’t happy with the randomness of calculation on different objects (leads, contacts, and opportunity). With some research help from my colleague Julian Casson I decided to improve my solution and I decided to change the data model in Salesforce and introduce a new object that holds the information on velocity calculation and is maintained with a few processes created in the process builder.

Solution – the prerequisites

In Salesforce create an object called Velocity with the following fields:

• First Visitor Activity Date – Field type Date/Time
• Prospect Conversion Date – Field type Date/Time
• MQL Date – Field type Date/Time
• SQL Date – Field type Date/Time
• Closed Won Date – Field type Date/Time
• Closed Lost Date – Field type Date/Time
• Lead – Field type Lookup to Lead
• Contact – Field type Lookup to Contact
• Opportunity – Field type Lookup to Opportunity
• Opportunity Stage – Field Type Formula (text) – Logic: TEXT(Opportunity__r.StageName)
• Prospect Velocity – Field Type Formula (number 18,0) – Logic: FLOOR(Prospect_Conversion_Date__c – First_Visitor_Activity_Date__c)
• MQL Velocity – Field Type Formula (number 18,0) – Logic: FLOOR(MQL_Date__c – Prospect_Conversion_Date__c)
• SQL Velocity – Field Type Formula (number 18,0) – Logic: FLOOR(SQL_Date__c – MQL_Date__c)
• Closed Won Velocity – Field Type Formula (number 18,0) – Logic: FLOOR(Closed_Won_Date__c – SQL_Date__c)
• Closed Lost Velocity – Field Type Formula (number 18,0) – Logic: FLOOR(Closed_Lost_Date__c – SQL_Date__c)

Solution – automatic processes

I have defined three different stages where I want to capture dates for my velocity object using the Salesforce Process Builder; on lead creation, on lead update and on opportunity update. Of course, each date is captured under different circumstances depending on the information gathered on the lead or opportunity. Most might agree with the criteria I am using for capturing my dates however each business can have their own logic, which should be taken into consideration when implementing this solution. You might also want to extend on it as I cannot guarantee that the solution I am showing in this blog take every business scenario into consideration. However, my approach is flexible and you should be able to make it your own.

Lead process builder on creation

When a lead is created a velocity record must be created and associated with the lead. This way we can make sure all the dates collected are in the same place and related to the lead and eventually contact and opportunity.

Name: Create Velocity Record

Object: Lead

• Start the process only when a record is created

Criteria: Create Velocity Record

• Criteria for Executing Actions: No criteria—just execute the actions!

Immediate Actions: Create Velocity Record

• Create a Record: Velocity
• Set Field Values: Lead Reference [Lead].Id

Lead process builder on update

Every time a lead is updated there might be new data we need to populate on the velocity record depending on where this lead is in the marketing to sales funnel.

Name: Lead Update Velocity

Object: Lead

• Start the process when a record is created or edited

Criteria: Update Velocity Record

• Criteria for Executing Actions: No criteria—just execute the actions!

Immediate Actions: Update Visitor Date

• Update a Record: Velocity
• Criteria for Updating Records: Updated records meet all conditions
• Filter the records you update based on these conditions: First Visitor Activity Date Is Null Boolean True
• Set Field Values: First Visitor Activity Reference [Lead].pi__first_activity__c

Immediate Actions: Update Prospect Conversion Date

• Update a Record: Velocity
• Criteria for Updating Records: Updated records meet all conditions
• Filter the records you update based on these conditions: Prospect Conversion Date Is Null Boolean True
• Set Field Values: Prospect Conversion Date Reference [Lead].pi__conversion_date__c

Name: Lead MQL Velocity

Object: Lead

• Start the process when a record is created or edited

Criteria: Is MQL?

• Criteria for Executing Actions: Conditions are met
• Set Conditions: [Lead].OwnerId Does Not Equals ID 00558000001LohnAAC
• Conditions: All of the conditions are met (AND)

Immediate Actions: Update MQL date

• Update a Record: Velocity
• Criteria for Updating Records: No criteria—just update the records!
• Set Field Values: MQL Date Formula NOW()

Name: Lead Conversion Velocity

Object: Lead

• Start the process when a record is created or edited

Criteria: Lead Conversion

• Criteria for Executing Actions: Conditions are met
• Set Conditions: [Lead].IsConverted Equals Boolean True
• Conditions: All of the conditions are met (AND)

Immediate Actions: Update Converted Data

• Update a Record: Velocity
• Criteria for Updating Records: No criteria—just update the records!
• Set Field Values: Contact Reference [Lead].ConvertedContactId
• Set Field Values: Opportunity Reference [Lead].ConvertedOpportunityId
• Set Field Values: SQL Date Formula NOW()

Opportunity process on close

Once the lead has converted we still want to capture information from the sales process in order to complete the velocity calculations. So we need one more process on the opportunity object.

Name: Opportunity Update Velocity

Object: Opportunity

• Start the process when a record is created or edited

Criteria: Is Closed Won?

• Criteria for Executing Actions: Conditions are met
• Set Conditions: [Opportunity].IsClosed Equals Boolean True
• Set Conditions: [Opportunity].IsWon Equals Boolean True
• Conditions: All of the conditions are met (AND)

Immediate Actions: Update Closed Won Date Velocity

• Update a Record: Velocity
• Criteria for Updating Records: No criteria—just update the records!
• Set Field Values: Closed Won Date Formula NOW()

Criteria: Is Closed Lost?

• Criteria for Executing Actions: Conditions are met
• Set Conditions: [Opportunity].IsClosed Equals Boolean True
• Set Conditions: [Opportunity].IsWon Equals Boolean False
• Conditions: All of the conditions are met (AND)

Immediate Actions: Update Closed Lost Date Velocity

• Update a Record: Velocity
• Criteria for Updating Records: No criteria—just update the records!
• Set Field Values: Closed Lost Date Formula NOW()

Let Einstein Analytics show you the data

When you have created all the fields and the processes in the Salesforce process builder you have all the information you need in order to create a new data set in Einstein Analytics. I’m sure there might be other information that is worth capturing in the marketing to sales funnel. I hope that this blog has inspired you to be creative with the process builder and expand on the velocity object.

This was originally posted on Rikke’s blog, Salesforce Blogger.