Customisable Pardot Prospect Lifecycle Report: MQL Reporting How You Want It

Share this article...

Prospect Lifecycle reporting is a Marketing Ops fundamental. Knowing the number of Marketing Qualified Leads (MQL) and Sales Qualified Leads (SQL) you have captured, and how many within a specific period of time, are KPIs that improve sales and marketing cooperation and show combined efforts.

A lack of definition and structure on what an MQL or SQL is for you, or reading off the wrong reports, can leave you confused (if your MQL criteria is not automated), or the sales team complaining that the leads suck (if your MQL criteria is too soft).

So, Marketers, it’s ok – your leads probably don’t suck. It is likely that they are just misunderstood.

As we saw in Yvette’s post, Pardot comes with a nice-looking Lifecycle Report out-of-the-box. This ‘velocity’ report is designed to show the speed prospects have progressed through your sales cycle.

On here, you can view 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:

Do not use these numbers without fully understanding the calculation behind them – or risk being misled.

How do you know if they are misleading? This is how Pardot defines MQL / SQL:

  • MQL: the date when the lead is assigned to a salesperson
  • SQL: the date the opportunity was created

This setup works well if you have a linear, neat lead generation and sales process – but, more often than not, the process is not linear. It’s instead a complex web of lead entry points and tracked touchpoints. This report will not work for you if:

  • Data sources: you have lead data entering Salesforce from sources other than Pardot, for example: Leads and Contacts entered into Salesforce.
  • Sync to Salesforce: you ‘assign’ all new prospects straight away for the records to sync to Salesforce (for sales attention, or reporting)
  • Point of SQL: a later opportunity stage (not the first stage) is officially ‘sales-qualified’

This is not an exhaustive list, and I hope that this has got you thinking deeper about how your own funnel operates. Now, we need to tell Salesforce and Pardot what MQL and SQL checkpoints we actually want. I’m going to run through a tutorial on how to do this – a relatively light-weight solution that will improve your marketing reporting.

Psst: If you are unfamiliar with this kind of configuration, I have included links to instructions wherever possible. To request a more in-depth post, use the survey at the bottom of this post to let me know.

Step 1: Define Your Marketing Qualified Leads (MQLs)

Step 2: Create Checkbox Fields

Create four fields on both the Salesforce Lead object and on the Contact object (8 fields in total):

  • ‘MQL’: type = checkbox
  • ‘MQL Date’: type = date
  • ‘SQL’: type = checkbox
  • ‘SQL Date’: type = date

Learn how to create Salesforce custom fields.

Tip 1: don’t forget to map Lead fields to Contact fields so that any data is carried across from lead to contact upon lead conversion!
Learn how to map Lead fields.
Tip 2: think twice about who should/needs to see these fields. You will be able to use the fields in reports without them being added to the page layout (avoiding unnecessary page clutter).


Step 3: Create Fields on Prospect

Create the same fields on the Prospect – but, the way that Pardot and Salesforce checkbox fields work together is quirky.

  • ‘MQL’ / ’SQL’: type = Radio button

The ‘MQL Date’ and ‘SQL Date’ fields remain as date fields. Select your Salesforce fields before clicking save.

Tip 1: Choose your sync behaviour to the ‘most recently updated record’. I advise this because your MQL trigger points may be set off from either the Salesforce or Pardot side.

Tip 2: Don’t fall into the API field name trap!

Step 4: Create Your Velocity Field


To recreate a key metric from the out-of-the-box Prospect Lifecycle Report, we need to add field a that will tell us 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. For the contact object:

  • Name = Days from MQL to SQL
  • Choose output type/Formula Return Type (step 2) = Number


  • Enter formula in the editor box: DATEVALUE(“SQL_Date__c”) – DATEVALUE(“MQL_Date__c”)
  • Decimal places = 0


Step 5: Create Your MQL / SQL Trigger Points & Time Stamps

Now comes the part to get creative. If you skipped Step 1 (yes, I’m looking at you), now is the time to revisit your definition of MQL and SQL in your organisation and get a unanimous agreement before going any further.

Once everyone is onboard with the definitions, so let’s look at our options for automating the checkpoint triggers:

  • Option 1 – Pardot Automation Rules: the simplest option to use, below is an example:

  • Option 2 – Salesforce Workflow Rules: these are great for updating MQL/SQL fields if the trigger is also on the Lead/Contact – for example: when a Lead’s score reaches 100 and the Lead Status is ‘Contacted’, then update the lead to MQL.

Note: Workflow Rules are not available on Professional Edition.

  • Option 3 – Salesforce Process Builder: similar to a workflow rule, but a more powerful option to use because Process Builder flows can update fields on other records related to the record that initiated the change – for example: when a Contact’s score reaches 100 and the Account is a ‘Target Account’ (custom account field), then update the contact to MQL.
  • Option 4 – 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.
    Tip 1: the contact needs to be related to the opportunity as a Contact Role, so get into good habits adding them to opportunities!

Tip 2: you can also achieve this with Pardot automation rules! (Option 1, and much, much simpler)

Finishing Touches

Now you have set up the fields and automation, you will be able to get some great reporting 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 customise your MQL / SQL checkpoints, a solution that is relatively light-weight and easy to set up. For a beefier solution that involves a velocity custom object, and leveraging Einstein Analytics for next-level reporting, Rikke has a very popular solution that you can graduate to when you and your organisation are ready.


Knowing the number of Marketing Qualified Leads (MQL) and Sales Qualified Leads (SQL) you have captured, and how many within a specific period of time, are fundamental marketing KPIs. This tutorial has enabled you to customise your MQL / SQL checkpoints, a solution that is relatively light-weight and easy to set up. No longer be misled with skewed reports, because you have defined exactly what an MQL and SQL means to your organisation.


2 thoughts on “Customisable Pardot Prospect Lifecycle Report: MQL Reporting How You Want It

  1. 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!

    1. 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!

Add Comment