# Hubspot

## Authentication methods

#### Legacy Apps (fka. Private App)

This method uses the Access Token of a Private App to authenticate.

{% hint style="info" %}
HubSpot have renamed Private Apps to Legacy Apps, however, they are still supported and adequate for data replication use cases. Visit the [HubSpot docs](https://developers.hubspot.com/docs/apps/legacy-apps/private-apps/overview).
{% endhint %}

To generate an access token, go to:

1. **Main sidebar > Development** > **Legacy apps**
2. You need a user with **Super Admin** privileges.
3. Create a private app, give it any name, logo and description you like (a descriptive name like "Omnata Sync" is best practise)
4. Add Scopes for Omnata to access objects and read/write:
   * **CRM** section > check read and/or write for **crm.objects.custom** and **crm.schemas.custom**
   * For inbound syncs; under Standard, check **crm.export**
   * For outbound syncs; under Standard, check **crm.import**
   * Read and/or Write for each of the objects you're syncing to/from
5. Click “Commit changes”

<figure><img src="https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2FDMAI0fCtaTTJprzg474J%2Fimage.png?alt=media&#x26;token=73157e89-d070-4706-b7d6-ffa5c7c6fd89" alt=""><figcaption></figcaption></figure>

Once you've completed the creation of the private app you will receive an Access token, which is what you use in the Omnata UI to create the connection.

<figure><img src="https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2F2ZcnV1NJHx19JdwDlG7r%2Fimage.png?alt=media&#x26;token=41c9226b-d1ab-49b6-916f-870cf81fe4b8" alt=""><figcaption></figcaption></figure>

## Inbound Syncs

{% hint style="info" %}
Omnata is an alternative the HubSpot add-on product called [Operations Hub](https://knowledge.hubspot.com/reports/query-hubspot-data-in-snowflake?_ga=2.220507227.290474770.1709697406-1287515257.1706901011&_gl=1*14e6w1z*_ga*MTI4NzUxNTI1Ny4xNzA2OTAxMDEx*_ga_LXTM6CQ0XK*MTcwOTY5NzQwNi4xLjAuMTcwOTY5NzQwNi42MC4wLjA.) which uses Snowflake data-sharing. Omnata's Hubspot plugin provides inbound syncing to Snowflake via direct API integration.
{% endhint %}

### Supported Sync Strategies

* Full Refresh
* Incremental

{% hint style="info" %}
There are two types of incremental sync:

1. Incremental (standard server-side, where API returns only the data updated or generated since the last sync)
2. Client-Side Incremental (API returns all available data and connector filters out only new records)
   {% endhint %}

### Supported Streams

The following objects are supported:

* [Campaigns](https://developers.hubspot.com/docs/methods/email/get_campaign_data) (Client-Side Incremental)
* [Companies](https://developers.hubspot.com/docs/api/crm/companies) (Incremental)
* [Contact Lists](http://developers.hubspot.com/docs/methods/lists/get_lists) (Incremental)
* [Contacts](https://developers.hubspot.com/docs/methods/contacts/get_contacts) (Incremental)
* [Contacts List Memberships](https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts)
* [Deal Pipelines](https://developers.hubspot.com/docs/methods/pipelines/get_pipelines_for_object_type) (Client-Side Incremental)
* [Deals](https://developers.hubspot.com/docs/api/crm/deals) (including Contact associations) (Incremental)
  * Records that have been deleted (archived) and stored in HubSpot's recycle bin will only be kept for 90 days, see [response from HubSpot Team](https://community.hubspot.com/t5/APIs-Integrations/Archived-deals-deleted-or-different/m-p/714157)
* [Deals Archived](https://developers.hubspot.com/docs/api/crm/deals) (including Contact associations) (Incremental)
* [Email Events](https://developers.hubspot.com/docs/methods/email/get_events) (Incremental)
* [Email Subscriptions](https://developers.hubspot.com/docs/methods/email/get_subscriptions)
* [Engagements Calls](https://developers.hubspot.com/docs/api/crm/calls) (Incremental)
* [Engagements Emails](https://developers.hubspot.com/docs/api/crm/email) (Incremental)
* [Engagements Meetings](https://developers.hubspot.com/docs/api/crm/meetings) (Incremental)
* [Engagements Notes](https://developers.hubspot.com/docs/api/crm/notes) (Incremental)
* [Engagements Tasks](https://developers.hubspot.com/docs/api/crm/tasks) (Incremental)
* [Forms](https://developers.hubspot.com/docs/api/marketing/forms) (Client-Side Incremental)
* [Form Submissions](https://legacydocs.hubspot.com/docs/methods/forms/get-submissions-for-a-form) (Client-Side Incremental)
* [Goals](https://developers.hubspot.com/docs/api/crm/goals) (Incremental)
* [Line Items](https://developers.hubspot.com/docs/api/crm/line-items) (Incremental)
* [Marketing Emails](https://legacydocs.hubspot.com/docs/methods/cms_email/get-all-marketing-email-statistics)
* [Owners](https://developers.hubspot.com/docs/methods/owners/get_owners) (Client-Side Incremental)
* [Products](https://developers.hubspot.com/docs/api/crm/products) (Incremental)
* [Property History](https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts) (Incremental)
* [Subscription Changes](https://developers.hubspot.com/docs/methods/email/get_subscriptions_timeline) (Incremental)
* [Tickets](https://developers.hubspot.com/docs/api/crm/tickets) (Incremental)
* [Ticket Pipelines](https://developers.hubspot.com/docs/api/crm/pipelines) (Client-Side Incremental)
* [Workflows](https://legacydocs.hubspot.com/docs/methods/workflows/v3/get_workflows) (Client-Side Incremental)

## Outbound Syncs

### Supported Sync Strategies

* Create
* Upsert
* Update

### Supported Targets

The following objects are supported:

* Contacts
* Companies
* Deals
* Notes
* Tickets
* Products
* Line Items
* Quotes
* Custom Objects

### Associations

Hubspot Associations can be created/updated in a variety of different ways.

When linking to other objects, you can use the Hubspot-generated Record ID, or any Alternate ID defined on that object. For example, when uploading Companies, you could link to Contacts via:

* The hubspot object ID, if you know it via an inbound sync (This appears as "\<object name> Association by Record ID(s)" in the field mapper)
* The standard email field (This appears as "\<object name" Association by Alternate ID(s): \<field name>" in the field mapper)
* A custom field which is unique (via the "Require each value for this property to be unique" checkbox). (This also appears as "\<object name" Association by Alternate ID(s): \<field name>" in the field mapper)

<figure><img src="https://2119005510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FedNbhp7XNeTdK7we4Ka5%2Fuploads%2Fgit-blob-0b26aef040c1ccfe8c955c46e0916df0e2a3ef79%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

You can also choose between creating unlabelled and labelled associations.

Whenever you want to create associations, you must always use an OBJECT column. Each key and value in the object represents the target object and the label to use (or null for an unlabelled association).

In the below examples, on the association columns are shown for brevity, but they normally accompany the setting of regular field values like first name, last name, email etc.

#### Same-object associations

Same-object associations exist between objects of the same type (e.g. contacts to other contacts)

CONTACTS source table:

<table data-full-width="false"><thead><tr><th width="197">CONTACT_NUMBER</th><th width="257">OTHER_CONTACT_NUMBERS</th><th>Comment</th></tr></thead><tbody><tr><td>1</td><td><p>{</p><p>2: 'Friend',</p><p>3: 'Friend'</p><p>}</p></td><td>Contacts with CONTACT_NUMBERs of 2 and 3 get associated with the label "Friend".</td></tr><tr><td>2</td><td>null</td><td>No associations</td></tr><tr><td>3</td><td>{<br>5: null<br>}</td><td>Contact with CONTACT_NUMBER 5 gets an unlabelled association</td></tr></tbody></table>

Here, the contacts are being identified via a unique numeric field.

You can mix and match the way you identify them:

<table><thead><tr><th width="127">Contact ID</th><th width="183">EMAIL</th><th width="298">OTHER_CONTACT_EMAILS</th><th>Comment</th></tr></thead><tbody><tr><td>1</td><td>simon@gmail.com</td><td>{<br>'john@gmail.com':'Friend',<br>'susan@outlook.com': 'Friend'<br>}</td><td>Contact with Email address "john@gmail.com" gets associated with the label "Friend".<br>Contact with Email address "susan@outlook.com" gets associated with the label "Colleague"</td></tr><tr><td>2</td><td>andrew@gmail.com</td><td><em>null</em></td><td>No associations</td></tr><tr><td>3</td><td>paul@gmail.com</td><td>{<br>'simon@gmail.com': null<br>}</td><td>Contact with Email address "simon@gmail.com" gets an unlabelled association</td></tr></tbody></table>

{% hint style="warning" %}
With same-object associations, each record must have all its associated using the same label (or all be unlabelled). This requirement is for loading efficiency, so that we can perform the associations via a single import task.\
If you have a requirement for same-object associations to have multiple labels per record, please contact <support@omnata.com>
{% endhint %}

#### Other-object associations

Other-object associations exist between objects of different types (e.g. contacts to companies).

These work exactly the same as same-object associations, except that each object is free to use a mix of labels when creating associations.

COMPANIES source table:

| COMPANY\_DOMAIN | ASSOCIATED\_CONTACT\_EMAILS                                                              |
| --------------- | ---------------------------------------------------------------------------------------- |
| acme.com        | <p>{<br>'<john@gmail.com>': 'Employee',<br>'<susan@outlook.com>': 'Ex-Employee'<br>}</p> |
| microsoft.com   | *null*                                                                                   |
| amazon.com      | <p>{<br>'<simon@gmail.com>': null<br>}</p>                                               |

#### Removing Associations

When Omnata creates a labelled association, Hubspot may automatically create an unlabelled association between the same two objects.

In the event that an association between two objects is later removed in the source table, Omnata will automatically remove the association in Hubspot.

When creating an outbound sync, you will see the configuration checkbox "Cleanup Default Associations after Labelled Association removal".

If this option is enabled, Omnata will remove any unlabelled or default object association, but only if all of the labelled associations have been removed.

### Updating Lifecycle Stage

When updating lifecycle stages, values that do not follow the specific order expected by the lifecycle are silently ignored during import.

For this reason, it is recommended instead to update another field, and [use a workflow](https://knowledge.hubspot.com/records/change-record-lifecycle-stages-in-bulk?hubs_content=knowledge.hubspot.com/records/use-lifecycle-stages\&hubs_content-cta=Set%20a%20property%20value%20action%20in%20a%20workflow#update-the-lifecycle-stage-with-a-workflow-professional-and-enterprise-only) to in turn update the lifecycle stage.

## Functions

### GET\_CUSTOM\_OBJECT\_SCHEMAS

Gets all property definitions for a specific HubSpot object type.

Parameters:

* CONNECTION\_SLUG (VARCHAR): The slug of the connection to use
* OBJECT\_TYPE (VARCHAR): The object type to get properties for (e.g., contacts, companies, deals)

Examples:

```sql
select * 
from table(OMNATA_HUBSPOT_PLUGIN.UDFS.GET_CUSTOM_OBJECT_SCHEMAS(
    CONNECTION_SLUG => 'my-hubspot-connection'
));
```

### BATCH\_CREATE\_ASSOCIATIONS

Batch create associations between HubSpot objects.

Parameters:

* CONNECTION\_PARAMETERS (OBJECT): The connection details, returned from the PLUGIN\_CONNECTION function (see below)
* FROM\_OBJECT\_TYPE (VARCHAR): The object type that associations originate from
* TO\_OBJECT\_TYPE (VARCHAR): The object type that associations point to
* FROM\_OBJECT\_ID (VARCHAR): The ID of the object the association originates from
* TO\_OBJECT\_ID (VARCHAR): The ID of the object the association points to
* ASSOCIATIONS (OBJECT): Association type definitions (should contain category and typeId). You can use GET\_ASSOCIATION\_DEFINITIONS to find these values.

Examples:

```sql
with src as (
select '43048040051' as CONTACT_ID,'22103767474' as COMPANY_ID, 'contact' as FROM_OBJECT_TYPE, 'company' as TO_OBJECT_TYPE
)
select * 
from src, table(OMNATA_HUBSPOT_PLUGIN.UDFS.BATCH_CREATE_ASSOCIATIONS(
    CONNECTION_PARAMETERS => OMNATA_HUBSPOT_PLUGIN.PLUGIN.PLUGIN_CONNECTION('my-hubspot-connection'),
    FROM_OBJECT_TYPE=>FROM_OBJECT_TYPE,
    TO_OBJECT_TYPE=>TO_OBJECT_TYPE,
    FROM_OBJECT_ID=>CONTACT_ID,
    TO_OBJECT_ID=>COMPANY_ID,
    ASSOCIATIONS=>{'category':'USER_DEFINED','typeId':3}
));
```

### BATCH\_DELETE\_ASSOCIATIONS

Batch delete associations between HubSpot objects

{% hint style="warning" %}
The Hubspot API which handles batch association deletes only raises an error if the object types are wrong.

The [docs](https://developers.hubspot.com/docs/api-reference/crm-associations-v4/batch/post-crm-v4-associations-fromObjectType-toObjectType-batch-archive) incorrectly state that a HTTP 200 response is returned, when in fact the endpoint returns 204 with no body.

If you pass in incorrect object IDs, you get the same response as if they were valid, therefore this UDF simply returns a STATUS column value of NO\_RESULT in both cases to reflect this behaviour.
{% endhint %}

Parameters:

* CONNECTION\_PARAMETERS (OBJECT): The connection details, returned from the PLUGIN\_CONNECTION function (see below)
* FROM\_OBJECT\_TYPE (VARCHAR): The object type that associations originate from
* TO\_OBJECT\_TYPE (VARCHAR): The object type that associations point to
* FROM\_OBJECT\_ID (VARCHAR): The ID of the object the association originates from
* TO\_OBJECT\_ID (VARCHAR): The ID of the object the association points to

Examples:

```sql
with src as (
select '43048040051' as CONTACT_ID,'22103767474' as COMPANY_ID, 'contact' as FROM_OBJECT_TYPE, 'company' as TO_OBJECT_TYPE
union all
select '43048040051' as CONTACT_ID,'22103593126' as COMPANY_ID, 'contact' as FROM_OBJECT_TYPE, 'company' as TO_OBJECT_TYPE
union all
select '43054706788' as CONTACT_ID,'22103740823' as COMPANY_ID, 'contact' as FROM_OBJECT_TYPE, 'company' as TO_OBJECT_TYPE
union all
select '43054706788' as CONTACT_ID,'22103776277' as COMPANY_ID, 'contact' as FROM_OBJECT_TYPE, 'company' as TO_OBJECT_TYPE
)
select * 
from src, table(OMNATA_HUBSPOT_PLUGIN.UDFS.BATCH_DELETE_ASSOCIATIONS(
    CONNECTION_PARAMETERS => OMNATA_HUBSPOT_PLUGIN.PLUGIN.PLUGIN_CONNECTION('my-hubspot-connection'),
    FROM_OBJECT_TYPE=>FROM_OBJECT_TYPE,
    TO_OBJECT_TYPE=>TO_OBJECT_TYPE,
    FROM_OBJECT_ID=>CONTACT_ID,
    TO_OBJECT_ID=>COMPANY_ID
));
```

### GET\_ALL\_OBJECT\_PROPERTIES

Get all property definitions for a specific HubSpot object type.

Parameters:

* CONNECTION\_SLUG (VARCHAR): The slug of the connection to query
* OBJECT\_TYPE (VARCHAR): The object type to get properties for (e.g., contacts, companies, deals)

Examples:

```sql
select * 
from table(OMNATA_HUBSPOT_PLUGIN.UDFS.GET_ALL_OBJECT_PROPERTIES(
    CONNECTION_SLUG => 'my-hubspot-connection',
    OBJECT_TYPE => 'contact'
));
```