data.ml
LKML Ecommerce supplies

Orders Shopping Cart

Orders Shopping Cart model for ecommerce supplies.

inferred

Blocks

orders_shopping_cart.view.lkml · 384 lines
inferred 
view: orders_shopping_cart {
  sql_table_name: Orders_ShoppingCart ;;

  dimension: base_item_name {
    type: string
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN '16oz'
  ELSE ${ordered_product_variant_name}
END ;;
  }

  dimension: base_item_quantity {
    type: number
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${quantity} * 2
  ELSE ${quantity}
END ;;
  }

  dimension: carrier {
    sql: ${TABLE}.Carrier ;;
  }

  dimension: cart_type {
    type: int
    sql: ${TABLE}.CartType ;;
  }

  dimension: chosen_color {
    sql: ${TABLE}.ChosenColor ;;
  }

  dimension: chosen_color_skumodifier {
    sql: ${TABLE}.ChosenColorSKUModifier ;;
  }

  dimension: chosen_size {
    sql: ${TABLE}.ChosenSize ;;
  }

  dimension: chosen_size_skumodifier {
    sql: ${TABLE}.ChosenSizeSKUModifier ;;
  }

  dimension: color_option_prompt {
    sql: ${TABLE}.ColorOptionPrompt ;;
  }

  dimension: customer_enters_price {
    type: yesno
    sql: ${TABLE}.CustomerEntersPrice ;;
  }

  dimension: customer_enters_price_prompt {
    sql: ${TABLE}.CustomerEntersPricePrompt ;;
  }

  dimension: customer_id {
    type: int
    sql: ${TABLE}.CustomerID ;;
  }

  dimension: distributor_id {
    type: int
    sql: ${TABLE}.DistributorID ;;
  }

  dimension: download_category {
    sql: ${TABLE}.DownloadCategory ;;
  }

  dimension: download_location {
    sql: ${TABLE}.DownloadLocation ;;
  }

  dimension: download_status {
    type: int
    sql: ${TABLE}.DownloadStatus ;;
  }

  dimension: download_valid_days {
    type: int
    sql: ${TABLE}.DownloadValidDays ;;
  }

  dimension: email_sent {
    type: yesno
    sql: ${TABLE}.EmailSent ;;
  }

  dimension: extension_data {
    sql: ${TABLE}.ExtensionData ;;
  }

  dimension: first_email_sent {
    type: yesno
    sql: ${TABLE}.FirstEmailSent ;;
  }

  dimension: free_shipping {
    type: yesno
    sql: ${TABLE}.FreeShipping ;;
  }

  dimension: gift_registry_for_customer_id {
    type: int
    sql: ${TABLE}.GiftRegistryForCustomerID ;;
  }

  dimension: gtin {
    sql: ${TABLE}.GTIN ;;
  }

  dimension: is_akit {
    type: yesno
    sql: ${TABLE}.IsAKit ;;
  }

  dimension: is_apack {
    type: yesno
    sql: ${TABLE}.IsAPack ;;
  }

  dimension: is_download {
    type: yesno
    sql: ${TABLE}.IsDownload ;;
  }

  dimension: is_gift {
    sql: ${TABLE}.IsGift ;;
  }

  dimension: is_secure_attachment {
    type: yesno
    sql: ${TABLE}.IsSecureAttachment ;;
  }

  dimension: is_ship_separately {
    type: yesno
    sql: ${TABLE}.IsShipSeparately ;;
  }

  dimension: is_system {
    type: yesno
    sql: ${TABLE}.IsSystem ;;
  }

  dimension: is_taxable {
    type: int
    sql: ${TABLE}.IsTaxable ;;
  }

  dimension: notes {
    sql: ${TABLE}.Notes ;;
  }

  dimension: order_number {
    type: int
    sql: ${TABLE}.OrderNumber ;;
  }

  dimension: pkey {
    primary_key: yes
    hidden: yes
    sql: CONCAT(${order_number}, ${shopping_cart_rec_id}) ;;
  }

  dimension: ordered_product_extended_price {
    sql: ${TABLE}.OrderedProductExtendedPrice ;;
  }

  dimension: ordered_product_manufacturer_part_number {
    sql: ${TABLE}.OrderedProductManufacturerPartNumber ;;
  }

  dimension: ordered_product_name {
    sql: CONVERT(varchar(80), ${TABLE}.OrderedProductName) ;;
  }

  dimension: ordered_product_price {
    sql: ${TABLE}.OrderedProductPrice ;;
  }

  dimension: ordered_product_quantity_discount_id {
    type: int
    sql: ${TABLE}.OrderedProductQuantityDiscountID ;;
  }

  dimension: ordered_product_quantity_discount_name {
    sql: ${TABLE}.OrderedProductQuantityDiscountName ;;
  }

  dimension: ordered_product_quantity_discount_percent {
    sql: ${TABLE}.OrderedProductQuantityDiscountPercent ;;
  }

  dimension: ordered_product_regular_price {
    sql: ${TABLE}.OrderedProductRegularPrice ;;
  }

  dimension: ordered_product_sale_price {
    sql: ${TABLE}.OrderedProductSalePrice ;;
  }

  dimension: ordered_product_sku {
    sql: ${TABLE}.OrderedProductSKU ;;
  }

  dimension: ordered_product_variant_name {
    sql: CONVERT(varchar(80), ${TABLE}.OrderedProductVariantName) ;;
  }

  dimension: ordered_product_weight {
    sql: ${TABLE}.OrderedProductWeight ;;
  }

  dimension: product_id {
    type: int
    sql: ${TABLE}.ProductID ;;
  }

  dimension: quantity {
    type: int
    sql: ${TABLE}.Quantity ;;
  }

  dimension: shipped_on {
    sql: ${TABLE}.ShippedOn ;;
  }

  dimension: shipping_address_id {
    type: int
    sql: ${TABLE}.ShippingAddressID ;;
  }

  dimension: shipping_detail {
    sql: ${TABLE}.ShippingDetail ;;
  }

  dimension: shipping_method {
    sql: ${TABLE}.ShippingMethod ;;
  }

  dimension: shipping_method_id {
    type: int
    sql: ${TABLE}.ShippingMethodID ;;
  }

  dimension: shipping_status {
    sql: ${TABLE}.ShippingStatus ;;
  }

  dimension: shipping_tracking_number {
    sql: ${TABLE}.ShippingTrackingNumber ;;
  }

  dimension: shopping_cart_rec_id {
    type: int
    sql: ${TABLE}.ShoppingCartRecID ;;
  }

  dimension: size_option_prompt {
    sql: ${TABLE}.SizeOptionPrompt ;;
  }

  dimension: subscription_interval {
    type: int
    sql: ${TABLE}.SubscriptionInterval ;;
  }

  dimension: subscription_interval_type {
    type: int
    sql: ${TABLE}.SubscriptionIntervalType ;;
  }

  dimension: tax_class_id {
    type: int
    sql: ${TABLE}.TaxClassID ;;
  }

  dimension: tax_rate {
    sql: ${TABLE}.TaxRate ;;
  }

  dimension: text_option {
    sql: ${TABLE}.TextOption ;;
  }

  dimension: text_option_prompt {
    sql: ${TABLE}.TextOptionPrompt ;;
  }

  dimension: variant_id {
    type: int
    sql: ${TABLE}.VariantID ;;
  }

  dimension: in_gallon {
    type: number
    sql: CASE
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${${TABLE}.calculated_inventory} * 0.03125
  ELSE 0
END ;;
  }

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

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

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

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

  measure: count_of_line_items_not_quantity {
    description: "This does not account for quantity ordered"
    type: count
    drill_fields: [detail*]
  }

  measure: total_quantity_sum {
    type: sum
    sql: ${base_item_quantity} ;;
  }

  measure: gallons_needed {
    type: sum
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${base_item_quantity} * 0.125
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${base_item_quantity} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${base_item_quantity} * 0.03125
  WHEN ${ordered_product_variant_name} = '15ml box of 140' THEN ${base_item_quantity} * 0.555
  WHEN ${ordered_product_variant_name} = '15ml box of 30' THEN ${base_item_quantity} * 0.12
  WHEN ${ordered_product_variant_name} = '15ml' THEN ${base_item_quantity} * 0.004
  WHEN ${ordered_product_variant_name} = '4ml' THEN ${base_item_quantity} * 0.0011
  ELSE 0
END ;;
  }

  measure: net_gallons {
    type: max
    sql: ${in_gallon} ;;
  }

  measure: make_gallon {
    type: sum
    sql: ${in_gallon} ;;
  }

  measure: gallons_on_hand {
    type: sum
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${${TABLE}.calculated_inventory} * 0.03125
  WHEN ${ordered_product_variant_name} = '15ml box of 140' THEN  ${${TABLE}.calculated_inventory} * 0.555
  WHEN ${ordered_product_variant_name} = '15ml box of 30' THEN  ${${TABLE}.calculated_inventory} * 0.12
  WHEN ${ordered_product_variant_name} = '15ml' THEN  ${${TABLE}.calculated_inventory} * 0.004
  WHEN ${ordered_product_variant_name} = '4ml' THEN  ${${TABLE}.calculated_inventory} * 0.0011
  ELSE 0
END ;;
  }

  measure: 4_mo_sales {
    type: number
    sql: ${total_quantity_sum} * 4 ;;
  }
}
orders_shopping_cart.view.lookml · 249 lines
source-preserved 
- view: orders_shopping_cart
  sql_table_name: Orders_ShoppingCart
  fields:
  - dimension: base_item_name
    type: string
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN '16oz'
  ELSE ${ordered_product_variant_name}
END
  - dimension: base_item_quantity
    type: number
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${quantity} * 2
  ELSE ${quantity}
END
  - dimension: carrier
    sql: ${TABLE}.Carrier
  - dimension: cart_type
    type: int
    sql: ${TABLE}.CartType
  - dimension: chosen_color
    sql: ${TABLE}.ChosenColor
  - dimension: chosen_color_skumodifier
    sql: ${TABLE}.ChosenColorSKUModifier
  - dimension: chosen_size
    sql: ${TABLE}.ChosenSize
  - dimension: chosen_size_skumodifier
    sql: ${TABLE}.ChosenSizeSKUModifier
  - dimension: color_option_prompt
    sql: ${TABLE}.ColorOptionPrompt
  - dimension: customer_enters_price
    type: yesno
    sql: ${TABLE}.CustomerEntersPrice
  - dimension: customer_enters_price_prompt
    sql: ${TABLE}.CustomerEntersPricePrompt
  - dimension: customer_id
    type: int
    sql: ${TABLE}.CustomerID
  - dimension: distributor_id
    type: int
    sql: ${TABLE}.DistributorID
  - dimension: download_category
    sql: ${TABLE}.DownloadCategory
  - dimension: download_location
    sql: ${TABLE}.DownloadLocation
  - dimension: download_status
    type: int
    sql: ${TABLE}.DownloadStatus
  - dimension: download_valid_days
    type: int
    sql: ${TABLE}.DownloadValidDays
  - dimension: email_sent
    type: yesno
    sql: ${TABLE}.EmailSent
  - dimension: extension_data
    sql: ${TABLE}.ExtensionData
  - dimension: first_email_sent
    type: yesno
    sql: ${TABLE}.FirstEmailSent
  - dimension: free_shipping
    type: yesno
    sql: ${TABLE}.FreeShipping
  - dimension: gift_registry_for_customer_id
    type: int
    sql: ${TABLE}.GiftRegistryForCustomerID
  - dimension: gtin
    sql: ${TABLE}.GTIN
  - dimension: is_akit
    type: yesno
    sql: ${TABLE}.IsAKit
  - dimension: is_apack
    type: yesno
    sql: ${TABLE}.IsAPack
  - dimension: is_download
    type: yesno
    sql: ${TABLE}.IsDownload
  - dimension: is_gift
    sql: ${TABLE}.IsGift
  - dimension: is_secure_attachment
    type: yesno
    sql: ${TABLE}.IsSecureAttachment
  - dimension: is_ship_separately
    type: yesno
    sql: ${TABLE}.IsShipSeparately
  - dimension: is_system
    type: yesno
    sql: ${TABLE}.IsSystem
  - dimension: is_taxable
    type: int
    sql: ${TABLE}.IsTaxable
  - dimension: notes
    sql: ${TABLE}.Notes
  - dimension: order_number
    type: int
    sql: ${TABLE}.OrderNumber
  - dimension: pkey
    primary_key: true
    hidden: true
    sql: CONCAT(${order_number}, ${shopping_cart_rec_id})
  - dimension: ordered_product_extended_price
    sql: ${TABLE}.OrderedProductExtendedPrice
  - dimension: ordered_product_manufacturer_part_number
    sql: ${TABLE}.OrderedProductManufacturerPartNumber
  - dimension: ordered_product_name
    sql: CONVERT(varchar(80), ${TABLE}.OrderedProductName)
  - dimension: ordered_product_price
    sql: ${TABLE}.OrderedProductPrice
  - dimension: ordered_product_quantity_discount_id
    type: int
    sql: ${TABLE}.OrderedProductQuantityDiscountID
  - dimension: ordered_product_quantity_discount_name
    sql: ${TABLE}.OrderedProductQuantityDiscountName
  - dimension: ordered_product_quantity_discount_percent
    sql: ${TABLE}.OrderedProductQuantityDiscountPercent
  - dimension: ordered_product_regular_price
    sql: ${TABLE}.OrderedProductRegularPrice
  - dimension: ordered_product_sale_price
    sql: ${TABLE}.OrderedProductSalePrice
  - dimension: ordered_product_sku
    sql: ${TABLE}.OrderedProductSKU
  - dimension: ordered_product_variant_name
    sql: CONVERT(varchar(80), ${TABLE}.OrderedProductVariantName)
  - dimension: ordered_product_weight
    sql: ${TABLE}.OrderedProductWeight
  - dimension: product_id
    type: int
    sql: ${TABLE}.ProductID
  - dimension: quantity
    type: int
    sql: ${TABLE}.Quantity
  - dimension: shipped_on
    sql: ${TABLE}.ShippedOn
  - dimension: shipping_address_id
    type: int
    sql: ${TABLE}.ShippingAddressID
  - dimension: shipping_detail
    sql: ${TABLE}.ShippingDetail
  - dimension: shipping_method
    sql: ${TABLE}.ShippingMethod
  - dimension: shipping_method_id
    type: int
    sql: ${TABLE}.ShippingMethodID
  - dimension: shipping_status
    sql: ${TABLE}.ShippingStatus
  - dimension: shipping_tracking_number
    sql: ${TABLE}.ShippingTrackingNumber
  - dimension: shopping_cart_rec_id
    type: int
    sql: ${TABLE}.ShoppingCartRecID
  - dimension: size_option_prompt
    sql: ${TABLE}.SizeOptionPrompt
  - dimension: subscription_interval
    type: int
    sql: ${TABLE}.SubscriptionInterval
  - dimension: subscription_interval_type
    type: int
    sql: ${TABLE}.SubscriptionIntervalType
  - dimension: tax_class_id
    type: int
    sql: ${TABLE}.TaxClassID
  - dimension: tax_rate
    sql: ${TABLE}.TaxRate
  - dimension: text_option
    sql: ${TABLE}.TextOption
  - dimension: text_option_prompt
    sql: ${TABLE}.TextOptionPrompt
  - dimension: variant_id
    type: int
    sql: ${TABLE}.VariantID
  - dimension: in_gallon
    type: number
    sql: CASE
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${${TABLE}.calculated_inventory} * 0.03125
  ELSE 0
END
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.CreatedOn
  - dimension_group: distributor_email_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DistributorEmailSentOn
  - dimension_group: download_released
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DownloadReleasedOn
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.UpdatedOn
  - measure: count_of_line_items_not_quantity
    type: count
    description: This does not account for quantity ordered
    drill_fields:
    - detail*
  - measure: total_quantity_sum
    type: sum
    sql: ${base_item_quantity}
  - measure: gallons_needed
    type: sum
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${base_item_quantity} * 0.125
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${base_item_quantity} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${base_item_quantity} * 0.03125
  WHEN ${ordered_product_variant_name} = '15ml box of 140' THEN ${base_item_quantity} * 0.555
  WHEN ${ordered_product_variant_name} = '15ml box of 30' THEN ${base_item_quantity} * 0.12
  WHEN ${ordered_product_variant_name} = '15ml' THEN ${base_item_quantity} * 0.004
  WHEN ${ordered_product_variant_name} = '4ml' THEN ${base_item_quantity} * 0.0011
  ELSE 0
END
  - measure: net_gallons
    type: max
    sql: ${in_gallon}
  - measure: make_gallon
    type: sum
    sql: ${in_gallon}
  - measure: gallons_on_hand
    type: sum
    sql: CASE
  WHEN ${ordered_product_variant_name} = '32oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '16oz' THEN ${${TABLE}.calculated_inventory} * 0.125
  WHEN ${ordered_product_variant_name} = '4oz' THEN ${${TABLE}.calculated_inventory} * 0.03125
  WHEN ${ordered_product_variant_name} = '15ml box of 140' THEN  ${${TABLE}.calculated_inventory} * 0.555
  WHEN ${ordered_product_variant_name} = '15ml box of 30' THEN  ${${TABLE}.calculated_inventory} * 0.12
  WHEN ${ordered_product_variant_name} = '15ml' THEN  ${${TABLE}.calculated_inventory} * 0.004
  WHEN ${ordered_product_variant_name} = '4ml' THEN  ${${TABLE}.calculated_inventory} * 0.0011
  ELSE 0
END
  - measure: 4_mo_sales
    type: number
    sql: ${total_quantity_sum} * 4

Contents

  • orders_shopping_cart.view.lkml
  • orders_shopping_cart.view.lookml