Connecting HubSpot to Google Data Studio: 6 Easy Steps

|

HubSpot to Google Data Studio

Introduction

HubSpot is a completely managed cloud-based service for customer relationship management. It can be used to manage marketing, sales and service. ‘Software-as-a-Service’ products like HubSpot spare businesses the effort of building custom solutions but at the same time rob them of the opportunity to run custom analytics and reporting on their data.

Image Source: www.commons.wikimedia.org/wiki

In most cases, you will find organizations using a separate application for their reporting requirements. This is where business intelligence tools like Data Studio comes in, Google Data Studio helps businesses create dashboards and reports for executive decision-making. It can connect to a number of data sources including on-premise and cloud-based ones. This post is about the steps involved in connecting HubSpot to Google Data Studio.

Table of Contents

Hevo: Integrating Data Effortlessly

Hevo is a No-Code Data Pipeline. You can easily load your data from HubSpot (Free Data Source) and multiple other sources in real-time for easy visualization in Google Data Studio. Hevo makes sure that you fulfill your needs at a reasonable price. It offers pre-built data integrations from 150+ data sources, including HubSpot. It helps transfer data from HubSpot to a destination of your choice for free.

Get started with hevo for free

Let’s look at some unbeatable features of Hevo:

  • Easy Implementation: Hevo offers a simple and intuitive user interface. 
  • Fully-Automated: You can automate your entire ETL process without writing any custom code.
  • Zero Maintenance: Once you’re done with the setup, Hevo manages all future tasks automatically.
  • Fault-Tolerant: Hevo can automatically detect anomalies from the incoming data and informs you immediately. 
  • Security: Hevo makes sure that your data is secured by using two-factor authentication and end-to-end encryption.
  • Zero Data Loss: Hevo makes sure that data is transferred from source to destination in real-time without any loss or change in data.
Sign up here for a 14-day free trial!

Getting Data from HubSpot to Google Data Studio

Google Data Studio does not provide a HubSpot connector but it allows data to be uploaded in the form of Google Sheets. HubSpot also provides APIs that allow data to be downloaded into many file formats. In this article, you will learn how to use different API’s provided by Google Sheets and HubSpot to move data into Google Data Studio.

Pre-requisites

  • A HubSpot admin account and API token generated from that account
  • A Google account
  • Basic understanding of business intelligence tools
Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Learn how to build a self-service data analytics stack for your use case.

Using a CSV File Generated via HubSpot APIs

This method uses Google Sheet’s app script mechanism to fetch data from HubSpot APIs to feed data to Data Studio. 

  1. Go to the HubSpot Admin section and generate API keys.  For this, go to Settings > Integrations > Generate API keys. 
  2. Go to Google Sheets and create a new sheet. In the new sheet, go to Tools 》Script Editor and start editing the script. You will begin by adding the header for Google Sheet.
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName('Blad1'); 
sheet.clear(); 
sheet.appendRow(["Dealname", "Dealstage", "Close date", "Amount"]); 
  1. You will now write a function to fetch JSON from HubSpot API. For this exercise, we will be using the deals API of HubSpot. 
var offset = 0; 
var queryParams = '&limit=250&offset=' + offset + '&properties=dealname&properties=dealstage&properties=closedate&properties=amount' 
var _url = 'https://api.hubapi.com/deals/v1/deal/paged?hapikey=' + api_key
var options = { "method": "GET", "muteHttpExceptions": true };
var apiResponse = UrlFetchApp.fetch(url + endpoint,options); 
var output_json = JSON.parse(apiResponse);
  1. You will now write values to the sheet we created using the below snippet.
while (output_json.status !== "error" && i < output_json.deals).length) {
        var dealname = output_json.deals[i].properties.dealname.value;
        var dealstage = output_json.deals[i].properties.dealstage.value;
        var closedate = new Date((output_json.deals[i].properties.closedate.value));
        var amount = output_json.deals[i].properties.amount.value;
        var deals = [dealname, dealstage, closedate, amount];
        sheet.appendRow(deals);
      }

You can set this function to be run based on a periodic trigger or use the custom function itself in the Google Sheet cell.

  1. The next step is to go to Data Studio connections and add the above Google Sheet as the source. To do this go to Data Studio and click on “Report” as shown below.
HubSpot to Google Data Studio - Connect Google Sheet as source
  1. Select Google Sheet when Data Studio asks for the data source and select the relevant sheet.
HubSpot to Google Data Studio - Connect Google Sheet as source

Once the sheet is selected, you should be able to create reports using data from HubSpot. The advantage of this approach is that you can ensure the sheet has the latest data by configuring the script to execute periodically or on-demand.

Limitations of this Approach

The above approach comes with its own share of pitfalls too:

  1. This method currently handles only the first page of results from HubSpot API. You will need additional custom development to implement pagination and get hold of your complete data. In other words, getting this to production needs an expert developer.
  2. In most cases, companies will need transformations to be executed on source data before creating reports in Data Studio. Such transformations are difficult to handle in the above approach. 
  3. Other than the deals API, HubSpot provides a number of APIs to extract data. Using all these APIs and integrating to Data Studio will need a herculean effort in developing as well as learning HubSpot APIs.

Conclusion

You have learned how you can move data from HubSpot to Google Sheets and then pull this data into Google Data Studio. However, it involves many steps and can be tedious. A simpler solution could be to integrate data from HubSpot and different sources so that data is analysis-ready and can be visualized easily in Data Studio.

visit our website to explore hevo

Try Hevo today for hassle-free data integration from SaaS applications, databases, and more. It helps transfer data from HubSpot to a destination of your choice for free. sign up for a 14 day free trial now.

Do you want to share your experience? Get in touch with us in the comments section below.

Talha
Software Developer, Hevo Data

Talha is a seasoned Software Developer, currently driving advancements in data integration at Hevo Data, where he have been instrumental in shaping a cutting-edge data integration platform for the past four years. With a significant tenure at Flipkart prior to their current role, he brought innovative solutions to the space of data connectivity and software development.

No-Code Data Pipeline for HubSpot