Leads

view: leads {
  sql_table_name: flexdb.leads ;;

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

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

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

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

  dimension: iteration_id {
    label: "revision_id"
    type: number
    sql: ${TABLE}.iteration_id ;;
  }

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

  dimension: ip_address {
    label: "IP Address"
    type: string
    sql: INET_NTOA(${TABLE}.ip_address) ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: cycle {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.extended_type = 'Repeater') ;;
  }

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

  dimension: bonesupport_upsell_count {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND o.extended_type='Upsell'
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=4 OR od.sku = 'DD-OST90~1' ) ) ;;
  }

  dimension: bonesupport_count {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=4 OR od.sku = 'DD-OST90~1' ) ) ;;
  }

  dimension: prostatesupport_count {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=12 OR od.sku = 'DD-PRST60~1' ) ) ;;
  }

  dimension: has_recurring_upsell {
    type: yesno
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.extended_type = 'Recurring Upsell' ) ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month, hour]
    datatype: epoch
    sql: ${TABLE}.time_stamp ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

- view: leads
  sql_table_name: flexdb.leads
  fields:
  - dimension: lead_id
    type: int
    primary_key: true
    sql: ${TABLE}.lead_id
  - dimension: visit_id
    sql: ${TABLE}.visit_id
  - dimension: visitor_id
    sql: ${TABLE}.visitor_id
  - dimension: product_id
    type: number
    sql: ${TABLE}.product_id
  - dimension: iteration_id
    type: number
    label: revision_id
    sql: ${TABLE}.iteration_id
  - dimension: sub_id
    sql: ${TABLE}.sub_id
  - dimension: ip_address
    type: string
    label: IP Address
    sql: INET_NTOA(${TABLE}.ip_address)
  - dimension: first_name
    sql: ${TABLE}.first_name
  - dimension: last_name
    sql: ${TABLE}.last_name
  - dimension: address
    sql: ${TABLE}.address
  - dimension: address_2
    sql: ${TABLE}.address_2
  - dimension: city
    sql: ${TABLE}.city
  - dimension: state
    sql: ${TABLE}.state
  - dimension: zip_code
    sql: ${TABLE}.zip_code
  - dimension: country
    sql: ${TABLE}.country
  - dimension: email_address
    sql: ${TABLE}.email_address
  - dimension: phone_number
    sql: ${TABLE}.phone_number
  - dimension: age
    type: number
    sql: ${TABLE}.age
  - dimension: cc_name
    sql: ${TABLE}.cc_name
  - dimension: cc_type
    sql: ${TABLE}.cc_type
  - dimension: cc_month
    sql: ${TABLE}.cc_month
  - dimension: cc_year
    sql: ${TABLE}.cc_year
  - dimension: tracking202subid
    type: number
    sql: ${TABLE}.tracking202subid
  - dimension: cycle
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.extended_type = 'Repeater')
  - dimension: posted_back
    type: number
    sql: ${TABLE}.posted_back
  - dimension: bonesupport_upsell_count
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND o.extended_type='Upsell'
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=4 OR od.sku = 'DD-OST90~1' ) )
  - dimension: bonesupport_count
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=4 OR od.sku = 'DD-OST90~1' ) )
  - dimension: prostatesupport_count
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
LEFT JOIN order_details od
USING(order_id)
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND (o.product_id=12 OR od.sku = 'DD-PRST60~1' ) )
  - dimension: has_recurring_upsell
    type: yesno
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.extended_type = 'Recurring Upsell' )
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour
    datatype: epoch
    sql: ${TABLE}.time_stamp
  - measure: count
    type: count
    drill_fields:
    - detail*