data.ml
dbt Venture capital

Company Touches

Company Touches model for venture capital.

inferred

Blocks

company_touches.sql · 110 lines
inferred 
-- models/company_touches.sql
{{ config(materialized='table') }}

SELECT
  intro.date as date
  , intro.description as title
  , intro.associated_company_id as associated_company_id
  , intro.contact_point_id as contact_id
  , 'Introduction' as touch_type
  , intro.outside_company_id as outside_company_id
  , intro.my_id as id
  , intro.points as points
  , NULL as meeting_duration
FROM introductions intro
UNION
SELECT
  event.date as date
  , event.name as title
  , contact.company_id as associated_company_id
  , invite.contact_id as contact_id
  , 'Invite' as touch_type
  , NULL as outside_company_id
  , invite.my_id as id
  , invite.points as points
  , NULL as meeting_duration
FROM invites invite
LEFT JOIN events event
ON event.my_id=invite.event_id
LEFT JOIN contacts contact
ON contact.my_id=invite.contact_id
UNION
SELECT
  ans.created_date as date
  , CONCAT("[",quest.content, "] ",  ans.content)as title
  , contact.company_id as associated_company_id
  , quest.contact_id as contact_id
  , 'Answered Question' as touch_type
  , NULL as outside_company_id
  , ans.my_id as id
  , ans.points as points
  , NULL as meeting_duration
FROM answers ans
LEFT JOIN questions quest
ON ans.question_id=quest.my_id
LEFT JOIN contacts contact
ON contact.my_id=quest.contact_id
UNION
SELECT
  rIntro.created_date as date
  , CONCAT(contact.first_name, ' ', contact.last_name) as title
  , rIntro.partner_company_id as associated_company_id
  , rIntro.candidate_id as contact_id
  , 'Recruiting Introduction' as touch_type
  , NULL as outside_company_id
  , rIntro.my_id as id
  , rIntro.points as points
  , NULL as meeting_duration
FROM recruiting_introductions rIntro
LEFT JOIN contacts contact
ON contact.my_id=rIntro.candidate_id
UNION
SELECT
  req.created_at as date
  , req.request as title
  , req.associated_company_id as associated_company_id
  , NULL as contact_id
  , 'Concierge Request' as touch_type
  , NULL as outside_company_id
  , req.my_id as id
  , '2' as points
  , NULL as meeting_duration
FROM concierge_requests req
UNION
SELECT
space.start_date as date
, space.location as title
, space.company_id as associated_company_id
, NULL as contact_id
, 'Office Space' as touch_type
, NULL as outside_company_id
, space.my_id as id
, space.points as points
, NULL as meeting_duration
FROM office_space space
UNION
SELECT
meeting.date as date
, NULL as title
, meeting.company_id as associated_company_id
, meeting.contact_point_id as contact_id
, 'Meeting' as touch_type
, NULL as outside_company_id
, meeting.my_id as id
, meeting.points as points
, meeting.duration as meeting_duration
FROM meeting meeting

# models/schema.yml
version: 2

models:
  - name: company_touches
    columns:
      - name: meeting_duration
      - name: id
      - name: touch_type
      - name: outside_company_id
      - name: contact_id
      - name: points
      - name: title

Assumptions

  • materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
company_touches.sql · 93 lines
source-preserved validation: parses 
SELECT
  intro.date as date
  , intro.description as title
  , intro.associated_company_id as associated_company_id
  , intro.contact_point_id as contact_id
  , 'Introduction' as touch_type
  , intro.outside_company_id as outside_company_id
  , intro.my_id as id
  , intro.points as points
  , NULL as meeting_duration
FROM introductions intro
UNION
SELECT
  event.date as date
  , event.name as title
  , contact.company_id as associated_company_id
  , invite.contact_id as contact_id
  , 'Invite' as touch_type
  , NULL as outside_company_id
  , invite.my_id as id
  , invite.points as points
  , NULL as meeting_duration
FROM invites invite
LEFT JOIN events event
ON event.my_id=invite.event_id
LEFT JOIN contacts contact
ON contact.my_id=invite.contact_id
UNION
SELECT
  ans.created_date as date
  , CONCAT("[",quest.content, "] ",  ans.content)as title
  , contact.company_id as associated_company_id
  , quest.contact_id as contact_id
  , 'Answered Question' as touch_type
  , NULL as outside_company_id
  , ans.my_id as id
  , ans.points as points
  , NULL as meeting_duration
FROM answers ans
LEFT JOIN questions quest
ON ans.question_id=quest.my_id
LEFT JOIN contacts contact
ON contact.my_id=quest.contact_id
UNION
SELECT
  rIntro.created_date as date
  , CONCAT(contact.first_name, ' ', contact.last_name) as title
  , rIntro.partner_company_id as associated_company_id
  , rIntro.candidate_id as contact_id
  , 'Recruiting Introduction' as touch_type
  , NULL as outside_company_id
  , rIntro.my_id as id
  , rIntro.points as points
  , NULL as meeting_duration
FROM recruiting_introductions rIntro
LEFT JOIN contacts contact
ON contact.my_id=rIntro.candidate_id
UNION
SELECT
  req.created_at as date
  , req.request as title
  , req.associated_company_id as associated_company_id
  , NULL as contact_id
  , 'Concierge Request' as touch_type
  , NULL as outside_company_id
  , req.my_id as id
  , '2' as points
  , NULL as meeting_duration
FROM concierge_requests req
UNION
SELECT
space.start_date as date
, space.location as title
, space.company_id as associated_company_id
, NULL as contact_id
, 'Office Space' as touch_type
, NULL as outside_company_id
, space.my_id as id
, space.points as points
, NULL as meeting_duration
FROM office_space space
UNION
SELECT
meeting.date as date
, NULL as title
, meeting.company_id as associated_company_id
, meeting.contact_point_id as contact_id
, 'Meeting' as touch_type
, NULL as outside_company_id
, meeting.my_id as id
, meeting.points as points
, meeting.duration as meeting_duration
FROM meeting meeting
company_touches.view.lkml · 144 lines
inferred 
view: company_touches {
  derived_table: {
    sql: SELECT
  intro.date as date
  , intro.description as title
  , intro.associated_company_id as associated_company_id
  , intro.contact_point_id as contact_id
  , 'Introduction' as touch_type
  , intro.outside_company_id as outside_company_id
  , intro.my_id as id
  , intro.points as points
  , NULL as meeting_duration
FROM introductions intro
UNION
SELECT
  event.date as date
  , event.name as title
  , contact.company_id as associated_company_id
  , invite.contact_id as contact_id
  , 'Invite' as touch_type
  , NULL as outside_company_id
  , invite.my_id as id
  , invite.points as points
  , NULL as meeting_duration
FROM invites invite
LEFT JOIN events event
ON event.my_id=invite.event_id
LEFT JOIN contacts contact
ON contact.my_id=invite.contact_id
UNION
SELECT
  ans.created_date as date
  , CONCAT("[",quest.content, "] ",  ans.content)as title
  , contact.company_id as associated_company_id
  , quest.contact_id as contact_id
  , 'Answered Question' as touch_type
  , NULL as outside_company_id
  , ans.my_id as id
  , ans.points as points
  , NULL as meeting_duration
FROM answers ans
LEFT JOIN questions quest
ON ans.question_id=quest.my_id
LEFT JOIN contacts contact
ON contact.my_id=quest.contact_id
UNION
SELECT
  rIntro.created_date as date
  , CONCAT(contact.first_name, ' ', contact.last_name) as title
  , rIntro.partner_company_id as associated_company_id
  , rIntro.candidate_id as contact_id
  , 'Recruiting Introduction' as touch_type
  , NULL as outside_company_id
  , rIntro.my_id as id
  , rIntro.points as points
  , NULL as meeting_duration
FROM recruiting_introductions rIntro
LEFT JOIN contacts contact
ON contact.my_id=rIntro.candidate_id
UNION
SELECT
  req.created_at as date
  , req.request as title
  , req.associated_company_id as associated_company_id
  , NULL as contact_id
  , 'Concierge Request' as touch_type
  , NULL as outside_company_id
  , req.my_id as id
  , '2' as points
  , NULL as meeting_duration
FROM concierge_requests req
UNION
SELECT
space.start_date as date
, space.location as title
, space.company_id as associated_company_id
, NULL as contact_id
, 'Office Space' as touch_type
, NULL as outside_company_id
, space.my_id as id
, space.points as points
, NULL as meeting_duration
FROM office_space space
UNION
SELECT
meeting.date as date
, NULL as title
, meeting.company_id as associated_company_id
, meeting.contact_point_id as contact_id
, 'Meeting' as touch_type
, NULL as outside_company_id
, meeting.my_id as id
, meeting.points as points
, meeting.duration as meeting_duration
FROM meeting meeting ;;
    persist_for: "4 hours"
  }

  dimension: meeting_duration {
    type: int
  }

  dimension: id {
    type: int
  }

  dimension: touch_type {
  }

  dimension: outside_company_id {
    type: int
  }

  dimension: contact_id {
    type: int
  }

  dimension: points {
    type: int
  }

  dimension: title {
  }

  dimension_group: date {
    type: time
    timeframes: [time, date, week, month]
  }

  measure: points_sum {
    type: sum
    sql: company_touches.points ;;
  }

  measure: points_percent {
    type: percent_of_total
    sql: ${points_sum} ;;
  }

  measure: count {
    type: count_distinct
    sql: ${touch_type} + ${id} ;;
  }
}
company_touches.view.lookml · 125 lines
source-preserved 
- view: company_touches
  derived_table:
    sql: SELECT
  intro.date as date
  , intro.description as title
  , intro.associated_company_id as associated_company_id
  , intro.contact_point_id as contact_id
  , 'Introduction' as touch_type
  , intro.outside_company_id as outside_company_id
  , intro.my_id as id
  , intro.points as points
  , NULL as meeting_duration
FROM introductions intro
UNION
SELECT
  event.date as date
  , event.name as title
  , contact.company_id as associated_company_id
  , invite.contact_id as contact_id
  , 'Invite' as touch_type
  , NULL as outside_company_id
  , invite.my_id as id
  , invite.points as points
  , NULL as meeting_duration
FROM invites invite
LEFT JOIN events event
ON event.my_id=invite.event_id
LEFT JOIN contacts contact
ON contact.my_id=invite.contact_id
UNION
SELECT
  ans.created_date as date
  , CONCAT("[",quest.content, "] ",  ans.content)as title
  , contact.company_id as associated_company_id
  , quest.contact_id as contact_id
  , 'Answered Question' as touch_type
  , NULL as outside_company_id
  , ans.my_id as id
  , ans.points as points
  , NULL as meeting_duration
FROM answers ans
LEFT JOIN questions quest
ON ans.question_id=quest.my_id
LEFT JOIN contacts contact
ON contact.my_id=quest.contact_id
UNION
SELECT
  rIntro.created_date as date
  , CONCAT(contact.first_name, ' ', contact.last_name) as title
  , rIntro.partner_company_id as associated_company_id
  , rIntro.candidate_id as contact_id
  , 'Recruiting Introduction' as touch_type
  , NULL as outside_company_id
  , rIntro.my_id as id
  , rIntro.points as points
  , NULL as meeting_duration
FROM recruiting_introductions rIntro
LEFT JOIN contacts contact
ON contact.my_id=rIntro.candidate_id
UNION
SELECT
  req.created_at as date
  , req.request as title
  , req.associated_company_id as associated_company_id
  , NULL as contact_id
  , 'Concierge Request' as touch_type
  , NULL as outside_company_id
  , req.my_id as id
  , '2' as points
  , NULL as meeting_duration
FROM concierge_requests req
UNION
SELECT
space.start_date as date
, space.location as title
, space.company_id as associated_company_id
, NULL as contact_id
, 'Office Space' as touch_type
, NULL as outside_company_id
, space.my_id as id
, space.points as points
, NULL as meeting_duration
FROM office_space space
UNION
SELECT
meeting.date as date
, NULL as title
, meeting.company_id as associated_company_id
, meeting.contact_point_id as contact_id
, 'Meeting' as touch_type
, NULL as outside_company_id
, meeting.my_id as id
, meeting.points as points
, meeting.duration as meeting_duration
FROM meeting meeting
    persist_for: 4 hours
  fields:
  - dimension: meeting_duration
    type: int
  - dimension: id
    type: int
  - dimension: touch_type
  - dimension: outside_company_id
    type: int
  - dimension: contact_id
    type: int
  - dimension: points
    type: int
  - dimension: title
  - dimension_group: date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
  - measure: points_sum
    type: sum
    sql: company_touches.points
  - measure: points_percent
    type: percent_of_total
    sql: ${points_sum}
  - measure: count
    type: count_distinct
    sql: ${touch_type} + ${id}

Contents

  • company_touches.sql
  • company_touches.sql
  • company_touches.view.lkml
  • company_touches.view.lookml