GoHighLevel Models

Copy-paste SQL models for turning raw GoHighLevel data into business-ready contact and location reporting.

Copy and Paste these GoHighLevel Models into your Data Layer to quickly go from raw data sources to business ready GoHighLevel 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. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts.

gohighlevel_locations_base

select
    TRIM(id) as location_id -- PK
  , TRIM(name) as location_name
  , TRIM(city) as city
  , TRIM(state) as state
  , TRIM(country) as country
  , TRIM(address) as address
  , TRIM(postal_code) as postal_code
  , TRIM(phone) as phone
  , RIGHT(REGEXP_REPLACE(TRIM(phone), '[^0-9]', '', 'g'), 10)::VARCHAR as phone_clean
  , TRIM(email) as email
  , TRIM(website) as website
  , TRIM(timezone) as timezone
  , __updated_at
from {{sources.gohighlevel.locations}}

gohighlevel_pipeline_stages_base

select
    TRIM(id) as pipeline_id -- PK (composite w/ stage_id)
  , TRIM(name) as pipeline_name
  , (stage->>'id') as stage_id -- PK (composite w/ pipeline_id)
  , (stage->>'name') as stage_name
  , (stage->>'position')::integer as position
  , (stage->>'stageWinProbability')::float as win_probability
from {{sources.gohighlevel.opportunity_pipeline_stages}}
   , jsonb_array_elements(stages) as stage

gohighlevel_contacts_base

select
    TRIM(id) as contact_id -- PK
  , TRIM(location_id) as location_id
  , LOWER(TRIM(contact_name)) as contact_name
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , LOWER(TRIM(email)) as email
  , TRIM(phone) as phone
  , RIGHT(REGEXP_REPLACE(TRIM(phone), '[^0-9]', '', 'g'), 10)::VARCHAR as phone_clean
  , TRIM(city) as city
  , TRIM(state) as state
  , TRIM(postal_code) as postal_code
  , TRIM(country) as country
  , TRIM(source) as source
  , TRIM(type) as type
  , dnd

  -- EXAMPLE ONLY — replace field ids with your own account's custom_fields ids
  -- (look these up via {{sources.gohighlevel.location__custom_fields}})
  , TRIM(
      (select cf->>'value' from jsonb_array_elements(custom_fields) as cf
       where cf->>'id' = '<your_field_id_1>' limit 1)
    ) as custom_field_example_text
  , (select cf->>'value' from jsonb_array_elements(custom_fields) as cf
     where cf->>'id' = '<your_field_id_2>' limit 1)::jsonb as custom_field_example_array

  -- attribution_source fields (first touch) — generic to all GHL accounts
  , TRIM(attribution_source->>'sessionSource') as attr_session_source
  , TRIM(attribution_source->>'medium') as attr_medium
  , TRIM(attribution_source->>'url') as attr_url
  , TRIM(attribution_source->>'referrer') as attr_referrer
  , TRIM(attribution_source->>'gaClientId') as attr_ga_client_id
  , (attribution_source->>'url') LIKE '%rwg_token=%' as is_reserve_with_google

  -- last_attribution_source fields (last touch) — generic to all GHL accounts
  , TRIM(last_attribution_source->>'sessionSource') as last_attr_session_source
  , TRIM(last_attribution_source->>'medium') as last_attr_medium
  , TRIM(last_attribution_source->>'url') as last_attr_url
  , TRIM(last_attribution_source->>'referrer') as last_attr_referrer
  , TRIM(last_attribution_source->>'gaClientId') as last_attr_ga_client_id
  , (last_attribution_source->>'url') LIKE '%rwg_token=%' as last_is_reserve_with_google

  , date_added::timestamp as created_at
  , date_updated::timestamp as updated_at
  , __updated_at
from {{sources.gohighlevel.contacts}}

Fact

Creates a “Fact” about a core concept. For example, creating a customer order fact that defines when a customer placed their first order. This then gets joined downstream in output.

gohighlevel_contacts_facts

select
    c.contact_id as contact_id -- PK
  , c.location_id
  , c.contact_name
  , c.first_name
  , c.last_name
  , c.email
  , c.phone
  , c.phone_clean
  , c.city
  , c.state
  , c.postal_code
  , c.country
  , c.source
  , c.type
  , c.dnd
  , c.custom_field_example_text
  , c.custom_field_example_array
  , c.attr_session_source
  , c.attr_medium
  , c.attr_url
  , c.attr_referrer
  , c.attr_ga_client_id
  , c.is_reserve_with_google
  , c.last_attr_session_source
  , c.last_attr_medium
  , c.last_attr_url
  , c.last_attr_referrer
  , c.last_attr_ga_client_id
  , c.last_is_reserve_with_google
  , c.created_at
  , c.updated_at
  , l.location_name
from {{models.gohighlevel_contacts_base}} c
left join {{models.gohighlevel_locations_base}} l on c.location_id = l.location_id

Output

Final tables ready for use in explores in Switchboard. Output tables typically represent core business concepts that get joined together.

gohighlevel_contacts_output

select
    c.contact_id as contact_id -- PK
  , c.created_at
  , c.updated_at
  , c.contact_name
  , c.first_name
  , c.last_name
  , c.email
  , c.phone
  , c.phone_clean
  , c.city
  , c.state
  , c.postal_code
  , c.country
  , c.source
  , c.type
  , c.dnd
  , c.custom_field_example_text
  , c.custom_field_example_array
  , c.location_id
  , c.location_name
  , c.attr_session_source
  , c.attr_medium
  , c.attr_url
  , c.attr_referrer
  , c.attr_ga_client_id
  , c.is_reserve_with_google
  , c.last_attr_session_source
  , c.last_attr_medium
  , c.last_attr_url
  , c.last_attr_referrer
  , c.last_attr_ga_client_id
  , c.last_is_reserve_with_google
from {{models.gohighlevel_contacts_facts}} c

GoHighLevel Models

On this page