HubSpot as a data source

Bring your data from HubSpot to Nekt.

HubSpot is a comprehensive customer relationship management (CRM) and marketing automation platform that helps businesses attract, engage, and delight customers. It provides tools for marketing, sales, customer service, and content management, all integrated into a single platform.

For more info on the streams and permissions, check the Hubspot API Documentation.

Configuring HubSpot as a Source

In the Sources tab, click on the “Add source” button located on the top right of your screen. Then, select the Hubspot option from the list of connectors.

Click Next and you’ll be prompted to add your access. Login with a Hubspot account that has Super Admin permissions in order to install the Nekt app into the workspace.

1. Add account access

Configure the following settings:

• Start Date: The date of the first record you want to sync with.
• Enable Leads Stream (Optional): Enable to extract leads data (requires HubSpot Pro, Enterprise, or Enterprise Plus).
• Extract Deal Associations (Optional): Enable to extract related entities for deals.
• Deal Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: calls, commerce_payments, communications, companies, contacts, deal_split, deals, emails, invoices, line_item, meetings, notes, orders, postal_mail, quotes, services, subscriptions, tasks, tickets.
• Extract Deal Property History (Optional): Enable to track property changes for deals.
• Deal Property History Fields (Optional): If enabled above, specify which properties to track (comma-separated). Default: dealstage.
• Extract Contact Associations (Optional): Enable to extract related entities for contacts.
• Contact Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: calls, communications, companies, contacts, emails, meetings, notes, subscriptions, tasks, tickets.
• Extract Contact Property History (Optional): Enable to track property changes for contacts.
• Contact Property History Fields (Optional): If enabled above, specify which properties to track (comma-separated). Default: lifecyclestage.
• Extract Ticket Associations (Optional): Enable to extract related entities for tickets.
• Ticket Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: calls, communications, companies, contacts, emails, meetings, notes, subscriptions, tasks, tickets.
• Extract Lead Associations (Optional): Enable to extract related entities for leads (requires Enable Leads Stream).
• Lead Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: calls, communications, companies, contacts, emails, meetings, notes, subscriptions, tasks, tickets.
• Extract Company Associations (Optional): Enable to extract related entities for companies.
• Company Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: calls, communications, companies, contacts, emails, meetings, notes, subscriptions, tasks, tickets.
• Extract Email Associations (Optional): Enable to extract related entities for emails.
• Email Associations List (Optional): If enabled above, specify which associations to extract (comma-separated). Default includes: companies, contacts, deals, tickets.
• Property objects (Optional): Comma separated list of HubSpot object types to fetch property definitions for. Default: calls, communication, company, contacts, deals, email, line_item, meeting, notes, postal_mail, product, task, tickets.
• Properties as JSON string (Optional): When enabled, the properties column in CRM object streams (contacts, companies, deals, tickets, etc.) is emitted as a JSON string instead of a structured object. Enable this if your HubSpot account has a very large number of custom properties and you are hitting destination limits on nested fields (e.g. BigQuery’s 10,000 field limit). Default: false.

Note: Enabling association and history options will increase extraction time but provide more detailed relationship data.

Click Next.

2. Select streams

Choose which data streams you want to sync. For faster extractions, select only the streams that are relevant to your analysis. You can select entire groups of streams or pick specific ones.

Tip: The stream can be found more easily by typing its name.

Select the streams and click Next.

3. Configure data streams

Customize how you want your data to appear in your catalog. Select the desired layer where the data will be placed, a folder to organize it inside the layer, a name for each table (which will effectively contain the fetched data) and the type of sync.

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: you can choose between INCREMENTAL and FULL_TABLE. Read more about Sync Types here.

Once you are done configuring, click Next.

4. Configure data source

Describe your data source for easy identification within your organization, not exceeding 140 characters.

To define your Trigger, consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).

Optionally, you can define some additional settings:

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

Once you are ready, click Next to finalize the setup.

5. Check your new source

You can view your new source on the Sources page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog.

For you to be able to see it on your Catalog, you need at least one successful source run.

Streams and Fields

Below you’ll find all available data streams from HubSpot and their corresponding fields:

Alongside all the fields listed here, most of the streams have additional/custom fields from Hubspot, specific to each workspace and account type. These fields are dynamically included based on your HubSpot configuration.

HubSpot accounts with a very large number of custom properties may exceed destination limits on nested fields. For example, BigQuery enforces a maximum of 10,000 fields per table, and the properties column in CRM object streams (contacts, companies, deals, tickets, etc.) can exceed this limit. If you run into this issue, enable the Properties as JSON string option in your source configuration to emit the properties column as a flat JSON string instead of a structured object.

Audit Logs

Account activity audit logs. Tracks user actions such as CRM object creation, property updates, and security activity. Note: Requires a HubSpot Enterprise account and the account-info.security.read scope.

Field	Type	Description
id	String	Unique identifier of the audit log entry
category	String	Category of the audit event (e.g. PIPELINE, PROPERTY_VALUE)
subCategory	String	Sub-category of the audit event (e.g. DEAL, CONTACT)
action	String	Action performed (e.g. UPDATE, PERFORM, CREATE)
targetObjectId	String	ID of the object that was acted upon
occurredAt	DateTime	Timestamp when the audit event occurred
actingUser	Object	User who performed the audited action (contains userId and userEmail)

Calls

Call activity and engagement data.

Field	Type	Description
id	String	Unique identifier for the call
properties.hs_call_direction	String	Call direction (inbound/outbound)
properties.hs_call_duration	String	Duration of the call
properties.hs_call_status	String	Call status
properties.hs_timestamp	String	When the call occurred
properties.hubspot_owner_id	String	Call owner
properties.hs_call_title	String	Call title or subject
properties.hs_call_body	String	Call notes or description
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Communications

Communication activities and engagements (SMS, WhatsApp, LinkedIn messages, etc.).

Field	Type	Description
id	String	Unique identifier for the communication
properties.hs_communication_channel_type	String	Channel type (SMS, WhatsApp, etc.)
properties.hs_communication_body	String	Communication content
properties.hs_timestamp	String	When the communication occurred
properties.hubspot_owner_id	String	Communication owner
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Companies

Company and organization records.

Field	Type	Description
id	String	Unique identifier for the company
properties.name	String	Company name
properties.domain	String	Company website domain
properties.industry	String	Industry classification
properties.phone	String	Company phone number
properties.address	String	Company address
properties.city	String	Company city
properties.state	String	Company state/region
properties.country	String	Company country
properties.zip	String	Postal code
properties.numberofemployees	String	Employee count
properties.annualrevenue	String	Annual revenue
properties.lifecyclestage	String	Company lifecycle stage
properties.hubspot_owner_id	String	Assigned owner
properties.createdate	String	Creation date
properties.hs_lastmodifieddate	String	Last modification date
associations	Array	Related records (if enabled)
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Contacts

Individual contact records and their properties.

Field	Type	Description
id	String	Unique identifier for the contact
properties.email	String	Email address
properties.firstname	String	First name
properties.lastname	String	Last name
properties.phone	String	Phone number
properties.company	String	Company name
properties.jobtitle	String	Job title
properties.lifecyclestage	String	Contact lifecycle stage
properties.hubspot_owner_id	String	Assigned owner
properties.createdate	String	Creation date
properties.lastmodifieddate	String	Last modification date
associations	Array	Related records (if enabled)
propertiesWithHistory	Object	Property change history (if enabled)
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Custom Objects

Custom objects defined in your HubSpot account. These are automatically discovered and extracted.

Note: Each custom object will appear as a separate stream with its own name based on the object definition in HubSpot. The fields are dynamic and depend on the custom object schema configured in your HubSpot account.

Field	Type	Description
id	String	Unique identifier for the custom object record
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the record is archived

Deals

Sales deals and opportunities.

Field	Type	Description
id	String	Unique identifier for the deal
properties.dealname	String	Deal name
properties.amount	String	Deal amount
properties.dealstage	String	Current stage
properties.pipeline	String	Pipeline ID
properties.closedate	String	Expected close date
properties.hubspot_owner_id	String	Assigned owner
properties.createdate	String	Creation date
properties.hs_lastmodifieddate	String	Last modification date
associations	Array	Related records (if enabled)
propertiesWithHistory	Object	Property change history (if enabled)
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Deal Pipelines

Deal pipeline configurations and stages for sales workflows.

Field	Type	Description
label	String	Display name of the pipeline
displayOrder	Integer	Order in which the pipeline is displayed
active	Boolean	Whether the pipeline is active
stages	Array	Array of pipeline stages with their configurations
objectType	String	Type of object this pipeline applies to
objectTypeId	String	ID of the object type
pipelineId	String	Unique identifier for the pipeline
createdAt	String	Date when pipeline was created
updatedAt	String	Last modification timestamp
default	Boolean	Whether this is the default pipeline

Emails

Email communications and messaging data.

Field	Type	Description
id	String	Unique identifier for the email
properties.hs_email_subject	String	Email subject line
properties.hs_email_text	String	Email body content
properties.hs_email_direction	String	Email direction (inbound/outbound)
properties.hs_email_status	String	Email status
properties.hs_email_sender_email	String	Sender email address
properties.hs_email_sender_firstname	String	Sender first name
properties.hs_email_sender_lastname	String	Sender last name
properties.hs_email_to_email	String	Recipient email address
properties.hs_email_to_firstname	String	Recipient first name
properties.hs_email_to_lastname	String	Recipient last name
properties.hs_timestamp	String	Email timestamp
properties.hubspot_owner_id	String	Assigned owner
properties.createdate	String	Date when email was created
properties.hs_lastmodifieddate	String	Last modification timestamp
associations	Array	Related records (if enabled)
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the email is archived

Email Subscriptions

Email subscription type definitions (e.g., marketing, legal basis) used for consent and preferences.

Field	Type	Description
id	String	Unique identifier for the subscription type
portalId	Integer	Associated portal ID
name	String	Name of the subscription type
description	String	Description of the email subscription
active	Boolean	Whether the subscription type is active
internal	Boolean	Whether the subscription is for internal use only
category	String	Category classification of the subscription
channel	String	Channel associated with the subscription
internalName	String	Internal name of the subscription
businessUnitId	Integer	Associated business unit ID

Forms

Form definitions and configurations.

Field	Type	Description
id	String	Unique identifier for the form
name	String	Form name
submitText	String	Submit button text
formType	String	Type of form
createdAt	String	Creation timestamp
updatedAt	String	Last update timestamp
fields	Array	Array of form fields and their properties

Form Submissions

Submitted form data and responses.

Field	Type	Description
id	String	Unique identifier for the submission
formId	String	Associated form ID
submittedAt	String	Submission timestamp
values	Object	Submitted form values
contactId	String	Associated contact ID

Feedback Submissions

Feedback and survey submissions from contacts.

Field	Type	Description
id	String	Unique identifier for the feedback submission
properties.name	String	Submitter name
properties.city	String	City from the submission
properties.state	String	State or status
properties.industry	String	Industry
properties.domain	String	Associated domain
properties.phone	String	Phone number
properties.createdDate	String	When the record was created
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the record is archived

Goals

Goal targets and tracking for sales and marketing performance.

Field	Type	Description
id	String	Unique identifier for the goal
properties.hs_goal_name	String	Name of the goal
properties.hs_target_amount	String	Target value or amount
properties.hs_start_datetime	String	Start date and time of the goal period
properties.hs_end_datetime	String	End date and time of the goal period
properties.hs_created_by_user_id	String	User who created the goal
properties.createdate	String	When the record was created
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the record is archived

Leads

Lead records for sales prospecting.

Note: This stream is only available for accounts with HubSpot Pro, Enterprise, or Enterprise Plus. Enable via “Enable Leads Stream” in the source configuration.

Field	Type	Description
id	String	Unique identifier for the lead
properties.hs_lead_name	String	Lead name
properties.hs_lead_status	String	Lead status
properties.hubspot_owner_id	String	Assigned owner
properties.hs_lastmodifieddate	String	Last modification timestamp
associations	Array	Related records (if enabled)
propertiesWithHistory	Object	Property change history (if enabled)
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the lead is archived

Line Items

Individual items within deals and quotes.

Field	Type	Description
id	String	Unique identifier for the line item
properties.name	String	Line item name
properties.price	String	Unit price
properties.quantity	String	Quantity ordered
properties.hs_product_id	String	Associated product ID
properties.hs_recurring_billing_period	String	Billing period
properties.createdate	String	Date when line item was created
properties.hs_lastmodifieddate	String	Last modification timestamp
properties.recurringbillingfrequency	String	Recurring billing frequency
createdAt	String	Date when record was created
updatedAt	String	Last modification timestamp
archived	Boolean	Whether the line item is archived

Marketing Emails

Marketing email campaigns and their performance statistics.

Field	Type	Description
id	String	Unique identifier for the marketing email
name	String	Email name
subject	String	Email subject line
state	String	Email state (draft, published, etc.)
type	String	Email type
isPublished	Boolean	Whether email is published
publishedAt	String	Publication timestamp
publishDate	String	Scheduled publish date
campaign	String	Associated campaign ID
campaignName	String	Campaign name
from	Object	Sender information (fromName, replyTo, customReplyTo)
to	Object	Recipient configuration (contactIds, contactLists, suppressGraymail)
subscriptionDetails	Object	Subscription information (subscriptionId, subscriptionName, officeLocationId)
content	String	Email content (JSON string)
emailTemplateMode	String	Template mode (e.g., DRAG_AND_DROP)
isAb	Boolean	Whether it’s an A/B test
isTransactional	Boolean	Whether it’s a transactional email
language	String	Email language
archived	Boolean	Whether the email is archived
createdAt	String	Creation timestamp
updatedAt	String	Last update timestamp
stats.counters	Object	Email performance counters (sent, delivered, open, click, bounce, etc.)
stats.ratios	Object	Performance ratios (openratio, clickratio, bounceratio, etc.)
stats.deviceBreakdown	Object	Opens/clicks by device type (computer, mobile, unknown)
webversion.enabled	Boolean	Whether web version is enabled
webversion.url	String	Web version URL

Meetings

Scheduled meetings and appointments.

Field	Type	Description
id	String	Unique identifier for the meeting
properties.hs_meeting_title	String	Meeting title
properties.hs_meeting_body	String	Meeting description
properties.hs_meeting_start_time	String	Start time
properties.hs_meeting_end_time	String	End time
properties.hs_meeting_outcome	String	Meeting outcome
properties.hubspot_owner_id	String	Meeting owner
createdAt	String	Creation timestamp
updatedAt	String	Last update timestamp

Notes

Notes and annotations on records.

Field	Type	Description
id	String	Unique identifier for the note
properties.hs_note_body	String	Note content
properties.hubspot_owner_id	String	Note creator
properties.hs_timestamp	String	Creation timestamp
properties.hs_attachment_ids	String	Attached file IDs
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Properties

Custom properties and field definitions for all HubSpot objects. This stream merges all individual property streams into a single comprehensive properties table. The hubspot_object field indicates which object the property belongs to.

Field	Type	Description
updatedAt	String	Timestamp when the record was last updated
createdAt	String	Timestamp when the record was created
name	String	Internal name of the property
label	String	Display label of the property
type	String	Type classification of the record
fieldType	String	Field type used in forms and UI
description	String	Description of the property
groupName	String	Name of the property group
options	Array	List of options for the property
displayOrder	Integer	Order in which the property is displayed
calculated	Boolean	Whether the property is calculated
externalOptions	Boolean	Whether the property uses external options
hasUniqueValue	Boolean	Whether the property must have a unique value
hidden	Boolean	Whether the property is hidden
hubspotDefined	Boolean	Whether the property is defined by HubSpot
modificationMetadata	Object	Metadata about property modification permissions
formField	Boolean	Whether the property is used in forms
hubspot_object	String	HubSpot object type the property belongs to
createdUserId	String	ID of the user who created the property
updatedUserId	String	ID of the user who last updated the property
referencedObjectType	String	Object type referenced by this property (e.g. OWNER)
calculationFormula	String	Formula used for calculated properties
archived	Boolean	Whether the property is archived
dataSensitivity	String	Data sensitivity level of the property

Owners

HubSpot user and owner information.

Field	Type	Description
id	String	Unique identifier for the owner
email	String	Owner’s email address
firstName	String	Owner’s first name
lastName	String	Owner’s last name
userId	String	Associated user ID
createdAt	String	Date when owner was created
updatedAt	String	Last modification timestamp
archived	Boolean	Whether the owner is archived

Postal Mail

Postal mail activities and engagements for direct mail campaigns.

Field	Type	Description
id	String	Unique identifier for the postal mail
properties.hs_postal_mail_body	String	Postal mail content
properties.hs_timestamp	String	When the postal mail was sent
properties.hubspot_owner_id	String	Owner
properties.hs_lastmodifieddate	String	Last modification timestamp
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Products

Product catalog and inventory information.

Field	Type	Description
id	String	Unique identifier for the product
properties.name	String	Product name
properties.description	String	Product description
properties.price	String	Product price
properties.hs_sku	String	Stock keeping unit
properties.hs_cost_of_goods_sold	String	Cost of goods sold
properties.createdate	String	Date when product was created
properties.hs_lastmodifieddate	String	Last modification timestamp
properties.hs_recurring_billing_period	String	Recurring billing period
createdAt	String	Date when record was created
updatedAt	String	Last modification timestamp
archived	Boolean	Whether the product is archived

Quotes

Sales quotes and proposals.

Field	Type	Description
id	String	Unique identifier for the quote
properties.hs_title	String	Quote title
properties.hs_quote_amount	String	Total quote amount
properties.hs_quote_number	String	Quote number
properties.hs_status	String	Quote status
properties.hs_expiration_date	String	Quote expiration date
properties.hs_terms	String	Quote terms
properties.hubspot_owner_id	String	Assigned owner
properties.hs_createdate	String	Date when quote was created
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp
archived	Boolean	Whether the quote is archived

Tasks

Task assignments and to-dos.

Field	Type	Description
id	String	Unique identifier for the task
properties.hs_task_subject	String	Task subject
properties.hs_task_status	String	Task status
properties.hs_task_priority	String	Priority level
properties.hs_task_type	String	Task type
properties.hs_timestamp	String	Creation timestamp
properties.hubspot_owner_id	String	Task owner
properties.hs_task_body	String	Task description
properties.hs_task_due_date	String	Due date
createdAt	String	Record creation timestamp
updatedAt	String	Record update timestamp

Tickets

Customer support tickets and service requests.

Field	Type	Description
id	String	Unique identifier for the ticket
properties.subject	String	Ticket subject
properties.hs_ticket_priority	String	Priority level
properties.hs_pipeline	String	Associated pipeline
properties.hs_pipeline_stage	String	Current stage
properties.hubspot_owner_id	String	Assigned owner
properties.createdate	String	Date when ticket was created
properties.hs_lastmodifieddate	String	Last modification timestamp
associations	Array	Related records (if enabled)
createdAt	String	Date when record was created
updatedAt	String	Last modification timestamp
archived	Boolean	Whether the ticket is archived

Ticket Pipelines

Ticket pipeline configurations and stages for customer support workflows.

Field	Type	Description
label	String	Display name of the pipeline
displayOrder	Integer	Order in which the pipeline is displayed
active	Boolean	Whether the pipeline is active
stages	Array	Array of pipeline stages with their configurations
objectType	String	Type of object this pipeline applies to
objectTypeId	String	ID of the object type
pipelineId	String	Unique identifier for the pipeline
createdAt	String	Date when pipeline was created
updatedAt	String	Last modification timestamp
default	Boolean	Whether this is the default pipeline

Users

HubSpot portal user information.

Field	Type	Description
id	String	Unique identifier for the user
email	String	User’s email address
roleIds	Array	Array of role IDs assigned to the user
primaryteamid	String	ID of the user’s primary team

Common data transformations

Many streams from Hubspot send all the relevant data in a single column, called properties. For you to remove them from this single array and transform each one in a new and individual column, use the following template:

SELECT
	-- list the regular columns you want to see in the final table
	id,
	createdat,
	updatedat,
	archived,
	_nekt_sync_at,
	-- list the fields that come inside 'properties' column and you want to have a specific column for
	properties.name,
	properties.price,
	properties.description
FROM
   -- example: "nekt.raw"."hubspot_products"
	"nekt_layer"."table_name"

Use Cases for Data Analysis

HubSpot data can be leveraged for comprehensive business intelligence and analytics. Here are some common use cases with SQL transformations:

1. Sales Performance Analytics

Objective: Track sales team performance, deal velocity, and revenue forecasting.

SQL code

Sales Performance Dashboard

-- Sales Performance Dashboard
WITH
   deal_metrics AS (
      SELECT
         d.properties.hubspot_owner_id AS hubspot_owner_id,
         o.firstname || ' ' || o.lastname AS owner_name,
         COUNT(*) AS total_deals,
         SUM(
            CAST(NULLIF(d.properties.amount, '') AS DECIMAL(15, 2))
         ) AS total_value,
         AVG(
            CAST(NULLIF(d.properties.amount, '') AS DECIMAL(15, 2))
         ) AS avg_deal_size,
         COUNT(
            CASE
               WHEN d.properties.dealstage = 'closedwon' THEN 1
            END
         ) AS won_deals,
         COUNT(
            CASE
               WHEN d.properties.dealstage = 'closedlost' THEN 1
            END
         ) AS lost_deals
      FROM
         "nekt_raw"."hubspot_deals" d
         LEFT JOIN "nekt_raw"."hubspot_owners" o ON d.properties.hubspot_owner_id = o.id
      WHERE
         CAST(SUBSTRING(d.properties.createdate, 1, 10) AS DATE) >= DATE_ADD ('month', -6, CURRENT_DATE)
      GROUP BY
         d.properties.hubspot_owner_id,
         o.firstname,
         o.lastname
   )
SELECT
   owner_name,
   total_deals,
   total_value,
   avg_deal_size,
   won_deals,
   lost_deals,
   ROUND((won_deals * 100.0 / total_deals), 2) AS win_rate,
   ROUND((total_value / NULLIF(total_deals, 0)), 2) AS avg_deal_value
FROM
   deal_metrics
ORDER BY
   total_value DESC

2. Customer Journey Analysis

Objective: Understand customer lifecycle stages and conversion rates.

SQL code

-- Customer Journey Funnel
WITH
   contact_stages AS (
      SELECT
         properties.lifecyclestage,
         COUNT(*) AS contact_count,
         COUNT(
            CASE
               WHEN properties.lifecyclestage IN (
                  'lead',
                  'marketingqualifiedlead',
                  'salesqualifiedlead'
               ) THEN 1
            END
         ) AS qualified_leads,
         COUNT(
            CASE
               WHEN properties.lifecyclestage = 'opportunity' THEN 1
            END
         ) AS opportunities,
         COUNT(
            CASE
               WHEN properties.lifecyclestage = 'customer' THEN 1
            END
         ) AS customers
      FROM
         "nekt_raw"."hubspot_contacts"
      WHERE
         createdat >= DATE_ADD ('month', -12, CURRENT_DATE)
      GROUP BY
         properties.lifecyclestage
   ),
   funnel_metrics AS (
      SELECT
         'Total Contacts' AS stage,
         SUM(contact_count) AS COUNT,
         100.0 AS conversion_rate
      FROM
         contact_stages
      UNION ALL
      SELECT
         'Qualified Leads' AS stage,
         SUM(qualified_leads) AS COUNT,
         ROUND(
            (SUM(qualified_leads) * 100.0 / SUM(contact_count)),
            2
         ) AS conversion_rate
      FROM
         contact_stages
      UNION ALL
      SELECT
         'Opportunities' AS stage,
         SUM(opportunities) AS COUNT,
         ROUND(
            (SUM(opportunities) * 100.0 / SUM(qualified_leads)),
            2
         ) AS conversion_rate
      FROM
         contact_stages
      UNION ALL
      SELECT
         'Customers' AS stage,
         SUM(customers) AS COUNT,
         ROUND((SUM(customers) * 100.0 / SUM(opportunities)), 2) AS conversion_rate
      FROM
         contact_stages
   )
SELECT
   *
FROM
   funnel_metrics
ORDER BY
   COUNT DESC

These use cases demonstrate how HubSpot data can be transformed into actionable business intelligence, enabling data-driven decision making across sales, marketing, and customer service operations.

Implementation Notes

• API Limits & Performance: Extracting association lists and property history involves making additional requests to HubSpot’s API per record. While the connector has been optimized with robust connection pooling to execute these details reliably, enabling multiple association lists simultaneously will still significantly impact the overall duration of the sync. Enable these features only when necessary.

Skills for agents

Download HubSpot skills file

HubSpot connector documentation as plain markdown, for use in AI agent contexts.