HubSpot to MySQL: 2 Easy Methods

This post shows you how to move data from HubSpot to MySQL. Hubspot is a cloud-based software suite to handle all activities related to customer relationship management and marketing automation. It is offered as a completely managed service and relieves customers of the pain in implementing and maintaining a highly available customer relationship management tool.  Hubspot comprises three modules – Marketing hub, Sales Hub, and Service hub.

Each of these can be paid for as individual services or as a complete suite. Such flexibility comes with its own share of inconveniences- The biggest one is that the most critical data of an organization is present in the cloud and can not be freely accessed for analytics. To circumvent this, most companies using Hubspot set up their own database systems and pulls data from Hubspot APIs. This allows them to combine Hubspot data with their existing data and derive meaningful insights.

Understanding HubSpot

HubSpot is a well-known Cloud-based CRM and an Inbound Marketing & Sales platform. It helps companies attract visitors, acquire leads, and convert them into customers. HubSpot is capable of doing this by providing its users with a variety of functionalities that allow Marketing & Sales teams at any organization to manage their key activities such as Content Creation, Social Media Sharing, Lead Capture, Workflow Management, Sales Pipeline Mapping, Performance Tracking, etc. all through a Single Platform. 

Hence, it allows businesses to be better equipped in managing Marketing & Sales activities efficiently and make a methodical plan on how they can effortlessly nurture leads through a buyer’s journey. In simple terms, it can be said that HubSpot is a software platform designed to help businesses market and sell more effectively.

Understanding MySQL

MySQL is considered to be one of the most popular Open-Source Relational Database Management Systems (RDBMS). MySQL implements a simple Client-Server Model that helps its users manage Relational Databases i.e. data stored in the form of rows and columns across tables. It uses the well-known query language, Structured Query Language (SQL) that allows users to perform all required CRUD (Create, Read, Update, Delete) operations.

Methods to Move Data from HubSpot to MySQL

Method 1: HubSpot to MySQL: Using Hubspot APIs

Hubspot allows access to its APIs through two authentication mechanisms – OAuth protocol and using a developer key-based authentication. The developer key-based authentication is simpler to implement and we will be using it in this tutorial.

Hubspot APIs are rate-limited. The rate specifications are provided through response headers. The headers contain information about the maximum number of requests and the time window for which the request count is being throttled. The APIs are also paginated with a limit of 100 entries per request. The API provides a property called vid-offset which the application needs to track and use along with the next request to retrieve the following data.

For this tutorial of moving data from HubSpot to MySQL, we will be using the contacts API from Hubspot. This API provides a list of all the customers added to Hubspot. The following steps will help you to move data from Hubspot to MySQL.

Step 1: The first step here to generate the developer token. For this, go to Settings > Integrations > Generate API keys. 

Step 2: Assuming you have the API keys, use the below curl command to fetch data from Contacts API.

curl -o contacts_data.json https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=<api_key>&count=10

As mentioned above the API is paginated. The API will return the number of results specified by the count parameter subject to a maximum of 100. If there are more than 100 results, the application will need to call the API again with the next vid-offset value. This key is present in every response. 

Step 3: The JSON response received contains a lot of details. We will try to extract a few relevant keys from this JSON – first name, last name, company name, and the identifier. Our aim is to convert this into a CSV for loading to MySQL.

cat contacts_response.json | jq -r ".contacts[] | [.vid,.properties.firstname.value,.properties.company.value,.properties.lastname.value] | @csv" >> contacts_data.csv

The generated CSV file will look as below.

9333497,moody,hevo ltd,tom 9333498,hopkins,mercury.com,richard 9333495,user2_name,,user3 9333494,jackson,extel-mario ltd,ganga 9333493,swetlana,telma-extel inc,jackson

Step 4: Create a table in MySQL to add the data

CREATE TABLE contacts (   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,   hub_id BIGINT,   first_name VARCHAR(50),   last_name VARCHAR(50),   company  VARCHAR(50) )

Step 5: Login into MySQL instance and use the below command to load data.

LOAD DATA LOCAL INFILE ‘contacts_data.csv’  INTO TABLE contacts  FIELDS TERMINATED BY ','  LINES TERMINATED BY 'rn'  (hub_id, first_name, company, last_name );

That loads data into the MySQL table. An alternative approach is to store the JSON as such into the MySQL document store. This choice will depend on the use case and the compatibility of a document type storage for the user’s requirement.

The above set of simple steps will help in implementing a simple application using the HubSpot API. But when this has to be done for a production database in a real-life scenario, there are a number of challenges the developer will face. Let’s have a look into the more complicated aspects of implementing this. 

A solution to escape all these challenges is to use a cloud-based ETL tool like Hevo. Hevo can automate the complete process of his migration in a matter of few clicks.

Limitations of Building Custom Code to Move Data from HubSpot to MySQL

1. The application needs to have appropriate logic in place to handle the rate limits of HubSpot. For this, the response headers have to be read and the request per second has to be adjusted continuously. 
2. Another challenge is the paginated nature of API and the related logic needed to keep track of vid-offset and form requests dynamically.
3. A separate hardware instance to stage the downloaded data before inserting it into the database is required in this approach. This may not be viable in an entirely cloud-based environment.
4. The developer will have a learning curve understanding the convoluted Hubspot JSON structure and writing the parsing logic to extract the relevant columns. 
5. In reality, this process is executed as part of a continuous real-time synchronizing process so that at any point in time, MySQL has a recent copy of HubSpot data. This also means the updates and deletes will have to be properly handled.

Method 2: Using Hevo Data to Move Data from HubSpot to MySQL

Hevo provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Hevo focuses on two simple steps to move your data from HubSpot to MySQL:

• Configure Source: Connect Hevo Data with HubSpot by providing a unique name for your Pipeline, along with details about your authorized HubSpot account. You can also choose the historical sync duration for your HubSpot data.

• Integrate Data: Complete HubSpot MySQL migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.

Check Out What Makes Hevo Amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Find out how integrating HubSpot with BigQuery can streamline your data management and analytics.

Conclusion

In this blog, you have learned about HubSpot and MySQL. You now know how to move your data from HubSpot to MySQL using 2 simple methods.

The conventional method needs you to have the technical expertise to configure the HubSpot MySQL Integration. But with Hevo you don’t need these confusing configurations.

What is your preferred approach to move data? Do let us know in the comments section below. 

FAQs to sync data from Hubspot to MySQL