GHL Report

ghl-report.sql

WITH 
custom_values AS (
  SELECT 
    location_id,
    COALESCE(
      JSONB_OBJECT_AGG(name, value) FILTER (WHERE name IS NOT NULL), 
      '{}'
    ) AS data
  FROM custom_values
  GROUP BY location_id
), 

custom_fields AS (
  SELECT 
    location_id,
    COALESCE(
      JSONB_OBJECT_AGG(name, raw_data) FILTER (
        WHERE name IN (
          'Contact UTM Source', 
          'Contact UTM Medium', 
          'Latest Campaign SKU', 
          'first_contact_date', 
          'first_appointment_made_date', 
          'first_close_date'
        )
      ), 
      '{}'
    ) AS data
  FROM custom_fields
  GROUP BY location_id
)

SELECT 
  opportunities.id as opportunity_id,
  opportunities.raw_data->>'name' as opportunity_name,  
  contacts.id as contact_id,
  opportunities.raw_data->'contact'->>'name' as contact_name,
  opportunities.raw_data->'contact'->>'email' as contact_email, 
  opportunities.raw_data->'contact'->>'phone' as contact_phone, 
  custom_values.data->>'Practice SKU' as practice_sku, 
  locations.id as subaccount_id,
  custom_values.data->>'Practice Name' as subaccount, 
  opportunities.raw_data->>'pipelineId' as pipeline_id, 
  pipelines.name as pipeline_name, 
  opportunities.raw_data->>'pipelineStageId' as stage_id, 
  jsonb_path_query_first(
    pipelines.stages, 
    format('$[*] ? (@.id == "%s")', opportunities.raw_data->>'pipelineStageId')::jsonpath
  )->>'name' AS stage_name, 
  opportunities.raw_data->>'status' as status, 
  opportunities.raw_data->>'monetaryValue' as monetary_value, 
  jsonb_path_query_first(
    contacts.raw_data->'customFields', 
    format('$[*] ? (@.id == "%s")', custom_fields.data->'Contact UTM Source'->>'id')::jsonpath
  ) ->>'value' AS utm_source,
  jsonb_path_query_first(
    contacts.raw_data->'customFields', 
    format('$[*] ? (@.id == "%s")', custom_fields.data->'Contact UTM Medium'->>'id')::jsonpath
  ) ->>'value' AS utm_medium, 
  (regexp_split_to_array(
    jsonb_path_query_first(
      contacts.raw_data->'customFields', 
      format('$[*] ? (@.id == "%s")', custom_fields.data->'Latest Campaign SKU'->>'id')::jsonpath
    ) ->>'value', 
    '\s+'
  ))[3] AS campaign_sku, 
  opportunities.raw_data->>'assignedTo' as assigned_to, 
  (
    SELECT 
      array_to_string(
        ARRAY(
          SELECT jsonb_array_elements_text(opportunities.raw_data->'contact'->'tags')
        ), 
        ', '
      )      
  ) as tags, 
  jsonb_path_query_first(
    contacts.raw_data->'customFields', 
    format('$[*] ? (@.id == "%s")', custom_fields.data->'first_contact_date'->>'id')::jsonpath
  ) ->>'value' AS first_contact_date,
  jsonb_path_query_first(
    contacts.raw_data->'customFields', 
    format('$[*] ? (@.id == "%s")', custom_fields.data->'first_appointment_made_date'->>'id')::jsonpath
  ) ->>'value' AS first_appointment_made_date, 
  jsonb_path_query_first(
    contacts.raw_data->'customFields', 
    format('$[*] ? (@.id == "%s")', custom_fields.data->'first_close_date'->>'id')::jsonpath
  ) ->>'value' AS first_close_date, 
  opportunities.raw_data->>'createdAt' as created_at, 
  opportunities.raw_data->>'updatedAt' as updated_at
FROM 
  opportunities
INNER JOIN 
  locations ON locations.id = opportunities.location_id
INNER JOIN 
  contacts ON contacts.id = opportunities.raw_data::jsonb->'contact'->>'id'
LEFT JOIN 
  custom_values ON custom_values.location_id = locations.id
LEFT JOIN 
  custom_fields ON custom_fields.location_id = locations.id
LEFT JOIN 
  pipelines ON pipelines.id = opportunities.raw_data->>'pipelineId'
;