How to Sync Data via SFTP from Core Banking to HubSpot

Eric Pratt

May 1, 2024 17 min read

If your bank or credit union is considering HubSpot for its powerful sales and marketing capabilities you're probably trying to figure out how to load critical data from your Core banking software. This article provides a step-by-step guide to setting up an SFTP connection for an automated data sync from Core to HubSpot.

As experts in implementing HubSpot for Financial Institutions, Instrumental Group is adept at implementing a variety of solutions to help with this. While API integrations and more manual processes are also viable options, this article looks at the SFTP server solution specifically.

Why an SFTP connection is a great solution

In the digital world we live in, safeguarding sensitive data during transfer is crucial. With cyber threats on the rise, it's essential to have reliable file transfer solutions. Among these, SFTP (SSH File Transfer Protocol) stands out as the top choice for secure and seamless transfers between local and remote systems.

Seven reasons why SFTP is an excellent solution

Secure Transmission: SFTP utilizes SSH encryption, ensuring that data remains protected during transfer.
Timely Data Updates: Automates the frequency of sync to as fast as once per hour, ensuring users have accurate account and product data.
Robust Authentication: SFTP employs SSH keys for authentication, thwarting unauthorized access attempts.
Data Integrity: Through encryption, SFTP maintains the integrity and confidentiality of data, fortifying defenses against cyber threats.
Setup Involvement: Establishing an SFTP connection requires client software, an SFTP server, firewall configuration, and SSH key generation.
Reverse ETL Functionality: SFTP plays a crucial role in Reverse ETL, securely transmitting data from warehouses to operational systems, and facilitating data-driven decision-making.
Cost Effectiveness: An SFTP connection can be 25%-50% of the cost of direct API integration, without the ongoing maintenance requirements.

Core banking SFTP connection example

To help you understand the process and prepare the proper internal and external resources I'll provide examples of the steps and technical requirements.

Step 1 - Discovery & Planning

We'll need to work together to understand the data structure for your institution and build the most compatible structure within the limitations of your Core banking software and HubSpot. We recommend a 3-call series to define the data sync requirements and architecture strategy.

Kickoff call: Introductions to stakeholders, technology access confirmation, expectation setting, business case review, and quick reverse demo of your Core banking data structure.
Field mapping call: Confirm different field types and options for what data points can sync into HubSpot while ensuring your team understands their role in curating the mapping list.
Review call: Confirm the integration plan and confirm your team has a viable plan for generating and placing CSV files onto the SFTP server. This is detailed further below.

Step 2 - Requirements Mapping

As requirements come together through discovery, we prepare a sync workflow diagram of the proposed system to ensure the proper configuration in an easy-to-understand visual representation of the business requirements.

We define the required HubSpot configuration necessary to receive the data accurately. Core data generally lives at the Contact record in HubSpot, although Company record can also be leveraged for Commercial banking.

Sync Mapping Overview: Visual diagram that outlines how Core banking source fields/objects will be mapped to HubSpot's properties/objects.

Step 3 - HubSpot Field Configuration

Once all data is mapped we'll get to work configuring HubSpot to accurately reflect mapping requirements. This is the technical exercise of mapping all required data points from Core banking fields to new fields inside HubSpot. To accomplish the sync it's critical to establish a clean 1-1 field relationship, where every data point exists in HubSpot. Without the field configuration, your sync will produce countless errors.

Generally includes a combination of HubSpot fields and around 50 to 60 custom fields using standard HubSpot field types.
Depending on your preferences for organizing your banking product data we may include a couple of HubSpot custom objects. Custom objects allow for the convenient grouping of complex data sets, creating a more intuitive experience for your users inside HubSpot.

Step 4 - SFTP Data Sync via CSV

Now it's configuration time. We typically build an SFTP-based data sync solution using one of the software solutions below to import CSV files into HubSpot. The data sync aims to address urgent needs for data synchronization, ensuring quick and efficient transfer of information between an SFTP source server with a CSV file and HubSpot. The diagram below illustrates the data flow as well as two frequently used software solutions, Skyvia and Zapier.

Step 5 - Core to CSV upload

We'll need to get the data from Core into CSV, and from CSV into the SFTP server for import. Your team will develop a simple process to generate CSV files from Core and upload them to the SFTP server mentioned above. By performing this step internally you'll avoid the security risk of giving our team direct access to your Core banking software.

While this process can be done manually, our suggestion is to automate it for a daily upload. Your internal development resource can automate the process easily and we can assist where needed. Again, this should all be finalized during requirements mapping to ensure roles and expectations are clear. The left side of the graphic above illustrates the posting of CSV from Core to FTP.

Step 6 - Testing & Quality Assurance

Testing and verification start once the data sync is complete. Testing should not be overlooked. With the volume and types of data points you're planning to send into HubSpot accuracy and security are paramount. We suggest running quality assurance on the data and working with your technical resources to ensure accuracy, train them how to use and access rebuilt data and make sure no critical data is missing.

Step 7 - Activate & Communicate

The SFTP connection should be activated once confirmed and ready. Now the fun part is possible, using the data to market to your prospects and customers. Whether you're looking to acquire new customers, grow deposits, expand wallet share with cross-selling of other products, or just retain your best relationships. That's all the good stuff we can do in HubSpot once we get the right data at our fingertips.

SFTP server options to consider

To pull off this project, we'll need a proper file server with SFTP access credentials for the integration solution to use. SFTP server hosting options are:

You can self-host and be responsible for creating and maintaining the server, structure, and credentials
Instrumental group creates and maintains the SFTP server for a monthly fee
You can take on the challenge of sourcing a service and maintaining a license with the SFTP server of your choosing.

Your IT may already have a great SFTP server solution in place. If not, you'll need to source one and have it set up. An option we recommend is SFTPCloud. There are other solutions if this doesn't work for your team.

SFTP software options to consider

While there are many options available we recommend considering Skyvia and Zapier first. The amount of data and sync frequency should lead your decision within the discovery and mapping process. Here are some reference notes on each.

Skyvia: $79/mo. You will be required to create and retain a Skyvia Standard data integration license.

50 items are allowed in 1 sync per hour.
- Items are anything that can be mapped including objects, activities, associations and etc.
Up to 500k records synced per month (can purchase more)
Custom property mapping
Association mapping
Activity logging
Custom Object mapping
One directional CSV to HubSpot
Integration Resource: https://skyvia.com/data-integration/integrate-hubspot-sftp

Zapier: $49/mo. You will be required to create and retain a Zapier Professional license.

Includes Zap filters and formatting for CSV parsing
Up to 2k task runs per month
For a single object sync, we will use a minimum of 3 tasks per sync.
Multiple syncs per day
Custom property mapping
Association mapping
Activity logging
Custom Object mapping
Multi-directional
CSV compatibility with Zapier’s CSV import utility
- Limited to files of 150KB or less in size and
- Files of no more than 1000 rows by 10 columns.
Requires free EasyFTP easyftp license. Allows for monitoring of up to 20 files using FTP/SFTP with Zapier. EasyFTP looks for changes or updates to files on the SFTP server. If more than 20 files need to be monitored there may be an added cost for the EasyFTP license.
Integration Resource: https://zapier.com/apps/easyftp/integrations/hubspot

A couple of other technologies for your IT team to research:

See how Core data is stored in HubSpot

If you're wondering what HubSpot looks like once the fields are configured, the video below might help. I walk through our HubSpot demo portal, built to empower different types of users within financial institutions. Loan Officers, Branch Managers, Marketers, and Customer Relations all with visibility into the same information. Priceless.

See HubSpot in action

If you want to see how all of this looks in a live application, check out the case study below. It includes a multi-phase HubSpot implementation with an SFTP connection to sync data from Fiserv Spectrum.