By Alex Rolnik

Hubspot Object Updates - BigQuery

Integrates with:

The Hubspot Object Updates Kit ingests entity data into the data warehouse and uses Hubspot’s API to update objects in Hubspot with additional information retrieved from the data warehouse. The flexible nature of the API gives the user the ability to update any of the CRM objects within Hubspot (Deals, Contacts, Companies, etc.) using this Kit.

This Kit includes…

  • Hubspot entity data ingestion using the predefined reports functionality
  • Sample SQL code to build a JSON structured variable which will update an object’s properties within Hubspot
  • Action river that updates the internal Hubspot object
  • Logic that orchestrates the Kit as a whole

For more information on the Hubspot API, see here

Minimum RPU Consumption: 10 (This can be reduced by limiting the number of entities being pulled in the entity data ingestion river)

Configuring this Kit for use

Variables

In this Kit, there are three global variables that are used to make for dynamic use of target configuration. 

  • {Dataset_Hubspot} is used in all Target configurations and queries as the target dataset ID.
  • {Hubspot_Alert_Group} is used as the alert email address(es) for when a river fails (optional).

Go to the Variables menu on the left side navigation bar and create variables for Dataset_Hubspot and Hubspot_Alert_Group. Then fill in the values as the Google BigQuery dataset where you would like the data to land and the email address(es) to which you would like the failure emails sent.

Beyond the global variables, there are two additional river variables that need to be initialized in order to get the Kit to complete successfully. Adding values to these already created parameters can be done within the “Hubspot Object Updates – Logic” river by clicking the top right “Variables” button.

  • {river_api_key} is the account-specific API key which allows developers to create custom applications with Hubspot’s APIs. 
  • {river_objects} is used as the object which the Kit will update within Hubspot. It can be any of the following values in the CRM object list: companies, contacts, deals, line_items, products, tickets, quotes. The sample code in the Kit is updating the Deals object in Hubspot.

Directions (Logic Updates)

The sample logic within the Kit can easily be changed to update different properties and/or Hubspot objects. In the code, the HUBSPOT_DEALS table from the “Hubspot Entities” river is joined to the GEOTARGETS sample table within BigQuery to update the “Country Code” and “Status” properties of the Deals objects.

Updating this Kit to be personalized will require changing 4 parts of the sample logic. 

  1. The entity table used will need to change depending on the type of object you’re updating. We are updating a deal so we use the deals table.
  2. The sample logic joins the two tables on the “en_name” field to get the values for the “Country Code” and “Status” properties. You’ll need to change this logic to join on a specific field within the Hubspot object and a table in the data warehouse.
  3. The DEALID field (line 15) will also need to be substituted based on the Hubspot object you’re looking to update.
  4. On lines 11 and 16, the TO_JSON_STRING and STRUCT functions build the JSON structure for the API request body seen below. 

Connections

Kits are imported with ‘blank’ source and target connections. In order to use the kit, you have two options:

  • Swap out blank connections with existing connections
  • Add credentials to the blank connections that come with the imported Kit

Have questions about this Kit?

Set up a meeting with a Rivery solutions engineer.

Easily Solve Your Most Complex Data Challenges

No Credit Card Required.