How to Connect HubSpot to BigQuery: A Step-by-Step Guide

HubSpot provides unparalleled data depth across CRM, marketing, and sales, offering invaluable insights into your business performance through comprehensive dashboards. Despite its robust reporting capabilities, many organizations choose to export their HubSpot data to BigQuery for centralization and enhanced analysis. 

If you’re considering this approach, this blog post will guide you through how to connect HubSpot to BigQuery or export data manually.

Method 1: Connect HubSpot to BigQuery with Coupler.io

HubSpot does not easily integrate all data points into other platforms. However, with Coupler.io, you can effortlessly export data from HubSpot to BigQuery without the need to write even a single line of code. Additionally, you can schedule the export process to run automatically, ensuring that your data is always up to date.

What data can you export from HubSpot with Coupler.io?

Coupler.io lets you pull a wide array of HubSpot data entities into BigQuery. Here is the current list:

Step-by-step connection with Coupler.io

Here’s a quick look at how to export HubSpot data to BigQuery:

Step 1: Collect data

Click PROCEED in the form below where we’ve already preselected HubSpot and BigQuery as the data source and destination. You’ll be offered to create a Coupler.io account for free with no credit card required.

Next, connect your HubSpot account and select the data entity you want to export from the dropdown list. For example, Deals, Contacts, Companies, or another. Here, we’ve selected Deals as an example.

If you wish, you can apply Column, Date, and Advanced filters. For example, you can export only specific columns, filter only Deals closed in 2024, and include only Deals in a particular Pipeline.

Click FINISH AND PROCEED if everything about the data source looks good. You can always go back and change any settings.

Step 2: Transform data

Coupler.io will show a preview of your HubSpot data. Here, you can also transform and organize your data before loading it into BigQuery:

• Reorder, hide, and rename columns.
• Filter and sort data as needed.
• Add custom columns using formulas.
• Merge or combine data (if you’ve connected multiple data sources).

Step 3: Load to BigQuery and schedule data refresh

Proceed to the Destination setup. 

To connect your BigQuery project, you need to upload a Google Cloud JSON key file. Follow these instructions to get the key. This file will be used to access the associated BigQuery project. It contains information such as the project ID and service account credentials.

Next, enter the dataset and table names where to load HubSpot data.

Configure the Automatic data refresh by toggling the button on and then specify your schedule preferences. 

Save and run the importer. Depending on your data size, you may need to wait a few minutes for the importer to finish running. When done, click VIEW RESULTS to open your table in BigQuery. 

Here are the results:

Bonus: HubSpot dashboard templates by Coupler.io

Coupler.io makes it very easy to export HubSpot data to BigQuery on a schedule—but that’s not all. You can also send your data (either from HubSpot or BigQuery) to BI tools like Looker Studio for seamless reporting. This way, you can create a HubSpot sales dashboard without any hassle. And if you aggregate data from HubSpot, Google Analytics, and your advertising platforms, you can create an insightful HubSpot marketing dashboard.

At the same time, it’s not necessary to do everything from scratch. Coupler.io offers free, ready-to-use HubSpot report templates. Just connect your data source and let Coupler.io do the rest.

CRM dashboard for HubSpot

Get a complete understanding of your HubSpot sales and dive deeper into open and closed deals. Track the team performance, see how sales shape up over time, and analyze what drives your results.

Try this dashboard

Sales KPI dashboard for HubSpot

Get an overview of the HubSpot sales metrics and KPIs to derive insights into pipeline health using our sales KPI dashboard.

Try this dashboard

Customer acquisition dashboard for HubSpot

Monitor your acquisition funnel and analyze its effectiveness using the insights from HubSpot CRM, advertising platforms, and Google Analytics 4.

Try this dashboard

Method 2:  Load HubSpot data into Google BigQuery with manual export and import

Let’s explore the manual method for loading HubSpot data into Google BigQuery. This approach is ideal if you need a one-time export. Or, if you want to familiarize yourself with the data transfer between the two platforms, which is helpful if you plan to set up an automated process later on. 

What data can you export from HubSpot? 

Here’s what you can pull from HubSpot without automation:

1. Export HubSpot data

Exporting data from HubSpot is generally straightforward. Go to the data you want to export by navigating the left menu, whether it be contacts, deals, or marketing data. Typically, the export option is on the top right menu, top grid menu, or within the data grid. Look for it as an “Export” button, a “Download” link, or an option inside a dropdown list.

Here are the steps to do it, using Deals as an example:

• Navigate to Deals: Use the left menu to click CRM > Deals. This will open your Deals page.
• Find the export option: If you are in the “list” view, click the Export button in the top right of the grid. If you’re in the “board” view, click Board options > Export view.

• Choose your export settings: In the Export window that appears, choose your file format, like CSV or Excel. Here, we select export as CSV:

• Export: Click the Export button. You’ll receive a notification and an email with a download link to your export file.
  Note: Download links to export files will expire after 30 days.

• Download: Simply click the notification to save it to your local folder. You can also use the link provided in the email sent to you.

And there you have it! Your Deals data is now ready to be imported to BigQuery!

Please note that most data, including CRM, sales, marketing, and analytics, can be exported in CSV or Excel (XLS or XLSX). However, certain data types use different formats: Quotes (PDF), Workflows (CSV or PNG), Website pages (HTML), Blog posts (HTML or CSV), Sitemaps (XML), and Website files (ZIP).

2. Import HubSpot data into BigQuery

Open Google Cloud Console and navigate to BigQuery. Follow the steps below to import the previously exported CSV file to a new table.

• In the Explorer pane, click +ADD. Then, in the Add pane that appears, select Local file as the source to import.

• In the Create table pane, select the following details and click CREATE TABLE.
  • Create table from: Upload.
  • Select file: Browse your CSV file from your local folder.
  • File format: CSV.
  • Destination section: Select your dataset and enter a new table name.
  • Schema: Tick the Auto detect option.

• Once the export is completed, open your table and click PREVIEW.

Other ways for connecting HubSpot to BigQuery

There are a couple of alternative methods worth considering:

HubSpot API

The HubSpot API provides a programmatical way to extract data from HubSpot and then load it into BigQuery. This method is flexible but requires developer knowledge. The process can also be automated for regular data transfer.

Generally, here are the steps using the API for HubSpot to BigQuery integration:

• API access: First, you’ll need to get API access by generating an API key. This key acts as your authentication credential, allowing you to interact with your HubSpot data programmatically.
• Export data: HubSpot provides endpoints for exporting various data, including CRM, analytics, and website data. For example, to export CRM records, you can make a POST request to /crm/v3/exports/export/async. Choose your preferred programming language to make API calls. HubSpot offers client libraries in languages like Node.js and Python to simplify the interaction.
• Prepare data for BigQuery: This might involve data cleaning or transformation. For example, you might need to standardize date values, extract specific parts from a string, etc., to ensure data integrity and usability.
• Load to BigQuery: There are several options for this. For example, you can use BigQuery API for programmatic data uploads. Alternatively, Google’s BigQuery Data Transfer Service may simplify the process and minimize your coding efforts.

HubSpot to Google BigQuery Integration (BETA)

This integration allows you to sync HubSpot directly with BigQuery through a user-friendly interface. However, it is available in beta and only with an Operations Hub Enterprise subscription. So first, ensure your HubSpot account is opted into this beta functionality: Access HubSpot data in Google BigQuery. 

Once your request for beta is approved, the feature will be updated automatically in your account. You can then follow these general steps in order:

• Install the integration: Navigate to Data Management > Integrations, then click the Visit App Marketplace button. Search for the Google BigQuery integration and install it. You will need to provide a BigQuery Bucket URL and Project ID for installation.
• Create a custom IAM role: In the Google Cloud Platform, create a custom IAM role with specific permissions for BigQuery and storage operations.
• Assign the custom role: Assign the custom IAM role to the Cloud Storage Service account by configuring bucket permissions and granting access.
• Grant permissions: Use IAM in Google Cloud to grant the HubSpot-generated Google BigQuery account permissions to modify BigQuery tables.
• Complete HubSpot setup: Finalize the setup in HubSpot by connecting apps, allowing data sync, and understanding the data types and sync frequency available for integration.

Since this feature is in beta, you should expect a limited range of data available for syncing. Learn more about the integration.

Enhance your workflow with effortless HubSpot to BigQuery integration

Many companies connect HubSpot to BigQuery to create a centralized data warehouse, making it easier to manage data in one place and focus on insightful analysis.

Automated solutions like Coupler.io offer simplicity and efficiency, minimizing errors and the need for manual efforts. Manual methods for HubSpot export data, while straightforward, can be tedious and time-consuming. Programmatic approaches using APIs provide flexibility and customization but require technical expertise. 

Each method serves different needs, but automation is invaluable for its ease and efficiency. Step into smarter HubSpot analytics and data management with Coupler.io.