HubSpot link
HubSpot is an inbound marketing and sales software that helps companies attract visitors, convert leads, and close customers.
Featureslink
Feature Name | Supported | Notes |
---|---|---|
Capture deletes | check | ASSOCIATION_TYPE , COMPANY , CONTACT , CONTACT_LIST , CONTACT_LIST_MEMBER , DEAL , DEAL_PIPELINE , DEAL_PIPELINE_STAGE , FORM , LINE_ITEM , OWNER_TEAM , PRODUCT , QUOTE , ROLE , TEAM , TEAM_USER , TICKET , TICKET_PIPELINE , TICKET_PIPELINE_STAGE , and USERS tables. |
Custom data | check | COMPANY , CONTACT , DEAL , LINE ITEMS , and PRODUCT tables only. We support custom objects. |
Data blocking | check | Column level |
Column hashing | check | |
Re-sync | check | Connector level |
History | check | COMPANY_PROPERTY_HISTORY , CONTACT_PROPERTY_HISTORY , DEAL_PROPERTY_HISTORY , DEAL_STAGE , LINE_ITEM_PROPERTY_HISTORY , PRODUCT_PROPERTY_HISTORY , QUOTE_PROPERTY_HISTORY and TICKET_PROPERTY_HISTORY tables \*. |
API configurable | check | API configuration |
Priority-first sync | check | EMAIL_EVENT_SENT , EMAIL_EVENT_DROPPED , EMAIL_EVENT_DELIVERED , EMAIL_EVENT_DEFERRED , EMAIL_EVENT_BOUNCE , EMAIL_EVENT_OPEN , EMAIL_EVENT_CLICK , EMAIL_EVENT_STATUS_CHANGE , EMAIL_EVENT_SPAM_REPORT , EMAIL_EVENT_FORWARD , EMAIL_EVENT_PRINT , and EMAIL_EVENT_SUPPRESSED tables. |
Fivetran data models | check | Get the models: source / transform; Supports Quickstart data models |
Private networking |
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
andCOMPANY_PROPERTY_HISTORY
. You can sync the history table only if you have included the current state table in your syncs.
Supported productslink
Product Name |
---|
HubSpot CRM |
Marketing Hub |
Sales Hub |
Service Hub |
Setup guidelink
Follow our step-by-step HubSpot setup guide to connect HubSpot with your destination using Fivetran connectors.
Sync overviewlink
Sync strategylink
During a sync if we find more than 9,900 updated records for the following endpoints, we re-import the associated source tables in full to capture all the changes:
COMPANIES
DEALS
ENGAGEMENTS
CUSTOM_OBJECTS
If we find 990 updated records for the LINE_ITEMS
, PRODUCT
, and TICKET
tables, we re-import the tables in full to capture all the changes.
TIP: If you observe these re-syncs on the dashboard, you can configure a higher sync frequency for the connector. For example, 5 minutes.
NOTE: The HubSpot API returns a maximum of 10,000 most recently updated records. We maintain a buffer of 100 to ensure data integrity and prevent API errors. When we detect that the number of updated records exceeds 9,900, we consider that the number is more than 10,000 and re-import the table in full. For the
LINE_ITEMS
,PRODUCT
, andTICKET
tables, the API returns a maximum of 1000 records. When we detect that the number of records exceeds 990, we re-import the table in full.
We do not sync calculated properties, i.e., properties with the calculated
field set as true
, because the calculated fields are derived from other properties.
Sync strategy for DEAL tableslink
We incrementally sync the DEAL
table. However, we re-import the table if we find more than 9,900 updated records.
The DEAL
table has two child tables, DEAL_PROPERTY_HISTORY
and DEAL_STAGE
. The DEAL_STAGE
table stores the deal_stage property
and its historical versions. We don't store the deal_stage
property in the DEAL_PROPERTY_HISTORY
table.
Sync strategy for ENGAGEMENT tableslink
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
- 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_PROPERTY_HISTORY
If we detect a new engagement type, we skip the metadata for the new engagement.
Sync strategy for EMAIL EVENT tableslink
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 tableslink
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. We may also sometimes do a full re-sync of these tables according to the connector sync strategy.
The incremental endpoint used to sync the COMPANY
table has a 10,000 records source-side limitation. Exceeding the limitation can trigger a full re-sync of the COMPANY
table. However, source APIs only return the latest version of a property in response to the incremental endpoint used to sync the COMPANY
table. Therefore, to avoid data integrity issues in the COMPANY_PROPERTY_HISTORY
table, we also re-sync every history version with a timestamp greater than the time of the last full re-sync of the COMPANY
table. This re-sync may lead to higher MAR usage for the COMPANY_PROPERTY_HISTORY
table.
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 tableslink
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 till2023-03-30
in the last sync, then in the current sync the analytics data is synced from2023-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, abreakdown
column value of7081c5b2-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 thedate
column represent the starting date of a week and month respectively. For example, if the value ofdate
column for a*_ANALYTICS_WEEKLY_REPORT
table record is2023-08-03
, then the record represents analytics data for the time period of a week starting on2023-08-03
and ending on2023-08-09
.For the
*_ANALYTICS_OVERALL_REPORT
tables, abreakdown
column value oftotals
indicates the aggregated data of all otherbreakdown
column values for that table.The HubSpot connector syncs the
*_ANALYTICS_*_REPORT
tables depending on the HubSpot account's permissions.
Sync notelink
If you exclude the child tables and later want to sync the tables, perform a full re-sync to backfill the data in the child tables.
Capture deleteslink
We use different strategies to capture deletes because the HubSpot API doesn't offer a mechanism to capture deletes:
Infer deletes for the following tables:
ASSOCIATION_TYPE
CONTACT_LIST
DEAL_PIPELINE
DEAL_PIPELINE_STAGE
FORM
LINE_ITEM
OWNER_TEAM
PRODUCT
QUOTE
ROLE
TEAM
TEAM_USER
TICKET
TICKET_PIPELINE
TICKET_PIPELINE_STAGE
USERS
We compare the tables against their previous version and capture deletes using the
_fivetran_deleted
system column.NOTE: If records of the
LINE_ITEM
,PRODUCT
,QUOTE
andTICKET
tables are permanently deleted between syncs, we don’t mark the_fivetran_deleted
column astrue
for these records.We use webhooks to capture deletes for the
COMPANY
,CONTACT
, andDEAL
tables.NOTE: If you created the connector using our REST API, we don't capture deletes for the
COMPANY
,CONTACT
, andDEAL
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 resync for the table during weekends.
Capture mergeslink
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 aproperty_hs_calculated_merged_vids
column which stores data of the merged object ids.To capture merges of the
DEAL
table, we have amerged_deal
table in your destination. This table reflects that deal with idmerged_deal_id
has been merged into deal with iddeal_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 asis_deleted = TRUE
.
Identifying primary associationslink
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 calculationslink
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:
deal_id | deal stage | date_entered | _fivetran_start | _fivetran_end |
---|---|---|---|---|
10 | 123 | 11:00 | 11:00 | 12:00 |
10 | 145 | 12:00 | 12:00 | 13:00 |
10 | 157 | 13:00 | 13:00 | 14:00 |
10 | 173 | 14:00 | 14:00 | MAX_TIMESTAMP_VALUE |
For
deal_id
10 in deal stage 145:date_entered_145
=_fivetran_start
= 12:00date_exited_145
=date_entered_157
=_fivetran_end
= 13:00time_in_145
= 1 hour (_fivetran_end
-_fivetran_start
)
For
deal_id
10 in deal stage 173:date_entered_173
=_fivetran_start
= 14:00date_exited_173
=_fivetran_end
= MAX_TIMESTAMP_VALUEtime_in_173
= MAX_TIMESTAMP_VALUE - 14:00 (_fivetran_end
-_fivetran_start
)
HubSpot Tickets API limitationslink
HubSpot's Tickets API endpoint returns a list of changes to the ticket objects. HubSpot limits the returned changes to the last 24 hours. To ensure data integrity, Fivetran triggers a re-sync of the TICKET
table if the cursor is older than 24 hours.
Schema informationlink
Marketing Hub schemalink
This schema is applicable for the HubSpot Marketing Hub product.
To zoom, open the ERD in a new window.This schema is applicable for the HubSpot connectors created after November 11, 2022, or the connectors that have migrated to the HubSpot API v3 endpoint.
To zoom, open the ERD in a new window.CRM and Sales Hub schemalink
This schema is applicable for the HubSpot CRM and Sales Hub products.
To zoom, open the ERD in a new window.This schema is applicable for the HubSpot connectors created after November 11, 2022, or the connectors that have migrated to the HubSpot API v3 endpoint.
To zoom, open the ERD in a new window.This schema is applicable for the HubSpot connectors created after May 12, 2023.
To zoom, open the ERD in a new window.Service Hub schemalink
This schema is applicable for the HubSpot Service Hub product.
To zoom, open the ERD in a new window.This schema is applicable for the HubSpot connectors created after May 12, 2023.
To zoom, open the ERD in a new window.Web Analytics schemalink
This schema is applicable for the HubSpot Web Analytics API
To zoom, open the ERD in a new window.Custom objectslink
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
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 theupdatedAt
time of the custom object changes due to another property change.
Negative timestamps from HubSpotlink
We automatically convert negative timestamps received from HubSpot to EPOCH, for example, 1st January 1970 00:00:00 UTC.
Syncing empty tables and columnslink
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.