Overview

Civis’s HubSpot data integration is used to import a client’s email marketing data from HubSpot. In practice, it is a collection of template scripts to query HubSpot’s API and write the data to Civis tables.  

Template Scripts

There are template scripts linked below: Campaigns, Contacts and Form Submissions, Email Events, Subscription Changes. The code backing each template hits a different API endpoint and then creates or updates one table on Civis Platform. In the case of Contacts and Form Submissions, two tables are created. 

To create a new script from these templates, click the template link and select the “New Custom Script” button

• Campaigns
  • https://platform.civisanalytics.com/spa/#/templates/scripts/58010
• Contacts and Form Submissions
  • https://platform.civisanalytics.com/spa/#/templates/scripts/58873
• Email Events
  • https://platform.civisanalytics.com/spa/#/templates/scripts/36980
• Subscription Changes
  • https://platform.civisanalytics.com/spa/#/templates/scripts/57773
• Marketing Emails
  • https://platform.civisanalytics.com/spa/#/templates/scripts/84626 

Filling in the Parameters

Parameters Common to all HubSpot Templates

Script Mode; Default

This parameter allows the user to run the script in different modes. So, for example, if you just want to test if the API key is valid without actually writing data to a table, you just need to select the test_connection option and it checks if you can make a call to HubSpot. And if you want to write data to the table, you’ll select the run option. The list_object option will list the different nodes/stages that make up the data pipeline. The help option will just list all the modes you can run and also the version that is currently running.

HubSpot Credential; Required

The Civis credential that holds your HubSpot API key or private app token.

HubSpot URI; Default

This displays the HubSpot API endpoint for the Template. 

Destination Database; Required

Specify the database you would like to import the data into

Destination Schema and Table; Required

Specify the schema and table name you would like to import the data into

Days; 

The number of days back to pull the data. For example, if run on 2020-06-04, it will pull data from 2020-06-03, based on UTC time. This parameter is mutually exclusive with State Date/End Date parameters. 

Start Date/ End Date; 

The date range to pull the data. This parameter is mutually exclusive with the Days parameter.

Action if Table Exists; Default

Specify an action to take if any of the destination tables already exist. The options are ‘fail’, ‘append’, ‘wipe’, or ‘drop’. The default is ‘fail’.

Contacts 

This returns newly created or modified contacts and their properties based on the canonical-vid which is Hubspot’s internal ID of the contact. Each modification of the contact results in a new row of data, based on the timestamp of the modification properties__lastmodifieddate__value.

If dates are not provided, the default behavior is to pull data for the current day  i.e. if today is July 1st in UTC, then it will pull start of day today and end of day today (start_date=2020-07-01T00:00 and end_date=2020-07-02T00:00).

By default, the “Hubspot URI” parameter points to the API endpoint for recently updated contacts (within the last 30 days). If you need to import older or historical data, set the “Hubspot URL” parameter to https://api.hubapi.com/contacts/v1/lists/all/contacts/all (documentation: https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts).

Form Submissions

API Endpoint: https://legacydocs.hubspot.com/docs/methods/contacts/contacts-overview

This returns all form submissions submitted by the contact. The table returned will be at the form-submission level, for which each form submission has its own id called conversion-id. It will also include the _source_key_vid which is mapped to the record id of the contact. 

Campaigns

API Endpoint: https://legacydocs.hubspot.com/docs/methods/email/get_campaign_data

This will return a full refresh of campaign data, keyed on the campaign id. As such it does not have a date range. This data includes the campaign name, for example, 20180604_Colab_CoraisVídeo_Resposta (Variation). 

Email Events

API Endpoint: https://legacydocs.hubspot.com/docs/methods/email/email_events_overview

This returns event data of email interactions. An event can be an open, click, unsubscribe, etc. The created field is the timestamp of the email event and the returned table will be keyed on the event id. 

Subscription Changes

API Endpoint: https://legacydocs.hubspot.com /docs/methods/email/get_subscriptions_timeline

CHANGE TYPE; Optional

This table returns details about email events that are subscription changes or bounces. Each subscription change is linked to an email event through the causedbyevent__id.

Marketing Emails

API Endpoint: https://legacydocs.hubspot.com/docs/methods/lists/marketing-email-overview 

This will return a table with the following fields from the Marketing Emails API: 

• ab,abstatus,absuccessmetric,abtestid,abtestpercentage,absoluteurl,campaign,campaignname,id,mailinglistsexcluded,mailinglistsincluded,subscriptionname,updated,created

The date parameters correspond to the created field, so will return data for emails created in the specific date range. 

Resources 

Civis Help documentation: 

• HubSpot API documentation: https://legacydocs.hubspot.com/docs/overview
• HubSpot private apps documentation: https://developers.hubspot.com/docs/api/private-apps
• Setting up a HubSpot credential: https://support.civisanalytics.com/hc/en-us/articles/25912912492429-Adding-a-HubSpot-Credential-to-Platform