vector_customers
diff --git a/explores/vector_customers.view.lkml b/explores/vector_customers.view.lkml new file mode 100644 index 0000000..78626d3 --- /dev/null +++ b/explores/vector_customers.view.lkml
@@ -0,0 +1,32 @@ +include: "/views/vector/customers.view.lkml" +# include: "/views/nal.view.lkml" +# include: "/views/revenue/account_sort.view.lkml" +# include: "/views/account_stats.view.lkml" +# include: "/views/apac_account_metadata.view.lkml" +explore: vector_customers { + view_label: "Account" + description: "Contains data on vector customers sourced from Vector, Anaplan and MDM" + # join: nal { + # view_label: "Account" + # sql_on: ${vector_customers.nal_id} = ${nal.nal_id} ;; + # type: left_outer + # relationship: many_to_one + # } + # join: account_sort { + # type: left_outer + # sql: ${vector_customers.reporting_id} = ${account_sort.reporting_id} ;; + # relationship: many_to_one + # } + # join: account_stats { + # view_label: "Account" + # sql_on: ${account_stats.reporting_id} = ${vector_customers.reporting_id} ;; + # relationship: many_to_one + # fields: [account_stats.rainbow_fields*] + # } + # join: apac_account_metadata { + # view_label: "Account" + # sql_on: ${apac_account_metadata.reporting_id} = ${vector_customers.reporting_id} ;; + # type: left_outer + # relationship: many_to_one + # } +}
diff --git a/views/vector/.gitkeep b/views/vector/.gitkeep new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/views/vector/.gitkeep
diff --git a/views/vector/customers.view.lkml b/views/vector/customers.view.lkml new file mode 100644 index 0000000..1af4c04 --- /dev/null +++ b/views/vector/customers.view.lkml
@@ -0,0 +1,244 @@ +##################################################################### +# 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] + } +}