Connect HubSpot and Google BigQuery (BETA)

Learn how to connect HubSpot and Google BigQuery to sync HubSpot data to your Google BigQuery instance. 

Connect HubSpot and BigQuery

To connect HubSpot and Google BigQuery, you must run through these steps in order:

1. Install Google BigQuery from the HubSpot App Marketplace
2. Create a custom IAM role in Google Cloud Platform
3. Assign the custom role to the Google Service Account
4. Grant the HubSpot-generated Google BigQuery account permissions to modify BigQuery tables
5. Complete set up in HubSpot

1. Install Google BigQuery from the HubSpot App Marketplace

It's recommended that you have your HubSpot account and Google BigQuery account open in separate tabs when connecting the two apps. 

• Make sure your HubSpot account is opted into this beta: Access HubSpot data in Google BigQuery. 
• Navigate to this link and select your HubSpot account. 
• In the upper-right, click Install app.
• Enter your Bucket URI and Project ID. 
• Click Next. 

• Review and take note of the Google Service and BigQuery accounts as you'll need them in the next step. Then, navigate to your Google Cloud console to complete the next step. 

2. Create a custom IAM role in Google Cloud Platform

• Log into your Google Cloud console. 
• Click the left navigation menu and select IAM & Admin > Roles. 
• At the top, click Create Role.
• Enter a title, optional description, and ID for the role. 
• Click Add permissions. 

• In the table, select the following permissions and click Add:

bigquery.datasets.create
bigquery.datasets.get
bigquery.datasets.getIamPolicy
bigquery.jobs.create
bigquery.tables.create
bigquery.tables.createIndex
bigquery.tables.createSnapshot
bigquery.tables.createTagBinding
bigquery.tables.delete
bigquery.tables.deleteIndex
bigquery.tables.deleteSnapshot
Bigquery.tables.update
bigquery.tables.get
storage.buckets.get
storage.objects.create
storage.objects.delete
storage.objects.get
storage.objects.list

• Click Create. 

3. Assign the custom role to the Cloud Storage Service account

• Click the left navigation menu and select Cloud Storage > Buckets. 
• Select a bucket to configure. 
• Click the Permissions tab.
• On the View by principals tab, click Grant access. 

• In the right panel, in the New principals field, enter the Google Service Account name from Step 2.
• In the Assign roles section, click the dropdown menu and select Custom > <role> where <role> is the custom IAM role created in Step 1. 
• Click Save. The Google Service account will be added to the Storage Object Viewer role. 

4. Grant the HubSpot-generated Google BigQuery Account permissions to modify BigQuery tables

• Click the left navigation menu and select IAM & Admin > IAM. 
• Click Grant Access.
• In the right panel, in the New principals field, enter the Google Service Account name from Step 2.
• In the Assign roles section, click the dropdown menu and select Custom > <role> where <role> is the custom IAM role created in Step 1. 

5. Complete set up in HubSpot

• In your HubSpot account, click Continue to Connected Apps. 

• In the dialog box, select the checkboxes, then click Allow and install. 

Data format for syncing between HubSpot and Google BigQuery

Available data for syncing

You can sync the following data from HubSpot to Google BigQuery: 

• Objects: standard and custom objects, such as contacts, companies, tickets, or products.
• Records: individual object records. 
• Associations: all association types and associated records.
• Events: event data for both standard HubSpot events and custom behavioral events. 
• Lists: HubSpot contact and company lists.
• Owners: record owners. 
• Pipelines: deal and ticket pipelines and pipeline stages. 
• Properties: properties and their values on the object records, including the property history. 

Object data of different types are organized and available in two ways:

• Individual: each object type is stored in its own table and has only records of the same object type. For example, objects_contacts contains only contact records.
• Combined: all records for all object types are combined into one database object. Thus, when querying, you need to specify a filter like “WHERE objectTypeId=’0-1’

Custom objects will be assigned an ID at the time of creation, and will be identified as 2-unique_ID. For example, 2-12345. 

objects_x tables

Each available object type, including custom objects, has its own specific table. These tables are named following the format OBJECTS_<object type name> where <object type name> is the plural form label of the objecttype.(Note that if the object type has no plural form, then <object type name> will be the singular form label of the object type name concatenated with “S”).

Below is a complete list of available HubSpot object types, a short description of the data they represent, and their corresponding tables.

object_with_object_properties

This table contains data from your HubSpot records. You can get one row for each record within an objecttype (e.g., all contact records in the contacts objecttype). 

object_properties_history

This table contains historical property values. For contacts, it contains the most recent 45 values. For all other objects, it contains the most recent 20 values. The table has a row for each historical property value. The current value of a property is determined by the most recent UPDATEDAT timestamp in the property's history.

Association queries

Like objects data, associations data is organized in two ways:

• Individual: each association type is stored in its own table and contains only records of that type. For example, associations_contacts_to_deals contains only association records between contact and deal objects. T
• Combined: all records for all association types are combined into one table.

associations_x_to_y 

Each association type has its own view, labeled as associations_<association type name> e.g. associations_contacts_to_deals. 

association_definitions

This table contains information about all of the available association types between HubSpot objects, with one row per association type.

property_definitions

This table contains information about object properties and event properties in your HubSpot account. Event properties include standard events, such as email opens and clicks, and custom behavioral event properties. This information can be helpful for defining values found in object_properties and events data.

Events

Events include standard HubSpot events and custom behavioral events. Each event has its own table, labeled as events_eventname.

object_and_event_type_definitions

This table contains definitions for all objects and events available in your HubSpot account.

Sync frequency and data organization

Data syncs daily between HubSpot and Google BigQuery at 0900 UTC +0. A folder will be created for each day that the data is synced with several subfolders for each table. The last seven syncs will be available in Google Cloud platform and tables in BigQuery are updated to point to the latest bucket dump.

Each of the following tables will be available as a folder with gzipped CSV files:

ASSOCIATIONS
ASSOCIATION_DEFINITIONS
EVENTS_[Event name]
LISTS
LIST_MEMBERSHIPS
OBJECTS
OBJECT_AND_EVENT_TYPE_DEFINITIONS
OBJECT_PROPERTIES
OBJECT_PROPERTIES_HISTORY
OBJECT_WITH_OBJECT_PROPERTIES
OWNERS
OWNER_TEAM
PIPELINES
PIPELINE_STAGES
PROPERTY_DEFINITIONS
TEAMS