HubSpot

HubSpot is an inbound marketing and sales software that helps companies attract visitors, convert leads, and close customers.

Features

IMPORTANT: The HubSpot connector doesn't support switching modes because we fetch history data directly from the APIs. We maintain two separate tables for HubSpot connector: one that reflects the current state and one for history mode. For example, on the connector's Schema tab, you will have two tables, COMPANY and COMPANY_PROPERTY_HISTORY. You can sync the history table only if you have included the current state table in your syncs.

Supported products

Setup guide

Follow our step-by-step HubSpot setup guide to connect HubSpot with your destination using Fivetran connectors.

Sync overview

Sync strategy

Sync strategy for DEAL tables

We incrementally sync the DEAL table.

The DEAL table has two child tables, DEAL_PROPERTY_HISTORY and DEAL_STAGE. We don't store the deal_stage_property in the DEAL_PROPERTY_HISTORY table. The DEAL_STAGE table stores the deal_stage_property and its historical versions.

Sync strategy for ENGAGEMENT tables

We incrementally sync the ENGAGEMENT and ENGAGEMENT_* tables according to the sync frequency you set in your Fivetran dashboard.

We sync metadata for the following engagement types into the corresponding destination tables:

• NOTE
• EMAIL
• TASK
• PUBLISHING_TASK
• MEETING
• CALL

We sync engagement metadata only if you select the corresponding engagement type table(s) above. The metadata is stored in the following tables:

• ENGAGEMENT
• ENGAGEMENT_COMPANY
• ENGAGEMENT_CONTACT
• ENGAGEMENT_DEAL
• ENGAGEMENT_GOAL
• ENGAGEMENT_LEAD
• ENGAGEMENT_PROPERTY_HISTORY

If we detect a new engagement type, we skip the metadata for the new engagement.

Sync strategy for EMAIL EVENT tables

In every sync, we re-fetch data that is twenty-five hours behind the data synced in the current incremental sync for the EMAIL_EVENT_* tables you have selected. We do this to capture events we may have missed because of HubSpot's event processing delays.

The EMAIL_EVENT table syncs data for only the event types you define in the source EMAIL_EVENT_* child tables.

To sync the EMAIL_EVENT table, make sure to select all the relevant EMAIL_EVENT_* child tables in the Schema tab of your connector's dashboard. If you don't select any of these child tables, we don't sync data into the EMAIL_EVENT table.

TIP: HubSpot's Email Events API sends bot events that may increase your total event count. To exclude these bot events, query the events with filtered_event = false.

Sync strategy for PROPERTY_HISTORY tables

Enabling history mode increases your monthly active rows (MAR) consumption because every change is recorded as a new row that counts towards MAR. We do incremental syncs of the history tables.

NOTE: The CONTACT_PROPERTY_HISTORY table contains different versions of each record, and syncing the table may contribute to higher MAR usage because of its size.

NOTE: When deleted records are restored at source, we sync their property history changes only after the time they were restored.

Sync strategy for Web Analytics tables

• To keep the analytics data updated, during incremental sync, we sync the data for the following granular analytics tables with the corresponding rollback sync durations:

  • *_ANALYTICS_DAILY_REPORT - 30 days
  • *_ANALYTICS_WEEKLY_REPORT - Four weeks
  • *_ANALYTICS_MONTHLY_REPORT - Two months

  For example, if the data for the GEOLOCATION_ANALYTICS_DAILY_REPORT table is synced till 2023-03-30 in the last sync, then in the current sync the analytics data is synced from 2023-03-01 till the current date.

  NOTE: This causes higher MAR usage for these tables at the beginning of each month.

• Fivetran performs full table re-syncs for all the *_ANALYTICS_OVERALL_REPORT tables in every sync, as they represent the rolled up analytics from the start to the current date.

  NOTE: For more information about MAR usage for re-imported tables, see our Troubleshooting documentation.

• For GEOLOCATION_ANALYTICS_*_REPORT tables, a breakdown column value of 7081c5b2-d128-4ec1-a9be-cba29cfc540a indicates the visits for which HubSpot is unable to capture the country code data.

• For the *_ANALYTICS_WEEKLY_REPORT and *_ANALYTICS_MONTHLY_REPORT tables, the values of the date column represent the starting date of a week and month respectively. For example, if the value of date column for a *_ANALYTICS_WEEKLY_REPORT table record is 2023-08-03, then the record represents analytics data for the time period of a week starting on 2023-08-03 and ending on 2023-08-09.

• For the *_ANALYTICS_OVERALL_REPORT tables, a breakdown column value of totals indicates the aggregated data of all other breakdown column values for that table.

• The HubSpot connector syncs the *_ANALYTICS_*_REPORT tables depending on the HubSpot account's permissions.

Sync note

If you exclude the child tables and later want to sync them, perform a table level re-sync of parent table to backfill the data in the child tables.

Capture deletes

We use different strategies to capture deletes because the HubSpot API doesn't offer a mechanism to capture deletes for every table:

• We capture deletes once a day via API call for the following tables:

  • FEEDBACK_SUBMISSION
  • GOAL
  • LEAD
  • LINE_ITEM
  • PRODUCT
  • QUOTE
  • TICKET

  NOTE: If records of the FEEDBACK_SUBMISSION,GOAL, LEAD, LINE_ITEM, PRODUCT, QUOTE, and TICKET tables are permanently deleted between syncs, we don’t mark the _fivetran_deleted column as true for these records.

• We infer deletes for the following tables by comparing the tables against their previous versions and capturing deletes using the _fivetran_deleted system column:

  • ASSOCIATION_TYPE
  • CONTACT_LIST
  • CUSTOM_EVENT_DEFINITION
  • CUSTOM_EVENT_DEFINITION_PROPERTY
  • DEAL_PIPELINE
  • DEAL_PIPELINE_STAGE
  • FORM
  • LEAD_PIPELINE
  • LEAD_PIPELINE_STAGE
  • OWNER_TEAM
  • ROLE
  • TEAM
  • TEAM_USER
  • TICKET_PIPELINE
  • TICKET_PIPELINE_STAGE
  • USERS

• We use webhooks to capture deletes for the COMPANY, CONTACT, and DEAL tables.

  NOTE: If you created the connector using our REST API, we don't capture deletes for the COMPANY, CONTACT, and DEAL tables, because these connectors are not authorized using the Fivetran HubSpot application.

• To capture deletes for the CONTACT_LIST_MEMBER table, we perform a weekly re-sync for the table during weekends. The MAR charged during the weekly re-sync is for incremental records only.

Capture merges

In HubSpot, you can merge two records into one record. For example, when you merge two contacts, the primary contact record remains after the merge and the secondary contact is merged into the primary record. For more information, see HubSpot's documentation.

We capture merges for the COMPANY, CONTACT, and DEAL tables.

• To capture merges of the CONTACT table, we have a property_hs_calculated_merged_vids column which stores data of the merged object ids.

• To capture merges of the DEAL table, we have a merged_deal table in your destination. This table reflects that deal with id merged_deal_id has been merged into deal with id deal_id.

  NOTE: In your destination table, you may observe the missing _fivetran_deleted column.

• To capture merges of the COMPANY table, we mark the company records as is_deleted = TRUE.

Identifying primary associations

We can find the primary company associated with an object. For example, if you want to identify the primary association in the DEAL_COMPANY table, use the following SQL query:

SELECT * FROM DEAL_COMPANY dc JOIN ASSOCIATION_TYPE t ON dc.type_id = t.id WHERE t.label LIKE 'Primary';

Similarly, you can query for other association labels.

Deal stage calculations

The HubSpot API returns only the date_entered value of the dealstage properties data. We sync this data to the DEAL_STAGE table.

Fivetran populates the date_entered value in the _fivetran_start column and sets the date_exited value in the _fivetran_end column. The date_exited value for a deal stage is the date_entered value of the next deal stage in chronological order.

You can calculate the time_in value as the difference between the _fivetran_start and _fivetran_end column values in the DEAL_STAGE table.

For example, in the DEAL_STAGE table:

• For deal_id 10 in deal stage 145:

  • date_entered_145 = _fivetran_start = 12:00
  • date_exited_145 = date_entered_157 = _fivetran_end = 13:00
  • time_in_145 = 1 hour (_fivetran_end - _fivetran_start)

• For deal_id 10 in deal stage 173:

  • date_entered_173 = _fivetran_start = 14:00
  • date_exited_173 = _fivetran_end = MAX_TIMESTAMP_VALUE
  • time_in_173 = MAX_TIMESTAMP_VALUE - 14:00 (_fivetran_end - _fivetran_start)

Table re-syncs

When you re-sync a parent table, we re-sync its child tables along with the parent table.

To re-sync an association table (with table name in [FROM_TABLE_NAME]_[TO_TABLE_NAME] format), you must re-sync the [FROM_TABLE_NAME] table. For example, to re-sync the association table COMPANY_CONTACT, you must re-sync the COMPANY table.

Similarly, to re-sync a custom association table (with table name in [FROM_TABLE_NAME]\_TO\_[TO_TABLE_NAME] format), you must re-sync the [FROM_TABLE_NAME] table.

NOTE: If you have created custom events in your HubSpot account by backdating them using the API, you must trigger a table re-sync of the CUSTOM_EVENT table to fetch backdated records, as we only perform a rollback sync of 25 hours.

Schema information

Marketing Hub schema

This schema is applicable for the HubSpot Marketing Hub product.

CRM and Sales Hub schema

This schema is applicable for the HubSpot CRM and Sales Hub products.

Service Hub schema

This schema is applicable for the HubSpot Service Hub product.

Web Analytics schema

This schema is applicable for the HubSpot Web Analytics API

Goals schema

This schema is applicable for the HubSpot Goals.

Custom objects

You can sync custom objects from your HubSpot account to your destination. We create a destination table for each custom object. We follow our standard table naming conventions.

We sync the properties you define for the custom objects into the PROPERTY table.

We create an association table in your destination to capture the associations between different custom objects and associations between a custom object and the following tables:

• COMPANY
• CONTACT
• DEAL
• EMAIL
• ENGAGEMENT
• GOAL
• LINE_ITEM
• PRODUCT
• TICKET

We name the custom object association tables using the format, [FROM_TABLE_NAME]\_TO\_[TO_TABLE_NAME].

NOTE: In HubSpot, a custom object's updatedAt time does not change for association changes. Also, HubSpot doesn't support webhook subscriptions for custom object association changes. Due to these limitations from HubSpot, we may not be able to sync some associations to the destination immediately in the incremental sync. We sync these associations whenever the updatedAt time of the custom object changes due to another property change.

Negative timestamps from HubSpot

We automatically convert negative timestamps received from HubSpot to EPOCH, for example, 1st January 1970 00:00:00 UTC.

Syncing empty tables and columns

The HubSpot connector does not support the creation of empty tables and columns in your destination.

We create a table in the destination only if we can retrieve the table data from the source. If HubSpot does not return any data for a source table, we don’t create the table in your destination.