在 Snowflake 中查询 HubSpot 数据
上次更新时间: 二月 13, 2025
除非另有说明,否则适用于以下任何订阅:
|
将 Snowflake 连接到您的 HubSpot 账户后,您可以使用 Snowflake 的数据共享从您的 Snowflake 账户查询 HubSpot 数据。
数据滞后和您的 Snowflake 帐户区域
您可以访问每 15 分钟更新一次的 V2_LIVE 模式和每天更新一次的 V2_DAILY 模式。
请注意:V2_LIVE 模式中的以下视图仅每日更新:关联定义、所有者、管道和管道阶段。
设置您的 Snowflake 环境
要在 Snowflake 中查询 HubSpot 数据,您需要选择要查询的仓库、数据库和模式。您可以在工作表级别进行设置,使其自动包含在查询中,也可以手动添加。
在工作表级别选择仓库、数据库和模式:
- 在工作表顶行,单击工作表操作栏。
- 在弹出窗口中
配置好环境后,你就可以对 HubSpot 数据进行查询,而无需在查询中手动添加数据库或模式类型。
要在查询中手动添加数据库和模式类型,请在FROM
字段开头添加数据库名称和模式。例如
SELECT objectTypeId, property_createddate
FROM hubspot_share_name.V2_LIVE.objects_deals
LIMIT 1
数据概览
查询使用 SQL 构建,可用于检索 HubSpot 数据,如
- 对象: 标准和自定义对象,如联系人、公司、产品。
- 记录: 个人记录,如个人联系人或公司。
- 关联:所有可用的关联类型和当前关联记录。
- 事件:标准 HubSpot 事件(如电子邮件打开)和自定义行为事件的事件数据。
- 列表:HubSpot联系人和公司列表。
- 所有者:HubSpot 账户中的用户,与他们被设置为所有者的记录相关。
- 管道: 交易和票据管道,包括管道阶段。
- 属性:客户关系管理记录上的属性及其值,包括属性历史记录。
了解有关 HubSpot CRM 数据模型的更多信息 。
请注意: 随着数据共享的不断发展,我们将引入一些变更,其中一些变更是破坏性的。强烈建议订阅HubSpot 开发人员变更日志。这些更改会在任何更新之前发布到更改日志中。
对返回的数据进行限制和排序
在构造查询时,可以通过在查询中包含LIMIT
和ORDER BY
来控制返回数据的数量和顺序。例如,要查询所有联系人及其属性,但只返回三个结果,并按联系人 ID 排序,可以按如下方式构造查询:
SELECT objectTypeId, updatedAt, ingestedAt
FROM objects_deals
ORDER BY objectId
LIMIT 10;
列数据类型
在 Snowflake 中查询数据时,返回的列取决于您查询的数据。您可以通过选择 左侧边栏中的 视图来查看 Snowflake 中给定表或视图的可用列 。
请注意:在本文档的其余部分,当上下文同时适用于表和视图时,我们使用数据库对象一词。
要返回特定列,请在查询的SELECT
字段中包含该列。例如,以下查询将只返回OBJECTID
和UpdatedAt
列:
SELECTobjectId, updatedAt
FROM objects_contacts
以下是查询可能返回的列的一般定义。如需了解下文未包含的其他定义,请参阅上述文章中的各个视图定义。
- OBJECTTYPEID:代表对象类型的VARCHAR值。例如,0-1表示联系人对象,0-2表示公司对象,2-1232可能表示账户中的自定义对象。在列表中,这指的是列表类型(如联系人或公司)。您可以使用object_and_event_type_definitions视图查询账户中的所有对象和事件 ID。
- OBJECTID:一个BIGINT数字,代表 HubSpot 账户中特定类型对象的一条记录。要识别一条记录,需要同时使用对象类型 ID 和对象 ID。
- INGESTEDAT:数据行添加到 Snowflake 的时间戳,用TIMESTAMP_NTZ值表示。与 HubSpot 中创建或更新操作的逻辑时间戳无关。
- 由于 HubSpot 的内部处理,即使行上的其他列没有变化,行的摄取时间戳也可能被更新。
- 在构建复制最近更改数据的ETL 流程时,建议根据摄取时间戳进行查询。此外,如果您的流程每天复制数据,建议复制前两天的数据,以考虑延迟因素。
- CREATEDAT 和 UPDATEDAT:数据在 HubSpot 中创建或更新的逻辑时间戳,用TIMESTAMP_NTZ 值表示。这些时间戳将与 HubSpot 中的时间戳相匹配。例如,HubSpot 中显示的记录创建日期时间将与 Snowflake 中CREATEDAT列中的时间戳相同。这些与摄取时间戳没有直接关系,不建议将ETL 流程建立在这些属性的基础上。
- COMBINEDASSOCIATIONTYPE:一个VARCHAR值,唯一标识关联定义。
转换数据类型
有时,从查询中返回的数据包含VARCHAR列,而我们希望将它们转换为不同的类型。在这种情况下,建议使用 Snowflake 函数将 VARCHAR 数据类型转换为所需类型。例如,在大多数数据库对象中,对象记录的属性都存储为 VARCHAR 数据类型,但有时也包含数字和日期时间值。可以使用以下函数
try_to_number、 try_to_timestamp_ntz等函数可用于将这些 VARCHAR 数据分别转换为NUMBER和DATETIME类型:
SELECT objectid, value AS amount_str, try_to_number(value) AS amountFROM object_properties
WHERE objecttypeid='0-3' AND name='amount'
LIMIT 1
对象查询
下面,我们将学习如何从特定的 HubSpot 对象和记录中查询数据以及返回的数据。
不同类型的对象数据以两种方式组织和提供:
- 单个:每个对象类型存储在自己的视图中,只包含相同对象类型的记录,例如 objects_contacts 只包含联系人记录。这样就无需在查询时指定对象类型 ID。由于不需要知道 ObjectTypeIds,所以这些视图更容易查询。它们还能以更实用的格式返回数据,每一行都对应一个对象及其属性的当前版本。只要查询速度不慢,建议使用这些视图来检索对象数据。更多信息请参阅下面的 Objects_X 视图。
- 合并: 所有对象类型的所有记录都合并为一个数据库对象。因此,在查询时,需要指定类似 "WHERE objectTypeId='0-1'"的过滤器。例如,查询时可以使用以下 objecttypeids:
对象名称 | 对象类型标识 |
联系方式 | 0-1 |
公司 | 0-2 |
细列项目 | 0-8 |
自定义对象将在创建时分配一个 ID,标识为2-unique_ID。例如,2-12345。了解有关object_and_event_type_definitions 的更多信息。
如果希望获得比 objects_x 视图更好的查询性能,这些数据库对象通常是合适的。更多详情,请参阅下面的 object_with_object_properties、object_properties、object 和 object_properties_history。
对象_x视图
在该数据库对象中,每个可用的对象类型(包括自定义对象)都有自己特定的视图,每个视图只包含该特定对象类型的对象记录。这些视图的命名格式为 OBJECTS_<对象类型名称>,其中 <对象类型名称> 是对象类型的复数形式标签(请注意,如果对象类型没有复数形式,则 <对象类型名称> 只是对象类型名称的单数形式标签,并与 "S "连接)。
对该视图的查询会为对象中的每条记录返回一行,对象的每个属性都作为单独的列。
请参阅下面的可用 HubSpot(即非定制)对象类型综合列表、它们所代表数据的简要说明以及相应的视图:
HubSpot 对象类型 |
对象类型标识 |
说明 |
0-1 |
与您的企业互动的个人相关信息 |
|
0-2 |
有关个别企业或组织的信息 |
|
0-3 |
有关联系人或公司收入机会的详细信息。 |
|
0-4 |
存储来自 CRM 操作的数据,包括备注、任务、电子邮件、会议和通话。 |
|
0-5 |
代表客户寻求帮助或支持。 |
|
0-14 |
用于与潜在买家分享定价信息。 |
|
0-15 |
HubSpot 表单个人提交的详细信息。 |
|
0-8 |
代表交易中销售产品的子集。产品附加到交易后,就成为一个细列项目。 |
|
0-11 |
多渠道接收信息的详细信息。 |
|
0-25 |
登陆页面的详细信息 |
|
0-27 |
存储待办事项信息 |
|
0-28 |
用于收集访客和联系人的线索信息 |
|
0-29 |
营销中心电子邮件的详细信息 |
|
AD_ACCOUNT |
0-30 |
存储您在 Linkedin、Facebook 和 Google 上的广告账户信息 |
0-31 |
有关广告系列的详细信息。一个广告系列可包含一个或多个广告 |
|
AD_GROUP |
0-32 |
在广告系列中对广告进行逻辑分组 |
AD |
0-33 |
单个广告的详细信息 |
0-35 |
相关营销资产和内容的信息,方便您衡量集体营销工作的效果。 |
|
网站页面 |
0-38 |
有关网站单个页面的数据 |
0-39 |
博客文章数据 |
|
目标 清单 |
0-45 |
基于属性或活动的对象记录分组信息 |
0-48 |
其他 CRM 记录(如联系人)拨打的电话 |
|
发票 |
0-53 |
管理发票并与外部会计系统同步 |
0-57 |
有关导入 Hubspot 的媒体资产的信息 |
|
0-58 |
序列是一系列有针对性、定时的电子邮件模板,用于在一段时间内培养联系人 |
|
0-72 |
交易拆分用于在多个用户之间共享交易积分 |
|
0-83 |
销售文件可为整个团队建立一个内容库,以便上传文件并与联系人共享。 |
|
0-19 |
存储提交给反馈调查的信息。 |
|
0-69 |
订阅包含定期付款的详细信息 |
|
0-101 |
包含从客户处收集的资金数据 |
每个数据库对象将返回以下列:
列名 |
类型 |
说明 |
OBJECTTYPEID |
Varchar |
对象类型的 ID(如联系人为 0-1)。 |
项目编号 |
数量 |
记录的 ID(如 ID 为 123752 的联系人记录)。 |
更新日期 |
时间戳 |
此对象在 HubSpot 中最后一次更新的日期和时间。 |
INGESTEDAT |
时间戳 |
Snowflake 上次摄取数据的日期和时间。 |
属性_* |
Varchar |
包含对象属性最新版本的单个列。对于具有日期或数字类型(由property_definitions 指定)的每个对象属性,将有两列:
|
示例 1
检索所有联系人的名、姓和生命周期阶段。
查询:
SELECT objectId, property_firstname AS firstname, property_lastname AS lastname,property_lifecyclestage AS lifecyclestageFROM objects_contact
返回:
项目编号 |
姓名 |
姓氏 |
生命周期 |
54325098 |
霍布斯 |
男爵 |
领导 |
96805401 |
米洛 |
金色 |
领导 |
6922028 |
亨利 |
莱利 |
领导 |
示例 2
查询:
SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmountFROM objects_deals
LIMIT 3
返回:
项目编号 |
物业名称 |
交易阶段 |
物业交易额 |
38960439 |
交易名称 1 |
闭园 |
34199 |
321740651 |
测试交易 123 |
14845019 |
8383663 |
98392101 |
弹出式顶篷 |
闭园 |
9309007 |
示例 3
按金额检索前三笔交易的交易名称和阶段。
查询:
由于交易量数据是数字数据类型,因此无需转换或转换就可用于排序。
SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmountFROM objects_deals
ORDER BY property_amount DESC
LIMIT 3
返回:
项目编号 |
物业名称 |
交易阶段 |
物业交易额 |
98392101 |
弹出式顶篷 |
闭园 |
9309007 |
321740651 |
测试交易 123 |
14845019 |
8383663 |
38960439 |
交易名称 1 |
闭园 |
34199 |
带对象属性的对象
该数据库对象包含来自 HubSpot 记录的数据。对该数据库对象的查询会为对象类型中的每条记录返回一条记录(例如,联系人对象类型中的所有联系人记录)。所有属性都存储在 "属性"列中,这是一个Snowflake 变体/JSON列。
该视图可以返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 对象类型的 ID(例如,联系人是0-1 )。 |
OBJECTID |
数量 | 记录的 ID(如 ID 为123752 的联系人记录)。 |
PROPERTIES |
变体 | 记录的财产价值。 |
UPDATEDAT |
时间戳 | 记录在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 接收的日期和时间。 |
请注意: 2022 年 1 月 25 日,object_with_object_properties表和视图中表示对象记录在 HubSpot 中最后更新日期和时间的列timestamp将改为updatedat。这是为了与 其他数据共享中表示日期时间数据的方式保持一致 。
由于这是一项重大变更,因此已在表和视图中 添加了新列 updatedat。在接下来的 90 天内,这两个新列将与它们要取代的列 timestamp 共存。
90 天后,即 2022 年 1 月 25 日,列 timestamp 将从表和视图中移除。在此日期之后,任何使用时间戳列查询object_with_object_properties表或视图的尝试都将失败。
示例 1
检索所有联系人及其属性。
查询:
SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
返回:
OBJECTTYPEID | 项目编号 | 物业 |
0-1 | 38960439 | {"创建日期":"1504656970152"...} |
0-1 | 321740651 | { "createdate":"1590183081949"...} |
0-1 | 98392101 | { "createdate":"1533566477279"...} |
... | ... | ... |
示例 2
最多可检索三个交易的名称和阶段。
查询:
SELECTobjectId,
properties:dealname::string AS dealname,
properties:dealstage::string AS dealstage,
properties:amount::number AS dealamount
FROM object_with_object_properties
WHERE objectTypeId = '0-3'
LIMIT 3;
返回:
项目编号 | DEALNAME | 交易阶段 | 交易额 |
38960439 | 交易名称 1 | 关闭丢失 | 7488939 |
321740651 | 交易名称 1 | 闭园 | 9292029 |
98392101 | 交易名称 1 | 关闭丢失 | 62626528 |
物件
该数据库对象是 object_with_object_properties 数据库对象中数据的子集。每条返回的记录都会返回每条记录的对象 ID。该数据库对象只返回现有记录。在 HubSpot 中删除记录时,该记录也会从该数据库对象中删除。该数据库对象是 object_with_object_properties 表中数据的子集。每条返回的记录都会返回每条记录的对象 ID。此视图只返回现有记录。在 HubSpot 中删除记录时,该记录也将从该视图中删除。
如果账户中的数据量较大,导致 object_with_object_properties 和 objects_x 视图执行缓慢,那么这个数据库对象就会非常有用。将此数据库对象与 object_properties 数据库对象一起使用,可以复制 object_with_object_properties 中的数据,同时保持对返回哪些属性的控制。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 对象类型的 ID(例如,联系人是0-1 )。 |
OBJECTID |
数量 | 记录的 ID(如 ID 为123752 的联系人记录)。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 采集的日期和时间。了解有关列数据类型的更多信息。 |
示例 1
检索所有联系人记录。
查询:
SELECT objectTypeId, objectId
FROM objects
WHERE objectTypeId = '0-1'
返回:
OBJECTTYPEID | 项目编号 |
0-1 | 36721864 |
0-1 | 103580363 |
0-1 | 56047670 |
... | ... |
对象属性
该数据库对象是object_with_object_properties数据库对象的子集。每一行都包含给定记录上设置的当前属性值。例如,联系人的"名"属性和 "姓"属性各有一行。如果一条记录没有属性值,该行要么显示为空字符串,要么根本不显示。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 对象类型的 ID(例如,联系人是0-1 )。 |
OBJECTID |
数量 | 记录的 ID(如 ID 为123752 的联系人记录)。 |
NAME |
Varchar | 属性名称。 |
VALUE |
时间戳 | 财产的价值。 |
UPDATEDAT |
时间戳 | 记录在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 接收的日期和时间。 |
示例 1
读取所有联系人的名、姓和城市属性。
查询:
SELECT objectTypeId, objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;
返回:
OBJECTTYPEID | 项目编号 | 姓名 | 价值 |
0-1 | 10401 | 姓名 | 布莱恩 |
0-1 | 10401 | 城市 | 都柏林 |
0-1 | 10401 | 姓氏 | 盖勒格 |
0-1 | 23451 | 姓名 | 费尔南多 |
0-1 | 23451 | 姓氏 | 菲耶罗 |
0-1 | 25751 | 姓名 | 查尔斯 |
... | ... | ... | ... |
示例 2
读取所有联系人的名、姓和生命周期阶段,并将其合并为每个联系人的一行。此示例使用SQL PIVOT 语法。
查询:
SELECT objectId, firstname, lastname, lifecyclestage
FROM
(
SELECT objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'lifecyclestage')
)
PIVOT(MAX(value) FOR name IN ('firstname', 'lastname', 'lifecyclestage')) AS p(objectId, firstname, lastname, lifecyclestage);
返回:
OBJECTTYPEID | 项目编号 | 姓名 | 姓氏 | 生命周期 |
0-1 | 54325098 | 霍布斯 | 男爵 | 领导 |
0-1 | 96805401 | 米洛 | 金色 | 领导 |
0-1 | 6922028 | 亨利 | 莱利 | 领导 |
... | ... | ... | ... | ... |
对象属性历史
该数据库对象包含历史属性值。对于联系人,它包含最近的 45 个值。对于所有其他对象,它包含最近的 20 个值。为每个历史属性值显示一行。属性的当前值由属性历史记录中最新的 UPDATEDAT 时间戳决定。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 对象类型的 ID(例如,联系人是0-1 )。 |
OBJECTID |
数量 | 记录的 ID(如 ID 为123752 的联系人记录)。 |
NAME |
Varchar | 属性名称。 |
VALUE |
时间戳 | 财产的价值。 |
UPDATEDAT |
时间戳 | 数据在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索单个联系人的单个属性的历史记录。
查询:
SELECT objectId, name, value, updatedAt
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 136493192
AND name = 'hs_predictivescoringtier'
ORDER BY UPDATEDAT DESC;
返回:
项目编号 | 姓名 | 价值 | 更新日期 |
136493192 |
hs_predictivescoringtier |
层级_1 |
2022-06-22 22:45:05.931 |
136493192 |
hs_predictivescoringtier |
第 2 层 |
2021-09-18 08:20:56.622 |
136493192 |
hs_predictivescoringtier |
第 3 层 |
2019-11-31 18:20:22.851 |
... |
... |
... |
... |
示例 2
读取生命周期阶段为订阅者的所有联系人的当前姓名。
查询:
SELECT
objectId,
properties:firstname::string AS firstname,
properties:lastname::string AS lastname
FROM object_with_object_properties
WHERE object_with_object_properties.objectTypeId = '0-1'
AND objectId IN (
SELECT objectId
FROM object_properties_history
WHERE
object_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);
返回:
项目编号 | 姓名 | 姓氏 |
54325098 | 霍布斯 | 男爵 |
96805401 | 米洛 | 金色 |
6922028 | 亨利 | 莱利 |
... | ... | ... |
关联查询
与对象数据一样,关联数据也有两种组织方式:
- 个性化:每种关联类型都存储在自己的视图中,只包含该类型的记录。例如,associations_contacts_to_deals 只包含联系人和交易对象之间的关联记录。这样就不需要在查询时指定关联类型 ID。这些视图不需要熟悉客户关系管理数据模型就能使用,因此查询起来非常简单。这些视图还能返回两个对象之间关联记录的丰富描述,在与 objects_x 视图连接时可以轻松使用。只要查询速度不慢,建议使用这些视图来检索关联数据。更多信息请参阅下面的关联_x_to_y 视图。
- 合并:所有关联类型的所有记录都合并到一个数据库对象中。因此,在查询时,需要指定一个过滤器,如 "WHERE combinedassociationtypeid='0-32'"。请参阅下文关联和关联定义中的更多内容。
关联_x_to_y视图
每种关联类型都有自己的视图,标记为 associations_<关联类型名称>,例如 associations_contacts_to_deals。每个视图都是关联数据库对象中数据的子集,只包含特定组合关联类型 ID 的记录。
每个数据库对象将返回以下列:
列名 |
类型 |
说明 |
合并协会类型标识 |
Varchar |
关联定义的唯一标识符。 |
关联类别 |
Varchar |
关联类型的来源。其中之一:hubspot_defined, user_defined, integrator_defined |
associationtypeid |
Varchar |
特定关联类别中关联定义的唯一标识符,即一个关联类别中不会有两个定义具有相同的 associationtypeid。 |
fromobjecttypeid |
Varchar |
关联定义来源的 objecttypeid。 |
从对象类型 |
Varchar |
关联定义来源的对象类型名称。 |
TOOBJECTTYPEID |
Varchar |
关联定义的目标 objecttypeid。 |
太物体类型 |
Varchar |
关联定义的目标对象类型名称。 |
姓名 |
Varchar |
关联定义的名称。 |
标签 |
Varchar |
关联定义的说明。 |
主要协会定义 |
布尔型 |
关联定义是否为主类型。如果为 "true",HubSpot 将在关联记录上显示该定义。 |
[FROMOBJECTTYPE_OBJECTID] 例如,对于 ASSOCIATIONS_CONTACTS_TO_DEALS 视图,这将是 CONTACT_OBJECTID |
数量 |
源对象的对象 ID。 |
[TOOBJECTTYPE_OBJECTID] 例如,对于 ASSOCIATIONS_CONTACTS_TO_DEALS 视图,这将是 DEAL_OBJECTID |
数量 |
目标对象的对象 ID。 |
INGESTEDAT |
时间戳 |
Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索与特定联系人相关的所有交易。
查询:
SELECT
contact_objectId、deal_objectId
FROM associations_contacts_to_deals
WHERE contactId = 6626541373
返回:
contact_objectid |
DEAL_OBJECTID |
6626541373 |
233620335 |
6626541373 |
90253678 |
示例 2
检索波哥大时区的所有联系人及其交易。
SELECT contacts.property_firstname contact_firstname、
contacts.property_lastname contact_lastname,
contacts.property_email contact_email,
deals.property_dealtype deal_type, deals.property_amount deal_amount
FROM objects_contacts contacts
JOIN associations_contacts_too_deals associations on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
where contacts.property_hs_timezone='america_slash_bogota'.
联系人姓名 |
联系人姓氏 |
联系电子邮件 |
交易类型 |
交易金额 |
戴维 |
马加良斯 |
dmagalhaes@example.com |
新业务 |
74848 |
费尔南达 |
罗德里格斯 |
fmoreira@example.com |
新业务 |
253530 |
示例 3
检索所有联系人和每个联系人的交易完成总金额。
SELECT SUM(deals.property_hs_closed_amount) AS
total_closed_amount, contacts.property_email contact_email
FROM objects_contacts contacts
JOIN associations_contacts_too_deals assoc on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
GROUP BY contact_email
ORDER BY total_closed_amount DESC
返回:
关闭总金额 |
联系电子邮件 |
98474793 |
johndoe@riverview.com |
849488 |
sallymae@longford.com |
关联定义
该数据库对象包含 HubSpot 对象之间所有可用关联类型的信息,每个关联类型有一行。你可以将此数据库对象与关联数据库对象连接起来,获取特定记录的关联信息。
同一对象之间可以有多种关联类型。主要关联类型是记录关联时在 HubSpot 中显示的类型,使用ISMAINASSOCIATIONDEFINITION
为true
的关联。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
COMBINEDASSOCIATIONTYPEID |
Varchar | 关联定义的唯一标识符。 |
CATEGORY |
Varchar | 关联类型的来源。其中之一:HUBSPOT_DEFINED ,USER_DEFINED 、INTEGRATOR_DEFINED |
ID |
数量 | 类别中关联定义的标识符。 |
FROMOBJECTTYPEID |
Varchar | 关联定义来源对象的 ID。 |
TOOBJECTTYPEID |
Varchar | 关联定义目标对象的 ID。 |
NAME |
Varchar | 关联定义的名称。 |
LABEL |
Varchar | 关联定义的标签。 |
ISMAINASSOCIATIONDEFINITION |
布尔型 | 关联定义是否为主类型。为真时,HubSpot 会在关联记录上显示该定义。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 采集的日期和时间。了解有关列数据类型的更多信息。 |
示例 1
检索账户中可用的关联类型。
查询:
SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY category, id
返回:
合并协会类型 | fromobjecttypeid | TOOBJECTTYPEID | 姓名 |
0-1 | 0-1 | 0-2 | 公司联系人 |
0-2 | 0-2 | 0-1 | 公司联系方式 |
0-3 | 0-3 | 0-1 | 交易到联系人 |
0-4 | 0-1 | 0-3 | 联系交易 |
0-5 | 0-3 | 0-2 | deal_too_company |
... | ... | ... | ... |
示例 2
检索交易与公司之间的可用关联类型。
查询:
SELECT
combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = trueORDER BY category, id
返回:
合并协会类型 | fromobjecttypeid | TOOBJECTTYPEID | 姓名 |
0-5 | 0-3 | 0-2 | deal_too_company |
0-271 | 0-3 | 0-2 | 交易至主要公司 |
0-341 | 0-3 | 0-2 | deal_too_comapny_unlabeled |
协会
该数据库对象包含特定记录之间关联的信息。每一行代表一个对象与另一个对象之间的关联。即使引用的记录已被删除,该数据库对象中仍可能存在记录。可以通过与对象表连接来检查记录是否存在。
如果账户数据量较大,导致关联_x_to_y 视图执行缓慢,则此数据库对象可能会很有用。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
COMBINEDASSOCIATIONTYPEID |
Varchar | 关联定义的唯一标识符。 |
FROMOBJECTID |
数量 | 源对象的 ID。 |
TOOBJECTID |
数量 | 目标对象的 ID。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 采集的日期和时间。了解有关列数据类型的更多信息。 |
示例 1
检索所有相关交易和联系人。
查询:
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE combinedAssociationTypeId = '0-5'
返回:
合并协会类型标识 | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
0-5 | 6616436082 | 3866015468 |
0-5 | 6690805943 | 7132752747 |
... | ... | ... |
示例 2
检索与特定联系人(联系人 ID:3005)相关的所有票单。
查询:
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;
返回:
合并协会类型 | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
示例 3
检索与特定联系人(联系人 ID:4464)相关的所有交易。此示例没有硬编码combinedAssociationTypeId,而是加入了association_definitions。
查询:
SELECT combinedAssociationTypeId, fromObjectId toObjectId,
FROM associations
WHERE
combinedAssociationTypeId = (
SELECT combinedAssociationTypeId
FROM association_definitions
WHERE fromObjectTypeId = '0-1'
AND toObjectTypeId = '0-3'
AND isMainAssociationDefinition)
AND fromObjectId = 4464;
返回:
合并协会类型标识 | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
业主询问
车主
该数据库对象包含 HubSpot 账户中的用户信息。用户可被设置为记录所有者,并根据其上下文同时包含用户 ID 和所有者 ID。这两个 ID 可用于识别同一个用户。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OWNERID |
数量 | 用户的所有者 ID。 |
USERID |
数量 | 用户的用户 ID。该列为空的用户已被删除。 |
EMAIL |
Varchar | 用户的电子邮件地址。 |
FIRSTNAME |
Varchar | 用户的名字。 |
LASTNAME |
Varchar | 用户的姓氏。 |
ARCHIVED |
布尔型 | 用户是否已被删除。 |
CREATEDAT |
时间戳 | 用户在 HubSpot 中创建的日期和时间。有关详细信息,请参阅列数据类型。 |
UPDATEDAT |
时间戳 | 用户在 HubSpot 中最后一次更新的日期和时间。有关详细信息,请参阅列数据类型。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索所有用户。WHERE archived = false
过滤器会从返回的数据中删除已删除的用户。
查询:
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE archived = false
返回:
业主 ID | 用户名 | 电子邮件 | 姓名 | 姓氏 |
29584574 | 4621126 | 233620335 | 霍布斯 | 男爵 |
30532717 | 4874784 | 3866015468 | 巴勃罗 | 沃尔特斯 |
30580321 | 925511 | 7132752747 | 米洛 | 金色 |
... | ... | ... | ... | ... |
示例 2
根据所有者 ID 检索特定所有者。
查询:
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE ownerId = 29584574;
返回:
业主 ID | 用户名 | 电子邮件 | 姓名 | 姓氏 |
29584574 | 4621126 | 233620335 | 霍布斯 | 男爵 |
示例 3
检索所有联系人及其所有者。此查询使用object_with_object_properties和联系人属性hubspot_owner_id 将记录与所有者连接。
SELECT
contact.objectId contactid,
contact.property_firstname contact_firstname,
contact.property_lastname contact_lastname,
contact.property_hubspot_owner_id contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM objects_contacts contact
JOIN owners owner ON contact.property_hubspot_owner_id = owner.ownerId
返回:
联系地址 | 联系人姓名 | 联系人姓氏 | contact_hubspot_owner_id | 业主姓名 | 业主姓氏 | 业主电子邮件 |
113834202 | 理查德 | 绿地 | 29584574 | 霍布斯 | 男爵 | hobbes.b@business.com |
53540801 | 福特 | 卡尔 | 30241125 | 米洛 | 金色 | milo.g@business.com |
... | ... | ... | ... | ... | ... | ... |
团队询问
团队
HubSpot 中的 "团队 "可让您将用户分为若干组,以便进行组织和报告。您可以拥有一个主团队和额外团队。
该数据库对象包含有关 HubSpot 账户中团队的信息。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
TEAMID | 数量 | 团队 ID。 |
队名 | Varchar | 队名 |
PARENTTEAMID | 数量 | 母队的队徽。 |
儿童艾滋病 | 阵列 | 子团队的团队 ID。 |
初级会员 | Varchar | 团队主要成员的所有者 ID。 |
二级成员 | 布尔型 | 团队二级成员的所有者 ID。 |
INGESTEDAT | 时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索所有团队。
查询:
SELECT teamId,teamName, primaryOwnerMembers
从团队
返回:
TEAMID | 队名 | 初级会员 |
29584574 | 分队 | [233620335, 1234553] |
30532717 | 超级团队 | [3866015468, 126221873] |
3058032 | 一个团队 | [7132752747] |
业主团队
该数据库对象包含 HubSpot 账户中哪些所有者对应哪些团队的信息。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
TEAMID | 数量 | 团队 ID。 |
业主 ID | 数量 | 用户的所有者 ID。 |
初等教育 | 布尔型 | 这个团队是否是这个老板的主要团队。 |
INGESTEDAT | 时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索指定球队的所有所有者。
查询:
SELECT teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574
返回
TEAMID | 队名 | 初等教育 |
29584574 | 233620335 | 真 |
29584574 | 1234553 | 真 |
管道查询
下面将学习如何查询 交易和票据管道数据,如账户中的可用管道及其阶段。
您可以将这些数据与对象数据连接起来,以获得管道的完整视图。例如,通过将 objects_deals_view 或object_with_object_properties数据与管道数据连接起来,可以查询交易及其管道。
管道
此数据库对象包含有关您账户中交易和票据管道的信息。返回的每一行代表一条管道。
该表可以返回以下列:
列名 | 类型 | 说明 |
PIPELINEID |
Varchar | 管道的 ID。 |
OBJECTTYPEID |
Varchar | 管道可包含的对象(如交易或票据)的 ID。 |
LABEL |
Varchar | 管道在 HubSpot 中的名称。 |
DISPLAYORDER |
数量 | 管道在 HubSpot 中的显示顺序。 |
ARCHIVED |
布尔型 | 管道是否已被删除。 |
CREATEDAT |
时间戳 | 管道创建时间。 |
UPDATEDAT |
时间戳 | 数据在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
按 HubSpot 中的顺序检索所有可用的交易管道。
查询:
SELECT objectTypeId, pipelineId, label
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;
返回:
OBJECTTYPEID | 管道标识 | 标签 |
0-3 | 1bed503c-37f7-4f51-9d40-7598902673f4 | 销售管道 |
0-3 | 12659678 | 许可证管道 |
0-3 | 75e28846-ad0d-4be2-a027-5e1da6590b98 | 新业务 |
... | ... | ... |
示例 2
检索特定管道中的交易。连接object_with_object_properties和管道。
查询:
SELECT
deals.objectId deal_id,
deals.property_dealname deal_name,
pipelines.label pipeline_name
FROM objects_deals deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.property_pipeline = pipelines.pipelineid
WHERE pipelines.objectTypeId = '0-3';
返回:
DEAL_ID | 交易名称 | 管道名称 |
605140072 | Meowmix 全球注册 | 销售管道 |
605457408 | Friskies 品牌重塑 | 销售渠道 |
604362473 | 新鲜步骤应用程序构建 | 销售管道 |
示例 3
与上面的例 2 相同,检索特定管道内的交易。但是,在连接中不要使用 objects_deals,而要使用object_with_object_properties。
SELECT
deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
WHERE deals.objecttypeId = '0-3'
AND pipelines.objectTypeId = '0-3';
返回:
DEAL_ID |
交易名称 |
管道名称 |
605140072 |
Meowmix 全球注册 |
销售管道 |
605457408 |
Friskies 品牌重塑 |
销售管道 |
604362473 |
新鲜步骤应用程序构建 |
销售管道 |
管道阶段
该视图包含管道内各个阶段的信息。通过组合对象类型 ID、管道 ID 和阶段 ID 来识别阶段。与管道连接时,使用对象类型 ID 和管道 ID。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
PIPELINEID |
Varchar | 管道的 ID。 |
STAGEID |
Varchar | 舞台的 ID。 |
LABEL |
Varchar | HubSpot 中的阶段名称。 |
DISPLAYORDER |
数量 | 管道在 HubSpot 中的显示顺序。 |
METADATA |
变体 | 关于舞台的 JSON 格式描述,包括舞台是开放还是关闭 |
ARCHIVED |
布尔型 | 管道阶段是否已删除。 |
CREATEDAT |
时间戳 | 管道阶段创建的时间。 |
示例 1
检索名为销售管道的交易管道的管道阶段。
查询:
SELECT pipelineid, stageId, label
FROM pipeline_stages
WHERE not archived
AND pipelineid = (
SELECT pipelineid
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
AND label = 'Sales Pipeline' LIMIT 1)
ORDER BY displayorder;
返回:
管道标识 | 阶段标识 | 标签 |
941650 | 941651 | 已联系 |
941650 | 941652 | 预约 |
941650 | 941653 | 发送发票 |
... | ... | ... |
示例 2
检索所有交易及其各自的管道阶段。
查询:
SELECTdeals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name,
pipeline_stages.label pipeline_stage_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
INNER JOIN pipeline_stages
ON deals.objectTypeId = pipeline_stages.pipelineid
AND deals.properties:pipeline::string = pipeline_stages.pipelineid
AND deals.properties:dealstage::string = pipeline_stages.stageId
WHERE deals.objecttypeId = '0-3';
返回:
DEAL_ID | 交易名称 | 管道名称 | 管道阶段名称 |
605140072 | Meowmix 全球注册 | 销售管道 | 发送合同 |
605457408 | Friskies 品牌重塑 | 销售管道 | 预约 |
604362473 | 新鲜步骤应用程序构建 | 销售管道 | 签署合同 |
... | ... | ... | ... |
物业查询
属性定义
该数据库对象包含有关 HubSpot 账户中对象属性和事件属性的信息。事件属性包括标准事件(如电子邮件打开和点击)和自定义行为事件属性。这些信息有助于定义object_properties和事件数据中的值。
你可以通过组合对象类型 ID 和名称来识别特定属性。您可以使用属性 API 获取类似信息。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTYPEID |
Varchar | 事件相关对象的 ID。 |
NAME |
Varchar | 属性的内部名称。 |
LABEL |
Varchar | 物业标签。 |
DESCRIPTION |
Varchar | 物业描述 |
TYPE |
Varchar | 属性类型(如字符串、数字、日期时间)。 |
FIELDTYPE |
Varchar | 属性的字段类型。定义属性在 HubSpot 和表单中的显示方式(如复选框、选择)。 |
DISPLAYORDER |
数量 | 属性的显示顺序。 |
GROUPNAME |
Varchar | 属性组名称。 |
OPTIONS |
变体 | 对于枚举属性,是该属性选项的数组,包括每个选项的属性(如显示顺序、标签)。 |
CREATEDUSERID |
数量 | 创建属性的用户 ID。 |
REFERENCEDOBJECTTYPE |
Varchar | 属性引用的对象类型。所有者属性将返回OWNER 值。 |
CALCULATED |
布尔型 | 属性是否为计算属性。 |
EXTERNALOPTIONS |
布尔型 | 属性的选项是否在外部系统中定义。 |
HASUNIQUEVALUE |
布尔型 | 属性值是否唯一。只能在通过 API 创建属性时设置。 |
HIDDEN |
布尔型 | 属性是否在 HubSpot 中隐藏。 |
SHOWCURRENCYSYMBOL |
布尔型 | 是否将数字属性值格式化为货币。 |
FORMFIELD |
布尔型 | 该属性是否可以在表单中使用。 |
READONLYDEFINITION |
布尔型 | 是否可以在 HubSpot 中编辑属性。 |
READONLYVALUE |
布尔型 | 是否可以在 HubSpot 中编辑属性值。 |
HUBSPOTDEFINED |
布尔型 | 属性是由 HubSpot 创建的,还是由用户创建的。 |
ARCHIVED |
布尔型 | 属性是否已被删除。 |
CREATEDAT |
时间戳 | 属性在 HubSpot 中创建的日期和时间。 |
UPDATEDAT |
时间戳 | 数据在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
读取所有联系人属性的名称和描述。
查询:
SELECT name, label, description
FROM property_definitions
WHERE objectTypeId = '0-1'
返回:
姓名 | 标签 | 说明 |
entered_sql_stage | 进入 SQL 阶段 | 开始计算 SQL 阶段天数。 |
第一个转换事件名称
|
第一次转换 | 该联系人提交的第一份表格。 |
地址 | 街道地址 | 联系人地址。 |
活动查询
事件包括标准 HubSpot 事件和自定义行为事件。每个事件都有自己的数据库对象,标记为events_eventname
。
名称 | |
事件广告互动 | 广告的详细信息,以及通过点击广告或提交表单与广告互动的联系人的详细信息。 |
events_ad_metrics_imported_v0 |
广告详情及其性能指标。 |
关键词 | 任何与会者在通话中提到的关键词的详细信息。 |
事件_被点击的电子邮件中的链接_v2 |
联系人在营销电子邮件中点击的链接。 |
events_clicked_link_in_tracked_inbox_email_v8 | 联系人在对话收件箱电子邮件中点击的链接。 |
已点击事件_cookie_banner_clicked | 点击 cookie 的联系人。 |
已浏览事件_cookie_banner_viewed | 查看 cookie 的联系人。 |
事件文档共享_v2 | 通过外部链接与联系人共享销售文件。 |
已查看的文档事件_v2 | 通过打开外部链接查看的销售文件和联系人。 |
事件文档已完成_v2 | 销售文件和已结清的联系人。 |
events_hs_scheduled_email_v2 | 提交到 HubSpot 电子邮件服务器发送的电子邮件。 |
events_mta_delivered_email_v2 | HubSpot 电子邮件服务器成功向收件人发送的电子邮件。 |
events_mta_bounced_email_v2 | 收件人电子邮件服务器拒绝的电子邮件发送尝试。 |
事件_已打开的电子邮件_v2 | 联系人的电子邮件打开率 |
已报告的垃圾邮件事件_email_v2 | 被收件人标记为垃圾邮件的电子邮件。 |
events_updated_email_subscription_status_v2 | 收件人对电子邮件订阅的更改。 |
事件_已打开跟踪收件箱_email_v8 | 水土保持收件箱电子邮件的联系人打开次数。 |
已播放的媒体事件 | 联系人的视频播放活动。 |
events_v2_contact_booked_meeting_through_sequence | 联系人通过 sequence 预订了一次会议。 |
events_v2_contact_enrolled_in_sequence | 按顺序注册的联系人。 |
events_v2_contact_finished_sequence | 序列完成。 |
events_v2_contact_replied_sequence_email | 联系人回复了通过序列发送的电子邮件(或未加入序列的传入电子邮件)。 |
events_v2_contact_unenrolled_from_sequence | 未从序列中注册的联系人。 |
events_v2_contact_unenrolled_manually_from_sequence | 用户手动取消了联系人的注册。 |
events_v2_contact_unsubscribed_sequence_email | 联系人取消订阅,取消序列。 |
events_v2_sequence_email_bounced | 序列电子邮件被退回。 |
events_v2_sequence_errored | 序列错误导致取消注册。 |
events_v2_sequence_step_executed | 执行顺序步骤。 |
已访问事件页面 | 按联系人分列的网站页面访问量。 |
events_<integration_event_name>_<unique_identifier>(事件_<集成事件名>_<唯一标识符> | 集成事件(时间线 V3 应用程序接口) |
您可以通过组合事件类型 ID 和 ID 来识别事件。返回的列取决于事件,每个事件属性有一列。所有事件都会返回以下列:
请注意: HubSpot 已在所有事件表和视图中添加了两列新内容:
occurredat
occurredatdateint
这些列将于2022 年 11 月 24 日取代occuredat
和occuredatdateint
列。届时,旧列将不再可用,使用这些列查询包含事件的数据库对象或视图的尝试将失败。了解有关Snowflake 数据共享中断更改的更多信息。
列名 | 类型 | 说明 |
EVENTTYPEID |
Varchar | 事件类型的 ID,类似于 OBJECTTYPEID 您可以使用object_and_event_type_definitions视图查询账户中的所有事件类型 ID。 |
ID |
Varchar | 每个事件类型的 ID 都是唯一的。在给定的事件类型中,不会有两条记录具有相同的 ID。 |
OBJECTTYPEID |
Varchar | 完成事件的对象类型。例如,电子邮件链接点击会在此列显示0-1 ,代表联系人对象。 |
OBJECTID |
数量 | 完成事件的记录的 ID。 |
OCCURREDAT |
时间戳 | 事件发生的时间。 |
|
数量 | 表示事件发生时间的数字。包含与OCCURREDAT 列相同的数据,但格式为数字,用作表的群集键的一部分。在大多数情况下,可以使用OCCURREDAT 时间戳来代替这个数字。 |
|
数量 | 表示事件发生时间的数字。包含与 请注意:该栏目将于 2022 年 11 月 24 日废弃,由 |
OCCUREDAT |
时间戳 | 事件发生的时间。 请注意:该栏目将于 2022 年 11 月 24 日废弃,由 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 采集的日期和时间。了解有关列数据类型的更多信息。 |
PROPERTY_* |
Varchar | 包含事件属性的单独列。 |
示例 1
检索最近的 50 个电子邮件点击事件。
查询:
SELECT eventTypeId, objectTypeId, objectId, occuredAt, property_hs_click_raw_url AS hs_click_raw_url
FROM events_clicked_link_in_email_V2
ORDER BY occuredAt
LIMIT 50;
返回:
EVENTTYPEID | OBJECTTYPEID | 项目编号 | 发生日期 | hs_click_raw_url |
4-666288 | 0-1 | 45318579 | 2016-08-04 21:06:43.245 | http://website.com?utm_campaign=Launch&utm_source=hs_email&utm_medium=email&utm_content=32520579 |
4-666288 | 0-1 | 80701 | 2016-08-29 13:31:28.622 | https://cta-image-cms2.hubspot.com/ctas/v2/public/cs/ci/?pg=f0f6585a-08fc-4273-8422-49cfaddcf8d8&pid=1976760&ecid=ACsprvtkRbakerw4BiWlaDL7xFAqau1aW9m1_PAqSfQiWarY69X7Ds42zHGYTCfhHJLrXUP86Nxu |
4-666288 | 0-1 | 26146013 | 2017-11-30 14:27:11.528 | https://hubs.ly/H097a37a?utm_source=hs_email&utm_medium=email&utm_content=58251290 |
... | ... | ... | ... | ... |
示例 2
按点击的联系人检索所有电子邮件点击事件。该查询通过与object_with_object_properties 连接获取联系人数据。
查询:
SELECT
click.occuredat click_timestamp,
click.property_hs_click_raw_url click_url,
contact.properties:firstname::varchar contact_firstname,
contact.properties:lastname::varchar contact_lastname
FROM events_clicked_link_in_email_V2 click
LEFT JOIN object_with_object_properties contact
ON click.objectTypeId = contact.objectTypeId
AND click.objectId = contact.objectId
LIMIT 3;
返回:
点击时间戳 | CLICK_URL | 联系人姓名 | 联系人姓氏 |
2019-01-04 22:35:41.264 | https://help.website.com/articles/tutorial/how-to-use-email-tool?utm_source=hs_automation&utm_medium=email&utm_content=68042217 | 霍布斯 | 男爵 |
2018-11-08 09:37:41.400 | https://www.website.net/webinar-case-study?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | 米洛 | 金色 |
2018-11-08 09:37:41.426 | https://www.website.net/contact?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | 哈洛 | 维拉里 |
... | ... | ... | ... |
对象和事件类型查询
对象和事件类型定义
该数据库对象包含 HubSpot 账户中所有对象和事件的定义。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 管道可包含的对象(如交易或票据)的 ID。 |
FULLYQUALIFIEDNAME |
Varchar | 对象或事件的名称。 |
INGESTEDAT |
时间戳 | 数据最后一次被 Snowflake 采集的日期和时间。了解有关列数据类型的更多信息。 |
示例 1
检索 HubSpot 账户中的所有对象和事件。
查询:
SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;
返回:
OBJECTTYPEID | 全称 |
0-1 | 联系 |
0-8 | 行_项目 |
0-11 | 对话 |
... | ... |
列表查询
下面将介绍如何查询列表,如可用的联系人和公司列表以及这些列表中包含的记录。
列表
该数据库对象包含有关 HubSpot 联系人和公司列表的信息。每个列表返回一行,列包括列表详细信息,如列表名称、大小和所含记录的对象类型。不包含每个列表中单条记录的信息(参见list_membership)。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
OBJECTTYPEID |
Varchar | 列表中记录的类型(如联系人或公司)。 |
LISTID |
数量 | 列表的 ID。与list-memberships表中的LISTID列连接。 |
CLASSICLISTID |
数量 | 与 HubSpot 中列表 URL 中 ID 匹配的联系人列表专用 ID。 |
NAME |
Varchar | 列表名称。 |
SIZE |
数量 | 列表中的记录数。 |
CREATEDBYUSERID |
数量 | 创建列表的用户的 ID。 |
CREATEDAT |
时间戳 | 在 HubSpot 中创建列表的时间。 |
UPDATEDBYUSERID |
数量 | 最近更新列表的用户的 ID。 |
UPDATEDAT |
时间戳 | 日期在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
检索所有联系人列表。
查询:
SELECT listId, name, size
FROM lists
WHERE objectTypeId = '0-1';
返回:
列表 | 姓名 | 尺寸 |
118131 | 每周外联活动 | 103 |
66156 | 博客用户 | 455 |
771852 | 2021 年最高订户数 | 37 |
... | ... | ... |
示例 2
检索名为Top subscribers 2021 的特定联系人列表。
查询:
SELECT listId, name, size
FROM lists
WHERE objectTypeId = '0-1'
ANDlistName = 'Top subscribers 2021';
返回:
列表 | 姓名 | 尺寸 |
771852 | 2021 年最高订户数 | 37 |
会员列表
该数据库对象包含哪些记录是哪些列表成员的信息,列表中每条记录一条记录。例如,如果您有一个包含 500 个联系人的博客订阅者列表,该数据库对象将返回 500 条记录。
只包含列表 ID 和对象 ID。有关详细信息,请与列表数据库对象连接。
该数据库对象可返回以下列:
列名 | 类型 | 说明 |
LISTID |
Varchar | 列表的 ID。连接到列表表中的 HS_LIST_ID 列。 |
OBJECTID |
Varchar | 列表中记录的 ID。 |
UPDATEDAT |
时间戳 | 数据在 HubSpot 中最后一次更新的日期和时间。了解有关列数据类型的更多信息。 |
INGESTEDAT |
时间戳 | Snowflake 上次摄取数据的日期和时间。 |
示例 1
读取 "顶级订阅者 2021"列表中所有联系人的 ID。
查询:
SELECT objectId
FROM list_memberships
INNER JOIN lists
ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021';
返回:
项目编号 |
54325098 |
96805401 |
6922028 |
... |
示例 2
检索 "2021 最高订阅者"列表中联系人姓名的详细信息。
查询:
SELECT
contact.objectId AS objectid,
contact.property_firstname AS firstname,
contact.property_lastname AS lastname
FROM objects_contacts contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021'
返回:
项目编号 |
名 |
姓氏 |
54325098 |
霍布斯 |
男爵 |
96805401 |
米洛 |
金色 |
6922028 |
亨利 |
莱利 |
... |
... |
... |
常见问题
我想生成类似于 Hubspot 应用程序报告的数据,该如何操作?
一般来说,您需要查看与之比较的报告中的列。如果报告可视化不是表格形式的,将其转换为表格后就会显示报告中包含的列。这些列通常是对象类型或事件类型的属性。下一步是找出列所对应的对象类型属性。您可以通过查询 property_definitions 来做到这一点:
SELECT * FROM property_definitions
WHERE label = [column_name]
可能并不总是完全匹配,因为有些属性名称通常会在应用程序中解析为更具描述性的名称。在这种情况下,使用 LIKE 运算符对 property_definitions 中的标签(甚至是描述)列进行模糊搜索会有所帮助。
在确定列属于哪种对象类型后,就可以确定要查询的内容了。只需搜索与该类型相对应的 object_x 视图即可。
如何将此数据类型链接到另一种数据类型,例如如何检索活动表单?
如果你需要将两个或多个不同的记录相互链接起来,那么就看看关联吧。关联表示 HubSpot CRM 中对象之间的关系。两种类型之间可能存在的所有不同关联都列在关联定义(association_definitions)中。在上面的示例中,要了解一个营销活动链接到的所有表单,需要查询 ASSOCIATIONS_CAMPAIGNS_TO_FORMS:
SELECT campaigns.property_hs_name campaign_name,
forms.property_hs_form_id form_id
FROM objects_campaigns campaigns
JOIN associations_campaigns_to_forms assoc ON
assoc.campaign_objectid=campaigns.objectid
JOIN objects_forms forms ON forms.objectid=assoc.form_objectid
GROUP BY campaign_name
如何了解 HubSpot-Snowflake 集成的状态?
要接收有关 HubSpot-Snowflake 集成的中断和问题的实时状态更新,请查询 data_share_status 视图:
SELECT * FROM hubspot.public.data_share_status
LIMIT 1
结果将显示为
状态 | 留言 | 更新日期 |
好的 | 暂无已知事件 | 2023-04-10 09:21:20.123 |
该视图按时间倒序提供有关集成状态的更新。将此查询结果整合到仪表板中,有助于跟踪集成的性能并检测任何停机或中断。