Item Histories

view: item_histories {
  sql_table_name: item_histories ;;

  dimension: credit_used {
    type: number
    sql: ${TABLE}.credit_used_cents / 100.0 ;;
  }

  dimension: customer_id {
    type: int
    hidden: yes
  }

  dimension: detailed_comments {
  }

  dimension: feedback {
    type: int
  }

  dimension: feedback_name {
  }

  dimension: general_comments {
  }

  dimension: id {
    type: int
    primary_key: yes
  }

  dimension: note {
  }

  dimension: item_descriptor_id {
    type: int
  }

  dimension: item_id {
    type: int
    hidden: yes
  }

  dimension: purchase_note {
  }

  dimension: quantity {
    type: int
  }

  dimension: recommendation_score {
    type: int
  }

  dimension: shipment_id {
    type: int
  }

  dimension: tax_rate {
    type: int
  }

  dimension: feedback_left_since_membership_start_days {
    type: number
    sql: ${updated_date}-${${TABLE}.start_date} ;;
  }

  dimension: feedback_left_since_membership_start_months {
    type: number
    sql: ${feedback_left_since_membership_start_days}/31 ;;
  }

  dimension: was_recommended {
    type: yesno
  }

  dimension: merchandise_revenue {
    type: number
    sql: ${TABLE}.purchase_price_cents / 100.0 ;;
  }

  dimension: average_merchandise_revenue {
    type: avg
    sql: ${merchandise_revenue} ;;
  }

  dimension: revenue_tier {
    type: tier
    sql: ${merchandise_revenue} ;;
  }

  dimension: purchase_sequence_number {
    type: number
    sql: CASE WHEN
  ${TABLE}.date_purchased IS NOT NULL
THEN
  (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.created_at < ${TABLE}.created_at
  AND ph.customer_id=${TABLE}.customer_id
  AND ph.date_purchased IS NOT NULL) + 1
ELSE NULL
END ;;
  }

  dimension: price_order_in_shipment {
    label: "Price Order in Shipment (1 is lowest)"
    type: int
    sql: (SELECT count(*)
FROM item_histories ph
LEFT JOIN item_descriptors pd on pd.id = ph.item_descriptor_id
WHERE ${TABLE}.shipment_id = ph.shipment_id
AND ${item_descriptors.retail_cents} > pd.retail_cents) + 1 ;;
  }

  dimension: product_descriptor_in_survey {
    type: yesno
    sql: (EXISTS(SELECT 1
       FROM style_survey_choices AS sss
       LEFT JOIN style_survey_answers AS ssr
       ON ssr.id = sss.survey_response_id
       WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
       AND ssr.customer_id = item_histories.customer_id)) ;;
  }

  dimension: months_btwn_received_and_purchased {
    type: number
    sql: (DATE_PART('year',${purchased_date}::date) - DATE_PART('year',${${TABLE}.date_received}::date))*12
+
(DATE_PART('month',${purchased_date}::date) - DATE_PART('month',${${TABLE}.date_received}::date)) ;;
  }

  dimension: months_btwn_received_and_damaged {
    type: number
    sql: (DATE_PART('year',${date_damaged_date}::date) - DATE_PART('year',${${TABLE}.date_received}::date))*12
+
(DATE_PART('month',${date_damaged_date}::date) - DATE_PART('month',${${TABLE}.date_received}::date)) ;;
  }

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

  dimension_group: date_damaged {
    type: time
    timeframes: [date, week, month]
  }

  dimension_group: date_lost {
    type: time
    timeframes: [date, week, month]
  }

  dimension_group: purchased {
    type: time
    timeframes: [yesno, hod, dow, date, week, month]
    sql: ${TABLE}.date_purchased ;;
  }

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

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

  measure: count {
    type: count_distinct
    sql: ${id} ;;
  }

  measure: count_positive_feedback {
    type: count
    filters: [feedback: "1 to"]
  }

  measure: percent_positive_feedback {
    type: number
    sql: 100.0 * ${count_positive_feedback} / nullif(${count},0) ;;
  }

  measure: positive_feedback_percent_of_total {
    type: percent_of_total
    sql: ${count_positive_feedback} ;;
  }

  measure: returned_to_inventory_count {
    type: count_distinct
    filters: [returned_to_inventory: "True"]
    sql: ${id} ;;
  }

  measure: percent_returned_to_inventory {
    type: number
    sql: 100.0 * ${returned_to_inventory_count}/NULLIF(${count},0) ;;
  }

  measure: average_feedback {
    type: avg
    sql: ${feedback} ;;
  }

  measure: average_offer_price {
    type: avg
    sql: ${item_descriptors.price} ;;
  }

  measure: total_merchandise_revenue {
    type: sum
    sql: ${merchandise_revenue} ;;
  }

  measure: bought_count {
    type: count_distinct
    filters: [feedback: "3"]
    sql: ${id} ;;
  }

  measure: percent_bought {
    type: number
    sql: 100.0 * ${bought_count}/NULLIF(${count},0) ;;
  }

  measure: no_feedback_count {
    type: count_distinct
    filters: [feedback: "0"]
    sql: ${id} ;;
  }

  measure: percent_no_feedback {
    type: number
    sql: 100.0 * ${no_feedback_count}/NULLIF(${count},0) ;;
  }

  measure: dislike_count {
    type: count_distinct
    filters: [feedback: "-2"]
    sql: ${id} ;;
  }

  measure: percent_dislike {
    type: number
    sql: 100.0 * ${dislike_count}/NULLIF(${count},0) ;;
  }

  measure: liked_count {
    type: count_distinct
    filters: [feedback: "1"]
    sql: ${id} ;;
  }

  measure: percent_liked {
    type: number
    sql: 100.0 * ${liked_count}/NULLIF(${count},0) ;;
  }

  measure: loved_count {
    type: count_distinct
    filters: [feedback: "2"]
    sql: ${id} ;;
  }

  measure: percent_loved {
    type: number
    sql: 100.0 * ${loved_count}/NULLIF(${count},0) ;;
  }

  measure: avg_months_to_purchase {
    type: avg
    sql: ${months_btwn_received_and_purchased} ;;
  }

  measure: avg_months_to_damage {
    type: avg
    sql: ${months_btwn_received_and_damaged} ;;
  }
}

- view: item_histories
  sql_table_name: item_histories
  fields:
  - dimension: credit_used
    type: number
    sql: ${TABLE}.credit_used_cents / 100.0
  - dimension: customer_id
    type: int
    hidden: true
  - dimension: detailed_comments
  - dimension: feedback
    type: int
  - dimension: feedback_name
  - dimension: general_comments
  - dimension: id
    type: int
    primary_key: true
  - dimension: note
  - dimension: item_descriptor_id
    type: int
  - dimension: item_id
    type: int
    hidden: true
  - dimension: purchase_note
  - dimension: quantity
    type: int
  - dimension: recommendation_score
    type: int
  - dimension: shipment_id
    type: int
  - dimension: tax_rate
    type: int
  - dimension: feedback_left_since_membership_start_days
    type: number
    sql: ${updated_date}-${${TABLE}.start_date}
  - dimension: feedback_left_since_membership_start_months
    type: number
    sql: ${feedback_left_since_membership_start_days}/31
  - dimension: was_recommended
    type: yesno
  - dimension: merchandise_revenue
    type: number
    sql: ${TABLE}.purchase_price_cents / 100.0
  - dimension: average_merchandise_revenue
    type: avg
    sql: ${merchandise_revenue}
  - dimension: revenue_tier
    type: tier
    sql: ${merchandise_revenue}
  - dimension: purchase_sequence_number
    type: number
    sql: CASE WHEN
  ${TABLE}.date_purchased IS NOT NULL
THEN
  (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.created_at < ${TABLE}.created_at
  AND ph.customer_id=${TABLE}.customer_id
  AND ph.date_purchased IS NOT NULL) + 1
ELSE NULL
END
  - dimension: price_order_in_shipment
    type: int
    label: Price Order in Shipment (1 is lowest)
    sql: (SELECT count(*)
FROM item_histories ph
LEFT JOIN item_descriptors pd on pd.id = ph.item_descriptor_id
WHERE ${TABLE}.shipment_id = ph.shipment_id
AND ${item_descriptors.retail_cents} > pd.retail_cents) + 1
  - dimension: product_descriptor_in_survey
    type: yesno
    sql: (EXISTS(SELECT 1
       FROM style_survey_choices AS sss
       LEFT JOIN style_survey_answers AS ssr
       ON ssr.id = sss.survey_response_id
       WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
       AND ssr.customer_id = item_histories.customer_id))
  - dimension: months_btwn_received_and_purchased
    type: number
    sql: (DATE_PART('year',${purchased_date}::date) - DATE_PART('year',${${TABLE}.date_received}::date))*12
+
(DATE_PART('month',${purchased_date}::date) - DATE_PART('month',${${TABLE}.date_received}::date))
  - dimension: months_btwn_received_and_damaged
    type: number
    sql: (DATE_PART('year',${date_damaged_date}::date) - DATE_PART('year',${${TABLE}.date_received}::date))*12
+
(DATE_PART('month',${date_damaged_date}::date) - DATE_PART('month',${${TABLE}.date_received}::date))
  - dimension_group: created
    type: time
    timeframes:
    - time
    - hod
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: date_damaged
    type: time
    timeframes:
    - date
    - week
    - month
  - dimension_group: date_lost
    type: time
    timeframes:
    - date
    - week
    - month
  - dimension_group: purchased
    type: time
    timeframes:
    - yesno
    - hod
    - dow
    - date
    - week
    - month
    sql: ${TABLE}.date_purchased
  - dimension_group: returned_to_inventory
    type: time
    timeframes:
    - yesno
    - date
    - week
    - month
    sql: ${TABLE}.date_returned
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - measure: count
    type: count_distinct
    sql: ${id}
  - measure: count_positive_feedback
    type: count
    filters:
    - feedback: 1 to
  - measure: percent_positive_feedback
    type: number
    sql: 100.0 * ${count_positive_feedback} / nullif(${count},0)
  - measure: positive_feedback_percent_of_total
    type: percent_of_total
    sql: ${count_positive_feedback}
  - measure: returned_to_inventory_count
    type: count_distinct
    filters:
    - returned_to_inventory: True
    sql: ${id}
  - measure: percent_returned_to_inventory
    type: number
    sql: 100.0 * ${returned_to_inventory_count}/NULLIF(${count},0)
  - measure: average_feedback
    type: avg
    sql: ${feedback}
  - measure: average_offer_price
    type: avg
    sql: ${item_descriptors.price}
  - measure: total_merchandise_revenue
    type: sum
    sql: ${merchandise_revenue}
  - measure: bought_count
    type: count_distinct
    filters:
    - feedback: 3
    sql: ${id}
  - measure: percent_bought
    type: number
    sql: 100.0 * ${bought_count}/NULLIF(${count},0)
  - measure: no_feedback_count
    type: count_distinct
    filters:
    - feedback: 0
    sql: ${id}
  - measure: percent_no_feedback
    type: number
    sql: 100.0 * ${no_feedback_count}/NULLIF(${count},0)
  - measure: dislike_count
    type: count_distinct
    filters:
    - feedback: -2
    sql: ${id}
  - measure: percent_dislike
    type: number
    sql: 100.0 * ${dislike_count}/NULLIF(${count},0)
  - measure: liked_count
    type: count_distinct
    filters:
    - feedback: 1
    sql: ${id}
  - measure: percent_liked
    type: number
    sql: 100.0 * ${liked_count}/NULLIF(${count},0)
  - measure: loved_count
    type: count_distinct
    filters:
    - feedback: 2
    sql: ${id}
  - measure: percent_loved
    type: number
    sql: 100.0 * ${loved_count}/NULLIF(${count},0)
  - measure: avg_months_to_purchase
    type: avg
    sql: ${months_btwn_received_and_purchased}
  - measure: avg_months_to_damage
    type: avg
    sql: ${months_btwn_received_and_damaged}