Hubspot Models

Copy-and-paste Hubspot model templates for deals, pipelines, contacts, and company reporting.

Copy and Paste these Hubspot Models into your Data Layer to quickly go from raw data souces -> business ready Hubspot Reporting. Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names.

Base

hubspot_deal_pipelines_base

select 
  pipeline_id --PK
  , label as pipeline_name
  , display_order
  , created_at 
from {{sources.hubspot.deal_pipeline}}
where _fivetran_deleted is false

hubspot_deal_pipeline_stages_base

select 
stage_id --PK
, pipeline_id 
, label as stage_name 
, is_closed 
, probability 
, display_order 
, created_at 
from {{sources.hubspot.deal_pipeline_stage}}
where _fivetran_deleted is false

hubspot_deal_stages_base

select 
  pipeline_id --PK
  , label as pipeline_name
  , display_order
  , created_at 
from {{sources.hubspot.deal_pipeline}}
where _fivetran_deleted is false

hubspot_deals_base

SELECT
    deal_id::VARCHAR                                 AS deal_id --PK
  , property_closedate::TIMESTAMP                     AS closed_at
  , property_hs_createdate::TIMESTAMP                 AS created_at
  , property_hs_projected_amount_in_home_currency::FLOAT AS projected_amount_home_currency
  , property_num_associated_contacts::INTEGER         AS num_associated_contacts
  , property_days_to_close::INTEGER                   AS days_to_close
  , property_hs_number_of_overdue_tasks::INTEGER      AS num_overdue_tasks
  , property_hs_analytics_source::VARCHAR             AS analytics_source
  , property_hs_is_closed_won::BOOLEAN                AS is_closed_won
  , property_associated_company_name::VARCHAR         AS associated_company_name
  , property_closed_lost_reason::VARCHAR              AS closed_lost_reason
  , deal_pipeline_id::VARCHAR                         AS pipeline_id
  , property_dealname::VARCHAR                        AS deal_name
  , deal_pipeline_stage_id::VARCHAR                   AS pipeline_stage_id
  , property_deal_status::VARCHAR                     AS deal_status
  , property_amount::FLOAT                            AS amount
  , owner_id::VARCHAR                                 AS owner_id
  , property_hs_primary_associated_company::VARCHAR   AS primary_associated_company_id 

FROM {{ sources.hubspot.deal }}
WHERE is_deleted = FALSE
  AND _fivetran_deleted = FALSE
;

hubspot_contacts_base

select 
  id as contact_id --PK
  , property_createdate as created_at 
  , property_email as email 
  , property_phone as phone 
  , property_firstname as first_name 
  , property_lastname as last_name 
  , property_lifecyclestage
  , property_associatedcompanyid
  , property_hs_is_unworked
  , property_hs_first_outreach_date
  , property_number_of_open_deals
  , property_hs_pipeline
  , property_marketing_landing_page_url
  , property_source
  , property_hs_latest_source
  , property_hs_latest_source_data_1
  , property_hs_latest_source_data_2
  , property_hubspot_owner_id
from {{sources.hubspot.contact}}
where is_deleted = false and _fivetran_deleted is false

hubspot_companies_base

select 
  id as company_id --PK
  , property_createdate as created_at 
  , property_closedate as closed_at 
  , property_name
  , property_phone
  , property_company_type
  , property_customer_type
  , property_county
  , property_city
  , property_first_campaign_contacted
  , property_company_source
  , property_decedent_display_name
  , property_hs_analytics_source
  , property_hs_all_owner_ids
  , property_hs_lead_status
  , property_is_qualified_lead
  , property_lifecyclestage
  , property_hubspot_owner_id
  , property_source
from {{sources.hubspot.company}}
where is_deleted = false and _fivetran_deleted is false

Output

hubspot_companies_output

select 
company_id --PK
  , created_at 
  , closed_at 
  , property_name
  , property_phone
  , property_company_type
  , property_customer_type
  , property_county
  , property_city
  , property_first_campaign_contacted
  , property_company_source
  , property_decedent_display_name
  , property_hs_analytics_source
  , property_hs_all_owner_ids
  , property_hs_lead_status
  , property_is_qualified_lead
  , property_lifecyclestage
  , property_hubspot_owner_id
  , property_source

from {{models.hubspot_companies_base}}

hubspot_deals_output

select 
  deals.deal_id --PK
  , deals.closed_at
  , deals.created_at
  , deals.owner_id
  , deals.is_closed_won
  , deals.deal_name
  , deals.deal_status
  , deals.closed_lost_reason
  , pipeline.pipeline_name
  , pipeline_stages.stage_name
  , deals.primary_associated_company

from {{models.hubspot_deals_base}} deals 
left join {{models.hubspot_deal_pipelines_base}} pipeline on deals.pipeline_id = pipeline.pipeline_id
left join {{models.hubspot_deal_pipeline_stages_base}} pipeline_stages on deals.pipeline_id = pipeline_stages.pipeline_id 
  and deals.pipeline_stage_id = pipeline_stages.stage_id

Hubspot Models

On this page