Data Dictionary

Data Dictionary

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 Campaigns IDs API to retrieve data for each campaign in a portal. See the Hubspot Campaigns API documentation for an overview and example data.

This resource’s default destination is hubspot_campaigns. The 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. The 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. The 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. The 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

Email Events

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_email_events. The primary key is id, which equals the ID of the campaign.

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. The 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. The 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. The 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. The 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