The struggle is real.
If you’re a longtime Salesforce customer, you know that Salesforce uses two objects — leads and contacts — to refer to a single person. And if you don't yet know what a royal pain creating a single report for leads and contacts can be, look no further thanthis buzzing forum on the Salesforce Success site.
"I honestly don't understand why SF created [separate] tables for Lead and Contacts. Why not have one table and use record types to distinguish? Then allow users to decide what type of report they want to run (just contacts, just leads, both)?...When I create a field, allow me to chose if it should be applied to just contacts, just leads, both."
— Salesforce User on Salesforce Success Website
Why build a unified leads-contacts report? Because doing so allows you to break down new vs. existing leads alongside your existing contacts, by lead source and dollar amount. Moreover, the unified report allows you to see visually how each lead source breaks down by attracting new leads vs. re-engaging existing leads or existing contacts.
At Bedrock, this dearth of a unified leads-contacts report hampered our ability to:
- Measure campaign impact
- Gauge how product launches affected net new leads, existing contacts, and opportunities
- Know which accounts to engage
- Prevent lost opportunities
- Minimize customer churn
Luckily, by eating our own fused (and sync) dog food, we eventually succeeded in building a unified report for Fusion leads, contacts, opportunities, and more. The following post talks about how we did it and how you can too. And if this problem resonates with you, download our e-book for step-by-step instructions.
1. Setup Custom Fields for Leads & Contacts in Salesforce
To track engagement for our Fusion product, we first had to configure a few custom fields in Salesforce for our lead and contact records.
First, we set up a field to track Fusion product interest using checkboxes. Then we created a dropdown field to track status by funnel stage.
Lead → MQL→ Sales Accepted Lead → Sales Qualified Lead → Opportunity → Customer
Thus, we first set Lead as the default Funnel Stage (Field Settings) and the Lead Record Type to Fusion if net new (Processor Builder). And just to be safe, we also captured manually-created Fusion records (e.g. when someone calls a sales line or emails us), setting Fusion to True for all Fusion Record Types (Workflow). Lastly, we mapped the newly created Fusion Lead fields to their Contact field counterparts (Map Lead Fields).
Since our Fusion product flow offers a free trial, we specified these conversions by referring to our Fusion Signup date with the following status paradigm:
Trial → Quality Trial → Customer
Where the fields:
- Fusion Signup Date = date of trial signup
- Quality Trial Date = date the end user became a quality trial
- Customer Date = date the end user became a customer
To create fields so your formulas are calculated directly around the number of days between these two funnel stages, do as we did by affixing these fields with formulas, substituting Fusion for whatever your product happens to be named:
- Fusion - Lead to Trial Days
- Fusion - Trial to Quality Trial Days
- Fusion - Quality Trial to Customer Days
With all that configured in Salesforce, it's high time to tag the corresponding fields in our inbound system.
2. Tag Inbound Fields
For each of the above, we then tagged values for these fields in HubSpot by setting product interest based on whether someone filled out a trial form and stamping dates for each user action.
As a real life example, when prospects and customers arrive on our website and engage with content, HubSpot captures our inbound Fusion leads via its Forms API.
For parity, we tagged the corresponding values in HubSpot for when someone fills out the Fusion Signup form, setting the following fields:
- Fusion Signup Date
- Funnel Stage (Trial)
- Increment Days in Fusion Trial by 1 every day
- Fusion Trial Expired (to True) on the 15th day
- Fusion to True if interested (Workflow)
In general, all our Fusion form entry points contain “Fusion” in the form name, so if an end user signs up for Fusion information but does not enter a Fusion Trial, they’re tagged accordingly.
With the default Funnel Stage of Lead set in Salesforce, and data syncing between HubSpot and Salesforce, we can now identify all Fusion Leads. And if we wanted to get even more granular about our free trial, we could also set the Funnel Stage to Quality Trial and manually stamp the Quality Trial Date with a workflow rule with the following criteria:
ISPICKVAL(Funnel_Stage__c, "Quality Trial"),
3. Map Fields in Fusion
Lastly, to connect Salesforce to Fusion, you must first create an account (for free).
1. Click Manage Connectors.
After you click Manage Connectors from the dashboard and select Salesforce.
2. Authenticate Salesforce
Authorize your Salesforce account. Once you sign in, Fusion will then begin ingesting all the data via your Salesforce instance’s API and building your cloud data warehouse.
3. Map Fields in Fusion
A mapping is how one field relates to another field for a given object. Fusion has two kinds:
• Default Mappings are automated and ensure you have standard data fed into your warehouse without having to do anything. Put another way, Fusion automatically maps your Salesforce contacts and leads to a single table, fused_contact, so you don’t need to do anything to bring those objects together as one. Below is a table of all the default Fusion mappings for Salesforce objects:
• Custom Mappings give you the flexibility to map in any data you’d like from your Salesforce objects. These you can manage from the navigation bar by clicking Schema:
Once there, you can then select your custom object mappings:
Clicking Select a Type will then let you choose which objects to map together. For example, we map Contact to Lead Source Detail, since the latter is a custom field we built in Salesforce:
If you're looking for inspiration about what to map yourself, here is a list of other custom mappings we made for our Fusion product by using Salesforce fields:
Contact / Campaign — Salesforce / Lead / Campaign
Contact / Referred by Account — Salesforce / Lead / Referred By Account
Contact / Lead Status Detail — Salesforce / Lead / Lead Status Detail
Company / Product Start Date — Salesforce / Account / Fusion Start Date
Company / Product Cancelled Date — Salesforce / Account / Fusion Cancelled Date
Company / Total MRR — Salesforce / Account / Total MRR
Company / Product — Salesforce / Account / Fusion
Company / Product MRR — Salesforce / Account / Fusion MRR
Company / Product App ID — Salesforce / Account / Fusion App ID
Company / Action X — Salesforce / Account / Action X
Company / Status Detail — Salesforce / Account / Status Detail
Company / Status — Salesforce / Account / Status
Company / Days to Onboarding Completed — Salesforce / Account / Days to Onboarding Completed
Company / Onboarding Completed Date — Salesforce / Account / Onboarding Completed Date
Company / Onboarding Start Date — Salesforce / Account / Onboarding Started Date
Company / Stage Detail — Salesforce / Account / Stage Detail
Company / Stage — Salesforce / Account / Stage
Connect Contacts to Opportunities
In addition, to report on opportunity results for both, you can join the fused_contact table to the fused_opportunity table via the links_contact_opportunity table – and then pull in to the report whether an opportunity is created, forecasted revenue, forecast percentages, and more. Below are examples of custom mappings you might create for Opportunities:
Opportunity / Owner ID — Salesforce / Opportunity / Owner ID
Opportunity / Fusion Product Family — Salesforce / Opportunity / Fusion Family
Opportunity / Contract Price — Salesforce / Opportunity / Contract Price
Opportunity / Closed Lost Detail — Salesforce / Opportunity / Closed Lost Detail
Opportunity / Closed Lost Reason — Salesforce / Opportunity / Closed Lost Reason
Opportunity / Stage — Salesforce / Opportunity / Stage
Opportunity / Close Date — Salesforce / Opportunity / Close Date
Company / Active Customer — Salesforce / Account / Active Customer
Regardless of whether you use the default or custom mappings, Salesforce Lead and Salesforce Contact objects will always get mapped to the same Fused Contact field. So when your cloud data warehouse builds, it will populate the fused_contact table with both leads and contacts.
4. Reporting in Your BI Tool of Choice
Once done, we were then able to report on how many “net new” and existing leads we’d engaged, which opportunities had converted into customers, and how much we’d spent on any given account to gauge pipeline revenue by campaign, product, interaction channel, and more. We can also do this in any BI tool that accepts MySQL as a data source.
All you need to do is click Get Credentials in Fusion, download your warehouse credentials, then plug them into tools like Tableau, Power BI, and Looker.
5. Try It Yourself
To connect Salesforce to Fusion, visit fusion.bedrockdata.com/get-started-now and create your account — for free. Or download our e-book here.
Once connected, you’ll find that the fused_contact table contains both Salesforce leads and contacts data.
And if you use other software — such as Marketo, HubSpot, Zendesk, or NetSuite — you can add more data sources anytime from with Fusion.
So if you want to build a unified leads-contacts report in Salesforce, click below to get started.