Hubspot

Hubspot

This document provides instructions for integrating Hubspot data into Panoply through the Hubspot API. The following items will be covered:

Hubspot Data Integration

To integrate Hubspot data into Panoply via the Hubspot CRM Pipelines API using default selections, complete the following steps. For more advanced options, complete the following and refer to the subsequent sections for detailed information.

  1. From the Home page in the Panoply dashboard, click Data Sources. Then, click the Add Data Source button.
  2. In the Data Sources – Choose Source Type window, select Hubspot. Hubspot is listed under APIs.
  3. In the Data Sources – Hubspot screen, enter your API key and then click Next.
  4. Select the resources you want to import. See the list of resources for more detail on each resource.
  5. (Optional) To customize the ingestion from your data source, review the advanced options.
  6. Click Collect.

The Data Sources – Hubspot window will appear grayed out while the data integration is pending. A small green progress bar appears below Hubspot.

You will be prompted to set up the integration of another data source. You can set up multiple data integrations without impacting the ingestion of the already scheduled or pending data integrations.

From the Data Sources main menu, you can monitor the data ingestion status of the scheduled and pending data integrations. Once the data ingestion is complete, you can clean or transform your data in the Tables menu.

Advanced Data Integration Options

Clicking Show next to Advanced will expand the Data Sources - Hubspot window to include Destination, Primary Key, Exclude, Parse string, and Truncate table.

  • Destination - Default is hubspot_{__table}, where {__table} is a variable. Each resource has its own destination table, prefixed with hubspot_.

  • Primary Key - Default is id, built out of source fields from each resource, as shown here:

Resource Source field(s) that create the primary key
Campaigns id
Company companyid
Contacts canonical-vid
Deals dealid
Engagements id
Forms portalid
guid
Owners portalid
ownerid
Pipelines pipelineid
stageid

Note that a single resource can result in multiple destination tables due to nested data. See the Subtables section for additional detail.

Data Model

Panoply ingests Hubspot data into your Panoply database via HubSpot’s APIs. Read Hubspot’s APIs overview for a high-level summary of these APIs.

Subtables

Because Panoply creates subtables out of nested data, Hubspot data from one resource can result in multiple destination tables. Each subtable is named by appending the attribute name of the nested data to the name of the original destination table.

For example, the default original destination table for the Owners resource is hubspot_owners. However, the Owners resource data includes a nested list called remoteList so Panoply creates a hubspot_owners_remotelist subtable.

Each subtable includes an id field as its primary key. Subtables include a foreign key to allow queries to easily join the subtable to its parent table. The foreign key is the parent table’s name followed by _id and is equal to the parent table’s id field. For example, hubspot_company_properties_versions has a primary key of id and a foreign key of hubspot_company_properties_id that is equal to the hubspot_company_properties table’s id key.

The relationship between parent table and subtable is shown in this example:

Table Primary key Foreign key Notes
hubspot_owners id none id is {portalid}-{ownerid}
hubspot_owners_remotelist id hubspot_owners_id hubspot_owners_id is tied to the id from hubspot_owners

Note that multiple layers of nesting can result in multiple subtables, as shown in this example:

Table Primary key Foreign key Notes
hubspot_deals id none Original resource table
hubspot_deals_properties id hubspot_deals_id hubspot_deals_id is tied to the id from hubspot_deals
hubspot_deals_properties_versions id hubspot_deals_properties_id hubspot_deals_properties_id is tied to the id from hubspot_deals_properties

Subtables created from nested data include __updatetime and __sendtime metadata fields. All other Hubspot tables include __updatetime, __sendtime, and __table metadata fields.

Here’s an example of how to extract two datapoints (amount and dealname from a Hubspot subtable (in this case, hubspot_deals_properties):

SELECT hubspot_deals_id,
      MAX(CASE WHEN "key" = 'amount' THEN value ELSE NULL END) amount,
      MAX(CASE WHEN "key" = 'dealname' THEN value ELSE NULL END) dealname
FROM hubspot_deals_properties
GROUP BY 1;

The idea is to get values for the specified key:

MAX(CASE WHEN "key" = 'KEY GOES HERE' THEN value ELSE NULL END)

Connecting Tables

To combine nested data (key-value pair tables) into a single table, you can use queries to create views. In a Redshift database, views are a handy shortcut to speed up a workflow. You can create a view for any query that needs multiple contact, form, deal, or company properties together in a table.

Here is an example query that does this, and also shows how to change Unix timestamps into dates:

SELECT 	p.hubspot_contacts_id,
MAX(CASE WHEN "key" = 'email' THEN p.value ELSE NULL END) email,
-- convert raw Unix timestamps into dates
MAX(CASE WHEN "key" = 'createdate' THEN timestamp 'epoch' + p.value / 1000 * interval '1 second'  ELSE NULL END) createdate,
MAX(CASE WHEN "key" = 'hs_analytics_source' THEN p.value ELSE NULL END) hs_analytics_source
FROM 	hubspot_contacts_properties p
GROUP BY 1

You can add as many columns as you like by continuing to JOIN the properties table to this table and including the values in the SELECT clause.

List of Resources

In this Panoply integration, the following resources are available:

Campaigns

Panoply uses the Hubspot Email Events API to retrieve data for each campaign in a portal. See the Hubspot Email Events API documentation for an overview and example data.

This resource’s default destination is hubspot_campaigns. Primary key is id, which equals the ID of the campaign.

Company

Panoply uses the Hubspot Companies API to retrieve all of the companies in a portal. See the Hubspot Companies API documentation for field descriptions and example data.

This resource’s default destination is hubspot_company. Primary key is id, which equals companyid.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_company_properties
  • hubspot_company_properties_versions

Contacts

Panoply uses the Hubspot Contacts API to retrieve all of the contacts in a portal. See the Hubspot Contacts API documentation for field descriptions and example data

This resource’s default destination is hubspot_contacts. Primary key is id, which equals canonical-vid.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_contacts
  • hubspot_contacts_form-submissions
  • hubspot_contacts_form-submissions_contact-associated-by
  • hubspot_contacts_identity-profiles
  • hubspot_contacts_identity-profiles_identities
  • hubspot_contacts_identity-profiles_linked-vids
  • hubspot_contacts_merge-audits
  • hubspot_contacts_merge-audits_merged_from_email
  • hubspot_contacts_merge-audits_merged_from_email_source-vids
  • hubspot_contacts_merge-audits_merged_to_email
  • hubspot_contacts_merged-vids
  • hubspot_contacts_properties
  • hubspot_contacts_properties_value_parsed
  • hubspot_contacts_properties_versions
  • hubspot_contacts_properties_versions_source-vids
  • hubspot_contacts_properties_versions_value_parsed

Deals

Panoply uses the Hubspot Deals API to retrieve all of the deals in a portal. See the Hubspot Deals API documentation for field descriptions and example data

This resource’s default destination is hubspot_deals. Primary key is id, which equals dealid.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_deals_associations
  • hubspot_deals_associations_associatedcompanyids
  • hubspot_deals_associations_associatedvids
  • hubspot_deals_properties
  • hubspot_deals_properties_versions

Engagements

Panoply uses the Hubspot Engagements API to retrieve all of the engagements in a portal. See the Hubspot Engagements API documentation for field descriptions and example data.

This resource’s default destination is hubspot_engagements. Primary key is id, which equals the ID for the engagement.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_engagements_associations_companyids
  • hubspot_engagements_associations_contactids
  • hubspot_engagements_associations_dealids
  • hubspot_engagements_associations_ownerids
  • hubspot_engagements_metadata
  • hubspot_engagements_metadata_emailsendeventid
  • hubspot_engagements_scheduledtasks

Forms

Panoply uses the Hubspot Forms API to retrieve all of the forms in a portal. See the Hubspot Forms API documentation for field descriptions and example data

This resource’s default destination is hubspot_forms. Primary key is id, which equals a dash-separated concatenation of portalid and guid.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_forms_formfieldgroups
  • hubspot_forms_formfieldgroups_fields
  • hubspot_forms_formfieldgroups_fields_options
  • hubspot_forms_formfieldgroups_fields_selectedoptions
  • hubspot_forms_formfieldgroups_fields_validation
  • hubspot_forms_formfieldgroups_richtext
  • hubspot_forms_metadata
  • hubspot_forms_multivariatetest

Owners

Panoply uses the Hubspot Owners API to retrieve all of the owners in a portal. See the Hubspot Owners API documentation for field descriptions and example data

This resource’s default destination is hubspot_owners. Primary key is id, which equals a dash-separated concatenation of portalid and ownerid.

Data from this resource includes nested data, and therefore Panoply creates additional destination tables based on your source data, such as the following:

  • hubspot_owners_remotelist

Pipelines

Panoply uses the Hubspot CRM Pipelines API to retrieve all of the pipelines for deals and tickets. See the Hubspot CRM Pipelines API documentation for field descriptions and example data

This resource’s default destination is hubspot_pipelines. Primary key is id, which equals a dash-separated concatenation of pipelineid and stageid.

Pipelines metadata is embedded into the pipelines data and includes a metadata_ prefix.

Getting started is easy! Get all your data in one place in minutes.
Try Free