Understanding Your HubSpot Schema

This tutorial is a basic explanation of the default HubSpot schema found in Stitch. A full summary of each schema attribute is provided to enable you to better understand and use HubSpot effectively for your marketing analytics. A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Note: The primary key in every table is denoted by 🔑

Campaign

This table contains information about a specific campaign in HubSpot.

AttributeDescription
App ID: appIdThe ID of the HubSpot application that sent out the campaign.
App Name: appNameThe name of the HubSpot application that sent out the campaign.
Content ID: contentIdThe ID associated with the content of the campaign.
🔑ID: idThe ID of the campaign.
Name: nameThe name of the campaign.
Number Included: numIncludedThe number of included campaigns.
Number Queued: numQueuedThe number of queued campaigns.
Subject: subjectThe subject of the campaign.
SubType: subTypeThe subtype of the campaign. Ex: blog post
Type: typeThe primary type of the campaign. Ex: landing page

Company

This table contains information about the company data in HubSpot.

AttributeDescription
🔑Company ID: companyIdThe ID of the company.
Portal ID: portalIdThe ID of the portal the company is associated with.

Contact

This table contains information about individual contacts in HubSpot.

AttributeDescription
🔑Canonical Vid: canonical_vidThe primary ID for the contact if the contact has multiple vids.
Is Contact: is-contactWhether or not the contact is a valid record.
Portal ID: portal-idThe ID of the portal the contact is associated with.
Profile Token: profile-tokenA unique token that can be used to view the contact without logging into HubSpot.
Profile URL: profile-urlA unique token that can be used to view the contact without logging into HubSpot. Anyone with this URL can view, but not edit, the contact’s record.
Version Timestamp: versionTimestampWhen the last update to the contact or its properties occurred.
Vid: vidThe internal ID of the contact.

Subtable: merged-vids

This subtable contains information about a list of vids that have been merged into this specific contact record.

AttributeDescription
🔑_sdc_source_key_canonical-vidThe canonical ID of the contact. Contacts may have multiple vids, but the canonical-vid will be the primary ID for a contact.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Type: typeThe vid that was merged into the contact record.

Subtable: identity-profiles

This subtable contains information about a list of the identities of a specific contact.

AttributeDescription
🔑_sdc_source_key_canonical-vidThe canonical ID of the contact. Contacts may have multiple vids, but the canonical-vid will be the primary ID for a contact.
🔑_sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Deleted and Changed Timestamp: deleted-changed-timestampThe timestamp of the last change or delete applied to the contact’s identity profile.
Identities: identitiesA list of identities for the contact.
Saved At Timestamp: saved-at-timestampWhen the last update to the contact’s identity occurred.
Vid: vidThe original vid for the contact’s identity

Subtable: list-memberships

This subtable contains information about a list of a specific contact’s memberships in contact lists.

AttributeDescription
🔑 _sdc_source_key_canonical-vidThe canonical ID of the contact. Contacts may have multiple vids, but the canonical-vid will be the primary ID for a contact.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Internal List ID: internalListIdThe contact’s internal list ID.
Is Member: is-memberIndicates if the contact is a member of the list.
Static List ID: static-list-idThe ID of the contact list.
Timestamp: timestampWhen the contact joined the list.
Vid: vidThe primary ID for the contact.

Subtable: form-submissions

This subtable contains information about a list of form submissions for the contact.

AttributeDescription
🔑_sdc_source_key_canonical-vidThe canonical ID of the contact. Contacts may have multiple vids, but the canonical-vid will be the primary ID for a contact.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Conversion ID: conversion-idThe unique ID for the specific form conversion.
Form ID: form-idThe GUID (Globally Unique Identifier) of the form that the submission belongs to.
Page URL: page-urlThe URL that the form was submitted on.
Portal ID: portal-idThe ID of the portal of the submission.
Timestamp: timestampWhen the submission occurred.
Title: titleThe title of the page where the form was submitted.

Subtable: merge-audits

This subtable contains information about any merges that have occurred for the specific contact record.

AttributeDescription
🔑_sdc_source_key_canonical-vidThe canonical ID of the contact.
🔑_sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0
Canonical Vid: canonical-vidThe primary ID of the main contact, or the record that was merged into.
Merged From Email:
  1. Value- merged-from-email_value
  2. Source-Type- merged-from-email_source-type
  3. Source ID- merged-from-email_source-id
  4. Source Label- merged-from-email_source-label
  5. Timestamp- merged-from-email_timestamp
  6. Source Vids- merged-from-email_source-vids
  1. The email address of the secondary contact at the time of the merge.
  2. The method by which the email property was last updated.
  3. More data related to the source type.
  4. More data related to the source type.
  5. When the last email address was last updated.
  6. A list of secondary contact vids.
Merged To Email:
  1. Value- merged-to-email_value
  2. Source-Type- merged-to-email_source-type
  3. Source ID- merged-to-email_source-id
  4. Source Label- merged-to-email_source-label
  5. Timestamp- merged-to-email__timestamp
  1. The email address of the primary contact at the time of the merge.
  2. The method by which the last email property was updated.
  3. More data related to the source type.
  4. More data related to the source type.
  5. When the last email address was last updated.
Number Properties Moved: num-properties-movedThe total number of contact properties that were updated as a result of the merge.
Timestamp: timestampWhen the merge occurred.
User ID: user-idThe internal ID of the user that completed the merge.
Vid to Merge: vid-to-mergeThe vid of the secondary contact, or the record that information was merged from.

Contact_list

This table contains information about contact lists in HubSpot. Contact lists are used to group contacts.

AttributeDescription
Archived: archivedIndicates if the list has been archived.
Created At: createdAtThe time at which the list was created.
Deletable: deletableIndicates if the list can be deleted.
Dynamic: dynamicIndicates if the list is dynamic; if it’s not, it’s static.
Internal List Id: internalListIdThe internal list ID that belongs to the list.
🔑List Id: listIdThe unique ID of the list.
Metadata Error: metadata_errorErrors that occurred the previous time the list was processed.
Metadata Last Processing State Change At: metadata_last_processing_state_change_atThe previous time the list’s processing state changed.
Metadata Last Size Change At: metadata_last_size_change_atThe previous time the size of the list changed.
Metadata Processing: metadata_processingIndicates the processing status of the list.
Metadata Size: metadata_sizeIndicates the current size of the list.
Name: nameThe name of the list
Parent Id: parentIdThe ID of the folder the list belongs to.
Portal Id: portalIdThe ID of the portal the list belongs to.
Read Only: readOnlyIndicates if the list is read-only.
Updated At: updatedAtThe time of the most recent update.

Subtable: filters

This subtable contains information about the list of filters used to define list membership.

AttributeDescription
🔑_sdc_source_key_listIdThe canonical ID of the contact.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
valueDetails about the filters used to define list membership.

Contacts_by_company

This table contains information about contact and company ID pairs. It allows you to join relevant contact information to company information.

AttributeDescription
🔑Contact ID: contact-idThe ID of the contact.
🔑Company ID: company-idThe ID of the company.

Deals

This table contains information about the deals in HubSpot.

AttributeDescription
Amount Properties:
  1. Source- properties_amount_source
  2. Timestamp- properties_amount_timestamp
  3. Value- properties_amount_value
  1. The method used to set the amount.
  2. The time when the amount was set.
  3. The actual amount of the deal.

Associated Companies, Deals, Vids: associations_associatedCompanyIds

associations_associatedDealIds

associations_associatedVidIds

The IDs of the companies, deals, and vid associated with the deal.
🔑Deal ID: dealIdThe deal’s ID.
Deal Properties:
  1. Source– properties_dealname_source
  2. Source ID– properties_dealname_sourceId
  3. Timestamp– properties_dealname_timestamp
  4. Value– properties_dealname_value
  1. The method used to set the deal value.
  2. Extra details about the source.
  3. The time the current deal value was set.
  4. The current value of the deal.
🔑Portal ID: portalIdThe ID of the portal that’s related to the deal.

Email_events

This table contains information about email events and how people respond to them.

AttributeDescription
App ID: appIdThe ID of the HubSpot app that sent the email.
Browser Properties:
  1. Family- browser_family
  2. Name– browser_name
  3. Producer– browser_producer
  4. Producer URL-   browser_producerUrl
  5.  Type– browser_type
  6. URL– browser_url
  1. The family of the browser that hosted the event.
  2. The name of the browser that hosted the event.
  3. The producer of the browser that hosted the event.
  4. The producer URL that hosted the event.
  5. The type of browser that hosted the event.
  6. The URL of the browser that hosted the event.
Created: createdThe time and date the event was created.
Device Type: deviceTypeThe type of device used to host the event.
Duration: durationThe approximate number of milliseconds the user had opened the email message.
Email Campaign ID: emailCampaignIdThe ID of the email campaign that the email is part of.
Email Campaign Group ID: emailCampaignGroupIdThe ID of the campaign group associated with the email.
Filtered Event: filteredEventIndicated if the event was filtered.
From Address: fromThe from field of the email.
HM ID: hmidThe auto-generated ID that corresponds to the header X-HubSpot-MID in the email message.
🔑ID: idThe email event’s ID.
IP Address: ipAddressThe IP address that is the origin of the event.
Link ID: linkIdThe ID of the link the recipient clicked on in the email.
Location:
  1. City- location_city
  2. Country– loction_country
  3. State– location_state
  1. The city where the event took place.
  2. The country where the event took place.
  3. The state where the event took place.
Portal ID: portalIdThe ID of the portal in HubSpot that sent the email.
Recipient: recipientThe email address of the recipient.
Response: responseThe response from the recipient’s email server.
Sent Created: sentBy_createdThe time the email was registered as SENT.
Sent ID: sentBy_idThe ID of the email’s registered SENT.
SMTP ID: smtpIdThe ID that HubSpot attaches to the email.
Subject: subjectThe subject line of the email.
Type: typeThe type of the event.
URL: urlThe URL in the email that the recipient clicked.
User Agent: userAgentThe user agent that is responsible for the event.

Engagements

This table contains information about all engagement possibilities in HubSpot.

AttributeDescription
Active: activeIndicates if the engagement is active.
Created At: createdAtThe time the engagement was created.
🔑ID: idThe ID of the engagement.
Metadata Body: metadata_body
  • The body for NOTE engagements.
  • The body or details for TASK engagements.
  • The body or details for MEETING engagement.
  • The details or notes for a CALL engagement.
Metadata for Call (for call engagements only):
  1. External ID– metadata_externalId
  2. External Account ID-   metadata_externalAccountId
  3. Duration– metadata_durationMilliseconds
  4. From Number– metadata_fromNumber
  5. Recording URL– metadata_recordingUrl
  6. To Number– metadata_toNumber
  1. The internal ID of the call made in HubSpot.
  2. The internal ID of the HubSpot account used to place the call.
  3. The length of the call in milliseconds.
  4. The phone number that made the call.
  5. The URL of the recorded call’s file.
  6. The number that was called.
Metadata for Email (for email engagements only):
  1. HTML- metadata_html
  2. Text- metadata_text
  1. The body of the HTML email.
  2. The body of the text only email.
Metadata From:
  1. Email- metadata_from_email
  2. First Name– metadata_from_firstName
  3.  Last Name– metadata_from_lastName
  1. The email address of the sender.
  2. The first name of the sender.
  3. The last name of the sender.
Metadata for Meeting (for meeting engagements only):
  1. End Time- metadata_endTime
  2. Start Time– metadata_startTime
  3. Title– metadata_title
  1. The end time of the meeting.
  2. The start time of the meeting.
  3. The title or subject of the meeting.
Metadata Subject: metadata_subject
  1. The subject for EMAIL engagements.
  2. The subject or title for TASK engagements.
Metadata for Task (for task engagements only):
  1. Object Type- metadata_forObjectType
  2.  Status– metadata_status
  1. The object type of the task.
  2. The status of the task.
Metadata To:
  1. BCC- metadata_bcc_email
  2.  CC– metadata_cc_email
  3. To- metadata_to_email
  1. The email address of the BCC’d recipient.
  2. The email address of the CC’d recipient.
  3. The email address of the recipient.
Last Updated: lastUpdatedThe time of the last update to the engagement.
Owner ID: ownerIdThe ID of the owner that’s associated with the engagement.
Portal ID: portalIdThe ID of the engagement’s portal.
Timestamp: timestampThe time the engagement should appear in the timeline.
Type: typeThe type of engagement.

Subtable: associations

This subtable contains information about the IDs of the objects associated with the engagement.

AttributeDescription
🔑_sdc_source_key_idThe ID of the engagement.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Company IDs: companyIdsThe IDs of the companies that are associated with the engagement.
Contact IDs: contactIdsThe IDs of the contacts that are associated with the engagement.
Deal IDs: dealIdsThe IDs of the deals that are associated with the engagement.

Subtable: attachments

This subtable contains information about the IDs of the files from the file manager that should display in the attachments list when viewing the engagement in HubSpot.

AttributeDescription
🔑_sdc_source_key_idThe ID of the engagement.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
ID: idThe ID of the attachment.

Forms

This table contains information about website forms in HubSpot.

AttributeDescription
Campaign GUID: campaignGuidThe Globally Unique Identifier (GUID) of the form’s campaign.
Captcha Enabled: captchaEnabledIndicates if captcha is enabled on the form.
Cloneable: cloneableIndicated if the form is cloneable.
Created At: createdAtThe time the form was created.
CSS Class: cssClassThe form’s CSS class.
Deletable: deletableIndicates if the form is deletable.
Deleted At: deletedAtThe time the form was deleted.
Editable: editableIndicates if the form is editable.
Form Type: formTypeThe type of the form.
🔑GUID: guidThe GUID of the form.
Ignore Current Values: ignoreCurrentValuesIndicates if the form will pre-populate fields with known values for known contacts.
Inline Message: inlineMessageThe gratitude message that is displayed after the form is submitted.
Lead Nurturing Campaign ID: leadNurturingCampaignIdThe ID of the form’s lead nurturing campaign.
Method: methodThe API method used to send the form submission.
Name: nameThe name of the form.
Notify Recipients: notifyRecipientsThe list of email addresses that should be sent notifications of form submissions.
Portal ID: portalIdThe form’s portal ID.
Redirect: redirectThe URL the visitor will be redirected to after filling out the form.
Submit Text: submitTextThe text that is displayed for a user to submit a form.
Updated At: updatedAtThe last time the form was updated.

Subtable: formFieldGroups

This subtable contains information about the fields in a form.

AttributeDescription
🔑_sdc_source_key_guidThe GUID of the form.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Default: defaultIndicates if the form group is default.
Fields: fieldsThe details about the fields in a form.
Is Smart Group: isSmartGroupIndicates if the form field group is a smart group.
Rich Text Content: richText_contentThe content of the rich text separator.

Subtable: metaData

This subtable contains information about the metadata of the form.

AttributeDescription
🔑_sdc_source_key_guidThe GUID of the form.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.

Owners

This table contains information about the owners in HubSpot. Owners are created and updated in HubSpot when new users are added or when owners are synced from elsewhere.

AttributeDescription
Created At: createdAtThe time when the owner was created.
Email: emailThe email address belonging to the owner.
First Name: firstNameThe first name of the owner.
Contacts Access: hasContactsAccessIndicates if the owner has access to contacts.
Last Name: lastNameThe last name of the owner.
🔑Owner ID: ownerIdThe owner’s ID.
🔑Portal ID: portalIdThe owner’s portal ID.
Signature: signatureThe owner’s signature.
Type: typeThe owner’s type.
Updated At: updatedAtThe last time the owner was updated.

Subtable: remoteList

This table contains information about the remote list associated with the owner.

AttributeDescription
🔑 _sdc_source_key_ownerIdThe ID of the owner.
🔑 _sdc_source_key_portalIdThe portal’s ID that is associated with the owner.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Active: activeIndicates if the owner is active.
Owner ID: ownerIdThe ID of the owner.
Portal ID: portalIdThe owner’s portal ID.
Remote ID: remoteIdThe ID of the remote list.
Remote Type: remoteTypeThe type of the remote list.

Subscription_changes

This table contains information about changes made to subscriptions.

AttributeDescription
🔑Portal ID: portalIdThe subscription change event’s portal ID.
🔑Recipient: recipientThe contact associated with the subscription change event.
🔑Timestamp: timestampThe time of the subscription change.

Subtable: changes

This table contains information about the subscription change event.

AttributeDescription
🔑_sdc_source_key_recipientThe contact associated with the subscription change.
🔑 _sdc_source_key_portalIdThe portal’s ID that is associated with the subscription change.
🔑_sdc_source_key_timestampThe time that the subscription change occurred.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Caused By Event:
  1. ID- causedByEvent_id
  2. Created- causedByEvent_created
  1. The ID of the event that caused the change.
  2. The time the event that caused the change took place.
Change: changedThe action related to the change.
Change Type: changeTypeThe type of change.
Portal ID: portalIdThe change’s portal ID.
Source: sourceThe source of the change.
Subscription ID: subscriptionIdThe ID of the subscription involved in the change.
Timestamp: timestampThe time when the change took place.

Workflow

This table contains information about the workflows in HubSpot.

AttributeDescription
Contact List IDs:
  1. Active-  contactListIds_active
  2. Enrolled- contactListIds_enrolled
  3. Steps- contactListIds_steps
  1. The number of contacts that are active for the workflow.
  2. The number of contacts that are currently enrolled in the workflow.
  3. The summary info for the contacts belonging to to the workflow.
Enabled: enabledIndicates if the workflow is enabled in HubSpot.
🔑ID: idThe workflow’s ID.
Inserted At: inserted-atThe time when the workflow was inserted.
Name: nameThe workflow’s name.
Type: typeThe workflow’s type.
Updated At: updatedAtThe last time the workflow was updated.

Subtable: personaTagIds

This subtable contains information about the personas related to the workflow.

AttributeDescription
🔑_sdc_source_key_idThe ID of the workflow.
🔑 _sdc_level_0_idA composite key for table. Value will auto-increment for each unique record, beginning with 0.
Value: valueThe persona ID that is related to the workflow.

Foreign Key Join

A foreign key is a column in a table that establishes an association with another table via shared values. To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs that are attached to the beginning of the name of the referenced table. Below are some of the relations you could have when joining the different HubSpot tables.

Note: All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

campaign with email_event

Join these tables when you want to link a campaign with an email event.

  • Foreign key: campaign.id <–> email_event.emailCampaignId

contact with contact_list_memberships

Join these tables when you want to link a contact with its memberships in different contact lists.

  • Foreign key: contact.vid <–> list-memberships.vid

contact_list_memberships with contact_list

Join these tables when you want to link contact memberships within different contact lists.

  • Foreign key: list-memberships.internalListId <–> contact_list.internalListId

Resource

About Bryn Burns

Hi! I'm Bryn Burns. I am a current senior at Virginia Tech pursuing degrees in Statistics and Mathematics. Data science and visualization are two things I'm very passionate about, as well as working with numbers and helping people learn. I'm thrilled to share my knowledge here at The Data School!