Channel-based revenue attribution using Salesforce and HubSpot

When using HubSpot to drive inbound lead generation and Salesforce to manage customer relationships and opportunities, channel-based reporting is a real challenge.

Here's one way to do it by mapping HubSpot source data to Saleforce's contact and opportunity objects.

While powerful, Salesforce is a peculiar beast. Even after integrating it carefully with HubSpot, you may still find that you are unable to analyse and report on data as you would like in the enterprise CRM tool.

Because of the way that Salesforce's lead, contact, account and opportunity 'objects' are wired, analysing revenue, or the pipeline, by channel or source is particularly awkward.

The root cause is that by default Salesforce does not allocate any source data to an opportunity. And, when creating reports on opportunities, you cannot (easily) cross reference any other object the might hold that information, such as contacts. 

Even if Salesforce would let you cross reference the contact object, lead source in Salesforce is manually set using a predefined picklist and is not populated automatically from other systems like HubSpot.

To analyse the return on investment from marketing channels like organic search, social media and PPC you are going to have to get lead source information from HubSpot into Salesforce and from the contact object into the opportunity object. Finally you are going to have to create reports that power dashboards that reveal the relevant metrics. 

Creating the Salesforce lead source pick list 

'Lead Source' is a picklist that, while initially populated with default values, can be customised to your needs. 

The first step in channel-based reporting is configuring this list so that it contains all of the channels that you want to measure.

Later on we will be mapping the HubSpot field 'Original Source Type' to the lead source field, so it should serve as a good guide on which online channels to include in your picklist.

The final picklist need not match exactly the contents of HubSpot's original source type field. It should also contain your offline channels i.e. prospecting, trade shows, personal referral etc.

To configure the picklist, go to your Salesforce settings and select Customize > Leads > Fields. Click on Lead Source (not edit) and then in the values section, click new. Detailed instructions can be found in this Salesforce Knowledge Article: Add/create a new Lead Source.

You should also retire unused picklist values now to clean up reporting later on.

Here's an example of a configured picklist:-

Mapping original source type to lead source

When you integrate Salesforce with HubSpot a mapping between lead source and a custom HubSpot field with the same name is created automatically.

This means that we can easily manipulate the contents of these mapped fields in HubSpot and have the information sync back to Salesforce. This does not happen automatically, however, and a workflow is required.

Important!: The contents of HubSpot's original source type field is set shortly after the contact is created - meaning there is a delay during which the field is blank. When using original source type to trigger workflows, always include a criteria like 'is known', otherwise you may get unexpected results.

Create a new workflow with the starting criteria:

• Original source type is known
• AND
• Original source type is none of offline sources, e.g:
  
  

You want to exclude contacts from offline sources as these are most likely contacts that have been created in Salesforce or by importing a list. In either case, these should already have a lead source value representing their offline channel and we don’t want to overwrite this with 'offline sources' in HubSpot.

Your workflow should then set the value of the lead source field based on the value in the original source type field.

You are unlikely to be able to use a copy function to do this for a couple of reasons:

• Firstly, fields in HubSpot and Salesforce have hidden unique IDs which follow a different format to the name you see when operating the systems. For a copy function to work, these IDs have to match exactly and are unlikely to.
• Secondly, you may be using different groupings in lead source compared to original source type - for example grouping organic search and direct traffic together as one channel.

The more likely solution is to use a series of if/then branches to test the value of the original source type and then set lead source to the desired value accordingly, e.g:

If you have historic data that you want to update the lead source on, make sure to select 'enroll existing contacts that meet the stating criteria' when turning on the workflow. If then go to the Salesforce integration settings and edit the lead source mapping, without actually making any changes, you get the option to force Salesforce to re-sync that field for all contacts, bringing it up to date with the changes.

Creating opportunities the right way

Now that lead source is properly populated in the contact object, you need it to pass over to any opportunity that you create.

Because an opportunity can have many contacts, and a contact many opportunities, Salesforce doesn't make any assumptions for you here.

The best way to do this is to always create opportunities (and accounts for that matter) from the contact object/screen. When doing it this way, the lead source of the contact is automatically transferred to the new opportunity.

That contact is also automatically marked as the primary contact on the opportunity, which can be useful when trying to create a closed loop using automatic lifecycle stage transitions.

If you are unable to enforce this workflow for any reason, you may struggle to achieve channel based revenue reporting. One possible, but very manual, workaround is to export contact/account and opportunity/account reports, use Excel to copy the relevant lead source data from one to the other - I did this by de-duplicating the contact/account report and using a VLOOKUP function to copy values from  it to the opportunity/account report - and then re-import that data into the opportunity object.

Reports and dashboards

The final step in channel-based revenue reporting is creating the reports and dashboard widgets that reveal the relevant information. 

To start this, create a couple of reports such as:

• Number of contacts by lead source - to show which channels are generating the most new contacts
• Sum of opportunity values by lead source - to show which channels are creating the pipeline
• Sum of closed won opportunity values by lead source - to show which channels have generated the most revenue 

When these are ready, on a new or existing dashboard, create widgets based on each of these reports.