Articles in this section

See more

HubSpot Imports

Avatar
Imaad Khan
Updated
Follow

The Import from HubSpot script template imports data from a HubSpot object under your HubSpot account onto Civis Platform.

Parameters

  • HubSpot Access Token (required): Access token for HubSpot API. Please see this page to set one up.
  • HubSpot Standard Object (optional): The HubSpot standard object to import data from. Either this or the “HubSpot Custom Object” below, but not both, must be specified. The supported standard objects are listed below. Most of them are CRM objects, for which the Exports API is used to access data and the Properties API is used to determine the columns and their data types. For CRM objects, the scopes “crm.export”, “crm.objects.<OBJECT>.read”, and “crm.schemas.<OBJECT>.read” need to be available for your HubSpot access token. For special cases as well as non-CRM objects, please refer to the notes for the respective object below:
    • Appointments
    • Calls
    • Communications
    • Companies
    • Contacts
    • Courses
    • Deals
    • Emails (individual, one-to-one emails, as opposed to “Marketing emails” below)
      • The following fields that may contain extremely long strings due to email data are imported as a binary data type (e.g., VARBYTE if you’re on Redshift):
        • hs_email_html
        • hs_email_text
        • hs_body_preview_html
    • Email events
      • The scope “content” is needed for your access token.
      • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “id” and the last modified timestamp column is “created”.
      • Data is accessed through the “email events” endpoint.
    • Email subscriptions
      • The scope “communication_preferences.read” or “communication_preferences.read_write” is needed for your access token.
      • This import option requires a list of email addresses coming from a SQL query that provides a column called “email” (exact column name required – typically from the “Contacts” data imported by this template). Provide a SELECT SQL query at the parameter “SQL Query for Parent IDs”, e.g., “SELECT email FROM your_schema.your_contacts_tablename”.
      • Data is accessed through this “subscription preferences” endpoint.
  • Feedback submissions
  • Forms
    • The scope “forms” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “guid” and the last modified timestamp column is “updatedAt”.
    • Data is accessed through the “forms” endpoint.
  • Form submissions
    • The scope “forms” is needed for your access token.
    • This import option does not support “upsert” for the “Existing Table Rows” parameter.
    • Data is accessed through the “form submissions” endpoint.
  • Invoices
  • Landing pages
    • The scope “content” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “id” and the last modified timestamp column is “updatedAt”.
    • Data is accessed through the “landing pages” endpoint.
  • Leads
  • Line items
  • List memberships
    • The scope “crm.lists.read” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key columns are ["listId", "recordId"] and the last modified timestamp column is “membershipTimestamp”.
    • This import option requires a list of list IDs coming from a SQL query that provides a column called “listid” (or “listId” if your database is BigQuery). Provide a SELECT SQL query at the parameter “SQL Query for Parent IDs”, e.g., “SELECT listid FROM your_schema.your_tablename”. The data containing a list of list IDs are available from the import option “Lists (segments)”.
    • Data is accessed through the “list memberships” endpoint. Please note that a “listId” column is added to each row in the imported data, in order to make it possible to link list IDs to record IDs.
  • Listings
  • Lists (segments)
    • The scope “crm.lists.read” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “listId” and the last modified timestamp column is “updatedAt”.
    • Data is accessed through the “lists search” endpoint.
  • Marketing emails (mass emails for marketing, as opposed to one-to-one “Emails” above)
    • The scope “content” is needed for your access token.
    • The column “content” may contain extremely long strings due to email data. Therefore, it is imported as a binary data type (e.g., VARBYTE if you’re on Redshift).
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “id”, and the last modified timestamp column “updatedAt”.
    • Data is accessed through the Marketing Email API.
  • Marketing events
    • The scope “crm.objects.marketing_events.read” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “objectId” and the last modified timestamp column “updatedAt”.
    • Data is accessed through the Marketing Events API.
  • Marketing events participations breakdown
    • The scope “crm.objects.marketing_events.read” is needed for your access token.
    • This import option does not support “upsert” for the “Existing Table Rows” parameter. If the “Days” parameter is specified, it is used to filter the “marketing events” data using their “updatedAt” field, and then the participations breakdown data is retrieved for all these marketing events.
  • Meetings
  • Notes
  • Orders
  • Payments
  • Postal mail
  • Products
  • Quotes
  • Services
  • Subscriptions (for recurring payments)
    • This option is distinct from “Email subscriptions” above.
  • Subscription changes
    • The scope “content” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “normalizedEmailId” and the last modified timestamp column is “timestamp”.
    • Data is accessed through the “subscription timeline” endpoint.
  • Tasks
  • Tickets
  • Users
  • Workflows
    • The scope “automation” is needed for your access token.
    • If the “Existing Table Rows” parameter is “upsert”, the primary key column is “id” and the last modified timestamp column is “updatedAt”.
    • Data is accessed through the Workflow Details API.
  • HubSpot Custom Object ID (optional): The HubSpot custom object to import data from. Provide the object ID (which looks something like 2-3456789). Either this or the “HubSpot Standard Object” above, but not both, must be specified.
  • Days (optional): The number of days (as an integer) of data to import. If not specified, all data will be imported. If specified, only data modified in the last N days will be imported, as determined by the hs_lastmodifieddate column. For incremental syncs in an automated pipeline, combine this parameter with the “existing table rows = upsert” option below, e.g., “existing table rows = upsert” and “days = 7” if your pipeline is scheduled to run weekly.
  • Start Date (optional): The start date (inclusive) for data to import, in YYYY-MM-DD format. If specified, only data modified on or after this date will be imported. Currently, only the "Email events" import option supports this parameter.
  • End Date (optional): The end date (inclusive) for data to import, in YYYY-MM-DD format. If specified, only data modified on or before this date will be imported. Currently, only the "Email events" import option supports this parameter.
  • SQL Query for Parent IDs (optional): If importing a HubSpot object whose API endpoint requires parent object IDs, provide a SQL query that returns the list of parent IDs to import data for. Please see the documentation above for specific HubSpot objects that require parent IDs and details on how to construct this query.
  • Database (required): The Civis Platform database to which to write records.
  • Database Credential (required): A valid database credential for the Civis Platform database. To use the credential associated with the logged-in user, choose from the dropdown the credential that has this user’s Civis Platform username.
  • Schema and Tablename (required): The schema and table name you would like to import the data into, e.g., schema.table.
  • Column Data Type Overrides (optional): A mapping from column names to data types to use instead of the data types from the data source. Each line should be in the form of "column_name: DATA_TYPE" (without quotes), e.g., "foo_bar: VARCHAR". Only provide an entry for columns for which you want to override the data type; do not provide entries for columns that should use the default data type from the data source. This parameter is useful for data type issues that would otherwise cause the import to fail, e.g., use "foobar: VARCHAR" if the data source says "foobar" should be a numeric type but it actually contains non-numeric values.
  • Existing Table Exists (optional): The action to take if a table with the requested name already exists. Options are:
    • Append: Records will be appended to the existing table
    • Fail: The sync job fails if the table exists
    • Drop: Removes the entire table including column information.
    • Truncate: Records on the table will be wiped out while maintaining the current columns.
    • Upsert: For upserts, unless otherwise stated for specific HubSpot objects listed above, the ID and last modified timestamp columns to be used are 'hs_object_id' and 'hs_lastmodifieddate', respectively. For incremental syncs in an automated pipeline, combine this “upsert” option with the “days” parameter above, e.g., “existing table rows = upsert” and “days = 7” if your pipeline is scheduled to run weekly.

 

Note: The new “Import from HubSpot” script template documented above should be used if possible instead of the legacy templates listed in the rest of this page. For existing custom scripts using one of the old templates below, users are strongly encouraged to migrate them to the new template. To facilitate transition, all the old templates will remain as-is and won’t be removed, though they will no longer receive any updates.

 

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.  

Legacy 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

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 

API Endpoint: https://legacydocs.hubspot.com/docs/methods/contacts/get_recently_updated_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 (for contacts that aren’t newly created and modified, please use the “All Contacts” script template instead). 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).

All Contacts

API Endpoint:

https://developers.hubspot.com/docs/reference/api/crm/exports

This returns contacts and all their properties based on a datetime range filtered by lastmodifieddate.

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

  • 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

Related articles

Comments

0 comments

Article is closed for comments.