How to Connect & Load HubSpot to BigQuery: 4 Best Methods

Organizations often use HubSpot to manage their marketing, sales, operations, and customer support. However, all this data provides better insights when combined to create a single source of truth. By transferring data from HubSpot to BigQuery, a powerful data warehouse, businesses can perform advanced data analysis, driving informed decision-making.

In this guide, we’ll explore the top methods to connect HubSpot to BigQuery, ensuring you can choose the most efficient approach for your business needs.

Want to jump straight to the methods? Click here to skip ahead.

What is HubSpot?

HubSpot is a cloud-based platform to help organizations streamline sales, marketing, support, and more. The HubSpot platform encompasses five hubs—CRM, Marketing, CMS, Operations, and Service—each targeting a different aspect of your business. 

Features of HubSpot Hubs

• HubSpot Marketing hub: Helps you collect leads, automate marketing campaigns, and more.
  
• HubSpot Sales hub: Enables you to automate sales pipelines, get 360^o view of contacts, and more.
  
• HubSpot Service hub: Allows you to add a knowledge base, collect customer feedback, and generate support tickets.
  
• HubSpot CMS hub: Assist you in building websites with a drag-and-drop editor, optimize SEO strategies, and more.
  
• HubSpot Operations hub: Helps you sync data across different platforms, improve data quality, and more.

What is BigQuery?

BigQuery is Google’s fully-managed, serverless, cloud-based enterprise data warehouse. BigQuery is built on Dremel technology. Dremel turns SQL queries into execution trees. This tree architecture model makes for easy and efficient querying and aggregating results. 

BigQuery supports columnar storage to provide superior analytics query performance. For analytics, you can either run simple SQL queries or connect BigQuery with business intelligence tools.

BigQuery is built to manage petabyte-scale analytics. Hence, it can collect vast amounts of data from different sources to support your big data analytics. 

Features of BigQuery

• BigQuery Data Transfer: The BQ data transfer service automates data movement into BigQuery regularly based on the defined schedules.
  
• BigQuery ML: You can use simple SQL queries to create and execute ML models in BigQuery.
  
• BigQuery BI Engine: The built-in query engine processes large queries in seconds.
  
• BigQuery GIS: The Geographic Information Systems (GIS) feature provides information about location and mapping through geospatial analysis.

Why Move Data From HubSpot to BigQuery?

Moving data from HubSpot to BigQuery brings several benefits:

• Data Analytics: When you move HubSpot data to BigQuery tables, you can create a single source of truth for analytics requirements. It helps you generate insights into your customer and business operations data for better decision-making.
• Scalability: BigQuery can handle massive amounts of data. As your business generates more data, you can easily accommodate it on BigQuery. 
• Security and Compliance: BigQuery, built on Google Cloud, offers security and compliance features, like auditing, access controls, and data encryption. When you move your data to BigQuery, it’s stored securely and in compliance with regulations.
• Cost Savings: BigQuery offers a flexible pricing model. You only need to pay for the amount of data and processing power you use. This results in significant cost savings than when maintaining your own on-premise data warehouse.

Here’s a list of the data you can move to backup HubSpot to BigQuery:

• Activity data (clicks, views, opens, URL redirects, etc.)
• Calls-to-action (CTA) analytics
• Contact lists
• CRM data
• Customer feedback
• Form submission data
• Marketing emails
• Sales data

Methods to Move Data From HubSpot to BigQuery

Now that you know about both platforms and their essential features, let’s look at how you can move data from HubSpot to BigQuery. You can choose from these four methods:

1. HubSpot Private App - A direct integration method using HubSpot’s private apps.
   
2. Manually through CSV files - A straightforward approach by exporting and importing CSV files.
   
3. Custom integration - Build a custom data pipeline for flexibility and control.
   
4. SaaS alternatives - Automate real-time data transfer using third-party tools.

Now, let's dive into each method.

Prerequisites for HubSpot to BigQuery Integration

Before you begin moving data from HubSpot to BigQuery, you need to ensure the following prerequisites:

1. A Google Cloud Platform account with billing enabled.
2. A HubSpot account with admin access.
3. Google Cloud SDK installed in your CLI.
4. The BigQuery API enabled in your Google Cloud project.
5. BigQuery admin permissions to load data into tables of your BigQuery dataset.

These prerequisites are essential for setting up any of the integration methods below, especially for the HubSpot Private App and SaaS solutions.

Method #1: HubSpot Private App for Moving Data to BigQuery

One of the most reliable methods for transferring data from HubSpot to BigQuery is through HubSpot’s private app integration.

Steps to Transfer HubSpot Data to BigQuery Using HubSpot’s Private App:

1. Create a Private App

Earlier, internal integrations used a HubSpot API key that provided read and write access to all of your HubSpot CRM data. However, HubSpot API keys were discontinued from being used for authentication to access HubSpot APIs.

With HubSpot API key migration made compulsory, all API keys got deactivated. All existing API key integrations were migrated to private apps. Now, you can use HubSpot’s APIs through private apps to access specific data from your HubSpot account. 

Once you’ve authorized what each private app can request or change in your account, a unique access token gets generated for your app.

• Log in to your HubSpot account. Click on the Settings icon in the main navigation bar.
  
• From the left sidebar menu, navigate to Integrations → Private Apps.
  
• Click on Create a private app.
  
• On the Basic Info tab, provide basic app details:
  • Enter an app name or click on Generate a new random name.
  • Get your pointer over the logo placeholder and click on the upload icon. You can upload a square logo unique to your app.
  • Enter your app’s description.
    
• Click on the Scopes tab.
  
• For each scope you’d like to enable for your private app’s access, select the Read or Write checkbox. To search for a specific scope, go to the Find a Scope search bar.
  
• Upon completion of app configuration, click on the Create app at the top right.
  
• A dialog box will appear. Review the information about the access token, then click on Continue creating.

After creating your app, you can use the app’s access token to make API calls. Suppose you want to edit your app’s info or change the scopes, click on Edit details.

2. Generate Access Token:

• Once your private app is created, HubSpot will generate a unique access token for the app.
• To view the token, go to the app's details page and click Show token. Copy this token to authenticate future API requests.

3. Make API Calls Using the Access Token:

• Use HubSpot’s APIs with the access token to fetch data. HubSpot’s API endpoints return JSON data, which you can transform and load into BigQuery.
• For example, to retrieve all contacts, use this API request in Node.js:

plaintext
axios.get('https://api.hubapi.com/crm/v3/objects/contacts', {
  headers: {
    'Authorization': `Bearer YOUR_TOKEN`,  // Replace YOUR_TOKEN with the actual token from HubSpot
    'Content-Type': 'application/json'
  }
})
.then(response => {
  console.log(response.data);
})
.catch(error => {
  console.error(error);
});

• Note: The "Bearer" prefix is part of the OAuth protocol, and it tells the API that the token following it (YOUR_TOKEN) is the access token. Replace [YOUR_TOKEN] with the actual token you generated from HubSpot.

4. Set Up BigQuery Dataset:

• Create a new dataset in BigQuery to store the HubSpot data.
• Run the following command in your Google Cloud CLI:

plaintext
bq mk hs_data

• Next, create an empty table for your contacts data:

plaintext
bq mk --table --expiration 86400 --description "Contacts table" hs_data.hs_contacts_table

5. Load Data Into BigQuery:

• After fetching data from HubSpot, upload it to BigQuery. For example, if the contacts data is in a JSON format, use this command:

plaintext
bq load --source_format=NEWLINE_DELIMITED_JSON hs_data.hs_contacts_table ./contacts_data.json ./contacts_schema.json

• Ensure the schema file is prepared to match the JSON data structure.

6. Automate the Data Transfer:

• For ongoing data synchronization, create a script that fetches data regularly and loads it into BigQuery. You can automate the script using cron jobs or other scheduling tools.
• Example of scheduling a daily load at 6 PM:

plaintext
0 18 * * * /bin/scripts/backup.sh

Additional Features of HubSpot’s Private App:

• OAuth-Based Security: HubSpot’s private apps use OAuth for secure access to your data.
• Monitoring and Error Handling: Implement error handling in your API calls and monitor them for data consistency.
• Scopes and Permissions: Choose specific scopes (e.g., read-only access) to control what data your app can interact with.

This method provides a secure and customizable way to transfer data from HubSpot to BigQuery by leveraging HubSpot’s API and Google Cloud tools.

Method #2: Move HubSpot Data to BigQuery Using Manual CSV Files

Among the easier options to move HubSpot data to BigQuery is to download and upload CSV files manually. 

You have the option to Import & Export data from your HubSpot account. You can export the following records:

• Calls
• Contacts
• Companies
• Deals
• Payments
• Tickets
• Custom objects

When you export any records, you can select the file format (XLS, XLSX, CSV). You can also Include only properties in the view or include all properties on records. Upon exporting, you’ll receive an email with a download link to your export file. These download links expire after 30 days.

Once you’ve downloaded the file, you can move it to your Google BigQuery platform.

Pros of moving data from HubSpot to BigQuery with manual CSV files

• It’s a relatively simple method.
• There is no entry barrier, and you can do it quickly.

Cons of moving data from HubSpot to BigQuery with manual CSV files

• Every time there’s an update, you must manually download and move.
• There is no continuous data flow.

Method #3: Build Custom Integrations for HubSpot to BigQuery Data Transfer

If your team is up to the task, you can build your own in-house data pipeline to move data from HubSpot to BigQuery. However, this involves spending a considerable amount of time to get the integration up and running. You also need to spend time, resources, and money to manage and maintain data pipelines.

HubSpot and BigQuery constantly change or update their APIs. This might translate into upstream or downstream failures unless properly addressed.

The custom integration option is worthwhile if your data needs are very specific to your business. However, this option is not a scalable process for syncing data to additional SaaS apps. It will require spending more time to build or maintain data pipelines. You must also take care of data security with custom integrations.

Method #4: Use SaaS Solutions like Estuary Flow for Real-Time HubSpot to BigQuery Integration

While the methods mentioned above work well, the most efficient option in terms of saving time, resources, and costs is SaaS alternatives.

Some of the problems involved in the previous methods include:

• Time-consuming: Each of the methods mentioned above is time-consuming. Whether it’s the manual method, exporting to CSV, XLS, or XLSX, or building a custom integration.
  
• Latency: The problem with using manual CSV files is that any updates need to be manually moved. Every time your source data changes, you’ll have to re-export the CSVs to get real-time data.

The SaaS solution we'll focus on here is Estuary Flow.

You can use Flow to extract data from your HubSpot account and write the data to a range of destinations, including Google BigQuery. Once you deploy the pipeline, it continually operates in real time. The data moves from HubSpot to BigQuery in real time, keeping track of any changes or updates.

Steps to Use Estuary Flow for HubSpot to BigQuery Integration:

Step 1: Sign In or Register to Estuary

• Sign in to your Estuary account. If you don’t have one, sign up for free.

Step 2: Capture Data from HubSpot

• Click on Captures. Next, click on the New Capture button.

• Once you’re on the Captures page, you must enter your source database in the Search Connectors search bar. In this case, it will be Hubspot. After you’ve located the desired source database, click Capture. You’ll be redirected to the selected database’s Capture page. Choose a start data for your capture, and click Authenticate Your Hubspot Account to allow Hubspot and Flow to connect.

• Click on Next. Flow connects to your Hubspot account and detects all the data resources available there. When you're ready, click on Save and Publish.

Step 2: Set BigQuery as Destination

• There are two ways to set up your data’s destination. You can either click on Materialize Connections in the pop-up following a successful capture, or navigate to the Estuary dashboard and click on Materializations on the left-side pane. Then, click New Materialization.

In this case, BigQuery will be the materialization option to select. 

BigQuery has a few more prerequisites you’ll need to meet before you can connect to Flow successfully. So before you continue, follow the steps here. 

Once that’s done, you can set up your destination as easily as you did with the source. As you did with Hubspot, fill in the required fields and click Next. Use the Collection Selector to add the data captured from Hubspot, if it wasn’t filled in automatically. Then, click Save and Publish. 

For a more detailed set of instructions, see the Estuary documentation on:

• Detailed steps to create a Data Flow like this one.
  
• The Hubspot source connector.
  
• The BigQuery materialization connector.

Conclusion

HubSpot is a vital component of the tech stack for several businesses. With its many benefits, it empowers you to improve customer relationships and hone your communication strategies. However, when you extract and combine your HubSpot data with other data sources, the business growth potential increases exponentially. Moving your data from HubSpot to BigQuery allows you to reap the benefits of a single source of truth. This, in turn, boosts the growth of your business.

Ready to integrate your HubSpot data with BigQuery effortlessly? Start your data syncing with Estuary Flow today for real-time insights. Get Started it Free!

FAQs

How do I connect HubSpot to BigQuery?

You can connect HubSpot to BigQuery using HubSpot’s private app, CSV files, custom-built integrations, or SaaS solutions like Estuary Flow.

Can I transfer HubSpot data to BigQuery in real-time?

Yes, using SaaS tools like Estuary Flow, you can transfer HubSpot data to BigQuery in real-time.

What is the best tool for HubSpot to BigQuery integration?

Estuary Flow is a great tool for real-time and automated data transfer from HubSpot to BigQuery, making it an efficient choice for most businesses.

Here are some other interesting reads to move data from HubSpot to other platform:

• HubSpot to Snowflake
• HubSpot to Databricks
• HubSpot to Redshift