How to Connect HubSpot to Looker Studio in 6 Easy Steps

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.
• Looker Studio ( 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 Looker Studio.

Methods to Connect HubSpot to Looker Studio

1. Looker Studio does not provide a HubSpot connector but it allows data to be uploaded in the form of Google Sheets.
2. 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

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"]); 

3. 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);

4. 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.

5. 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” .
6. Select Google Sheet when Data Studio asks for the data source and select the relevant sheet.

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 Looker Studio ( 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.

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