Salesforce Lead

view: salesforce_lead {
  sql_table_name: lead_reporting ;;

  dimension: external_lead_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.external_lead_id ;;
  }

  dimension: company_name {
    sql: ${TABLE}.company_text ;;
  }

  dimension: convertedaccount_id {
    hidden: yes
    sql: ${TABLE}.convertedaccount_id ;;
  }

  dimension: convertedcontact_id {
    hidden: yes
    sql: ${TABLE}.convertedcontact_id ;;
  }

  dimension: deleted_flag {
    type: yesno
    sql: ${TABLE}.deleted_flag ;;
  }

  dimension: email_address {
    sql: ${TABLE}.email_text ;;
  }

  dimension: fullname {
    sql: ${TABLE}.fullname_text ;;
  }

  dimension: original_source_info {
    sql: ${TABLE}.originalsourceinfo_text ;;
  }

  dimension: original_sourcetype {
    sql: ${TABLE}.originalsourcetype_text ;;
  }

  dimension: owner_idd {
    hidden: yes
    sql: ${owner_id} ;;
  }

  dimension: owner_id {
    hidden: yes
    sql: coalesce(${contact.owner_id},${TABLE}.owner_id) ;;
  }

  dimension: external_account_id {
    type: int
    hidden: yes
    sql: ${TABLE}.external_account_id ;;
  }

  dimension: external_contact_id {
    type: int
    hidden: yes
    sql: ${TABLE}.external_contact_id ;;
  }

  dimension: status_text {
    sql: ${TABLE}.status_text ;;
  }

  dimension: lead_source {
    sql: ${TABLE}.lead_source ;;
  }

  dimension: lead_source_notes {
    sql: ${TABLE}.lead_source_notes ;;
  }

  dimension: converted {
    type: yesno
    sql: ${TABLE}.converted_text::boolean ;;
  }

  dimension: lifecycle_stage {
    sql: coalesce(${contact.lifecycle_stage},${TABLE}.lifecycle_stage) ;;
  }

  dimension: lifecycle_stage_ordered {
  }

  dimension: FTA {
    sql: ${TABLE}.first_touch_attribution ;;
  }

  dimension: MQL_time {
    sql: ${TABLE}.mql_time ;;
  }

  dimension: sql_time {
    sql: ${TABLE}.sql_time ;;
  }

  dimension: sal_time {
    sql: ${TABLE}.sal_time ;;
  }

  dimension: ppc_ad {
    sql: ${TABLE}.ppc_ad ;;
  }

  dimension: ppc_keyword {
    sql: ${TABLE}.ppc_keyword ;;
  }

  dimension: ppc_ad_group {
    sql: ${TABLE}.ppc_ad_group ;;
  }

  dimension: original_referrer {
    sql: ${TABLE}.original_referrer ;;
  }

  dimension: lead_id {
    description: "Lead ID"
    sql: coalesce(${convertedcontact_id},${TABLE}.lead_id) ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_time ;;
  }

  dimension_group: registration {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.registration_time ;;
  }

  measure: count {
    type: count_distinct
    drill_fields: [drill_fields*]
    sql: coalesce(${contact.external_contact_id}, ${external_contact_id},${external_lead_id}) ;;
  }
}

- view: salesforce_lead
  sql_table_name: lead_reporting
  fields:
  - dimension: external_lead_id
    type: int
    primary_key: true
    sql: ${TABLE}.external_lead_id
  - dimension: company_name
    sql: ${TABLE}.company_text
  - dimension: convertedaccount_id
    hidden: true
    sql: ${TABLE}.convertedaccount_id
  - dimension: convertedcontact_id
    hidden: true
    sql: ${TABLE}.convertedcontact_id
  - dimension: deleted_flag
    type: yesno
    sql: ${TABLE}.deleted_flag
  - dimension: email_address
    sql: ${TABLE}.email_text
  - dimension: fullname
    sql: ${TABLE}.fullname_text
  - dimension: original_source_info
    sql: ${TABLE}.originalsourceinfo_text
  - dimension: original_sourcetype
    sql: ${TABLE}.originalsourcetype_text
  - dimension: owner_idd
    hidden: true
    sql: ${owner_id}
  - dimension: owner_id
    hidden: true
    sql: coalesce(${contact.owner_id},${TABLE}.owner_id)
  - dimension: external_account_id
    type: int
    hidden: true
    sql: ${TABLE}.external_account_id
  - dimension: external_contact_id
    type: int
    hidden: true
    sql: ${TABLE}.external_contact_id
  - dimension: status_text
    sql: ${TABLE}.status_text
  - dimension: lead_source
    sql: ${TABLE}.lead_source
  - dimension: lead_source_notes
    sql: ${TABLE}.lead_source_notes
  - dimension: converted
    type: yesno
    sql: ${TABLE}.converted_text::boolean
  - dimension: lifecycle_stage
    sql: coalesce(${contact.lifecycle_stage},${TABLE}.lifecycle_stage)
  - dimension: lifecycle_stage_ordered
  - dimension: FTA
    sql: ${TABLE}.first_touch_attribution
  - dimension: MQL_time
    sql: ${TABLE}.mql_time
  - dimension: sql_time
    sql: ${TABLE}.sql_time
  - dimension: sal_time
    sql: ${TABLE}.sal_time
  - dimension: ppc_ad
    sql: ${TABLE}.ppc_ad
  - dimension: ppc_keyword
    sql: ${TABLE}.ppc_keyword
  - dimension: ppc_ad_group
    sql: ${TABLE}.ppc_ad_group
  - dimension: original_referrer
    sql: ${TABLE}.original_referrer
  - dimension: lead_id
    description: Lead ID
    sql: coalesce(${convertedcontact_id},${TABLE}.lead_id)
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_time
  - dimension_group: registration
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.registration_time
  - measure: count
    type: count_distinct
    drill_fields:
    - drill_fields*
    sql: coalesce(${contact.external_contact_id}, ${external_contact_id},${external_lead_id})