Sales Flat Order

view: sales_flat_order {
  sql_table_name: sales_flat_order ;;

  dimension: order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.entity_id ;;
  }

  dimension: adjustment_negative {
    type: number
    hidden: yes
    sql: ${TABLE}.adjustment_negative ;;
  }

  dimension: adjustment_positive {
    type: number
    hidden: yes
    sql: ${TABLE}.adjustment_positive ;;
  }

  dimension: billing_address_id {
    type: number
    sql: ${TABLE}.billing_address_id ;;
  }

  dimension: can_ship_partially {
    type: number
    sql: ${TABLE}.can_ship_partially ;;
  }

  dimension: can_ship_partially_item {
    type: number
    sql: ${TABLE}.can_ship_partially_item ;;
  }

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

  dimension: coupon_rule_name {
    sql: ${TABLE}.coupon_rule_name ;;
  }

  dimension: customer_balance_amount {
    type: number
    sql: ${TABLE}.customer_balance_amount ;;
  }

  dimension: customer_id {
    type: number
    hidden: yes
    sql: ${TABLE}.customer_id ;;
  }

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

  dimension: discount_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.discount_amount ;;
  }

  dimension: has_discount {
    type: yesno
    sql: ${discount_amount} > 0 ;;
  }

  dimension: discount_description {
    sql: ${TABLE}.discount_description ;;
  }

  dimension: is_email_sent {
    type: yesno
    sql: ${TABLE}.email_sent = 1 ;;
  }

  dimension: gift_cards_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.gift_cards_amount ;;
  }

  dimension: has_gift_card {
    type: yesno
    sql: ${gift_cards_amount} > 0 ;;
  }

  dimension: gift_message_id {
    type: number
    hidden: yes
    sql: ${TABLE}.gift_message_id ;;
  }

  dimension: gift_certificate_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.giftcert_amount ;;
  }

  dimension: has_gift_certificate {
    type: yesno
    sql: ${gift_certificate_amount} > 0 ;;
  }

  dimension: gift_certificate_code {
    sql: ${TABLE}.giftcert_code ;;
  }

  dimension: order_amount {
    type: number
    sql: ${TABLE}.grand_total ;;
  }

  dimension: increment_id {
    hidden: yes
    sql: ${TABLE}.increment_id ;;
  }

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

  dimension: original_increment_id {
    hidden: yes
    sql: ${TABLE}.original_increment_id ;;
  }

  dimension: protect_code {
    sql: ${TABLE}.protect_code ;;
  }

  dimension: quote_address_id {
    type: number
    hidden: yes
    sql: ${TABLE}.quote_address_id ;;
  }

  dimension: quote_id {
    type: number
    hidden: yes
    sql: ${TABLE}.quote_id ;;
  }

  dimension: campaign {
    sql: ${TABLE}.rjm_utm_campaign ;;
  }

  dimension: content {
    sql: ${TABLE}.rjm_utm_content ;;
  }

  dimension: medium {
    sql: ${TABLE}.rjm_utm_medium ;;
  }

  dimension: source {
    sql: ${TABLE}.rjm_utm_source ;;
  }

  dimension: term {
    sql: ${TABLE}.rjm_utm_term ;;
  }

  dimension: shipping_address_id {
    type: number
    hidden: yes
    sql: ${TABLE}.shipping_address_id ;;
  }

  dimension: shipping_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.shipping_amount ;;
  }

  dimension: is_shipping_canceled {
    type: yesno
    sql: ${TABLE}.shipping_canceled = 1 ;;
  }

  dimension: shipping_incl_tax {
    type: number
    hidden: yes
    sql: ${TABLE}.shipping_incl_tax ;;
  }

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

  dimension: state {
    sql: ${TABLE}.state ;;
  }

  dimension: status {
    sql: ${TABLE}.status ;;
  }

  dimension: store_id {
    type: number
    hidden: yes
    sql: ${TABLE}.store_id ;;
  }

  dimension: store_name {
    sql: ${TABLE}.store_name ;;
  }

  dimension: subtotal_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.subtotal ;;
  }

  dimension: subtotal_incl_tax {
    type: number
    hidden: yes
    sql: ${TABLE}.subtotal_incl_tax ;;
  }

  dimension: swat_note {
    hidden: yes
    sql: ${TABLE}.swat_note ;;
  }

  dimension: tax_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.tax_amount ;;
  }

  dimension: canceled_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.total_canceled ;;
  }

  dimension: due_amount {
    type: number
    sql: ${TABLE}.total_due ;;
  }

  dimension: invoiced_amount {
    type: number
    sql: ${TABLE}.total_invoiced ;;
  }

  dimension: item_count {
    type: number
    sql: ${TABLE}.total_item_count ;;
  }

  dimension: paid_amount {
    type: number
    sql: ${TABLE}.total_paid ;;
  }

  dimension: quantity {
    type: number
    sql: ${TABLE}.total_qty_ordered ;;
  }

  dimension: refunded_amount {
    type: number
    sql: ${TABLE}.total_refunded ;;
  }

  dimension: weight {
    type: number
    sql: ${TABLE}.weight ;;
  }

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

  measure: count {
    type: count
  }

  measure: total_due_amount {
    type: sum
    sql: ${due_amount} ;;
  }

  measure: total_invoiced_amount {
    type: sum
    sql: ${invoiced_amount} ;;
  }

  measure: total_item_count {
    type: sum
    sql: ${item_count} ;;
  }

  measure: total_paid_amount {
    type: sum
    sql: ${paid_amount} ;;
  }

  measure: average_paid_amount {
    type: average
    sql: ${paid_amount} ;;
  }

  measure: total_quantity {
    type: sum
    sql: ${quantity} ;;
  }

  measure: total_refunded_amount {
    type: sum
    sql: ${refunded_amount} ;;
  }

  measure: total_discount_amount {
    type: sum
    sql: ${discount_amount} ;;
  }

  measure: total_gift_cards_amount {
    type: sum
    sql: ${gift_cards_amount} ;;
  }

  measure: total_order_amount {
    type: sum
    sql: ${order_amount} ;;
  }

  measure: total_shipping_amount {
    type: sum
    sql: ${shipping_amount} ;;
  }

  measure: total_subtotal_amount {
    type: sum
    sql: ${subtotal_amount} ;;
  }
  set: detail {
    fields: [customer_firstname, customer_lastname, customer_middlename, store_name, coupon_rule_name, customer_group.customer_group_id, gift_message.gift_message_id]
  }
}

- view: sales_flat_order
  sql_table_name: sales_flat_order
  fields:
  - dimension: order_id
    type: number
    primary_key: true
    sql: ${TABLE}.entity_id
  - dimension: adjustment_negative
    type: number
    hidden: true
    sql: ${TABLE}.adjustment_negative
  - dimension: adjustment_positive
    type: number
    hidden: true
    sql: ${TABLE}.adjustment_positive
  - dimension: billing_address_id
    type: number
    sql: ${TABLE}.billing_address_id
  - dimension: can_ship_partially
    type: number
    sql: ${TABLE}.can_ship_partially
  - dimension: can_ship_partially_item
    type: number
    sql: ${TABLE}.can_ship_partially_item
  - dimension: coupon_code
    sql: ${TABLE}.coupon_code
  - dimension: coupon_rule_name
    sql: ${TABLE}.coupon_rule_name
  - dimension: customer_balance_amount
    type: number
    sql: ${TABLE}.customer_balance_amount
  - dimension: customer_id
    type: number
    hidden: true
    sql: ${TABLE}.customer_id
  - dimension: customer_is_guest
    type: yesno
    sql: ${TABLE}.customer_is_guest = 1
  - dimension: discount_amount
    type: number
    hidden: true
    sql: ${TABLE}.discount_amount
  - dimension: has_discount
    type: yesno
    sql: ${discount_amount} > 0
  - dimension: discount_description
    sql: ${TABLE}.discount_description
  - dimension: is_email_sent
    type: yesno
    sql: ${TABLE}.email_sent = 1
  - dimension: gift_cards_amount
    type: number
    hidden: true
    sql: ${TABLE}.gift_cards_amount
  - dimension: has_gift_card
    type: yesno
    sql: ${gift_cards_amount} > 0
  - dimension: gift_message_id
    type: number
    hidden: true
    sql: ${TABLE}.gift_message_id
  - dimension: gift_certificate_amount
    type: number
    hidden: true
    sql: ${TABLE}.giftcert_amount
  - dimension: has_gift_certificate
    type: yesno
    sql: ${gift_certificate_amount} > 0
  - dimension: gift_certificate_code
    sql: ${TABLE}.giftcert_code
  - dimension: order_amount
    type: number
    sql: ${TABLE}.grand_total
  - dimension: increment_id
    hidden: true
    sql: ${TABLE}.increment_id
  - dimension: is_virtual
    type: yesno
    sql: ${TABLE}.is_virtual = 1
  - dimension: original_increment_id
    hidden: true
    sql: ${TABLE}.original_increment_id
  - dimension: protect_code
    sql: ${TABLE}.protect_code
  - dimension: quote_address_id
    type: number
    hidden: true
    sql: ${TABLE}.quote_address_id
  - dimension: quote_id
    type: number
    hidden: true
    sql: ${TABLE}.quote_id
  - dimension: campaign
    sql: ${TABLE}.rjm_utm_campaign
  - dimension: content
    sql: ${TABLE}.rjm_utm_content
  - dimension: medium
    sql: ${TABLE}.rjm_utm_medium
  - dimension: source
    sql: ${TABLE}.rjm_utm_source
  - dimension: term
    sql: ${TABLE}.rjm_utm_term
  - dimension: shipping_address_id
    type: number
    hidden: true
    sql: ${TABLE}.shipping_address_id
  - dimension: shipping_amount
    type: number
    hidden: true
    sql: ${TABLE}.shipping_amount
  - dimension: is_shipping_canceled
    type: yesno
    sql: ${TABLE}.shipping_canceled = 1
  - dimension: shipping_incl_tax
    type: number
    hidden: true
    sql: ${TABLE}.shipping_incl_tax
  - dimension: shipping_method
    sql: ${TABLE}.shipping_method
  - dimension: state
    sql: ${TABLE}.state
  - dimension: status
    sql: ${TABLE}.status
  - dimension: store_id
    type: number
    hidden: true
    sql: ${TABLE}.store_id
  - dimension: store_name
    sql: ${TABLE}.store_name
  - dimension: subtotal_amount
    type: number
    hidden: true
    sql: ${TABLE}.subtotal
  - dimension: subtotal_incl_tax
    type: number
    hidden: true
    sql: ${TABLE}.subtotal_incl_tax
  - dimension: swat_note
    hidden: true
    sql: ${TABLE}.swat_note
  - dimension: tax_amount
    type: number
    hidden: true
    sql: ${TABLE}.tax_amount
  - dimension: canceled_amount
    type: number
    hidden: true
    sql: ${TABLE}.total_canceled
  - dimension: due_amount
    type: number
    sql: ${TABLE}.total_due
  - dimension: invoiced_amount
    type: number
    sql: ${TABLE}.total_invoiced
  - dimension: item_count
    type: number
    sql: ${TABLE}.total_item_count
  - dimension: paid_amount
    type: number
    sql: ${TABLE}.total_paid
  - dimension: quantity
    type: number
    sql: ${TABLE}.total_qty_ordered
  - dimension: refunded_amount
    type: number
    sql: ${TABLE}.total_refunded
  - dimension: weight
    type: number
    sql: ${TABLE}.weight
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: count
    type: count
  - measure: total_due_amount
    type: sum
    sql: ${due_amount}
  - measure: total_invoiced_amount
    type: sum
    sql: ${invoiced_amount}
  - measure: total_item_count
    type: sum
    sql: ${item_count}
  - measure: total_paid_amount
    type: sum
    sql: ${paid_amount}
  - measure: average_paid_amount
    type: average
    sql: ${paid_amount}
  - measure: total_quantity
    type: sum
    sql: ${quantity}
  - measure: total_refunded_amount
    type: sum
    sql: ${refunded_amount}
  - measure: total_discount_amount
    type: sum
    sql: ${discount_amount}
  - measure: total_gift_cards_amount
    type: sum
    sql: ${gift_cards_amount}
  - measure: total_order_amount
    type: sum
    sql: ${order_amount}
  - measure: total_shipping_amount
    type: sum
    sql: ${shipping_amount}
  - measure: total_subtotal_amount
    type: sum
    sql: ${subtotal_amount}