How to view HubSpot engagement information in an APEX application

This is a relatively short blog to remind everyone how easy it is to configure and utilise RESTful web services provided by other (SaaS) systems. They allow you to get the data from them into your Oracle Database, then utilise the power of the out-of-the-box reporting tools in APEX to add value and produce reports with information that it may not be possible to generate in the original solution.

We were recently engaged by an organisation whose primary CRM solution is HubSpot. HubSpot is a very powerful marketing software which can monitor and track engagement and sales with clients and customers.

The organisation already used Oracle Cloud and had an Oracle Autonomous Database (APEX Development service workload type). Therefore, the infrastructure was already in place to host the application and managing the environment would be quick and easy going forward. They had started to build APEX applications to quickly replace existing spreadsheets and other processes at the organisation.

They also needed to create a report in HubSpot which told them how often they’d engaged with different companies over X timeframe, broken down by engagement type. They had already tried building this report directly in HubSpot but it hadn’t quite met their expectations.

HubSpot has an API (see here for a link to the documentation for the one utilised). Note: At the time of writing HubSpot’s APIs are in the midst of transition from v2 to v3. For this reason and because it remains stable and fully supported, and in order to complete the project as quickly as possible, we utilised v2 of the API. The team also have experience of a previous integration on v2. Also, for this initial exercise and Proof-of-Concept we utilised an API Key for authentication. For security it is better to use OAuth/OAuth2.

As with any integration with a web service to retrieve data, we’ve found that the best place to start is by using a standard REST client to become familiar with the calls and to confirm that it is working as we expect it to. The best option for this is Postman which allows you to save and build collections of the different calls and test them:

As mentioned at the beginning, the information we were trying to gather was ‘number of engagements per organisation’. To find this information using this API requires the following calls:

• Get All Companies (To get the full list of companies). Note: to get additional properties returned you need to add ‘&properties=*property name*’ query params.
• Get Associations for a CRM object (To get all the associated engagement IDs for a company) Note: the ObjectID required is 7 for the ‘company to engagement’ join.
• Get an Engagement (To get the details of the engagement, e.g. it’s type)

There used to be a Get Associated Engagements API which returned the details of all engagements for an organisation. This would have meant just two API calls, however this endpoint has been deprecated.

Once you have established and tested the calls you need to retrieve the data. To do this the next decision is how you are going to get this data in your APEX application in order to then report on it. You have two options:

• Connect and configure them declaratively in the APEX builder using REST Data Sources (formerly called Web Source Modules).
• Write a PL/SQL package which utilises calls to APEX_WEB_SERVICE to retrieve the data and then use the APEX_JSON API to parse the results.

Which approach you take will depend mainly on factors such as which version of APEX you are using, how nested the data is within the API calls (i.e. how many layers you will have to go through), potential API call limits, where your previous experience/preference lies and the type of approach you want to take i.e. dynamic, up to date information or a more synchronised approach. On that last point of synchronisation, up until recently if you wanted the data synchronised to local tables, you would have been required to go down the PL/SQL route. However, synchronisation to local tables is now possible when configured as REST Data Sources. See here for more information.

With the three nested calls needed to retrieve the information required, wanting to reduce the number of API calls, prior experience and the information didn’t need to be up to date by the second. We decided in this instance to implement a PL/SQL package which would be called on a DB schedule once a day to refresh/update the information on which to report.

The calls to the different web services using APEX_WEB_SERVICE were implemented in a package (you can click on the file below for an example of calling Get All Companies, parsing the results and merging in to a local table). The procedure also deals with the manner of pagination of the results which HubSpot utilises. The only things you would have to add is to declare the base URL as g_base_url in the package specification, and either supply your API key to the procedure or update it to use OAUTH2.

PROCEDURE get_companies(p_api_key IN VARCHAR2) IS

lc_data CLOB;
lj_rows apex_json.t_values;
ln_row_count PLS_INTEGER; 
lv_has_more VARCHAR2(5) := 'false';
lv_offset NUMBER;

ln_company_id company.id%type;
lv_is_deleted company.deleted%type;
lv_company_name company.company_name%type;
lv_company_type company.company_type%type;
lv_relationship company.relationship%type;
lv_website company.website%type;
lv_finance_ref company.finance_ref%type; 

BEGIN

LOOP

if lv_has_more = 'false' then
lc_data := apex_web_service.make_rest_request(p_url => g_base_url || '/companies/v2/companies/paged?hapikey=' || p_api_key || '&properties=name&properties=organisation_type&properties= relationship&properties=website&properties=finance_ ref&limit=100',
p_http_method => 'GET');
else 
lc_data := apex_web_service.make_rest_request(p_url => g_base_url || '/companies/v2/companies/paged?hapikey=' || p_api_key || '&properties=name&properties=organisation_type&properties= relationship&properties=website&properties=finance_ ref&limit=100&offset=' || lv_offset,
p_http_method => 'GET');
end if;

SELECT jt."has-more", jt.offset
INTO lv_has_more, lv_offset
FROM JSON_TABLE(
TREAT ( 
lc_data AS JSON) , '$[*]' columns (
"has-more" VARCHAR2(5),
offset NUMBER
)
) jt;

apex_json.parse(lj_rows,
lc_data);

ln_row_count := apex_json.get_count(p_path => 'companies',
p_values => lj_rows);

IF ln_row_count > 0 THEN

FOR x IN 1 .. ln_row_count
LOOP
ln_company_id := apex_json.get_varchar2(p_path => 'companies[%d].companyId',
p0 => x,
p_values => lj_rows);
lv_is_deleted := apex_json.get_varchar2(p_path => 'companies[%d].isDeleted',
p0 => x,
p_values => lj_rows);
lv_company_name := apex_json.get_varchar2(p_path => 'companies[%d].properties.name.value',
p0 => x,
p_values => lj_rows);
lv_company_type := apex_json.get_varchar2(p_path => 'companies[%d].properties.organisation_type.value',
p0 => x,
p_values => lj_rows);
lv_relationship := apex_json.get_varchar2(p_path => 'companies[%d].properties.relationship.value',
p0 => x,
p_values => lj_rows);
lv_website := apex_json.get_varchar2(p_path => 'companies[%d].properties.website.value',
p0 => x,
p_values => lj_rows);
lv_finance_ref := apex_json.get_varchar2(p_path => 'companies[%d].properties.finance_ref.value',
p0 => x,
p_values => lj_rows);

IF ln_company_id IS NOT NULL THEN
MERGE INTO company h
USING (SELECT ln_company_id company_id,
lv_is_deleted deleted,
lv_company_name company_name,
lv_company_type company_type,
lv_relationship relationship,
lv_website website,
lv_finance_ref finance_ref
FROM dual) d
ON (h.id = d.company_id)
WHEN MATCHED THEN
UPDATE
SET h.company_name = d.company_name,
h.company_type = d.company_type,
h.relationship = d.relationship,
h.website = d.website,
h.deleted = d.deleted,
h.finance_ref = d.finance_ref
WHEN NOT MATCHED THEN
INSERT
(id,
company_name,
company_type,
relationship,
website,
finance_ref,
deleted)
VALUES
(d.company_id,
d.company_name,
d.company_type,
d.relationship,
d.website,
d.finance_ref,
d.deleted);
END IF;

END LOOP;

END IF;

EXIT WHEN lv_has_more = 'false';
END LOOP;

COMMIT;

END get_companies;

Two very similar procedures were then built to call Get Associations for a CRM object for every company we had already stored, to create our many-to-many join table. Then Get an Engagement was called for each engagement for which we did not already have the information and then inserted into an engagement table.

The final step to get the report was to create an Interactive Report over the data and then utilise a Pivot function to format the data as required. We also added a couple of filters to the report which when changed, utilise a dynamic action to refresh the report for a particular time frame.

And that’s it, we had our report which showed the required information.

Summary

With the recent enhancements to the declarative ways of integrating with APIs such as Synchronisation, the ability to Synchronize Parent-Child REST Sources and also APEX Automations, we’re almost at the cross over phase between changing from utilising PL/SQL packages running on a schedule and doing everything for integrations more declaratively. Providing it’s feasible for the next integration we work on, we’ll be sure to use these and report back on any findings. Get integrating!