There’s one focus that Pardot has become known for: tracking a prospect’s first touch; however, once you begin mixing in cross-functional teams and multiple data sources (including Salesforce), the clarity on ‘first touch’ becomes increasingly blurred.
If only there was a simple report that would show which campaign was the prospect’s first touch? A report that won’t require tons of automation or custom code behind the scenes, and one that’s not at the mercy of human error… you’ve come to the right place!
This post will walk you through the report I built to show which campaigns were generating the most leads, using one simple feature: a row-level summary field. I will also go on to discuss the solution caveats and limitations with other options I explored – so keep reading on to the end!
The First Touch, Blurred
There’s one focus that Pardot has become known for: tracking a prospect’s first touch. A ’touch’ refers to a prospect’s interaction with your organisation through the buyer journey, whether that be on the website, online resources, at trade shows, and more. As I already mentioned in the introduction, once you begin mixing in other teams and multiple data sources, the clarity on ‘first touch’ becomes increasingly blurred. A common frustration is this: salespeople enter leads into Salesforce, the leads syncing to Pardot as prospects, and use the connector campaign as their prospect campaign (therefore, disguising the true first touch).
At least in Salesforce, we have a birds-eye view of all lead and contact data, all sources, and most importantly, all activity.
Defining ‘Net New’
Through this post, I will use the term ’net new’ leads. These are leads that were new to the database, created as a result of the campaign activity. This does not include leads that were previously in your Salesforce database, even if the campaign in question is their first marketing touchpoint.
Sneak Peek at the Finished Product
I don’t usually show the end result before the tutorial, but it makes sense on this occasion. Here’s the report, before I proceed to explain each part:
First, let’s look at the ’NetNewLead’ column, the most key mechanism of the report. It’s a row-level summary formula , a feature that Admins have been clamouring for and was delivered in the Winter ‘20 release.
The purpose of this report is to distinguish between leads that were created at the same time as the campaign (and therefore, separate those leads that were in Salesforce prior to the campaign). It uses two crucial dates:
- Lead Created Date
- Campaign Member First Associated Date
This formula will show ‘1’, if the Lead Created Date and Campaign Member First Associated Date are the same (and 0 if not).
Step 1: Create the Report
Create a new report with the type ‘Campaigns with Leads and Converted Lead Information’. Add the fields you want to appear as columns in the report.
Step 2: Add Row-level Summary Formula
Click on the dropdown arrow in the columns section, and select ‘Add Row-level Formula’.
- Set the column name: Net New Lead (or similar)
- Formula output type: Number
- Decimal places: 0
Paste the following formula in the box, and click ‘validate’ just to be sure:
If(DateValue(CREATED_DATE)=DateValue(MEMBER_CREATED_DATE),1,0)
If you would like further information, you can check out Arpit’s quick tutorial on Row-level Summary Formulas.
Step 3: Group your Report
You may have decided how you would like to group your report, most likely to will be by campaign name, but there are other options eg. campaign type.
The Net New Leads column should have been automatically added as a column. Now you can choose how to summarise this.
- Sum: shows the total for your groups, eg. how many net new leads were in Campaign A.
- Average: eg. what’s the average number of net new leads in Tradeshow campaigns?
- Min: eg. what is the maximum number of net new leads in 2019 campaigns?
- Max (similar to the above)
Add a chart to visualise your data too! Even a simple bar chart like the one below is a great comparison tool between campaign successes.
Not 100% Accurate
I hold my hands up, this report will not be 100% accurate. Before you throw it away into the rubbish bin, hear me out.
If someone is added to more than 1 campaign on the same day, they will appear as ‘net new’ in 2 campaigns; if you can live with this slight skewing of data, then this is still a practical solution for you!
Failed Attempts
You may not be interested in this section, I understand. I did try out a few alternative solutions that I want to share with you, especially why they failed so you don’t turn down a dead end path yourself.
Failure #1: ‘Campaign’ Field on Leads
There’s a field called ‘Campaign’ on the Lead object. As a lookup between leads and campaigns, I thought it would be ideal. However, it’s known to have exceptional field behaviour, or as Steve Mo put it, “some funky kind of ‘one-time use’ field that is only editable when you create a new Lead”. If users don’t input the campaign upon creating the lead, the chance to use that field is lost.
Failure #2: Net New Formula Field on Leads
I thought a checkbox formula field would be a neat solution, without requiring automation or data mass updates. So I went about creating the checkbox field on the campaign member object.
Why am I brushing past this? Once I had created the field and began testing, it became clear that the logic was flawed. When I converted a lead into a contact, the checkbox was lost. This is because Salesforce can no longer ‘read’ the converted lead record, and so cannot piece together the information required for the formula field.
Failure #3: Net New Workflow on Campaign Members
I abandoned this for now in in the interest of time for the end of year reporting needs.
The workflow checks the campaign member checkbox to true, and remains that way. By using automation to check the checkbox, we are no longer at the mercy of flaky formulas (Failure #2). However, errors became apparent while testing, because Salesforce was attempting to both create and update the record at the same time. Further tweaking is required, but watch this space in case I have a breakthrough!
Summary
’Net new’ leads are leads that were new to the database, created as a result of the campaign activity. I have shown you how to create a row-level formula in a Salesforce Report that shows net new vs. not net new, then summarise the field to answer different questions you may be asking about your campaign activity.
As I have already stressed, this report will not be 100% accurate (if someone is added to more than 1 campaign on the same day, they will appear as ‘net new’ in 2 campaigns); if you can live with this slight skewing of data, then this is still a practical solution for you!
Comments: