SQL Recommended Synchronization

What information to integrate from source to destination

The next section gives guidelines on how to better synchronize data from ERP to HubSpot with some best practices. The names of the properties on both ERP and HubSpot are an example, we suggest you to use customized properties according to your specific needs.

Start by creating a new view with customers ERP data. Below is an example query to create the view with generic ERP_Field to be replaced with the specific ERP field.

USE [ERPBridge]
GO

CREATE view [dbo].[V_Companies] as 

select
	ERP_Code as ERPCode
	,ERP_Field as CompanyName
	,ERP_Field as StreetAddress
	,ERP_Field as City
	,ERP_Field as ZIP
	,ERP_Field as Country
	,ERP_Field as Vat
	,ERP_Field as FiscalCode
	,ERP_Field as SalesAgent
	,ERP_Field as Email
	,ERP_Field as CompanyDomain
	,ERP_Field as PEC
	,ERP_Field as Phone
	,ERP_Field as PriceList
	,ERP_Field as Discount
	,ERP_Field as PaymentTerms
	,ERP_Field as CustomerCategory
	,ERP_Field as LastOrder
	,ERP_Field as OrderedCurrentYear
	,ERP_Field as Ordered_year_1
	,ERP_Field as Ordered_year_2
	,ERP_Field as RevenueCurrentYear
	,ERP_Field as Revenue_year_1
	,ERP_Field as Revenue_year_2
	,ERP_Field as Unpaid
	,GETDATE() as LastSync
	
from
	ERPCustomerTable

where
	LastModifiedDate=GETDATE()	
	-- for incremental synchronization once/twice a day

GO

The result is the creation of a SQL table that collects data from the query.

General Information

About this companyDetailsField type on HubSpot

ERPCode

Custom field created to match HubSpot and ERP records, captures the ID code of the record from the ERP

Single-line text

CompanyName

Single-line text

CompanyDomain

Single-line text

VAT / Partita IVA

Single-line text

FiscalCode

Single-line text

SalesAgent

The reference agent for that customer, useful to associate a company owner via Workflow

Single-line text

Email

Single-line text

PEC

Single-line text

Phone

Phone number

Geographical Information

Geographical InformationField type on HubSpot

StreetAddress

Single-line text

City

Single-line text

ZIP

Single-line text

Country

Dropdown select

ERP Information

HubSpot Property NameDetailsField type on HubSpot

PriceList

The price list information we want to transfer to HubSpot

Dropdown select

Discount

Dropdown select

PaymentsTerms

Dropdown select

CustomerCategory

Useful for profiling the company as the ERP items

Dropdown select

LastOrder

To be used as trigger in Workflows to notify Sales Agent about lost customers

Date picker

OrderedCurrentYear

Total money raised in the current year - Still to be paid

Number - Currency

Ordered_year_1

Total money raised last year - Could be used to compare the orders amount in different years

Number - Currency

Ordered_year_2

Total money raised two years ago - Could be used to compare the orders amount in different years

Number - Currency

RevenueCurrentYear

Actual Client Revenue

Number - Currency

Revenue_year_1

Total Revenue last year - Could be used to compare the revenue amount in different years

Number - Currency

Revenue_year_2

Total Revenue two years ago - Could be used to compare the revenue amount in different years

Number - Currency

Unpaid

Amount not paid by the customer resulting from the due date of a payment associated with an invoice - The data can inform about the amount not paid by the customer and can block new orders or activate the credit recovery process

Number - Currency

LastSync

For keeping track of the last sync made with the ERP

Date picker

To optimize the integration, prepare a custom view in SQL with the data you want to integrate on HubSpot Companies. To this view, you can add or remove fields at any time, perform format conversions (casts) and correctly manage the entries of the properties checkboxes, multiple checkboxes, dropdown select, radio select and HubSpot user.

When creating the properties on HubSpot that will be used in ERP Bridge, carefully consider the type of data you are bringing to the CRM. If the options are few, select a choosing options property type, if the options are many and updated frequently, select a single-line text.

For property type choosing options is essential to understand how often the property will be updated, because any unalignment between ERP fields and HubSpot will result in an error. If the property updates on a regular basis the advice is to select single-line text.

Last updated