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.

​
1. Add your HubSpot access

  1. 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.
  2. 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.
  3. The Start Date is the date of the first record you want to sync with.
  4. Enable the following toggle buttons if you want to extract data of entities related to deals or contacts. This significantly increases the extraction time, but you have this option and can even decide which data should be connected and which should not by editing the field that will open below it.
  5. Click Next.

​
2. Select your Hubspot streams

  1. The next step is letting us know which streams you want to bring. You can select entire groups of streams or only a subset of them.
    Tip: The stream can be found more easily by typing its name.
  2. Click Next.

​
3. Configure your Hubspot data streams

  1. 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: depending on the data you are bringing to the lake, you can choose between INCREMENTAL and FULL_TABLE. Read more about Sync Types here.
  1. Click Next.

​
4. Configure your Hubspot data source

  1. Describe your data source for easy identification within your organization. You can inform things like what data it brings, to which team it belongs, etc.
  2. 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).
  3. Optionally, you can define some additional settings (if available).
  • Configure Delta Log Retention and determine for how log 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.

​
Check your new source!

  1. Click Next to finalize the setup. Once completed, you’ll receive confirmation that your new source is set up!
  2. You can view your new source on the Sources page. Now, for you to be able to see it on your Catalog, you have to wait for the pipeline to run. You can now monitor it on the Sources page to see its execution and completion. If needed, manually trigger the pipeline by clicking on the refresh icon. Once executed, your new table will appear in the Catalog section.
If you encounter any issues, reach out to us via Slack, and we’ll gladly assist you!

​
Available Streams and Fields

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

HubSpot portal user information.

  • id - Unique identifier for the user
  • email - User’s email address
  • roleIds - Array of role IDs assigned to the user
  • primaryteamid - ID of the user’s primary team

HubSpot user and owner information.

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

Ticket pipeline configurations and stages for customer support workflows.

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

Deal pipeline configurations and stages for sales workflows.

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

Custom properties and field definitions for note objects. This stream merges all property streams into a single comprehensive properties table.

  • updatedAt - Last modification timestamp
  • createdAt - Date when property was created
  • name - Property name
  • label - Display label
  • type - Property data type
  • fieldType - Field type
  • description - Property description
  • groupName - Property group
  • options - Available options for enum types
  • displayOrder - Display order
  • calculated - Whether it’s a calculated property
  • externalOptions - Whether it has external options
  • hasUniqueValue - Whether it requires unique values
  • hidden - Whether the property is hidden
  • hubspotDefined - Whether it’s a HubSpot-defined property
  • modificationMetadata - Metadata about modification made
  • formField - Whether it’s used in forms
  • hubspot_object - Associated HubSpot object type

Individual items within deals and quotes.

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

Product catalog and inventory information.

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

Customer support tickets and service requests.

  • id - Unique identifier for the ticket
  • properties.subject - Ticket subject
  • properties.hs_ticket_priority - Priority level
  • properties.hs_pipeline - Associated pipeline
  • properties.hs_pipeline_stage - Current stage
  • properties.hubspot_owner_id - Assigned owner
  • properties.createdate - Date when ticket was created
  • properties.hs_lastmodifieddate - Last modification timestamp
  • createdAt - Date when record was created
  • updatedAt - Last modification timestamp
  • archived - Whether the ticket is archived

Sales quotes and proposals.

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

Email communications and messaging data.

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

​
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

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