Best practices: How to combine data from different HubSpot data entities in a single sheet?

The HubSpot importer lets you pull data separately from the following entities: Deals, Contacts, and Companies. If you need to import mixed data (some fields from one entity and some fields from another one), please follow this guide:

Step 1: Import fields from the entities using separate HubSpot importers 

For example, if you need to import specific fields from Deals and Contacts, set up two HubSpot importers: one for Deals, another one for Contacts.

Step 2: Use the Google Sheets formulas to lookup the required columns

Read our blog post to discover the power of the VLOOKUP function in Google Sheets.

Use case:

We need to import three fields from Persons ( name, org_id.name, and org_id.address), and one field from Deals (formatted_value). 

Step 1

Set up two HubSpot importers with the following parameters:

HubSpot importer #1:

• Data entity: Deals
• When configuring this importer, navigate to Destinations => Show advanced => type in C1 into the "Cell address" field

That's how the imported Deals data set looks:

You have to find the field which is available in both Contacts and Deals data entities, so you can use it to vertically lookup the data.

In our case, the field's name in Deals is:  associations.contacts.0

HubSpot importer #2:

• Data entity: Contacts

That's how the imported Contacts data set looks now:

In Contacts the same field is called:  id

Step 2

We are going to pull data from the  email located in the Contacts data entity over to Deals and place it into A column.

And the data from the  jobtitle column located in the Contacts data entity will go to the B column of the Deals sheet.

Now apply the following VLOOKUP formula to the A1 and B1 cells of the sheet with HubSpot Deals:

={"Email";iferror(arrayformula(vlookup(AW2:AW, Contacts!A2:FH,15,false)))}

={"Job Title";iferror(arrayformula(vlookup(AW2:AW, Contacts!A2:FH,127,false)))}