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 company	Details	Field 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 Information	Field type on HubSpot
StreetAddress	Single-line text
City	Single-line text
ZIP	Single-line text
Country	Dropdown select

ERP Information

HubSpot Property Name	Details	Field 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.