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.
The result is the creation of a SQL table that collects data from the query.
General Information
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
Single-line text
PEC
Single-line text
Phone
Phone number
Geographical Information
StreetAddress
Single-line text
City
Single-line text
ZIP
Single-line text
Country
Dropdown select
ERP Information
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