Customizable MQL Reporting (Beyond the Pardot Lifecycle Report)
Lifecycle reporting is fundamental to Marketing Ops. Knowing how many marketing qualified leads (MQL) and sales qualified leads (SQL) you have captured in a specific period of time are KPIs that improve sales and marketing alignment and demonstrate shared success.
It’s not always plain sailing. I’m sure many marketers envy teams with a seamless, conflict-free lead lifecycle. There are reasons why organizations end up in a mess – what counts as an MQL or SQL isn’t clearly defined, there’s a lack of structure in the hand-off process, the appropriate reports don’t exist, and countless other causes.
Marketers, it’s ok – your leads probably don’t suck. It’s more likely that they are just misunderstood.
We want to inform Salesforce and Pardot (Account Engagement) what our MQL and SQL checkpoints actually are.
This guide will show you how to do this – a relatively light-weight solution that will improve your marketing reporting – and finally set the record straight on how many MQLs and SQLs are being generated.
Pardot Lifecycle Report
Pardot comes with a Lifecycle Report, out-of-the-box. This ‘velocity’ report is designed to show the speed prospects have progressed through your sales cycle. You can view the totals for MQL, SQL, and Won Customers. Great!
You probably rushed to check this out in your own Pardot account, only to panic when you saw something like this:
My first piece of advice: Don’t use these numbers without fully understanding how they are calculated. How do you know if they are misleading?
This is how Pardot defines MQL / SQL:
- MQL: The date when the prospect is assigned to a Salesforce user.
- SQL: The date the Salesforce Opportunity was created.
This setup works if you use those exact definitions. Instead, lead generation and qualification is a complex web of lead entry points and tracked touch points:
- Data sources: You have lead data entering Salesforce from sources other than Pardot, for example, Leads and Contacts entered manually into Salesforce.
- Sync to Salesforce: You assign all new prospects straight away, so that records sync to Salesforce for reporting benefits, or early qualification.
- Point of SQL: An Opportunity stage (not the first stage) later in the pipeline is used as the ‘sales-qualified’ point.
This is not an exhaustive list, with other reasons I haven’t mentioned – however, I hope that this has got you thinking deeper about how your own funnel operates.
Step 1: Define What Marketing Qualified Means to You
One size doesn’t fit all. The guide below will help you through the process of defining what a Marketing Qualified Lead (MQL) is to your organization:
Step 2: Create Checkbox Fields
Create four fields on both the Salesforce Lead object and on the Contact object (so, eight fields in total):
- MQL (type = checkbox),
- MQL Date (type = date),
- SQL (type = checkbox),
- SQL Date (type = date).
Learn how to create Salesforce custom fields.
- Field Mapping: Don’t forget to map Lead fields to their counterparts on the Contact object. This will mean that data is carried across when the Lead is converted into a Contact. Learn how to map Lead fields.
- Field Access: Think twice about who should see and edit these fields. Avoid unnecessary clutter on your pages, and rouge users editing these fields. You will be able to use the fields in reports without them being added to the page layout.
Step 3: Create Prospect Fields
Create the same fields on the prospect object that you created on Leads and Contacts in step 1. The way that Pardot and Salesforce checkbox fields work together is quirky; instead of checkboxes, the Pardot equivalent is “radio button”:
- MQL (type = radio button)
- SQL (type = radio button)
The ‘MQL Date’ and ‘SQL Date’ fields remain as date fields. Map to the fields on the Salesforce side before saving each prospect field.
- Sync Behavior: Choose the ‘most recently updated record’. I advise this because your MQL trigger points may be set off from either the Salesforce or Pardot side.
- API Name Trap: Don’t fall into this trap.
Step 4: Create the Velocity Field
The velocity field will capture the time a prospect took to go from MQL to SQL: one qualification checkpoint to the next.
A Salesforce formula field will do the trick. On the Lead and Contact objects create a custom field:
- Name: Days from MQL to SQL
- Field type: Formula
- Formula Return Type: Number
- Enter this formula in the editor box: DATEVALUE(“SQL_Date__c”) – DATEVALUE(“MQL_Date__c”)
- Decimal places: 0
Step 5: Create the MQL / SQL Trigger Points and Time Stamps
If you skipped Step 1 (yes, I’m looking at you!), now is the time to revisit your definitions of MQL and SQL in your organization before going any further.
Once everyone is onboard with the definitions, let’s look at our options for automating the checkpoint triggers:
Option 1 – Pardot Automation Rules
Automation rules are the simplest option to use, see an example below. Reasons to not use this option are:
- If you’ve reached your Automation rule limit.
- Leads can cycle back through qualification multiple times.
Option 2 and 3 – Salesforce Automations
Option 2 – Salesforce Record-Triggered Flow:
Flows are great for updating MQL/SQL fields if the trigger is also on the Lead/Contact . For example, when a Lead’s score reaches at least 100 and the Lead Status is ‘Contacted’, then update the Lead fields you wish, such as the MQL Date or checkbox.
Option 3 – Triggers:
You will need a developer to code Apex triggers when you run out of point-and-click options for certain use cases. For example, to update a contact’s SQL status and date based on an opportunity status change*. You can point them to this article.
Note: the Contact needs to be related to the Opportunity as a Contact Role, so get into good habits adding them to Opportunities!
Now you have set up the fields and automation, you will have some great reporting to explore – even by using standard Salesforce reports and dashboards.
Plus, if you did decide to add the fields to the page in Step 2, then you would end up with something like this:
Rikke’s Velocity Calculation
This tutorial has enabled you to define MQL/SQL checkpoints in line with how your organization operates. It’s a relatively light-weight solution that’s easy to set up.
You may need to go further, for more robust reporting. Rikke’s popular solution involves a velocity custom object, and should be something you consider before building anything.
Knowing how many marketing qualified leads (MQL) and sales qualified leads (SQL) will help improve sales and marketing alignment and demonstrate shared success.
Your Leads don’t suck – they’re just misunderstood. You need to inform Salesforce and Pardot (Account Engagement) what your MQL and SQL checkpoints actually are, finally set the record straight on how many MQLs and SQLs are being generated.
Hi! This was super helpful and easy to follow! I do have one suggestion though. I’m unsure if it’s because of the edition or maybe just updates in Salesforce in general, but I would maybe make a note around Step 4, the Velocity Field and how you have to enter that formula. Unfortunately, the way it’s listed now (SQL_Date__c – MQL_Date__c), does not work and leads to formula syntax errors. I also kept getting another error that those API fields could not be used in this formula. After some forum digging and playing around with functions, I was able to figure it out! For this formula, you have to enter the DATEVALUE function and use double quotes in the API field names. This is what my final formula was: DATEVALUE(“SQL_Date__c”) – DATEVALUE(“MQL_Date__c”) and this led to no syntax errors and finally let me save it correctly. Again, just a suggestion as it looks like Salesforce won’t let you enter it the other way anymore. Thanks again for the article!
Hi Shonna, thanks for pointing this out! Yes, absolutely DATEVALUE is required – I must have overlooked that in the write-up.
Glad you found the post useful!
I am still getting a syntax error… any other recommendations on how to fix this?
This is what I typed in and it’s not working… DATEVALUE(“SQL_Date__c”) – DATEVALUE(“MQL_Date__c”)
I might be missing something, but how do we actually create the funnel report?
This is excellent, as always. Thank you!