data.ml
LKML Ecommerce supplies

Orders

Orders model for ecommerce supplies.

inferred

Blocks

orders.view.lkml · 749 lines
inferred 
view: orders {
  sql_table_name: Orders ;;

  dimension: affiliate_commission_recorded {
    type: yesno
    sql: ${TABLE}.AffiliateCommissionRecorded = 1 ;;
  }

  dimension: affiliate_id {
    type: int
    sql: ${TABLE}.AffiliateID ;;
  }

  dimension: already_confirmed {
    type: yesno
    sql: ${TABLE}.AlreadyConfirmed = 1 ;;
  }

  dimension: authorization_code {
    sql: ${TABLE}.AuthorizationCode ;;
  }

  dimension: authorization_pnref {
    hidden: yes
    sql: ${TABLE}.AuthorizationPNREF ;;
  }

  dimension: authorization_result {
    sql: ${TABLE}.AuthorizationResult ;;
  }

  dimension: avsresult {
    sql: ${TABLE}.AVSResult ;;
  }

  dimension: billing_address1 {
    sql: ${TABLE}.BillingAddress1 ;;
  }

  dimension: billing_address2 {
    sql: ${TABLE}.BillingAddress2 ;;
  }

  dimension: billing_city {
    sql: ${TABLE}.BillingCity ;;
  }

  dimension: billing_company {
    sql: ${TABLE}.BillingCompany ;;
  }

  dimension: billing_country {
    sql: ${TABLE}.BillingCountry ;;
  }

  dimension: billing_equals_shipping {
    type: yesno
    sql: ${TABLE}.BillingEqualsShipping = 1 ;;
  }

  dimension: billing_first_name {
    sql: ${TABLE}.BillingFirstName ;;
  }

  dimension: billing_last_name {
    sql: ${TABLE}.BillingLastName ;;
  }

  dimension: billing_phone {
    sql: ${TABLE}.BillingPhone ;;
  }

  dimension: billing_state {
    sql: ${TABLE}.BillingState ;;
  }

  dimension: billing_suite {
    sql: ${TABLE}.BillingSuite ;;
  }

  dimension: billing_zip {
    sql: ${TABLE}.BillingZip ;;
  }

  dimension: buy_safe_command {
    sql: ${TABLE}.BuySafeCommand ;;
  }

  dimension: buy_safe_result {
    sql: ${TABLE}.BuySafeResult ;;
  }

  dimension: capture_txcommand {
    sql: ${TABLE}.CaptureTXCommand ;;
  }

  dimension: capture_txresult {
    hidden: yes
    sql: ${TABLE}.CaptureTXResult ;;
  }

  dimension: card_expiration_month {
    sql: ${TABLE}.CardExpirationMonth ;;
  }

  dimension: card_expiration_year {
    sql: ${TABLE}.CardExpirationYear ;;
  }

  dimension: card_issue_number {
    sql: ${TABLE}.CardIssueNumber ;;
  }

  dimension: card_name {
    sql: ${TABLE}.CardName ;;
  }

  dimension: card_number {
    sql: ${TABLE}.CardNumber ;;
  }

  dimension: card_start_date {
    sql: ${TABLE}.CardStartDate ;;
  }

  dimension: card_type {
    sql: ${TABLE}.CardType ;;
  }

  dimension: cardinal_authenticate_result {
    sql: ${TABLE}.CardinalAuthenticateResult ;;
  }

  dimension: cardinal_gateway_parms {
    sql: ${TABLE}.CardinalGatewayParms ;;
  }

  dimension: cardinal_lookup_result {
    sql: ${TABLE}.CardinalLookupResult ;;
  }

  dimension: carrier_reported_rate {
    sql: ${TABLE}.CarrierReportedRate ;;
  }

  dimension: carrier_reported_weight {
    sql: ${TABLE}.CarrierReportedWeight ;;
  }

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

  dimension: coin_base_order_number {
    sql: ${TABLE}.CoinBaseOrderNumber ;;
  }

  dimension: coupon_code {
    sql: ${TABLE}.CouponCode ;;
  }

  dimension: coupon_description {
    sql: ${TABLE}.CouponDescription ;;
  }

  dimension: coupon_discount_amount {
    sql: ${TABLE}.CouponDiscountAmount ;;
  }

  dimension: coupon_discount_percent {
    sql: ${TABLE}.CouponDiscountPercent ;;
  }

  dimension: coupon_includes_free_shipping {
    type: yesno
    sql: ${TABLE}.CouponIncludesFreeShipping = 1 ;;
  }

  dimension: coupon_product_ids {
    sql: ${TABLE}.CouponProductIDs ;;
  }

  dimension: coupon_type {
    type: int
    sql: ${TABLE}.CouponType ;;
  }

  dimension: months_since_customer_creation {
    type: number
    sql: DATEDIFF(month, ${customer.created_time},${created_time}) ;;
  }

  dimension: crypt {
    type: int
    sql: ${TABLE}.Crypt ;;
  }

  dimension: customer_guid {
    sql: ${TABLE}.CustomerGUID ;;
  }

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

  dimension: customer_service_notes {
    sql: ${TABLE}.CustomerServiceNotes ;;
  }

  dimension: deleted {
    type: yesno
    sql: ${TABLE}.Deleted = 1 ;;
  }

  dimension: e_check_bank_abacode {
    sql: ${TABLE}.eCheckBankABACode ;;
  }

  dimension: e_check_bank_account_name {
    sql: ${TABLE}.eCheckBankAccountName ;;
  }

  dimension: e_check_bank_account_number {
    sql: ${TABLE}.eCheckBankAccountNumber ;;
  }

  dimension: e_check_bank_account_type {
    sql: ${TABLE}.eCheckBankAccountType ;;
  }

  dimension: e_check_bank_name {
    sql: ${TABLE}.eCheckBankName ;;
  }

  dimension: email {
    sql: ${TABLE}.Email ;;
  }

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

  dimension: finalization_data {
    sql: ${TABLE}.FinalizationData ;;
  }

  dimension: first_name {
    sql: convert(nvarchar(80), ${TABLE}.FirstName) ;;
  }

  dimension: inventory_was_reduced {
    type: int
    sql: ${TABLE}.InventoryWasReduced ;;
  }

  dimension: is_new {
    type: int
    sql: ${TABLE}.IsNew ;;
  }

  dimension: is_printed {
    type: int
    sql: ${TABLE}.IsPrinted ;;
  }

  dimension: last4 {
    sql: ${TABLE}.Last4 ;;
  }

  dimension: last_ipaddress {
    sql: ${TABLE}.LastIPAddress ;;
  }

  dimension: last_name {
    sql: ${TABLE}.LastName ;;
  }

  dimension: level_allows_coupons {
    type: yesno
    sql: ${TABLE}.LevelAllowsCoupons = 1 ;;
  }

  dimension: level_allows_quantity_discounts {
    type: yesno
    sql: ${TABLE}.LevelAllowsQuantityDiscounts = 1 ;;
  }

  dimension: level_discount_amount {
    sql: ${TABLE}.LevelDiscountAmount ;;
  }

  dimension: level_discount_percent {
    sql: ${TABLE}.LevelDiscountPercent ;;
  }

  dimension: level_discounts_apply_to_extended_prices {
    type: yesno
    sql: ${TABLE}.LevelDiscountsApplyToExtendedPrices = 1 ;;
  }

  dimension: level_has_free_shipping {
    type: yesno
    sql: ${TABLE}.LevelHasFreeShipping = 1 ;;
  }

  dimension: level_has_no_tax {
    type: yesno
    sql: ${TABLE}.LevelHasNoTax = 1 ;;
  }

  dimension: level_id {
    type: int
    sql: ${TABLE}.LevelID ;;
  }

  dimension: level_name {
    sql: ${TABLE}.LevelName ;;
  }

  dimension: locale_setting {
    sql: ${TABLE}.LocaleSetting ;;
  }

  dimension: max_mind_details {
    sql: ${TABLE}.MaxMindDetails ;;
  }

  dimension: max_mind_fraud_score {
    type: number
    sql: ${TABLE}.MaxMindFraudScore ;;
  }

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

  dimension: ok_to_email {
    type: yesno
    sql: ${TABLE}.OkToEmail = 1 ;;
  }

  dimension: order_guid {
    hidden: yes
    sql: ${TABLE}.OrderGUID ;;
  }

  dimension: order_notes {
    sql: ${TABLE}.OrderNotes ;;
  }

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

  dimension: order_options {
    sql: CONVERT(varchar(255), ${TABLE}.OrderOptions) ;;
  }

  dimension: order_shipping_costs {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}.OrderShippingCosts ;;
  }

  dimension: order_subtotal {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}.OrderSubtotal ;;
  }

  dimension: order_tax {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}.OrderTax ;;
  }

  dimension: order_total {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}.OrderTotal ;;
  }

  dimension: order_weight {
    sql: ${TABLE}.OrderWeight ;;
  }

  dimension: parent_order_number {
    type: int
    sql: ${TABLE}.ParentOrderNumber ;;
  }

  dimension: payment_gateway {
    sql: ${TABLE}.PaymentGateway ;;
  }

  dimension: payment_method {
    sql: ${TABLE}.PaymentMethod ;;
  }

  dimension: phone {
    sql: ${TABLE}.Phone ;;
  }

  dimension: ponumber {
    sql: ${TABLE}.PONumber ;;
  }

  dimension: quote_checkout {
    type: yesno
    sql: ${TABLE}.QuoteCheckout = 1 ;;
  }

  dimension: ready_to_ship {
    type: yesno
    sql: ${TABLE}.ReadyToShip = 1 ;;
  }

  dimension: receipt_html {
    sql: ${TABLE}.ReceiptHtml ;;
  }

  dimension: recurring_subscription_command {
    sql: ${TABLE}.RecurringSubscriptionCommand ;;
  }

  dimension: recurring_subscription_id {
    sql: ${TABLE}.RecurringSubscriptionID ;;
  }

  dimension: recurring_subscription_result {
    sql: ${TABLE}.RecurringSubscriptionResult ;;
  }

  dimension: referrer {
    sql: ${TABLE}.Referrer ;;
  }

  dimension: refund_reason {
    sql: CONVERT(varchar(255), ${TABLE}.RefundReason) ;;
  }

  dimension: refund_txcommand {
    sql: CONVERT(varchar(255), ${TABLE}.RefundTXCommand) ;;
  }

  dimension: refund_txresult {
    sql: CONVERT(varchar(255), ${TABLE}.RefundTXResult) ;;
  }

  dimension: related_order_number {
    type: int
    sql: ${TABLE}.RelatedOrderNumber ;;
  }

  dimension: rtship_request {
    sql: ${TABLE}.RTShipRequest ;;
  }

  dimension: rtship_response {
    sql: ${TABLE}.RTShipResponse ;;
  }

  dimension: days_between_order_created_and_shipped {
    type: number
    sql: DATEDIFF(day, ${created_time}, ${shipped_time}) ;;
  }

  dimension: hours_between_order_created_and_shipped {
    type: number
    sql: DATEDIFF(hour, ${created_time}, ${shipped_time}) ;;
  }

  dimension: shipped_via {
    sql: ${TABLE}.ShippedVIA ;;
  }

  dimension: shipping_address1 {
    sql: ${TABLE}.ShippingAddress1 ;;
  }

  dimension: shipping_address2 {
    sql: ${TABLE}.ShippingAddress2 ;;
  }

  dimension: shipping_calculation_id {
    type: int
    sql: ${TABLE}.ShippingCalculationID ;;
  }

  dimension: shipping_city {
    sql: ${TABLE}.ShippingCity ;;
  }

  dimension: shipping_company {
    sql: ${TABLE}.ShippingCompany ;;
  }

  dimension: shipping_country {
    sql: ${TABLE}.ShippingCountry ;;
  }

  dimension: shipping_first_name {
    sql: ${TABLE}.ShippingFirstName ;;
  }

  dimension: shipping_last_name {
    sql: ${TABLE}.ShippingLastName ;;
  }

  dimension: shipping_method {
    sql: CONVERT(varchar(255), ${TABLE}.ShippingMethod) ;;
  }

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

  dimension: shipping_phone {
    sql: ${TABLE}.ShippingPhone ;;
  }

  dimension: shipping_residence_type {
    type: int
    sql: ${TABLE}.ShippingResidenceType ;;
  }

  dimension: shipping_state {
    sql: ${TABLE}.ShippingState ;;
  }

  dimension: shipping_suite {
    sql: ${TABLE}.ShippingSuite ;;
  }

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

  dimension: shipping_zip {
    sql: ${TABLE}.ShippingZip ;;
  }

  dimension: skin_id {
    type: int
    sql: ${TABLE}.SkinID ;;
  }

  dimension: store_id {
    type: int
    sql: ${TABLE}.StoreID ;;
  }

  dimension: store_version {
    sql: ${TABLE}.StoreVersion ;;
  }

  dimension: thub_accounting_ref {
    sql: ${TABLE}.THUB_ACCOUNTING_REF ;;
  }

  dimension: thub_posted_to_accounting {
    sql: ${TABLE}.THUB_POSTED_TO_ACCOUNTING ;;
  }

  dimension: tracking_url {
    sql: ${TABLE}.TrackingURL ;;
  }

  dimension: transaction_command {
    sql: ${TABLE}.TransactionCommand ;;
  }

  dimension: transaction_state {
    sql: ${TABLE}.TransactionState ;;
  }

  dimension: transaction_type {
    type: int
    sql: ${TABLE}.TransactionType ;;
  }

  dimension: vatregistration_id {
    sql: ${TABLE}.VATRegistrationID ;;
  }

  dimension: void_txcommand {
    sql: ${TABLE}.VoidTXCommand ;;
  }

  dimension: void_txresult {
    sql: ${TABLE}.VoidTXResult ;;
  }

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

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

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

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

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

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

  dimension_group: frauded {
    type: time
    timeframes: [time, date, week, month, yesno]
    sql: ${TABLE}.FraudedOn ;;
  }

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

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

  dimension_group: refunded {
    type: time
    timeframes: [time, date, week, month, yesno]
    sql: ${TABLE}.RefundedOn ;;
  }

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

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

  dimension_group: shipped {
    type: time
    timeframes: [time, date, week, month, yesno]
    sql: ${TABLE}.ShippedOn ;;
  }

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

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

  dimension_group: voided {
    type: time
    timeframes: [time, date, week, month, yesno]
    sql: ${TABLE}.VoidedOn ;;
  }

  measure: count {
    type: count
    drill_fields: [affiliate_id, created_date, count]
  }

  measure: order_total_sum_m {
    label: "Order Total (M)"
    type: sum
    value_format: "$0.000,,\" M\""
    drill_fields: [detail*]
    sql: ${order_total} ;;
  }

  measure: order_total_sum {
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${order_total} ;;
  }

  measure: order_subtotal_sum {
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${order_subtotal} ;;
  }

  measure: count_refunded {
    type: count
    filters: [refunded: "Yes"]
    drill_fields: [detail*]
  }

  measure: percent_refunded {
    type: number
    sql: 100.0 * ${count_refunded} / ${count} ;;
  }

  measure: count_voided {
    type: count
    filters: [voided: "Yes"]
    drill_fields: [detail*]
  }

  measure: percent_voided {
    type: number
    sql: 100.0 * ${count_voided} / ${count} ;;
  }

  measure: average_hours_between_order_created_and_shipped {
    type: average
    sql: ${hours_between_order_created_and_shipped} ;;
  }
}
orders.view.lookml · 506 lines
source-preserved 
- view: orders
  sql_table_name: Orders
  fields:
  - dimension: affiliate_commission_recorded
    type: yesno
    sql: ${TABLE}.AffiliateCommissionRecorded = 1
  - dimension: affiliate_id
    type: int
    sql: ${TABLE}.AffiliateID
  - dimension: already_confirmed
    type: yesno
    sql: ${TABLE}.AlreadyConfirmed = 1
  - dimension: authorization_code
    sql: ${TABLE}.AuthorizationCode
  - dimension: authorization_pnref
    hidden: true
    sql: ${TABLE}.AuthorizationPNREF
  - dimension: authorization_result
    sql: ${TABLE}.AuthorizationResult
  - dimension: avsresult
    sql: ${TABLE}.AVSResult
  - dimension: billing_address1
    sql: ${TABLE}.BillingAddress1
  - dimension: billing_address2
    sql: ${TABLE}.BillingAddress2
  - dimension: billing_city
    sql: ${TABLE}.BillingCity
  - dimension: billing_company
    sql: ${TABLE}.BillingCompany
  - dimension: billing_country
    sql: ${TABLE}.BillingCountry
  - dimension: billing_equals_shipping
    type: yesno
    sql: ${TABLE}.BillingEqualsShipping = 1
  - dimension: billing_first_name
    sql: ${TABLE}.BillingFirstName
  - dimension: billing_last_name
    sql: ${TABLE}.BillingLastName
  - dimension: billing_phone
    sql: ${TABLE}.BillingPhone
  - dimension: billing_state
    sql: ${TABLE}.BillingState
  - dimension: billing_suite
    sql: ${TABLE}.BillingSuite
  - dimension: billing_zip
    sql: ${TABLE}.BillingZip
  - dimension: buy_safe_command
    sql: ${TABLE}.BuySafeCommand
  - dimension: buy_safe_result
    sql: ${TABLE}.BuySafeResult
  - dimension: capture_txcommand
    sql: ${TABLE}.CaptureTXCommand
  - dimension: capture_txresult
    hidden: true
    sql: ${TABLE}.CaptureTXResult
  - dimension: card_expiration_month
    sql: ${TABLE}.CardExpirationMonth
  - dimension: card_expiration_year
    sql: ${TABLE}.CardExpirationYear
  - dimension: card_issue_number
    sql: ${TABLE}.CardIssueNumber
  - dimension: card_name
    sql: ${TABLE}.CardName
  - dimension: card_number
    sql: ${TABLE}.CardNumber
  - dimension: card_start_date
    sql: ${TABLE}.CardStartDate
  - dimension: card_type
    sql: ${TABLE}.CardType
  - dimension: cardinal_authenticate_result
    sql: ${TABLE}.CardinalAuthenticateResult
  - dimension: cardinal_gateway_parms
    sql: ${TABLE}.CardinalGatewayParms
  - dimension: cardinal_lookup_result
    sql: ${TABLE}.CardinalLookupResult
  - dimension: carrier_reported_rate
    sql: ${TABLE}.CarrierReportedRate
  - dimension: carrier_reported_weight
    sql: ${TABLE}.CarrierReportedWeight
  - dimension: cart_type
    type: int
    sql: ${TABLE}.CartType
  - dimension: coin_base_order_number
    sql: ${TABLE}.CoinBaseOrderNumber
  - dimension: coupon_code
    sql: ${TABLE}.CouponCode
  - dimension: coupon_description
    sql: ${TABLE}.CouponDescription
  - dimension: coupon_discount_amount
    sql: ${TABLE}.CouponDiscountAmount
  - dimension: coupon_discount_percent
    sql: ${TABLE}.CouponDiscountPercent
  - dimension: coupon_includes_free_shipping
    type: yesno
    sql: ${TABLE}.CouponIncludesFreeShipping = 1
  - dimension: coupon_product_ids
    sql: ${TABLE}.CouponProductIDs
  - dimension: coupon_type
    type: int
    sql: ${TABLE}.CouponType
  - dimension: months_since_customer_creation
    type: number
    sql: DATEDIFF(month, ${customer.created_time},${created_time})
  - dimension: crypt
    type: int
    sql: ${TABLE}.Crypt
  - dimension: customer_guid
    sql: ${TABLE}.CustomerGUID
  - dimension: customer_id
    type: int
    sql: ${TABLE}.CustomerID
  - dimension: customer_service_notes
    sql: ${TABLE}.CustomerServiceNotes
  - dimension: deleted
    type: yesno
    sql: ${TABLE}.Deleted = 1
  - dimension: e_check_bank_abacode
    sql: ${TABLE}.eCheckBankABACode
  - dimension: e_check_bank_account_name
    sql: ${TABLE}.eCheckBankAccountName
  - dimension: e_check_bank_account_number
    sql: ${TABLE}.eCheckBankAccountNumber
  - dimension: e_check_bank_account_type
    sql: ${TABLE}.eCheckBankAccountType
  - dimension: e_check_bank_name
    sql: ${TABLE}.eCheckBankName
  - dimension: email
    sql: ${TABLE}.Email
  - dimension: extension_data
    sql: ${TABLE}.ExtensionData
  - dimension: finalization_data
    sql: ${TABLE}.FinalizationData
  - dimension: first_name
    sql: convert(nvarchar(80), ${TABLE}.FirstName)
  - dimension: inventory_was_reduced
    type: int
    sql: ${TABLE}.InventoryWasReduced
  - dimension: is_new
    type: int
    sql: ${TABLE}.IsNew
  - dimension: is_printed
    type: int
    sql: ${TABLE}.IsPrinted
  - dimension: last4
    sql: ${TABLE}.Last4
  - dimension: last_ipaddress
    sql: ${TABLE}.LastIPAddress
  - dimension: last_name
    sql: ${TABLE}.LastName
  - dimension: level_allows_coupons
    type: yesno
    sql: ${TABLE}.LevelAllowsCoupons = 1
  - dimension: level_allows_quantity_discounts
    type: yesno
    sql: ${TABLE}.LevelAllowsQuantityDiscounts = 1
  - dimension: level_discount_amount
    sql: ${TABLE}.LevelDiscountAmount
  - dimension: level_discount_percent
    sql: ${TABLE}.LevelDiscountPercent
  - dimension: level_discounts_apply_to_extended_prices
    type: yesno
    sql: ${TABLE}.LevelDiscountsApplyToExtendedPrices = 1
  - dimension: level_has_free_shipping
    type: yesno
    sql: ${TABLE}.LevelHasFreeShipping = 1
  - dimension: level_has_no_tax
    type: yesno
    sql: ${TABLE}.LevelHasNoTax = 1
  - dimension: level_id
    type: int
    sql: ${TABLE}.LevelID
  - dimension: level_name
    sql: ${TABLE}.LevelName
  - dimension: locale_setting
    sql: ${TABLE}.LocaleSetting
  - dimension: max_mind_details
    sql: ${TABLE}.MaxMindDetails
  - dimension: max_mind_fraud_score
    type: number
    sql: ${TABLE}.MaxMindFraudScore
  - dimension: notes
    hidden: true
    sql: ${TABLE}.Notes
  - dimension: ok_to_email
    type: yesno
    sql: ${TABLE}.OkToEmail = 1
  - dimension: order_guid
    hidden: true
    sql: ${TABLE}.OrderGUID
  - dimension: order_notes
    sql: ${TABLE}.OrderNotes
  - dimension: order_number
    type: int
    primary_key: true
    sql: ${TABLE}.OrderNumber
  - dimension: order_options
    sql: CONVERT(varchar(255), ${TABLE}.OrderOptions)
  - dimension: order_shipping_costs
    type: number
    value_format: $0.00
    sql: ${TABLE}.OrderShippingCosts
  - dimension: order_subtotal
    type: number
    value_format: $0.00
    sql: ${TABLE}.OrderSubtotal
  - dimension: order_tax
    type: number
    value_format: $0.00
    sql: ${TABLE}.OrderTax
  - dimension: order_total
    type: number
    value_format: $0.00
    sql: ${TABLE}.OrderTotal
  - dimension: order_weight
    sql: ${TABLE}.OrderWeight
  - dimension: parent_order_number
    type: int
    sql: ${TABLE}.ParentOrderNumber
  - dimension: payment_gateway
    sql: ${TABLE}.PaymentGateway
  - dimension: payment_method
    sql: ${TABLE}.PaymentMethod
  - dimension: phone
    sql: ${TABLE}.Phone
  - dimension: ponumber
    sql: ${TABLE}.PONumber
  - dimension: quote_checkout
    type: yesno
    sql: ${TABLE}.QuoteCheckout = 1
  - dimension: ready_to_ship
    type: yesno
    sql: ${TABLE}.ReadyToShip = 1
  - dimension: receipt_html
    sql: ${TABLE}.ReceiptHtml
  - dimension: recurring_subscription_command
    sql: ${TABLE}.RecurringSubscriptionCommand
  - dimension: recurring_subscription_id
    sql: ${TABLE}.RecurringSubscriptionID
  - dimension: recurring_subscription_result
    sql: ${TABLE}.RecurringSubscriptionResult
  - dimension: referrer
    sql: ${TABLE}.Referrer
  - dimension: refund_reason
    sql: CONVERT(varchar(255), ${TABLE}.RefundReason)
  - dimension: refund_txcommand
    sql: CONVERT(varchar(255), ${TABLE}.RefundTXCommand)
  - dimension: refund_txresult
    sql: CONVERT(varchar(255), ${TABLE}.RefundTXResult)
  - dimension: related_order_number
    type: int
    sql: ${TABLE}.RelatedOrderNumber
  - dimension: rtship_request
    sql: ${TABLE}.RTShipRequest
  - dimension: rtship_response
    sql: ${TABLE}.RTShipResponse
  - dimension: days_between_order_created_and_shipped
    type: number
    sql: DATEDIFF(day, ${created_time}, ${shipped_time})
  - dimension: hours_between_order_created_and_shipped
    type: number
    sql: DATEDIFF(hour, ${created_time}, ${shipped_time})
  - dimension: shipped_via
    sql: ${TABLE}.ShippedVIA
  - dimension: shipping_address1
    sql: ${TABLE}.ShippingAddress1
  - dimension: shipping_address2
    sql: ${TABLE}.ShippingAddress2
  - dimension: shipping_calculation_id
    type: int
    sql: ${TABLE}.ShippingCalculationID
  - dimension: shipping_city
    sql: ${TABLE}.ShippingCity
  - dimension: shipping_company
    sql: ${TABLE}.ShippingCompany
  - dimension: shipping_country
    sql: ${TABLE}.ShippingCountry
  - dimension: shipping_first_name
    sql: ${TABLE}.ShippingFirstName
  - dimension: shipping_last_name
    sql: ${TABLE}.ShippingLastName
  - dimension: shipping_method
    sql: CONVERT(varchar(255), ${TABLE}.ShippingMethod)
  - dimension: shipping_method_id
    type: int
    sql: ${TABLE}.ShippingMethodID
  - dimension: shipping_phone
    sql: ${TABLE}.ShippingPhone
  - dimension: shipping_residence_type
    type: int
    sql: ${TABLE}.ShippingResidenceType
  - dimension: shipping_state
    sql: ${TABLE}.ShippingState
  - dimension: shipping_suite
    sql: ${TABLE}.ShippingSuite
  - dimension: shipping_tracking_number
    sql: ${TABLE}.ShippingTrackingNumber
  - dimension: shipping_zip
    sql: ${TABLE}.ShippingZip
  - dimension: skin_id
    type: int
    sql: ${TABLE}.SkinID
  - dimension: store_id
    type: int
    sql: ${TABLE}.StoreID
  - dimension: store_version
    sql: ${TABLE}.StoreVersion
  - dimension: thub_accounting_ref
    sql: ${TABLE}.THUB_ACCOUNTING_REF
  - dimension: thub_posted_to_accounting
    sql: ${TABLE}.THUB_POSTED_TO_ACCOUNTING
  - dimension: tracking_url
    sql: ${TABLE}.TrackingURL
  - dimension: transaction_command
    sql: ${TABLE}.TransactionCommand
  - dimension: transaction_state
    sql: ${TABLE}.TransactionState
  - dimension: transaction_type
    type: int
    sql: ${TABLE}.TransactionType
  - dimension: vatregistration_id
    sql: ${TABLE}.VATRegistrationID
  - dimension: void_txcommand
    sql: ${TABLE}.VoidTXCommand
  - dimension: void_txresult
    sql: ${TABLE}.VoidTXResult
  - dimension_group: authorized
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.AuthorizedOn
  - dimension_group: captured
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.CapturedOn
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour_of_day
    - day_of_week
    - year
    sql: ${TABLE}.CreatedOn
  - dimension_group: distributor_email_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DistributorEmailSentOn
  - dimension_group: download_email_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DownloadEmailSentOn
  - dimension_group: edited
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.EditedOn
  - dimension_group: frauded
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - yesno
    sql: ${TABLE}.FraudedOn
  - dimension_group: order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.OrderDate
  - dimension_group: receipt_email_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.ReceiptEmailSentOn
  - dimension_group: refunded
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - yesno
    sql: ${TABLE}.RefundedOn
  - dimension_group: register
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.RegisterDate
  - dimension_group: shipped_email_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.ShippedEMailSentOn
  - dimension_group: shipped
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - yesno
    sql: ${TABLE}.ShippedOn
  - dimension_group: thub_posted
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.THUB_POSTED_DATE
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.UpdatedOn
  - dimension_group: voided
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - yesno
    sql: ${TABLE}.VoidedOn
  - measure: count
    type: count
    drill_fields:
    - affiliate_id
    - created_date
    - count
  - measure: order_total_sum_m
    type: sum
    label: Order Total (M)
    value_format: '$0.000,," M"'
    drill_fields:
    - detail*
    sql: ${order_total}
  - measure: order_total_sum
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${order_total}
  - measure: order_subtotal_sum
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${order_subtotal}
  - measure: count_refunded
    type: count
    drill_fields:
    - detail*
    filters:
    - refunded: Yes
  - measure: percent_refunded
    type: number
    sql: 100.0 * ${count_refunded} / ${count}
  - measure: count_voided
    type: count
    drill_fields:
    - detail*
    filters:
    - voided: Yes
  - measure: percent_voided
    type: number
    sql: 100.0 * ${count_voided} / ${count}
  - measure: average_hours_between_order_created_and_shipped
    type: average
    sql: ${hours_between_order_created_and_shipped}

Contents

  • orders.view.lkml
  • orders.view.lookml