BetaConnect HubSpot to Google BigQuery

Sync data from Google BigQuery with HubSpot CRM objects, such as contacts, companies, deals, associations, and other data from your account.

The sections below walk you through how to authorize the Google BigQuery app, then configure syncing settings between your Google Cloud account and HubSpot.

Connect and configure the Google BigQuery integration

To connect your Google BigQuery account to HubSpot:

1. In your HubSpot account, click the Marketplace icon in the top navigation bar, then select HubSpot Marketplace.
2. Click the search bar and enter Google BigQuery.
3. Click Google BigQuery from the listing page.
4. Click Install.
5. Click Continue setup.

You'll be redirected to the Data integration tool, where a side pane will guide you through syncing data between your HubSpot and BigQuery accounts, based on the directions you want to sync:

• Google BigQuery → HubSpot: configure syncing from BigQuery to HubSpot.
• HubSpot → Google BigQuery: configure syncing from HubSpot to BigQuery.

Sync Google BigQuery data to your HubSpot account

To send Google BigQuery data to HubSpot, add and configure a new user with the required roles in your BigQuery account:

1. In the right pane, select Google BigQuery → HubSpot.
2. Copy the Google service account email that HubSpot generates.

3. In your Google Cloud account, navigate to your project IAM settings, create a new user with the service account email you copied from HubSpot, then add the BigQuery Data Viewer and BigQuery Job User roles for the newly created user.

4. Copy the Project ID from your Google Cloud account.
5. Back in HubSpot, paste in the Project ID, then click Test connection.
6. Once the connection is verified, click Next to complete the setup.

Sync HubSpot data to your Google BigQuery account

To send HubSpot data to Google BigQuery, you'll configure a user with the required permissions in your Google Cloud account, along with specifying the associated GCP project and GCS bucket URI.

Please note: you'll need Google Cloud admin access to the GCP project where the BigQuery dataset will live, and a Google Cloud Storage (GCS) bucket where HubSpot will stage Parquet files.

1. In the right pane, select HubSpot → Google BigQuery.
2. Copy the Google service account email that HubSpot generates.

3. In your Google Cloud account, navigate to your project IAM settings, create a new user with the service account email you copied from HubSpot, then add the BigQuery Data Viewer, BigQuery Job User, and Storage object owner roles for the newly created user.
   

4. Copy the Project ID from your Google Cloud account.
5. Back in HubSpot, paste in the Project ID.
6. Add your Google Cloud Storage (GCS) bucket URI:
   • The URI for the GCS bucket should follow the structure: gs://{bucket}/hubspot-dataout/{portalId}/{runId}/{tableName}/
   • BigQuery external tables are created in a dedicated dataset in the GCP project and follow the format: hubspot_data_set_{hubId}
   • Data will be written to the GCS bucket, and the associated tables will reference parquet files in GCS.
   • Learn more about creating GCS buckets in Google's documentation.
7. Click Test connection.
8. Once the connection is verified, click Next.
9. Select the checkboxes next to the data sources you'd like to sync (e.g., Contacts, Associations, Events, etc).
10. Configure the sync frequency and schedule.
11. Click Start sync.

Monitor syncing

After you've configured syncing between your accounts, you can review syncing status and history from the sync details page:

1. In your HubSpot account, navigate to Data Management > Data Integration.
2. Under Monitor your imports & syncs, click the App syncs tab.
3. Click the search bar and search for Google BigQuery.
4. Click Google BigQuery. Note that multiple entries may appear if you've configured syncing in both directions.
5. On the sync details page, you can manage and review your sync settings and history:
   • To archive or edit syncing details, in the top right, click Actions then select the corresponding option.
   • To immediately trigger a sync, in the top right, click Sync now.
   • Synced records, associations, and errors are summarized at the top of the page.
   • Recent sync activity will appear in the table. Click the Sync history tab to review specific sync attempts and completions. You can filter by specific date ranges or a sync status.

Sync statuses and error handling

The table below outlines possible syncing statuses:

If a sync fails:

• A Failed status will appear on the sync details page, along with information about which tables succeeded or failed to sync.
• No data is partially written on failure: BigQuery tables only update on successful completion of a sync.
• The next scheduled sync will automatically re-attempt a full sync.
• You can trigger a re-sync manually at any time from the sync details page.

Network allowlisting

Syncing HubSpot data to third-party providers like BigQuery is routed through HubSpot's proxy IPs. These IP ranges are used by other HubSpot integrations, such as data sync and webhooks.

If your organization restricts inbound access to your GCP resources, you should allowlist the following CIDR ranges based on your HubSpot hublet:

Learn how to find out where your HubSpot account data is hosted.

Beta limitations

Keep the following limitations in mind during this beta:

• Sync frequency: only a limited set of sync frequencies are available, including once, every 6 hours, every 12 hours, daily, weekly, and monthly. You can manually trigger a re-sync from the sync details page in HubSpot at any time.
• One export per data type: each object type, event type, or association pair can only be exported in one sync configuration per HubSpot account.
  
  • For example, if contacts are already being exported to one BigQuery destination, they cannot be added to a second configuration until the original destination is archived.
  • This prevents duplicate data pipelines and ensures a single source of truth per data type in your data warehouse.
• Limited sensitive data support: you can store and sync sensitive data to your HubSpot account, but you cannot sync sensitive data to your BigQuery account.
• No support for object_x_views: the object_x_views object isn't currently supported for syncing with BigQuery. Use object_with_object_properties, or join objects with object_properties to get equivalent per-record views.

Next steps

After you've connected the BigQuery integration and set up your sync settings, learn about the schema for synced tables in BigQuery.