e-Zest members share technology ideas to foster digital transformation.

Integrating Hubspot and Custom ERP using SiddhiQL

Written by Pranav Shah | Nov 28, 2018 2:01:53 PM
What is Hubspot?

Hubspot is a CRM tool for use by marketing and sales departments, which track and manage interactions between a company and its customers. It also has other product to take care of content management, social media marketing, web analytics, and landing pages development. It is an efficient tool which can also integrate with Gmail and Microsoft Office. It is a cloud-based product which provides SaaS (Software as a Service). It can be considered as an entire marketing solution.

Image Courtesy: www.Hubspot.com


What is a custom ERP?

Custom ERP is a project management tool which helps its users to plan, organize and manage resources. Across management levels from the CEO to team members down the chain, custom ERP is used to consume and enter the relevant information.

Custom ERP need a backend database. It can be MS-SQL or MySQL or any other RDBMS. This database can be connected to Kafka - which is based on a publisher-subscriber model and can be used as a Kafka connector to custom ERP database. The integration of these two tools can be done by stream processing.

Here comes in picture stream processing studio – SiddhiQL.

What is SiddhiQL?

SiddhiQL is a complex event processor designed to identify complex events in the stream, processes the complex events and notify them in real time. SiddhiQL can have input stream events right from HTTP, Kafka, TCP, in-memory, WSO2Event, e-mail, JMS, file, RabbitMQ, MQTT, WebSocket, Twitter and Amazon SQS. Basically, SiddhiQL can accept event inputs from many different types of sources, process it to generate insights and publish them to many types of a sink.

In our case, Kafka source of SiddhiQL is used to identify the complex stream coming from Kafka connector connected to custom ERP database. The stream contains the data in the payload that could be of created through the new project, milestones – which can be achieved in a project or any other functionality in which custom ERP works. SiddhiQL identifies the required events from Kafka source and it will send that particular event from Kafka stream to Hubspot.

Sample SiddhiQL snippet for identifying an event from Kafka is as follows:

@App:name("HelloKafka")
@source(type='kafka', topic.list='kafka_topic', partition.no.list='0',
threading.option='single.thread', group.id="group", bootstrap.servers='localhost:9092', @map(type='json'))
define stream SweetProductionStream (projectCode string, projectName string, totalBid int, expectedDuration int, expectedStartDate string, expectedEndDate string, over bool);

HubAPI:

Hubspot supports API through which deals, companies, contact can be created, updated and deleted. Various APIs can be called to Hubspot; basically, in two ways – using unique Hapikey in URL of API call and OAuth 2.0. In our case, we will be using Hapikey of Hubspot account.

Some of the APIs include:


Workflow:

In a custom ERP, the project milestones can be created through simple steps. Similarly, milestones can be created as a custom deal property in Hubspot which offers flexibility to create custom properties as we need. As ERP has backend database in some RDBMS, it provides different views from which project tables are created. One such view is Milestone view which contains fields like milestone name, project name, milestone amount and other required details. Kafka can be connected to this ERP RDBMS database view. This access provides the integration of source Kafka and SiddhiQL that will identify the project update stream and pass it to the extension program which eventually is intended to update deal related to that project.

Workflow for integration of custom ERP and Hubspot
As the stream is received in the extension program, it will check for current values of a milestone received in the database. The steps included for updating a milestone or creating a new milestone in Hubspot include:

  • SiddhiQL identifies events from a custom ERP
  • Get all deals APIs from which dealId is extracted
  • Using dealId call to get a deal API from which initial milestones and their values can be identified
  • Form JSON for updated milestone amount and total deal amount
  • Update the deal with payload in JSON using deal API

Sample update a deal API can be as follows:
Example URL:
https://api.hubapi.com/deals/v1/deal/19320611?Hapikey=demo

Note: Your "content-type" must be application/JSON.
Example deal update JSON:
{
"properties": [
{ "name": "amount",
"value": "70000"
}
]
}

As SiddhiQL application can be kept running, this enables changes from a custom ERP to be reflected in Hubspot in real time. Vice-versa, data from Hubspot can also be reflected in Whizible in real time. This ensures total integration of the CRM application and project management tool. The system is made to withstand various scenarios like:

  • Alert notification via email when the project is not present in Hubspot
  • Total deal amount equals to the sum of all milestone amount
  • Notifications in Hubspot when a deal is updated via API
  • Upon successful updating, the event can be replicated in another Kafka topic or any other source/sink
Thus, integration of Hubspot and custom ERP can prove to be helpful for a company using it as it maintains the integrity of both the systems.