HubSpot + SQL Server - How to do daily Export & Import ?

[reedevtaneja]

Hi, I am looking to integrated HubSpot with my SQL server database which will act as a single source of truth between for all CRM & Sales data for reporting ( offline+online)

There are 2 use cases for the integrations:

1. Export all Contacts with a list of properties from HubSpot and push it to SQL server on a daily basis. I would prefer incremental ie only New or Updated contacts info will be updated in databases, if not possible then may be over-write the entire table everytime but offcourse the records limit can be a restriction.

2. Import contacts from SQL server stored in a different table and create them as new contacts in HubSpot. These will be offline contacts generated via offline mode. 

Both Import & Export should be done twice a day or atleast daily to ensure we have the data synced between database and HubSpot. ur estimated contacts may not go beyond 50K in next 2-3 years. 

Challenge is within API documentation, it requires contact id or vid in order to export /retrive the contact data, which we wont have for the new contacts created within HubSpot. O

Can you please advise on how to bulk export & import data from HubSpot to SQL server database without using an external app, we can build using Python. We are not sure which API to use and how to import/export contacts without contact id/vid.

Thanks

Reed 

[SergeyK]

Hi there!

As it was told, the best solution for your use case is to use a 3rd party tool. I can recommend you to try Skyvia. It natively supports both HubSpot and SQL Server connectors and allows you to easily integrate data in any direction using regular schedule. You can read more about its features here or on HubSpot marketplace.

Skyvia works good when you need to integrate cloud apps and databases using ETL/ELT approaches.

Hope it helps.

[nhatmusic]

Hi Reed,

We have built a daily process using Hubspot API v1 to solve similar use cases. Workflow

Download all contact from Hubspot to SQL table -> Update all contact from SQL to Hubspot (Creating new contact is included in this step)

Key: Central table in SQL will be truncated everytime, this table is used to store Hubspot contact and define which contact need to be updated/created in Hubspot contacts with vid.

- Hubspot Get All contact API will return all contact's vid for sure so there is probably no challenge to get vid for new contacts created within Hubspot. https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts

- API limit: since Hubspot limits to 100 contacts per API call, you need to use Contact Offset to loop through all contact and you should handle async API call well.

We are working on downloading only new/updated contacts to SQL  and Import only changed or new contact from SQL to Hubspot. We hope that Hubspot will release 2-way contact sync between Hubspot and SQL server in the future.

I hope that our idea will help you. Thanks!

[DLarson]

Reed,

We have built an extractor for pulling out HubSpot data, indexing it and creating a new SQL Server DB.   Still in Beta but will be launching shortly.  I would be happy to share it with you now.

Don

[skszymon]

Is it available somewhere? I could test it

[johnelmer]

@reedevtaneja @nhatmusic @DLarson @jpsanchez @dennisedson We offer an app in the HubSpot App Marketplace called MS SQL Server for HubSpot . The app pulls your HubSpot data into a SQL data warehouse via prebuilt API connectors. A SQL data warehouse in Azure is included in the cost.

Once your HubSpot data is in the data warehouse, it automatically refreshes on a scheduled basis. You can connect the SQL data warehouse to any other application that has a SQL connector. 

[jpsanchez · Accepted solution]

Hi Reed, 

The best aprouch is to use a connector between HubSpot and your SQL with a EL or ETL aprouch. ( if you wish you can use your own connector and or ETLs in the market) in my experience. 

I already work with this two ones.

https://app.hubspot.com/ecosystem/5890901/marketplace/apps/sales/data-management/import2datamigratio...

https://app.hubspot.com/ecosystem/5890901/marketplace/apps/marketing/analytics-data/stitch

The DYI . some examples. 

https://github.com/Jpsanchezasmalljob/tap-hubspot

https://github.com/Ucculer22/HubSpot-SQL-Data-Clean-Up

https://github.com/jpetrucciani/hubspot3

Please let me know if it helps you! 😉

Best

JP

[dennisedson]

Hi @reedevtaneja 

Thanks for submitting the question! 

Wanted to tag a few people from the community in here who may be able to assist!

@jpsanchez , @derekcavaliero , @DanielSanchez -- Hi all!  Do you guys have thoughts on this question?

Thanks in advance!