Velocity Calculation of the Marketing to Sales Funnel

Rikke Hovgaard 10. August 2017 Marketing, Summer 17 7
5
(1)

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.

How useful was this post?

Click on a star to rate useful the post is!

Written by


7 thoughts on “Velocity Calculation of the Marketing to Sales Funnel”

  • 1
    Lucy on March 7, 2018 Reply

    Hi Rikke, I remember you did this talk at the user group and would love to reference this piece and the solution in something that I’m putting together. I’ve read the intro through a couple of times and want to clarify that this solution is possible even without B2B marketing analytics? You say that the “activity and engagement data is not actually stored in Salesforce, hence it is not possible to create Salesforce reports on that data.” but, if a company only wanted to recreate the Pardot velocity funnel in Salesforce and not need to reference the specific activity, could it be done just with SF? Sorry if it sounds like a stupid question, just want to be sure I’m not mis-reading 🙂 Thanks, Lucy

    • 2
      Rikke on March 7, 2018 Reply

      Hi Lucy, be my guest 🙂 The idea of this solution was that the velocity report in Pardot will always be wrong if you assign Prospects in Salesforce right away to associate them to campaigns. So the idea was to capture the relevant time stamps in Salesforce. Now I suggest you could pull in this new data in EA just because the funnel there will be wrong too. You can of course use this new velocity object in Salesforce and create your reports there, that should totally be possible. Not sure how the project Pardot edge is going to effect this solution, but until that is GA this is still relevant. At that time you might want to stamp the activity instead with the velocity stage so you can see how many emails did it take to go from on stage to another or how many white papers did they consume before being sales qualified, you can then use that for optimizing the lead generation. Hope that made sense? Either way you are not misreading 🙂 Cheers Rikke

  • 3
    Brian on April 9, 2019 Reply

    Hello Rikke,
    Once you have created this in Salesforce how would you pull it into EA as a dataset. Would you just create a dataflow for the new velocity object, due an append and register it as a dataflow?

    • 4
      Rikke on April 29, 2019 Reply

      Well, the object is there so you pull in the main object. You can augment related data into it or use a formula field to copy it down. Just remember that might have an impact on your data sync.

  • 5
    Brian on April 24, 2019 Reply

    Name: Lead MQL Velocity

    Set Conditions: [Lead].OwnerId Does Not Equals ID 00558000001LohnAAC

    Can you explain this condition and where the ID code came from and how to update this with the correct Owner ID one based on the instance we are working in.

    • 6
      Rikke on April 29, 2019 Reply

      Your marketing leads probably go to a certain user or queue and you want to make sure they are not still in a prospecting stage, so I defined to look at the owner to help define when it had moved to MQL.

  • 7
    Josh Druck on October 22, 2019 Reply

    Love the post!

    My question is how does Salesforce and Pardot know which opp to add the MQL to? What if we have 4 opps that were created with the same account. Which opp would the MQL be placed on? Maybe this has something to do with Contact Roles? Is there a way we can ensure that the correct MQL is being placed on the correct opp?

Leave a Reply to Lucy Cancel 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.