ODBC Driver for HubSpot

Build 24.0.9062

Deals

Retrieve the available Deals in HubSpot. This table supports both OAuth and PrivateAppToken authentication.

Table Specific Information

A deal represents an ongoing transaction that a sales team is pursuing with a contact or company. It is tracked through pipeline stages until won or lost.

SELECT

When selecting deals, they can be filtered by columns marked below as 'Filterable'. For those columns, the supported server-side operators are =, !=, <, <=, >, >=. Further filtering is available using AND and OR combinations. A maximum of 2 OR operators are supported server-side, with each of these operators supporting up to 2 AND operators inside of them. For example: 

SELECT * FROM Deals WHERE [Amount] > 100 AND [Close Date] > '2020-01-01'
SELECT * FROM Deals WHERE [Deal Name] = 'John' OR [Deal Name] = 'William'

In the below server-offloading examples, "{Column}" represents any column you'd like to filter and "{Value}" represents any value you want to filter it on: 

SELECT * FROM Deals WHERE {Column} = {Value} AND {Column} < {Value} AND {Column} > {Value}
SELECT * FROM Deals WHERE {Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}
SELECT * FROM Deals WHERE ({Column} = {Value} AND {Column} = {Value}) OR ({Column} != {Value} AND {Column} >= {Value})
SELECT * FROM Deals WHERE ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}) OR ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}) OR ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value})

INSERT


INSERT INTO Deals (Currency) VALUES ('USD')

UPDATE


UPDATE Deals SET Currency = 'EUR' WHERE ID = '471395623'

DELETE


DELETE FROM Deals WHERE ID = '471395623'

Columns

Name Type ReadOnly References Filterable Description
Id [KEY] Long False True

Unique identifier of the object.

Archived Bool True False

Whether the object is archived or not.

CreatedAt Datetime True False

When the object was created.

UpdatedAt Datetime True False

When the object was last updated.

ItemURL String True False

Url that points directly to the HubSpot item.

Amount in company currency Decimal True True

The amount of the deal_ using the exchange rate_ in your company's currency.

Days to close Decimal True True

The number of days the deal took to close.

Currency String False True

Currency code for the deal.

Annual contract value Decimal True True

The annual contract value _ACV_ of this deal.

Business units String False True

The business units this record is assigned to.

Deal Collaborator String True True

Owner ids of the users involved in closing the deal.

Deal Split Users String True True

The owner ids of all associated Deal Splits. This property is set automatically by HubSpot.

Latest Source String True True

Source for the contact either directly or indirectly associated with the last session activity for this deal.

Latest Source Company String True True

Source for the company with an associated contact with the last session activity for this deal.

Latest Source Contact String True True

Source for the directly associated contact with the last session activity for this deal.

Latest Source Data 1 String True True

Additional source details of the last session attributed to any contacts that are directly or indirectly associated with this deal.

Latest Source Data 1 Company String True True

Additional source details of the last session attributed to any contacts that are indirectly associated with this deal _via a company association_.

Latest Source Data 1 Contact String True True

Additional source details of the last session attributed to any contacts that are directly associated with this deal.

Latest Source Data 2 String True True

Additional source details of the last session attributed to any contacts that are directly or indirectly associated with this deal.

Latest Source Data 2 Company String True True

Additional source details of the last session attributed to any contacts that are indirectly associated with this deal.

Latest Source Data 2 Contact String True True

Additional source details of the last session attributed to any contacts that are directly associated with this deal.

Latest Source Timestamp Datetime True True

Timestamp of when latest source occurred for either a directly or indirectly associated contact.

Latest Source Timestamp Company Datetime True True

Timestamp of when latest source occurred for an indirectly associated contact.

Latest Source Timestamp Contact Datetime True True

Timestamp of when latest source occurred for a directly associated contact.

Original Source Type String False True

Original source for the contact with the earliest activity for this deal.

Original Source Data 1 String True True

Additional information about the original source for the associated contact_ or associated company if there is no contact_ with the oldest value for the Time first seen property.

Original Source Data 2 String True True

Additional information about the original source for the associated contact_ or associated company if there is no contact_ with the oldest value for the Time first seen property.

Annual recurring revenue Decimal True True

The annual recurring revenue _ARR_ of this deal.

HubSpot Campaign String False True

The marketing campaign the deal is associated with.

Closed Deal Amount Decimal True True

Returns the amount if the deal is closed. Else_ returns 0.

Closed Deal Amount In Home Currency Decimal True True

Returns the amount in home currency if the deal is closed. Else_ returns 0.

Created by user ID Decimal True True

The user that created this object. This value is automatically set by HubSpot and may not be modified.

Date entered 'Appointment Scheduled _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Appointment Scheduled' stage_ 'Sales Pipeline' pipeline.

Date entered 'Closed Lost _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Closed Lost' stage_ 'Sales Pipeline' pipeline.

Date entered 'Closed Won _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Closed Won' stage_ 'Sales Pipeline' pipeline.

Date entered 'Contract Sent _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Contract Sent' stage_ 'Sales Pipeline' pipeline.

Date entered 'Decision Maker BoughtIn _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Decision Maker Bought_In' stage_ 'Sales Pipeline' pipeline.

Date entered 'Presentation Scheduled _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Presentation Scheduled' stage_ 'Sales Pipeline' pipeline.

Date entered 'Qualified To Buy _Sales Pipeline_' Datetime True True

The date and time when the deal entered the 'Qualified To Buy' stage_ 'Sales Pipeline' pipeline.

Date exited 'Appointment Scheduled _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Appointment Scheduled' stage_ 'Sales Pipeline' pipeline.

Date exited 'Closed Lost _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Closed Lost' stage_ 'Sales Pipeline' pipeline.

Date exited 'Closed Won _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Closed Won' stage_ 'Sales Pipeline' pipeline.

Date exited 'Contract Sent _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Contract Sent' stage_ 'Sales Pipeline' pipeline.

Date exited 'Decision Maker BoughtIn _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Decision Maker Bought_In' stage_ 'Sales Pipeline' pipeline.

Date exited 'Presentation Scheduled _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Presentation Scheduled' stage_ 'Sales Pipeline' pipeline.

Date exited 'Qualified To Buy _Sales Pipeline_' Datetime True True

The date and time when the deal exited the 'Qualified To Buy' stage_ 'Sales Pipeline' pipeline.

Deal amount calculation preference String False True

Specifies how deal amount should be calculated from line items.

Deal probability Decimal False True

The probability a deal will close. This defaults to the deal stage probability setting.

Deal stage probability shadow Decimal True True

Fall back property for calculating the deal stage when no customer override exist. Probability between 0 and 1 of deal stage. Defaults to 0 for unknown deal stages.

Exchange rate Decimal True True

This is the exchange rate used to convert the deal amount into your company currency.

Forecast amount Decimal True True

The custom forecasted deal value calculated by multiplying the forecast probability and deal amount in your company_s currency.

Forecast probability Decimal False True

The custom percent probability a deal will close.

Is Deal Closed_ Bool True True

True if the deal was won or lost.

Is Closed Won Bool True True

True if the deal is in the closed won state_ false otherwise.

Deal Split Added Bool True True

Indicates if the deal is split between multiple users.

Last Modified Date Datetime True True

Most recent timestamp of any property update for this deal. This includes HubSpot internal properties_ which can be visible or hidden. This property is updated automatically.

Likelihood to close by the close date Decimal True True

Hubspot predicted likelihood between 0 and 1 of the deal to close by the close date.

Global Term Line Item Discount Percentage String False True

For internal HubSpot Application use only. Global term for the discount percentage applied.

Global Term Line Item Discount Percentage Enabled Bool False True

For internal HubSpot Application use only. Indicates if the Global term for the discount percentage is enabled.

Global Term Line Item Recurring Billing Period String False True

For internal HubSpot Application use only. Global term for product recurring billing duration.

Global Term Line Item Recurring Billing Period Enabled Bool False True

For internal HubSpot Application use only. Indicates if the Global term for product recurring billing duration is enabled.

Global Term Line Item Recurring Billing Start Date String False True

For internal HubSpot Application use only. Global term for recurring billing start date for a line item.

Global Term Line Item Recurring Billing Start Date Enabled Bool False True

For internal HubSpot Application use only. Indicates if the Global term for recurring billing start date for a line item is enabled.

Global Term Line Item Recurring Billing Frequency String False True

For internal HubSpot Application use only. Global term for how frequently the product is billed.

Global Term Line Item Recurring Billing Frequency Enabled Bool False True

For internal HubSpot Application use only. Indicates if the Global term for how frequently the product is billed is enabled.

Forecast category String False True

The likelihood a deal will close. This property is used for manual forecasting your deals.

Merged object IDs String True True

The list of object IDs that have been merged into this object. This value is automatically set by HubSpot and may not be modified.

Monthly recurring revenue Decimal True True

The monthly recurring revenue _MRR_ of this deal.

Next step String False True

A short description of the next step for the deal.

Number of Active Deal Registrations Decimal True True

The number of active deal registrations associated with this deal. This property is set automatically by HubSpot.

Number of Deal Registrations Decimal True True

The number of deal registrations associated with this deal. This property is set automatically by HubSpot.

Number of Deal Splits Decimal True True

The number of deal splits associated with this deal. This property is set automatically by HubSpot.

Number of target accounts Decimal True True

The number of target account companies associated with this deal. This property is set automatically by HubSpot.

Record ID Decimal True True

The unique ID for this record. This value is automatically set by HubSpot and may not be modified.

Pinned Engagement ID Decimal True True

The object ID of the current pinned engagement. This value is automatically set by HubSpot and may not be modified.

The predicted deal amount Decimal True True

Returns the multiplication of the deal amount times the predicted likelihood of the deal to close by the close date.

The predicted deal amount in your company's currency Decimal True True

Returns the multiplication of the deal amount in your company's currency times the predicted likelihood of the deal to close by the close date.

Priority String False True

Weighted amount Decimal True True

Returns the multiplication of the amount times the probability of the deal closing.

Weighted amount in company currency Decimal True True

Returns the multiplication of the amount in home currency times the probability of the deal closing.

Read Only Object Bool True True

Is the object read only.

Total contract value Decimal True True

The total contract value _TCV_ of this deal.

Time in 'Appointment Scheduled _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Appointment Scheduled' stage_ 'Sales Pipeline' pipeline.

Time in 'Closed Lost _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Closed Lost' stage_ 'Sales Pipeline' pipeline.

Time in 'Closed Won _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Closed Won' stage_ 'Sales Pipeline' pipeline.

Time in 'Contract Sent _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Contract Sent' stage_ 'Sales Pipeline' pipeline.

Time in 'Decision Maker BoughtIn _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Decision Maker Bought_In' stage_ 'Sales Pipeline' pipeline.

Time in 'Presentation Scheduled _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Presentation Scheduled' stage_ 'Sales Pipeline' pipeline.

Time in 'Qualified To Buy _Sales Pipeline_' Decimal True True

The total time in seconds spent by the deal in the 'Qualified To Buy' stage_ 'Sales Pipeline' pipeline.

Unique creation key String True True

Unique property used for idempotent creates.

Updated by user ID Decimal True True

The user that last updated this object. This value is automatically set by HubSpot and may not be modified.

User IDs of all notification followers String True True

The user IDs of all users that have clicked follow within the object to opt_in to getting follow notifications.

User IDs of all notification unfollowers String True True

The user IDs of all object owners that have clicked unfollow within the object to opt_out of getting follow notifications.

User IDs of all owners String True True

The user IDs of all owners of this object.

Owner Assigned Date Datetime True True

The date the most recent deal owner was assigned to a deal. This is updated automatically by HubSpot.

Deal Name String False True

The name given to this deal.

Amount Decimal False True

The total amount of the deal.

Deal Stage String False True

The stage of the deal. Deal stages allow you to categorize and track the progress of the deals that you are working on.

Pipeline String False True

The pipeline the deal is in. This determines which stages are options for the deal.

Close Date Datetime False True

Date the deal was closed. This property is set automatically by HubSpot.

Create Date Datetime False True

Date the deal was created. This property is set automatically by HubSpot.

Date of last meeting booked in meetings tool Datetime True True

The date of the most recent meeting an associated contact has booked through the meetings tool.

Campaign of last booking in meetings tool String True True

This UTM parameter shows which marketing campaign _e.g. a specific email_ referred an associated contact to the meetings tool for their most recent booking. This property is only populated when you add tracking parameters to your meeting link.

Medium of last booking in meetings tool String True True

This UTM parameter shows which channel _e.g. email_ referred an associated contact to the meetings tool for their most recent booking. This property is only populated when you add tracking parameters to your meeting link.

Source of last booking in meetings tool String True True

This UTM parameter shows which site _e.g. Twitter_ referred an associated contact to the meetings tool for their most recent booking. This property is only populated when you add tracking parameters to your meeting link.

Latest meeting activity Datetime True True

The date of the most recent meeting _past or upcoming_ logged for_ scheduled with_ or booked by a contact associated with this deal.

Recent Sales Email Replied Date Datetime True True

The last time a tracked sales email was replied to for this deal.

Deal owner String False True

User the deal is assigned to. Assign additional users to a deal record by creating a custom user property.

Last Contacted Datetime True True

The last time a call_ sales email_ or meeting was logged for this deal. This is set automatically by HubSpot based on user actions.

Last Activity Date Datetime True True

The last time a note_ call_ email_ meeting_ or task was logged for a deal. This is updated automatically by HubSpot.

Next Activity Date Datetime True True

The date of the next upcoming activity for a deal. This property is set automatically by HubSpot based on user action. This includes logging a future call_ sales email_ or meeting using the Log feature_ as well as creating a future task or scheduling a future meeting. This is updated automatically by HubSpot.

Number of times contacted Decimal True True

The total number of sales activities _notes_ calls_ emails_ meetings_ or tasks_ logged for a deal. This is updated automatically by HubSpot.

Number of Sales Activities Decimal True True

The total number of times a sales email or call has been logged for a deal. This is updated automatically by HubSpot.

HubSpot Create Date Datetime True True

The date the deal was created. This property is set automatically by HubSpot.

HubSpot Team String True True

Primary team of the deal owner. This property is set automatically by HubSpot.

Deal Type String False True

The type of deal. By default_ categorize your deal as either a New Business or Existing Business.

All owner ids String True True

The value of all owner referencing properties for this object_ both default and custom.

Deal Description String False True

Description of the deal.

All team ids String True True

The team ids corresponding to all owner referencing properties for this object_ both default and custom.

All accessible team ids String True True

The team ids_ including up the team hierarchy_ corresponding to all owner referencing properties for this object_ both default and custom.

Number of Associated Contacts Decimal True True

The number of contacts associated with this deal. This property is set automatically by HubSpot.

Closed Lost Reason String False True

Reason why this deal was lost.

Closed Won Reason String False True

Reason why this deal was won.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062