#####################################################################
# Owner: paolarenteria@, hillz@
# Created Date: January 2022
# Purpose: Imports the vector customers table with PK as reporting ID
#####################################################################
include: "/views/region.view.lkml"
view: vector_customers {
  extends: [region]
  derived_table: {
    sql: SELECT
             'reporting_id'           reporting_id
           , 'access_key_hierarchy'   access_key_hierarchy
           , STRUCT(
                 'account_name' as account_name
               , 'segment' as segment
               , 'nal_id' as nal_id
               , 'nal_name' as nal_name
               , 'nal_cluster' as nal_cluster
               , 'region' as region
               , 'sub_region' as sub_region
               , 'micro_region' as micro_region
               , 'smb_region' as smb_region
               , 'smb_sub_region' as smb_sub_region
               , 'industry' as industry
               , 'sub_industry' as sub_industry
               , 'micro_industry' as micro_industry
               , false as is_digital_native
               , 'nal_region_detail' as nal_region_detail
             )                        core
           , 'sfdc_account_id'        as sfdc_account_id
           , 'raw_domains'            raw_domains
           , 'raw_websites'           raw_websites
           , 'sub_account_name'       sub_account_name
           , STRUCT(
                  false as startup
               , 'funding_bucket' as funding_bucket
               , 'last_funding_type' as last_funding_type
               , 'verticals' as verticals
             )                        digital_native_details
           , 'nal_sub_cluster'        nal_sub_cluster
           , false is_isv;;
  }
  ##### PRIMARY KEY #####
  dimension: sfdc_account_id {
    label: "SFDC Account ID"
    group_label: "Identifiers"
    description: "The Vector id of the account. If this field equals reporting_id then the details are for the parent account otherwise the details are for a child account."
    primary_key: yes
    type: string
    sql: ${TABLE}.sfdc_account_id ;;
  }
  ##### FOREIGN KEYS #####
  dimension: reporting_id {
    group_label: "Identifiers"
    description: "The Vector id of the parent account"
    type: string
    sql: ${TABLE}.reporting_id;;
    link: {
      url: "https://vector.lightning.force.com/lightning/r/Account/{{value}}/view"
      label: "View Account in Vector"
    }
  }
  ##### DIMENSIONS #####
  dimension: access_key_hierarchy {
    hidden: yes
    description: "Access Key Hierarchy which determines LDAPs that can access accounts"
    sql: ${TABLE}.access_key_hierarchy ;;
    type: string
  }
  dimension: account_name {
    group_label: "Identifiers"
    description: "The name of the parent account sourced from Anaplan"
    type: string
    sql: ${TABLE}.sub_account_name ;;
    link: {
      url: "/dashboards-next/82?Sub-Account+Name={{ filterable_value | url_encode }}"
      label: "Revenue Trends"
    }
    link: {
      url: "/dashboards-next/137?Sub-Account+Name={{ filterable_value | url_encode }}"
      label: "Product Usage Trends"
    }
  }
  dimension: account_is_startup {
    group_label: "Digital Native Details"
    description: "Returns yes if the child account is flagged as a Startup"
    type: yesno
    sql: ${TABLE}.digital_native_details.startup ;;
  }
  dimension: digital_native_funding_bucket {
    group_label: "Digital Native Details"
    label: "Funding Bucket"
    description: "Funding categorized in buckets for digital native accounts, e.g. 0-0.5M"
    type: string
    sql: ${TABLE}.digital_native_details.funding_bucket ;;
  }
  dimension: digital_native_last_funding_type {
    group_label: "Digital Native Details"
    label: "Last Funding Type"
    description: "Last funding type for digital native accounts, e.g. angel, series_a, etc."
    type: string
    sql: ${TABLE}.digital_native_details.last_funding_type ;;
  }
  dimension: digital_native_verticals {
    group_label: "Digital Native Details"
    label: "Verticals"
    description: "Vertical for digital native accounts"
    type: string
    sql: ${TABLE}.digital_native_details.verticals ;;
  }
  dimension: industry {
    group_label: "Industry"
    view_label: "Account"
    description: "Industry of the account"
    sql: ${TABLE}.core.industry ;;
  }
  dimension: is_digital_native {
    description: "Digital Native or Traditional"
    label: "Digital Native vs. Traditional"
    suggestions: ["Digital Native", "Traditional"]
    type: string
    sql: IF(${TABLE}.core.is_digital_native, "Digital Native", "Traditional") ;;
  }
  dimension: is_isv {
    label: "Is ISV?"
    description: "Is this an ISV customer based on data loaded from gtp_analysis_prod.ecosystem_partners"
    type: yesno
    sql: ${TABLE}.is_isv ;;
  }
  dimension: micro_region {
    sql: ${TABLE}.core.micro_region ;;
  }
  dimension: micro_industry {
    view_label: "Account"
    group_label: "Industry"
    description: "Micro Industry of the account"
    sql: ${TABLE}.core.micro_industry ;;
  }
  dimension: nal_cluster {
    sql: ${TABLE}.core.nal_cluster ;;
  }
  dimension: nal_id {
    sql: ${TABLE}.core.nal_id ;;
  }
  dimension: nal_name {
    sql: ${TABLE}.core.nal_name ;;
  }
  dimension: nal_sub_cluster {
    label: "NAL Subcluster"
    group_label: "Region"
    description: "Sub-cluster for a NAL"
    type: string
    sql: ${TABLE}.nal_sub_cluster ;;
  }
  dimension: region {
    sql: ${TABLE}.core.region ;;
  }
  dimension: reporting_account_name {
    group_label: "Identifiers"
    description: "Top most non roll up “customer” account in the hierarchy"
    type: string
    sql: ${TABLE}.core.account_name ;;
    required_fields: [reporting_id]
    link: {
      url: "/dashboards-next/100?Reporting+Account+Name={{ filterable_value | url_encode }}"
      label: "Account Lookup"
    }
    link: {
      url: "https://vector.lightning.force.com/lightning/r/Account/{{reporting_id._value}}/view"
      label: "View Account in Vector"
    }
    link: {
      url: "/dashboards-next/82?Reporting+Account+Name={{ filterable_value | url_encode }}"
      label: "Revenue Trends"
    }
    link: {
      url: "/dashboards-next/137?Reporting+Account+Name={{ filterable_value | url_encode }}"
      label: "Product Usage Trends"
    }
  }
  dimension: segment {
    description: "Segment, e.g. Scaled, Corporate, Select, Enterprise"
    type: string
    sql: ${TABLE}.core.segment ;;
  }
  dimension: segment_smb {
    hidden: yes
    description: "If segment is null then shown as scaled, this is for the join in Project Logan"
    type: string
    sql: IFNULL(${TABLE}.core.segment,'Scaled') ;;
  }
  dimension: smb_region {
    label: "SMB Region"
    description: "Region for SMB"
    type: string
    sql: ${TABLE}.core.smb_region ;;
  }
  dimension: smb_sub_region {
    label: "SMB Sub-Region"
    description: "Sub-Region for SMB"
    type: string
    sql: ${TABLE}.core.smb_sub_region ;;
  }
  dimension: sub_industry {
    view_label: "Account"
    group_label: "Industry"
    description: "Sub Industry of the account"
    sql: ${TABLE}.core.sub_industry ;;
  }
  dimension: sub_region {
    sql: ${TABLE}.core.sub_region ;;
  }
  dimension: vector_website {
    description: "An array of websites from Vector, unnested at join level"
    hidden: yes
    type: string
    sql: ${TABLE}.raw_websites ;;
  }
  dimension: vector_domain {
    description: "An array of domains from Vector, unnested at join level"
    hidden: yes
    type: string
    sql: ${TABLE}.raw_domains;;
  }
  #### MEASURES #####
  measure: count_sfdc_account_id {
    label: "Count SFDC Account ID"
    description: "Counts unique SFDC account IDs"
    type: count
    value_format_name: decimal_0
    drill_fields: [sfdc_account_id, reporting_account_name, segment, is_digital_native]
  }
  measure: count_reporting_id {
    description: "Counts unique reporting IDs"
    type: count_distinct
    sql: ${TABLE}.reporting_id ;;
    value_format_name: decimal_0
    drill_fields: [sfdc_account_id, reporting_account_name, segment, is_digital_native]
  }
  ##### SETS #####
  set: digital_native_details {
    fields: [account_is_startup, digital_native_funding_bucket, digital_native_last_funding_type, digital_native_verticals, is_digital_native]
  }
}