Consultar dados do HubSpot em Snowflake_PT
Ultima atualização: Março 4, 2022
Disponível para
Operations Hub Enterprise |
Após conectar o Snowflake à sua conta no HubSpot, você pode usar o compartilhamento de dados do Snowflake para consultar os dados do HubSpot a partir da sua conta no Snowflake.
As consultas são construídas usando SQL, e podem ser usadas para recuperar dados do HubSpot, como por exemplo:
- Objetos: objetos padrão e personalizados, como contatos, empresas, produtos.
- Registros: registros individuais, como um contato individual ou uma empresa.
- Associações: todos os tipos de associação disponíveis e registros atualmente associados.
- Proprietários: usuários na sua conta do HubSpot, em relação aos registros dos quais são definidos como proprietário.
- Pipelines: pipelines de negócios e tíquetes, incluindo as fases dos pipelines.
- Propriedades: propriedades e seus valores nos registros de CRM, incluindo histórico de propriedade.
- Eventos: dados de eventos para ambos os eventos padrão do HubSpot, como aberturas de e-mail, e eventos comportamentais personalizados.
- ListashubSpot: Contactos e listas de empresas (BETA).
Saiba mais sobre Modelo de dados de CRM do HubSpot.
Configure seu ambiente no Snowflake
Para consultar os dados do seu HubSpot no Snowflake, é necessário selecionar o warehouse, o banco de dados e o esquema a partir do qual pretende consultar. Você pode defini-los no nível da planilha para incluí-los automaticamente em suas consultas, ou pode adicioná-los manualmente.
Para selecionar seu warehouse, banco de dados e esquema no nível da planilha:
- Na linha superior da planilha, clique na barra Ações da planilha.
- Na janela pop-up:
- Clique no menu suspenso Warehouse e selecione seu warehouse.
- Clique no menu suspenso Banco de dados e selecione seu banco de dados do HubSpot.
- Clique no menu suspenso Esquema e selecione o esquema que você deseja usar:
- V2_LIVE: o esquema ativo baseado em Visualizações seguras dos dados do HubSpot. Os dados neste esquema são atualizados até cada 15 minutos. Se você estiver consultando um grande conjunto de dados, as consultas podem ser lentas. Neste caso, é recomendado copiar os dados para sua própria tabela primeiro com um processo ETL e depois consultar essa tabela.
- V2_DAILY (BETA): o esquema diário para consultar dados com base em tabelas. Os dados neste esquema são atualizados uma vez por dia. O uso deste esquema resultará em consultas mais rápidas, e é recomendado para consultas diretamente no Snowflake.
Com o seu ambiente configurado, você pode fazer consultas aos dados do seu HubSpot sem precisar adicionar manualmente seu banco de dados ou tipo de esquema às consultas.
Para adicionar manualmente o banco de dados e o tipo de esquema às consultas, adicione o nome do banco de dados seguido do esquema no início do campo FROM
. Por exemplo:
SELECT objectTypeId, objectId
FROM hubspot_share_name.V2_LIVE.object_with_object_properties
WHERE objectTypeId = '0-1'
Visão geral dos dados
As consultas são construídas usando SQL, e podem ser usadas para recuperar dados do HubSpot, como por exemplo:
- Objetos: objetos padrão e personalizados, como contatos, empresas, produtos.
- Registros: registros individuais, como um contato individual ou uma empresa.
- Associações: todos os tipos de associação disponíveis e registros atualmente associados.
- Proprietários: usuários na sua conta do HubSpot, em relação aos registros dos quais são definidos como proprietário.
- Pipelines: pipelines de negócios e tíquetes, incluindo as fases dos pipelines.
- Propriedades: propriedades e seus valores nos registros de CRM, incluindo histórico de propriedade.
- Eventos: dados de eventos para ambos os eventos padrão do HubSpot, como aberturas de e-mail, e eventos comportamentais personalizados.
- ListashubSpot: Contactos e listas de empresas (BETA).
Saiba mais sobre Modelo de dados de CRM do HubSpot.
Limitação e ordem de dados devolvidos
Ao construir consultas, você pode controlar a quantidade de dados devolvidos, juntamente com a ordem em que são devolvidos, incluindo LIMIT
e ORDER BY
na sua consulta. Por exemplo, para consultar todos os contatos com as suas propriedades, mas apenas retornar três resultados e ordená-los por ID de contato, você poderia criar a consulta da seguinte forma:
SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
ORDER BY objectId
LIMIT 10;
Consultas de objetos
Abaixo, aprenda como consultar dados de objetos e registros do HubSpot específicos, juntamente com os dados que são devolvidos.
É possível usar os seguintes IDs de objeto ao consultar:
Nome do objeto | ObjectTypeId |
Contatos | 0-1 |
Empresas | 0-2 |
Negócios | 0-3 |
Envolvimento | 0-4 |
Tíquetes | 0-5 |
Itens de linha | 0-8 |
Será atribuído um ID aos objetos personalizados no momento da criação, e será identificado como 2-unique_ID. Por exemplo, 2-12345. Saiba mais sobre object_and_event_type_definitions.
object_with_object_properties
Esta visualização contém dados dos seus registros do HubSpot. As consultas a esta tabela retornam uma linha para cada registro dentro de um objeto (por exemplo, todos os registros de contato no objeto de contato). Todas as propriedades são armazenadas na coluna Propriedades, que é uma coluna JSON/Variante do Snowflake.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O ID do tipo de objeto (por exemplo, contatos é 0-1 ). |
OBJECTID |
Número | O ID do registro (por exemplo, um registro de contato com um ID de 123752 ). |
PROPERTIES |
Variante | Os valores de propriedade dos registros. |
UPDATEDAT |
Data/hora | A data e hora em que o registro foi atualizado pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar todos os contatos e suas propriedades.
Consulta:
SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
Retorna:
OBJECTTYPEID | OBJECTID | PROPERTIES |
0-1 | 38960439 | {"createdate": "1504656970152"...} |
0-1 | 321740651 | {"createdate": "1590183081949"...} |
0-1 | 98392101 | {"createdate": "1533566477279"...} |
... | ... | ... |
Exemplo 2
Recuperar o nome do negócio e a fase de até três negócios.
Consulta:
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;
Retorna:
OBJECTID | DEALNAME | DEALSTAGE | DEALAMOUNT |
38960439 | Nome do negócio 1 |
2021-10-12 13:49:35.219
|
2021-10-12 13:49:35.219
|
321740651 | Nome do negócio 1 |
2021-10-12 13:49:35.219
|
2021-10-12 13:49:35.219
|
98392101 | Nome do negócio 1 |
2021-10-12 13:49:35.219
|
2021-10-12 13:49:35.219 |
objects
Esta visão é um subconjunto dos dados na tabela object_with_object_properties. Cada linha retornada retorna o ID do objeto para cada registro. Esta visualização apenas devolve registros existentes. Quando um registro for excluído do HubSpot, ele será removido desta visualização.
Esta visualização pode ser útil se a sua conta tiver uma grande quantidade de dados, fazendo com que object_with_object_properties funcione lentamente. O uso desta tabela com a tabela de object_properties torna possível replicar os dados em object_with_object_properties, enquanto mantém o controle sobre quais propriedades são devolvidas.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O ID do tipo de objeto (por exemplo, contatos é 0-1 ). |
OBJECTID |
Número | O ID do registro (por exemplo, um registro de contato com um ID de 123752 ). |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. Saiba mais sobre os tipos de dados das colunas. |
Exemplo 1
Recuperar todos os registros de contato.
Consulta:
SELECT objectTypeId, objectId
FROM objects
WHERE objectTypeId = '0-1'
Retorna:
OBJECTTYPEID | OBJECTID |
0-1 | 36721864 |
0-1 | 103580363 |
0-1 | 56047670 |
... | ... |
object_properties
Esta tabela é um subconjunto da tabela de object_with_object_properties. Cada linha contém o valor de propriedade atual definido em um determinado registro. Por exemplo, um contato terá uma linha para a propriedade Nome e uma linha para a propriedade Sobrenome. Se um registro não tiver um valor para uma propriedade, a linha aparecerá com uma string vazia ou pode não aparecer.
Esta tabela pode ser útil se sua conta tiver uma grande quantidade de dados, fazendo com que object_with_object_properties funcione lentamente. O uso desta tabela com a tabela de object_properties torna possível replicar os dados em object_with_object_properties, enquanto controla quais propriedades são devolvidas.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O ID do tipo de objeto (por exemplo, contatos é 0-1 ). |
OBJECTID |
Número | O ID do registro (por exemplo, um registro de contato com um ID de 123752 ). |
NAME |
Varchar | O nome da propriedade. |
VALUE |
Data/hora | O valor da propriedade. |
UPDATEDAT |
Data/hora | A data e hora em que o registro foi atualizado pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar propriedades de nome, sobrenome e cidade para todos os contatos.
Consulta:
SELECT objectTypeId, objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;
Retorna:
OBJECTTYPEID | OBJECTID | NAME | VALUE |
0-1 | 10401 | firstname | Brian |
0-1 | 10401 | city | Dublin |
0-1 | 10401 | lastname | Gallagher |
0-1 | 23451 | firstname | Fernando |
0-1 | 23451 | lastname | Fierro |
0-1 | 25751 | firstname | Charles |
... | ... | ... | ... |
Exemplo 2
Recuperar nome, sobrenome e fase do ciclo de vida de todos os contatos, e combiná-los em uma linha por contato. Este exemplo usa a sintaxe do SQL PIVOT.
Consulta:
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);
Retorna:
OBJECTTYPEID | OBJECTID | FIRSTNAME | LASTNAME | LIFECYCLESTAGE |
0-1 | 54325098 | Hobbes | Baron | lead |
0-1 | 96805401 | Milo | Gold | lead |
0-1 | 6922028 | Henry | Riley | lead |
... | ... | ... | ... | ... |
object_properties_history
Esta visualização contém valores históricos de propriedade. Para contatos, contém os 45 valores mais recentes. Para todos os outros objetos, ele contém os 20 valores mais recentes. Exibe uma linha para cada valor histórico de propriedade. O valor atual de uma propriedade é determinada pela data/hora mais recente UPDATEDAT no histórico da propriedade.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O ID do tipo de objeto (por exemplo, contatos é 0-1 ). |
OBJECTID |
Número | O ID do registro (por exemplo, um registro de contato com um ID de 123752 ). |
NAME |
Varchar | O nome da propriedade. |
VALUE |
Data/hora | O valor da propriedade. |
UPDATEDAT |
Data/hora | A data e hora em que os dados foram atualizados pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar o histórico de uma única propriedade para um único contato.
Consulta:
SELECT objectId, name, value, timestamp
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 585
AND name = 'hs_predictivescoringtier'
ORDER BY timestamp DESC;
Retorna:
OBJECTTYPEID | OBJECTID | FIRSTNAME | LASTNAME | LIFECYCLESTAGE |
0-1 | 54325098 | Hobbes | Baron | lead |
0-1 | 96805401 | Milo | Gold | lead |
0-1 | 6922028 | Henry | Riley | lead |
... | ... | ... | ... | ... |
Exemplo 2
Recuperar o nome atual de todos os contatos que já tiveram uma fase do ciclo de vida de Subscriber.
Consulta:
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'
);
Retorna:
OBJECTID | FIRSTNAME | LASTNAME |
54325098 | Hobbes | Baron |
96805401 | Milo | Gold |
6922028 | Henry | Riley |
... | ... | ... |
Consultas de associação
Abaixo, saiba como consultar associações, como os tipos disponíveis de associações entre objetos e os registros que estão atualmente associados.
association_definitions
Esta visão contém informações sobre todos os tipos de associação disponíveis entre os objetos HubSpot, com uma linha por tipo de associação. Você pode juntar esta tabela com a tabela de associações para obter informações de associações específicas do registro.
Pode haver vários tipos de associações entre os mesmos objetos. O principal tipo de associação, que é o tipo exibido no HubSpot quando os registros são associados, usa a associação em queISMAINASSOCIATIONDEFINITION
é verdadeira
.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
COMBINEDASSOCIATIONTYPEID |
Varchar | O identificador único da definição da associação. |
CATEGORIA |
Varchar | A fonte do tipo de associação. Um dos seguintes: HUBSPOT_DEFINED , USER_DEFINED , INTEGRATOR_DEFINED |
ID |
Número | O identificador da definição da associação dentro da sua categoria. |
FROMOBJECTTYPEID |
Varchar | O ID do objeto que é a fonte da definição da associação. |
TOOBJECTTYPEID |
Varchar | O ID do objeto que é o destino da definição da associação. |
NAME |
Varchar | O nome da definição da associação. |
LABEL |
Varchar | O rótulo da definição da associação. |
ISMAINASSOCIATIONDEFINITION |
Booleano | Se a definição da associação é o tipo principal. Quando verdadeira, a definição é aquela que o HubSpot exibe nos registros associados. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. Saiba mais sobre os tipos de dados das colunas. |
Exemplo 1
Recuperar os tipos de associações disponíveis na sua conta.
Consulta:
SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY category, id
Retorna:
COMBINEDASSOCIATIONTYPE | FROMOBJECTTYPEID | TOOBJECTTYPEID | NAME |
0-1 | 0-1 | 0-2 | CONTACT_TO_COMPANY |
0-2 | 0-2 | 0-1 | COMPANY_TO_CONTACT |
0-3 | 0-3 | 0-1 | DEAL_TO_CONTACT |
0-4 | 0-1 | 0-3 | CONTACT_TO_DEAL |
0-5 | 0-3 | 0-2 | DEAL_TO_COMPANY |
... | ... | ... | ... |
Exemplo 2
Recuperar os tipos de associação disponíveis entre negócios e empresas.
Consulta:
SELECT
combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = trueORDER BY category, id
Retorna:
COMBINEDASSOCIATIONTYPE | FROMOBJECTTYPEID | TOOBJECTTYPEID | NAME |
0-5 | 0-3 | 0-2 | DEAL_TO_COMPANY |
0-271 | 0-3 | 0-2 | DEAL_TO_PRIMARY_COMPANY |
0-341 | 0-3 | 0-2 | DEAL_TO_COMPANY_UNLABELED |
associations
Esta visualização contém informações sobre as associações entre registros específicos. Cada linha representa uma associação de um objeto a outro objeto. As linhas podem estar presentes nesta tabela, mesmo que os registros referenciados tenham sido excluídos. É possível verificar se existem registros unindo com a tabela de objetos.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
COMBINEDASSOCIATIONTYPEID |
Varchar | O identificador único da definição da associação. |
FROMOBJECTID |
Número | O ID do objeto fonte. |
TOOBJECTID |
Número | O ID do objeto de destino. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. Saiba mais sobre os tipos de dados das colunas. |
Exemplo 1
Recuperar todos os negócios e contatos associados.
Consulta:
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE combinedAssociationTypeId = '0-5'
Retorna:
COMBINEDASSOCIATIONTYPE | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
0-5 | 6616436082 | 3866015468 |
0-5 | 6690805943 | 7132752747 |
... | ... | ... |
Exemplo 2
Recuperar todos os tíquetes associados a um contato específico (ID de contato: 3005).
Consulta:
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;
Retorna:
COMBINEDASSOCIATIONTYPE | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
Exemplo 3
Recuperar todos os negócios associados a um contato específico (ID do contato: 4464). Em vez de codificar combinedAssociationTypeId, este exemplo junta-se a association_definitions.
Consulta:
SELECT combinedAssociationType, fromObjectId, toObjectId,
FROM associations
WHERE
combinedAssociationTypeId = (
SELECT combinedAssociationTypeId
FROM association_definitions
WHERE fromObjectTypeId = '0-1'
AND toObjectTypeId = '0-3'
AND isMainAssociationDefinition)
AND fromObjectId = 4464;
Retorna:
COMBINEDASSOCIATIONTYPE | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
Consultas do proprietário
owners
Esta visualização contém informação sobre os usuários na sua conta do HubSpot. Os usuários podem ser definidos como proprietários de registros e terão um ID de usuário e um ID de proprietário, dependendo de seu contexto. Os dois IDs podem ser usados para identificar o mesmo usuário.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OWNERID |
Número | O ID de proprietário do usuário. |
USERID |
Número | O ID de usuário do usuário. Os usuários com NULL nesta coluna foram excluídos. |
EMAIL |
Varchar | O endereço de e-mail do usuário. |
FIRSTNAME |
Varchar | O nome do usuário. |
LASTNAME |
Varchar | O sobrenome do usuário. |
ISACTIVO |
Booleano | Se o usuário foi excluído. |
CREATEDAT |
Data/hora | A data e hora em que o usuário foi criado no HubSpot. Consulte os tipos de dados da coluna para obter mais informações. |
UPDATEDAT |
Data/hora | A data e hora em que o usuário foi atualizado pela última vez no HubSpot. Consulte os tipos de dados da coluna para obter mais informações. |
Exemplo 1
Recuperar todos os usuários. O filtro WHERE isActive = true
remove os usuários excluídos dos dados retornados.
Consulta:
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE isActive = true
Retorna:
OWNERID | ACTIVEUSERID | FIRSTNAME | LASTNAME | |
29584574 | 4621126 | 233620335 | Hobbes | Baron |
30532717 | 4874784 | 3866015468 | Pablo | Walters |
30580321 | 925511 | 7132752747 | Milo | Gold |
... | ... | ... | ... | ... |
Exemplo 2
Recuperar um proprietário específico através do seu ID de proprietário.
Consulta:
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE ownerId = 29584574;
Retorna:
OWNERID | ACTIVEUSERID | FIRSTNAME | LASTNAME | |
29584574 | 4621126 | 233620335 | Hobbes | Baron |
Exemplo 3
Recuperar todos os contatos e seus proprietários. Esta consulta junta os registros dos proprietários com object_with_object_properties e hubspot_owner_idda propriedade de contato.
SELECT
contact.objectId contact_id,
contact.properties:firstname::string contact_firstname,
contact.properties:lastname::string contact_lastname,
contact.properties:hubspot_owner_id::number contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM object_with_object_properties contact
inner join owners owner on contact.properties:hubspot_owner_id::number = owner.ownerId
where objectTypeId = '0-1';
Retorna:
CONTACTID | CONTACT_FIRSTNAME | CONTACT_LASTNAME | CONTACT_HUBSPOT_OWNER_ID | OWNER_FIRSTNAME | OWNER_LASTNAME | OWNER_EMAIL |
113834202 | Richard | Greenfield | 29584574 | Hobbes | Baron | hobbes.b@business.com |
53540801 | Ford | Karl | 30241125 | Milo | Gold | milo.g@business.com |
... | ... | ... | ... | ... | ... | ... |
Consultas sobre pipeline
Abaixo, saiba como consultar os dados do negócio e do pipeline de tíquete, como os pipelines disponíveis na sua conta e as respectivas fases.
Você pode juntar estes dados com os dados do objeto para uma visão completa dos seus pipelines. Por exemplo, você pode consultar negócios e seus pipelines unindo os dados do object_with_object_properties com os dados dos pipelines.
pipelines
Esta visualização contém informações sobre o negócio e os pipelines de tíquete na sua conta. Cada linha devolvida representa um único pipeline.
Esta tabela pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
PIPELINEID |
Varchar | O ID do pipeline. |
OBJECTTYPEID |
Varchar | O ID do objeto que o pipeline pode conter (por exemplo, negócios ou tíquetes). |
LABEL |
Varchar | O nome do pipeline no HubSpot. |
DISPLAYORDER |
Número | A ordem em que o pipeline é exibido no HubSpot. |
ARQUIVADO |
Booleano | Se o pipeline foi excluído. |
CREATEDAT |
Data/hora | Quando o pipeline foi criado. |
UPDATEDAT |
Data/hora | A data e hora em que os dados foram atualizados pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar todos os pipelines disponíveis, ordenados pela sua ordem no HubSpot.
Consulta:
SELECT objectTypeId, pipelineId, label
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;
Retorna:
OBJECTTYPEID | PIPELINEID | LABEL |
0-3 | 1bed503c-37f7-4f51-9d40-7598902673f4 | Pipeline de vendas |
0-3 | 12659678 | Pipeline de licenças |
0-3 | 75e28846-ad0d-4be2-a027-5e1da6590b98 | Nova empresa |
... | ... | ... |
Exemplo 2
Recuperar negócios dentro de um pipeline específico. Une object_with_object_properties e pipelines.
Consulta:
SELECTdeals.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';
Retorna:
DEAL_ID | DEAL_NAME | PIPELINE_NAME |
605140072 | Inscrição da Meowmix | Pipeline de vendas |
605457408 | Rebrand da Friskies | Pipeline de vendas |
604362473 | Criação do aplicativo Fresh Step | Pipeline de vendas |
pipeline_stages
Esta visualização contém informações sobre etapas individuais dentro de um pipeline. Identificar etapas através da combinação do ID do tipo de objeto, ID do pipeline e ID da etapa. Ao unir com pipelines, usar o ID do tipo de objeto e ID d pipeline.
Esta tabela pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
PIPELINEID |
Varchar | O ID do pipeline. |
OBJECTTYPEID |
Varchar | O ID do objeto que o pipeline pode conter (por exemplo, negócios ou tíquetes). |
LABEL |
Varchar | O nome do pipeline no HubSpot. |
DISPLAYORDER |
Número | A ordem em que o pipeline é exibido no HubSpot. |
ARQUIVADO |
Booleano | Se o pipeline foi excluído. |
UPDATEDAT |
Data/hora | A data e hora em que os dados foram atualizados pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar as etapas de um pipeline para um pipeline de negócios chamado Pipeline de vendas.
Consulta:
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 = “Pipeline de vendas' LIMIT 1)
ORDER BY displayorder;
Retorna:
PIPELINEID | STAGEID | LABEL |
941650 | 941651 | Contatado |
941650 | 941652 | Compromisso agendado |
941650 | 941653 | Fatura enviada |
... | ... | ... |
Exemplo 2
Recuperar todos os negócios e suas respectivas fases de pipeline.
Consulta:
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';
Retorna:
DEAL_ID | DEAL_NAME | PIPELINE_NAME | PIPELINE_STAGE_NAME |
605140072 | Inscrição da Meowmix | Pipeline de vendas | Contrato enviado |
605457408 | Rebrand da Friskies | Pipeline de vendas | Compromisso agendado |
604362473 | Criação do aplicativo Fresh Step | Pipeline de vendas | Contrato assinado |
... | ... | ... | ... |
Consultas sobre propriedades
property_definitions
Esta visualização contém informações sobre propriedades de objetos e propriedades de eventos na sua conta do HubSpot. As propriedades de eventos incluem eventos padrão, como abertura e cliques de e-mail, e propriedades de eventos comportamentais personalizados. Essas informações podem ser úteis para definir valores encontrados nos dados de object_properties de events.
É possível identificar propriedades específicas combinando seu nome e ID do tipo de objeto. Você pode recuperar informações semelhantes usando as propriedades de API.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJETÍVEL |
Varchar | O ID do objeto com o qual o evento se relaciona. |
NAME |
Varchar | O nome interno da propriedade. |
LABEL |
Varchar | O rótulo da propriedade. |
DESCRIPTION |
Varchar | A descrição da propriedade. |
TIPO |
Varchar | O tipo da propriedade (por exemplo, string, número, data e hora). |
FIELDTYPE |
Varchar | O tipo de campo da propriedade. Define como a propriedade é exibida no HubSpot e em formulários (por exemplo, caixa de seleção, seleção). |
DISPLAYORDER |
Número | A ordem de exibição da propriedade. |
GROUPNAME |
Varchar | O nome do grupo da propriedade. |
OPÇÕES |
Variante | Para propriedades de enumeração, um conjunto de opções da propriedade, incluindo os atributos de cada opção (por exemplo, ordem de exibição, rótulo). |
CREATEDUSERID |
Número | O ID do usuário que criou a propriedade. |
REFERENCEDOBJECTTYPE |
Varchar | O tipo de objeto a qual a propriedade referencia. As propriedades do proprietário devolverão um valor de OWNER. |
CALCULADO |
Booleano | Se a propriedade é uma propriedade calculada. |
EXTERNALOPTIONS |
Booleano | Se as opções da propriedade estão definidas em um sistema externo. |
HASUNIQUEVALUE |
Booleano | Se os valores da propriedade são únicos. Só pode ser definido ao criar uma propriedade através da API. |
HIDDEN |
Booleano | Se a propriedade está oculta no HubSpot. |
SHOWCURRENCYSYMBOL |
Booleano | Se o número de valor de uma propriedade é formatado como moeda. |
FORMFIELD |
Booleano | Se esta propriedade pode ser usada em formulários. |
READONLYDEFINITION |
Booleano | Se a propriedade pode ser editada no HubSpot. |
READONLYVALUE |
Booleano | Se o valor da propriedade pode ser editado no HubSpot. |
HUBSPOTDEFINED |
Booleano | Se a propriedade foi criada pela HubSpot, versus criada por um usuário. |
ARQUIVADO |
Booleano | Se a propriedade foi excluída. |
CREATEDAT |
Data/hora | A data e hora em que a propriedade foi criada no HubSpot. |
UPDATEDAT |
Data/hora | A data e hora em que os dados foram atualizados pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar os nomes e as descrições de todas as propriedades do contato.
Consulta:
SELECT name, label, description
FROM property_definitions
WHERE objectTypeId = '0-1'
Retorna:
NAME | LABEL | DESCRIPTION |
enter_sql_stage | Entrada na fase SQL | Iniciar contagem de dias de fase SQL. |
first_conversion_event_name
|
Primeira conversão | O primeiro formulário que esse contato enviou. |
endereço | Endereço | O endereço do contato. |
Consultas do evento
Os eventos incluem eventos padrão e eventos comportamentais personalizados do HubSpot. Cada evento tem a sua própria visão, rotulada como eventos_nome_do_evento
.
Os eventos disponíveis incluem:
- Eventos de clique do anúncio
(events_ad_clicked
) - Aberturas de e-mail abre e cliques
(events_opened_email_V2
,events_click_link_in_email_v2
, etc.) - Análise da Web
(events_visited_page
)
Você pode identificar eventos combinando o ID e o ID do tipo de evento. As colunas que são devolvidas dependem do evento, com uma coluna para cada propriedade do evento. Todos os eventos retornarão as seguintes colunas:
Por favor, note: o HubSpot adicionou duas novas colunas a todas as tabelas e visualizações de eventos:
ocorrida em
ocorreu em
Estas colunas irão substituir as colunas ocorridas
em 24 de Abril de 2022. Nessa data, as colunas antigas não estarão mais disponíveis, e as tentativas de consultar uma tabela ou vista contendo eventos usando essas colunas falharão. Saiba mais sobre como quebrar mudanças para compartilhamento de dados do Floco de Neve.
Nome da coluna | Tipo | Descrição |
EVENTTYPEID |
Varchar | O ID do pipeline. |
ID |
Varchar | O ID do objeto que o pipeline pode conter (por exemplo, negócios ou tíquetes). |
OBJECTTYPEID |
Varchar | O tipo de objeto que completou o evento. Por exemplo, um clique no link de e-mail mostraria 0-1 nesta coluna, representando o objeto de contato. |
OBJECTID |
Número | O ID do registro que completou o evento. |
OCCURREDAT |
Data/hora | Quando o evento ocorreu. |
|
Número | Um número que representa quando o evento ocorreu. Contém os mesmos dados que a coluna OCCURREDAT , mas formatado como um número e utilizado como parte da chave de cluster da tabela. Na maioria dos casos, você pode usar o OCCURREDAT timestamp em vez deste número. |
|
Número |
Um número que representa quando o evento ocorreu. Contém os mesmos dados que a coluna Atenção: esta coluna será depreciada em 24 de Abril de 2022 e substituída por |
OCCUREDAT |
Data/hora |
Quando o evento ocorreu. Atenção: esta coluna será depreciada em 24 de Abril de 2022 e substituída por |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. Saiba mais sobre os tipos de dados das colunas. |
PROPRIEDADE_* |
Varchar | Colunas individuais que contêm as propriedades do evento. |
Exemplo 1
Recuperar os 50 mais recentes eventos de clique por e-mail.
Consulta:
SELECT eventTypeId, objectTypeId, objectId, occuredAt, hs_click_raw_url
FROM events_clicked_link_in_email_V2
ORDER BY occuredAt
LIMIT 50;
Retorna:
EVENTTYPEID | OBJECTTYPEID | OBJECTID | OCCUREDAT | 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 |
... | ... | ... | ... | ... |
Exemplo 2
Recuperar todos os eventos de clique por e-mail pelos contatos que clicaram. Esta consulta obtém dados de contato unindo-se com object_with_object_properties.
Consulta:
SELECT
click.timestamp 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;
Retorna:
CLICK_TIMESTAMP | CLICK_URL | CONTACT_FIRSTNAME | CONTACT_LASTNAME |
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 | Hobbes | Baron |
2018-11-08 09:37:41.400 | https://www.website.net/webinar-case-study?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | Milo | Gold |
2018-11-08 09:37:41.426 | https://www.website.net/contact?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | Carlow | Villari |
... | ... | ... | ... |
object_and_event_type_definitions
Esta visualização contém definições para todos os objetos e eventos disponíveis na sua conta do HubSpot.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O ID do objeto que o pipeline pode conter (por exemplo, negócios ou tíquetes). |
FULLYQUALIFIEDNAME |
Varchar | O nome do objeto ou evento. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. Saiba mais sobre os tipos de dados das colunas. |
Exemplo 1
Recuperar todos os objetos e eventos disponíveis na sua conta do HubSpot.
Consulta:
SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;
Retorna:
OBJECTTYPEID | FULLYQUALIFIEDNAME |
0-1 | CONTACT |
0-2 | COMPANY |
0-3 | DEAL |
0-4 | ENGAGEMENT |
0-5 | TICKET |
0-8 | LINE_ITEM |
0-11 | CONVERSATION |
... | ... |
Consultas de listas (BETA)
Abaixo, saiba como consultar suas listas, como as listas de contatos e empresas disponíveis e os registros incluídos nessas listas.
listas
Esta visualização contém informações sobre listas de contatos e de empresas do HubSpot. Retorna uma linha por lista, e as colunas incluem detalhes, como nome da lista, tamanho e tipo de objeto dos registros nela contidos. Não contém informações sobre os registros individuais dentro de cada lista (ver list_membership).
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
OBJECTTYPEID |
Varchar | O tipo de registros na lista (por exemplo, contato ou empresa). |
LISTID |
Número | O ID da lista. Une-se à coluna LISTID na tabela list-memberships. |
CLASSICLISTID |
Número | Um ID específico para listas de contatos que corresponda ao ID no URL da lista no HubSpot. |
LISTNAME |
Varchar | O nome da lista. |
LISTSIZE |
Número | O número de registros na lista. |
CRIADO PELO USUÁRIO |
Número | O ID do usuário que criou a lista. |
CREATEDAT |
Data/hora | Quando a lista foi criada no HubSpot. |
ATUALIZADO PELO USUÁRIO |
Número | O ID do usuário que mais recentemente atualizou a lista. |
UPDATEDAT |
Data/hora | A data e a hora em que a data foi atualizada pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar todas as listas de contatos.
Consulta:
SELECT listId, listName, listSize
FROM lists
WHERE objectTypeId = '0-1';
Retorna:
LISTID | LISTNAME | LISTSIZE |
118131 | Contato semanal | 103 |
66156 | Inscritos do blog | 455 |
771852 | Principais inscritos de 2021 | 37 |
... | ... | ... |
Exemplo 2
Recuperar uma lista de contatos específica chamada Principais inscritos de 2021.
Consulta:
SELECT listId, listName, listSize
FROM lists
WHERE objectTypeId = '0-1'
ANDlistName = “Principais inscritos de 2021';
Retorna:
LISTID | LISTNAME | LISTSIZE |
771852 | Principais inscritos de 2021 | 37 |
list_membership
Esta visualização contém informações sobre quais registros são membros de quais listas, com uma linha por registro na lista. Por exemplo, se você tiver uma lista de inscritos no Blog com 500 contatos, a tabela retornará 500 linhas.
Contém apenas IDs de lista e IDs de objeto. Para mais informações, una com a tabela lists.
Esta visualização pode retornar as seguintes colunas:
Nome da coluna | Tipo | Descrição |
LISTID |
Varchar | O ID da lista. Una a coluna HS_LIST_ID na tabela lists. |
OBJECTID |
Varchar | O ID do registro na lista. |
UPDATEDAT |
Data/hora | A data e hora em que os dados foram atualizados pela última vez no HubSpot. Saiba mais sobre os tipos de dados das colunas. |
INGESTEDAT |
Data/hora | A data e hora em que os dados foram consumidos pela última vez pelo Snowflake. |
Exemplo 1
Recuperar os IDs de todos os contatos da lista Principais inscritos de 2021.
Consulta:
SELECT objectId
FROM list_memberships
INNER JOIN lists
ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = “Principais inscritos de 2021';
Retorna:
OBJECTID |
54325098 |
96805401 |
6922028 |
... |
Exemplo 2
Recuperar o primeiro e último nome dos detalhes dos contactos na lista Top Subscribers 2021.
Consulta:
SELECT
contact.objectId AS objectid,
contact.properties:firstname::string AS firstname,
contact.properties:lastname::string AS lastname
FROM object_with_object_properties contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE
contact.objectTypeId = '0-1'
AND lists.objectTypeId = '0-1'
AND lists.listName = “Principais inscritos de 2021'
Retorna:
OBJECTID | FIRST NAME | LAST NAME |
54325098 | Hobbes | Baron |
96805401 | Milo | Gold |
6922028 | Henry | Riley |
... | ... | ... |
Tipos de dados de coluna
Ao consultar dados no Snowflake, as colunas que são devolvidas dependem dos dados que você está consultando. Você pode visualizar as colunas disponíveis para uma determinada tabela ou visualização no Snowflake selecionando a visualização na barra lateral esquerda.

Para retornar uma coluna específica, inclua essa coluna no campo SELECT
da sua consulta. Por exemplo, a seguinte consulta retornará apenas as colunas FROMOBJECTID
e TOOBJECTID
:
SELECTfromObjectTypeId, toObjectTypeId
FROM association_definitions
Abaixo estão as definições gerais para as colunas que uma consulta pode retornar. Para outras definições não incluídas abaixo, consulte as definições da visualização individual no artigo acima.
- OBJECTTYPEID: um valor VARCHAR que representa o tipo de um objeto. Por exemplo, 0-1 representa o objeto contatos, 0-2 representa o objeto empresas, e 2-1232 pode representar um objeto personalizado em sua conta. Em listas, isto se refere ao tipo de lista (por exemplo, contato ou empresa). Você pode consultar todos os IDs de objetos e eventos na sua conta usando a visualização object_and_event_type_definitions.
- OBJECTID: um número BIGINT que representa um único registro de um determinado tipo de objeto na sua conta do HubSpot. Para identificar um registro, é necessário usar tanto o ID do tipo de objeto quanto o ID do objeto.
- INGESTEDAT: data/hora de quando uma linha de dados foi adicionada ao Snowflake, representado por um valor TIMESTAMP_NTZ. Não está relacionado com a data/hora lógica de uma operação de criação ou atualização no HubSpot.
- Devido ao processamento interno do HubSpot, a data/hora de consumo de uma linha pode ser atualizada, mesmo que nenhuma outra coluna da linha tenha sido alterada.
- Ao construir processos ETL que copiam dados alterados recentemente, é recomendado basear suas consultas na data/hora de consumo. Além disso, se o seu fluxo copia dados diariamente, é recomendado copiar os dados dos dois dias anteriores para contabilizar os atrasos.
- CREATEDAT e UPDATEDAT: data/hora lógica de quando os dados foram criados ou atualizados no HubSpot, representado por um valor TIMESTAMP_NTZ. Estas datas/horas corresponderão às datas/horas no HubSpot. Por exemplo, a hora exibida no HubSpot para a data de criação de um registro será a mesma da data de criação do registro na coluna CREATEDAT no Snowflake. Estas não estão diretamente relacionadas com a data/hora de consumo, e não é recomendado basear os processos ETL nestas propriedades.
- COMBINEDASSOCIATIONTYPE: um valor VARCHAR que identifica de forma única uma definição de associação.
Thank you for your feedback, it means a lot to us.
Conteúdo relacionado
-
Criar relatório personalizado
Com relatórios personalizados, você tem mais flexibilidade para analisar objetos e atividades na sua conta da...
Central de conhecimento -
Criar relatórios de objetos únicos personalizados
Com um único relatório de objeto, você pode analisar um objeto, como contatos, em relação às propriedades do...
Central de conhecimento -
Criar URLs de rastreamento
As URLs de rastreamento ajudam a medir a eficácia das suas campanhas de marketing, fornecendo informações ao...
Central de conhecimento