Question

Creating a HubSpot Action Hub Integration

  • 21 February 2019
  • 1 reply
  • 250 views

I’ve been a part of more and more conversations lately where companies are trying to understand their customers and market to them better, especially in the online retail space (even more specifically the order online, pick up in store). This post is intended to show how you can integrate Looker with HubSpot via GCP cloud functions to create more data-driven lists of contacts.


First, major thank you to @jesse.carah and his post here on writing back to a BigQuery database with Looker action hub. This tutorial will be utilizing the majority of that post as the basis for the HubSpot Integration. Please familiarize yourself with the " Creating the Action List Cloud Function" and " Creating the Action Form Cloud Function" section of that post, in an effort to not re-invent the wheel I will not cover those sections again in detail as they are very closely mimicked for the HubSpot integration.


For the Action List function, you can use the below code. An important thing to note is, the “HubSpot API Key.” You will be prompted to enter your HubSpot API key when you add the Action to Looker. The execution code relies on the API key for access to your HubSpot instance:




Action List Code

def action_list(request):
r = request.get_json()
print(r) # so it shows up in logs

response = """
{
"integrations": [{
"name": "hubspot",
"label": "Update Contact in Hubspot",
"supported_action_types": ["query"],
"url": "https://us-central1-hubspottesting.cloudfunctions.net/action_execute",
"form_url": "https://us-central1-hubspottesting.cloudfunctions.net/action_form",
"icon_data_uri": "https://uploads-us-west-2.insided.com/looker-en/attachment/base64_5a36726cd1d9ea6f9e350c2e67a43f18.png",
"required_fields": [],
"params": [{
"name": "api_key",
"label": "API Key",
"required": true
},{
"name": "hub_key",
"label": "HubSpot API Key",
"required": true
}],
"supported_formats": ["json"],
"supported_formattings": ["unformatted"],
"supported_visualization_formattings": ["noapply"]
}]
}

"""
return response


For the Action Form code, you can use this:




Action Form Code

import json

def action_form(request):

form = """
[{
"name":"propValue",
"label":"Identifier",
"description":"** EMAIL MUST BE FIRST COLUMN IN RESULT SET **",
"type":"text",
"default":"Value to create a filtered list",
"required":true
}]
"""
return form


This final section is the code most specific to HubSpot. For this example we are using Looker to update a contact property. The code, as it is, updates a custom property called “lookertag”. You can create the lookertag property in your instance, or choose to update an existing property by changing that property in the code. This script will update contacts that already exists as well as add the contacts if they don’t.




Action Execute Code

import requests
import json
import datetime

#HubSpot API says it works best with increments of 100
def chunks(data):
for i in range(0, len(data), 100):
yield data[i:i+100]

def constructPropUpdate(contacts):
contacts_json = contacts.get_json()
print(contacts_json)
api_key = contacts_json['data']['api_key']
hub_key = contacts_json['data']['hub_key']
query_data = json.loads(contacts_json['attachment']['data'])

propValue = contacts_json['form_params']['propValue']
masterList = []
propUpdateUrl = "https://api.hubapi.com/contacts/v1/contact/batch/?hapikey="+hub_key
headers = {}
headers["Content-Type"] = "application/json"

today = datetime.datetime.today()
today.strftime('%m/%d/%Y')

for datum in query_data:
#Transform email into format expected by HubSpot API
propDict = {}
keys = list(datum.keys())
keys = keys[0]
propDict["email"] = datum[keys]
propDict["properties"] = []
propUpdate = {}
propUpdate["property"] = "lookertag"
propUpdate["value"] = contacts_json['form_params']['propValue'] + " " + today.strftime('%m/%d/%Y')
propDict["properties"].append(propUpdate)
masterList.append(propDict)

#parse the data into 100 length chunks
contact_chunks = list(chunks(masterList))

response = []
for chunk in contact_chunks:
resp = requests.post(propUpdateUrl,data=json.dumps(chunk),headers=headers)

response.append(resp.status_code == 202)

if False not in response:
return '{"looker": {"success": true}}'


In this example, we have filtered to customers that have purchased more than one Levi’s product in the last 4 weeks. We then provide Looker a value to set for the lookertag property:


You can determine if you want this to be a one time thing, on going on an interval, etc.


Once you click send or schedule, you will see those contacts in HubSpot and you can now leverage the lookertag value to create either a static or active HubSpot List by filtering on that property:


Mix it with a “Last modified date” filter, and now your Looker schedule will update the “Levi’s Lovers” List, and your HubSpot “Last modified date” filter will drop off contacts so they don’t continue to receive the promotion.


The HubSpot API provides a ton of end points that you could easily integrate following this same process, so hopefully this example spurs ideas on how you can create and manage more tailored HubSpot lists. Taking it one step further, modify this action to also write back to BigQuery so you can easily monitor and assess the performance of campaigns in Looker.


1 reply

@ryan.grojean Would this allow you to update any contact properties in HubSpot using the values returned in your look, or to just add people to HubSpot as contacts and tag them?


We use hubspot for communication to our end users, and use Looker to pull a list of those end users and their many properties, then perform a HubSpot import to update/create records as necessary. It would be awesome to get this more streamlined as it’s a pain right now.

Reply