| ##################################################################### |
| # 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] |
| } |
| } |
