The Spray Nozzle People are the leading distributors of standard and custom-designed spraying solutions. Customers include the world’s largest engineering, food processing, chemical, and petrochemical companies. With over 40,000 nozzles in its range, the company also provides innovative custom solutions, including using non-standard materials.

The Order System Situation

The Spray Nozzle People (SNP) started using HubSpot in early 2022 when Plus Your Business (PYB) helped them onboard. The company has successfully used HubSpot for Sales, Customer Service, and Marketing. Over the last year, the company has expressed its wish to integrate its ordering system - OrderWise (ERP) with HubSpot.

Without this integration, SNP would continue to experience a confusing and cumbersome ordering process, affecting the overall business. The main requirement from SNP was for “Orders” or Deals to move from the ordering system, OrderWise, to HubSpot at a set stage in the order process.

The Quick And Effective Solution

Because PYB had helped SNP onboard to HubSpot, PYB already had a deep knowledge of the company’s needs. This background knowledge helped to create a quick and effective solution that matched SNP’s needs. The solution considered existing integrations and how new integrations would work with them.

SNP needed a speedy solution that they could scale in the future. Given that there were no restrictions on the OrderWise side, such as IP limitations. ‘Make’, as an iPaaS, proved to be an ideal choice for building integration of the OrderWise API with HubSpot.

The Rollout Plan

After an initial meeting with SNP, PYB developed an implementation plan that followed a three-phase approach. This plan consisted of planning, implementation, and monitoring phases. This approach was communicated to SNP by PYB at the beginning of the project to ensure clarity and transparency.

To simplify communications, PYB avoided creating numerous small tasks in the plan. Instead, SNP was included in conversations between PYB and the ERP support team and updated during weekly meetings. A Gantt timeline (in the table below) provided a single point of reference for updates throughout the project.

Three Distinct Phases For Project Efficiency

1st Phase

During the first phase, PYB concentrated on collecting all the required data for the project. This data included obtaining API documentation, accessing the ERP environment, and communicating with the API support team to determine the necessary endpoints to achieve the goals.

PYB also worked on HubSpot data modelling (PoC) and custom export definitions from OrderWise. These were additional elements to what was in the API documentation. This phase was a significant milestone for the project. It ensured PYB had all the necessary inputs for the second phase.

2nd Phase

After completing phase one, the next step was creating a flow to deliver the solution. PYB used the data from phase one to generate the desired output for SNP. PYB then developed the solution on an iPaaS account (staging). This solution then went through a review with client sales reps and testers.

After a few sessions with SNP and some adjustments, PYB deployed the solution. The aim was to have the latest version perfectly customised on the HubSpot side. This process would help unlock the full potential of the CRM system. Finally, there was a migration from the staging environment to the client's live instances.

3rd Phase

Log monitoring for the iPaaS and HubSpot sides was part of PYB’s post-rollout maintenance. This area is accessible through PrivateApps. While ‘push notifications’ on the iPaaS side minimise the need for daily log checks. The checks are an integral part of the maintenance plan to monitor logs on the HubSpot side.

Managing Orders In HubSpot and OrderWise

The solution was to create or update deals in HubSpot based on Orders and closed Orders made in OrderWise. The process would work as follows:

• The sales reps from SNP would create the initial Deals in HubSpot. These would then go into the “Enquiry” stage.
• After the sales process, the sales reps could update the Deal with the Order Number and create new orders in OrderWise.
• Additionally, because the sales reps would put the new orders in OrderWise, the integration would create new Deals in HubSpot.

The solution saves SNP time from micromanaging entities and objects in HubSpot. The company could rely on data received from OrderWise instead. Deals in HubSpot would update with the requested data from OrderWise. Then HubSpot would act as a “listener” after this point.

The image below shows you the data flow for the OrderWise to HubSpot integration. It outlines OrderWise’s API, the iPaaS solution (Make.com), and HubSpot.

Breaking Down The Solution

There were ten main areas worked on during the solution. Here are the main points and the logic used for each area.

1. Retrieve Token from OrderWise

After the flow initialisation, there had to be a provision of AccessToken to use OrderWise API. PYB used Basic Authentication and GET request to their API on the  endpoint: https://xx.xx.x.xx/OWAPI/token/gettoken, using the username and password from a user that has granted the API access under the OrderWise UI.

2. Logic checker

A simple flow check helped to show if the ‘AccessToken’ variable was ‘empty’.

3. Custom DB: Get the last Cron Job timestamp

Inside the iPaaS solution (Make), PYB made a database which contained only two values:

1. Key
2. LastCronTime

In this step, the last timestamp was taken from the database and saved into a variable for further calls. 

4. List all orders created or modified after ‘LastCronTime’

In this step, PYB used endpoint https://xx.xx.x.xx/owapi/system/export-definition/20. This area was a custom-created Export definition written in SQL and created in OrderWise UI. Number ‘20’ describes the Export definition ID necessary to ping in the request.

It was a GET request, with Bearer Authorization, with content type ‘application/json’ in headers. The ‘LastCronTime’ was included in the request's body to define which Orders to fetch from OrderWise and then migrated to HubSpot.

Body example:

[
 {

   "name": "@lastexported",

   "value": ''

 }

]


In this step, PYB parsed the responses. Here’s a response example: 

[
            {
                "Order Status": "Completed",
                "Order Number": "Test49071",
                "Customer Name": "Custom Name",
                "Order Reference": "REN008996",
                "Order Type": "No type",
                "Input On Date": "2023-02-20T10:56:52.37",
                "Order Date": "2023-02-20T10:56:52.02",
                "Promised date": "2023-03-01T23:59:59",
                "Consignment Number": "123456789",
                "Delivery Method": "Next Day",
                "Date": "2023-02-22T13:59:31.16",
                "Account Manager": "Brett",
                "Total Amount": 2053.2,
                "Invoice Email Address": "email@domain.com"
            }

5. Update DB with the current timestamp

PYB updated the single row in the database with the current timestamp, which updates ‘LastCronTime’.

6. Parsing domain from variable ‘Invoice Email Address’

If the ‘Invoice Email Address’ was not empty, the system took the domain name from the email address for the domain name. This area would later be used in the HubSpot Search API when searching for a Company to associate with the Deal (Order).

7. JSON for ‘/crm/v3/objects/deals’ request

The next step was to create a JSON that would be included in the steps when the system put a new standard object in HubSpot (Deal).

Example: 

[
    {
        "amount": 2053.2,
        "dealname": "J G Pears Power (O&M) Limited - Test49072",
        "pipeline": null,
        "dealstage": null,
        "order_date": "2023-02-20T10:58:27.063Z",
        "order_type": "No type",
        "order_status": "Awaiting despatch",
        "customer_name": "J G Pears Power (O&M) Limited",
        "input_on_date": "2023-02-20T10:58:27.177Z",
        "promised_date": "2023-03-01T23:59:59.000Z",
        "account_manager": "Brett",
        "date__shipping_": null,
        "delivery_method": null,
        "order_reference": "REN008996",
        "ow_order_number": "Test49072",
        "ow_created_updated": true,
        "invoice_email_address": "adamdrewery@jgpears.com",
        "consignment_number__for_the_courier_": null
    }
]

It is important to note that the Pipeline and Deal Stage values were left empty with good reason. Because the integration uses Search API to search for existing deals with ‘ow_order_number’, it mainly updates deals in HubSpot.

There are three fields for OrderWise Order Number inside HubSpot CRM because Sales Reps can create more than one Order per Quote. This process ensures no duplicate Deals/Orders are made (i.e. in Hubspot). The ID which we use for searching the Deals is OrderWise Order Number.

For any cases where Order received from OW endpoint is not found in Hubspot. There is a logic inside Hubspot where Workflows can help route the Deals into one of two Pipelines:

1. Industrial Sales Pipeline
2. ProSprayers Sales Pipeline

8. Search Deals by Order Numbers

As described above, in HubSpot, we have three OW Order Number fields:

Endpoint used: /crm/v3/objects/deals/search

A PrivateApp inside HubSpot, previously made, has the following scopes:

When calling the Search endpoint, the Authorization Bearer is added in the header and ‘application/json’ as Content Type.

Body Example:

{
  "filterGroups": [
    {
      "filters": [
        {
          "value": "",
          "propertyName": "ow_order_number_2",
          "operator": "EQ"
        }
      ]
    }
  ],
  "sorts": [
    "hs_object_id"
  ],
  "properties": [
    "hs_object_id"
  ],
  "limit": 100,
  "after": 0
}

9. If at least one ‘hs_object_id’ is known

This branch was for cases when Search API had found a Deal in Hubspot matching at least one OrderWise Order Number (from up to 4 available). In this case, PYB took the JSON created previously and used the endpoint ‘/crm/v3/objects/deals/{dealId}’ to update the deal by Deal ID.

10. If no one ‘hs_object_id’ is known

This branch was for cases when the Search API did not find a Deal in Hubspot and matched at least one OrderWise Order Number (from up to 4 available). In these cases, PYB took the previously created JSON and used the endpoint ‘/crm/v3/objects/deals’ to make a new Deal.

There was also an additional step for newly created Deals, i.e., the system needed to associate between a Deal object and a Company object.  Calling the Search API, there was a search for a Company based on parsed domains from the beginning of the process.

The Endpoint used here was: /crm/v3/objects/company/search

Body request:

{
  "filterGroups": [
    {
      "filters": [
        {
          "value": "jgpears.com",
          "propertyName": "domain",
          "operator": "CONTAINS_TOKEN"
        }
      ]
    }
  ],
  "sorts": [
    "hs_object_id"
  ],
  "properties": [
    "hs_object_id"
  ],
  "limit": 1,
  "after": 0
}

When calling the Search and Association endpoint, PYB put the Authorization Bearer in the header and ‘application/json’ as Content Type.

For association, this endpoint was used: /crm/v4/objects/{objectType}/{objectId}/associations/{toObjectType}/{toObjectId}

An Efficient Logging Process For Errors

One of the reasons PYB chose ‘Make’ as an iPaaS solution was because of its built-in logging and error logging process. This solution meant PYB did not have to spend more time developing the features. You can see an example of the logging process in the image below.

Also, to ensure control for bad requests on the HubSpot side. PYB reviewed the PrivateApps logs at regular intervals. During the testing phase, SNP verified the integration flow to see if it worked for the company. This information allowed PYB to optimise the initial integration version with further updates.

To prevent potential errors, PYB also used logical checks and filters to identify and address issues. PYB built a custom database within the integration flow to store these errors. This data will allow SNP to optimise the flow in the future even further if the company desires so.

The Game-Changing Results For SNP

There were no native solutions available for OrderWise integration. Also, custom-built middleware solutions were not an option for the integration due to the potential cost implications for SNP. The PYB solution was the best approach for SNP to integrate OrderWise into HubSpot.

PYB was able to provide a solution that matched the exact requirements of the company. The implementation was a testament to HubSpot’s value when companies integrate as many areas as possible. Now the ordering process at SNP is more streamlined and works with other departments seamlessly.

Because PYB has a long-term collaboration plan with SNP, it also includes managing any upgrades for the integration. This continuity of support ensures clients have assistance during and after the implementation. It gives SNP and other clients the confidence to continue expanding their use of HubSpot.