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