Members

view: members {
  sql_table_name: members ;;

  dimension: membership_length_in_days {
    type: number
    sql: CASE
WHEN ${canceled_date} IS NULL
THEN current_date - ${membership_start_date}
ELSE canceled_date - ${membership_start_date}
END ;;
  }

  dimension: membership_length_in_month {
    type: number
    sql: ${membership_length_in_days}/30 ;;
  }

  dimension: membership_length_in_days_tier {
    type: tier
    sql: ${membership_length_in_days} ;;
  }

  dimension: cancellation_reason {
    type: int
  }

  dimension: cancellation_reason_name {
  }

  dimension: contact_id {
    type: int
    hidden: yes
  }

  dimension: customer_notes {
  }

  dimension: email {
  }

  dimension: first_name {
  }

  dimension: from_social_media {
  }

  dimension: from_social_media_binary {
    type: yesno
    sql: ${from_social_media} is not null ;;
  }

  dimension: avatar_file_name {
  }

  dimension: avatar {
    type: yesno
    sql: ${avatar_file_name} is not null ;;
  }

  dimension: id {
    type: int
    primary_key: yes
  }

  dimension: last_name {
  }

  dimension: last_shipment_id {
    type: int
  }

  dimension: name {
    sql: ${TABLE}.first_name || ' ' || ${TABLE}.last_name ;;
  }

  dimension: history {
    sql: ${TABLE}.id ;;
  }

  dimension: official_source {
    sql: lower(${TABLE}.official_source) ;;
  }

  dimension: partner_id {
    type: int
  }

  dimension: priority {
    type: int
  }

  dimension: priority_name {
  }

  dimension: purchased_product {
    type: yesno
  }

  dimension: recurly_code {
  }

  dimension: ring_size {
    type: number
  }

  dimension: shipping_status {
    type: int
  }

  dimension: shipping_status_name {
  }

  dimension: sign_in_count {
    type: int
  }

  dimension: sign_in_count_tier {
    type: tier
    sql: ${sign_in_count} ;;
  }

  dimension: signup_coupon_id {
    type: int
  }

  dimension: signup_coupon_name {
  }

  dimension: source {
  }

  dimension: status {
    type: int
  }

  dimension: status_name {
  }

  dimension: survey_id {
    type: int
  }

  dimension: lifetime_purchases {
    type: number
    sql: (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id
AND ph.date_purchased IS NOT NULL) ;;
  }

  dimension: first_purchase {
    type: time
    sql: (SELECT ph.date_purchased
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id
AND ph.date_purchased IS NOT NULL
ORDER BY ph.date_purchased ASC
LIMIT 1) ;;
  }

  dimension: days_til_first_purchase {
    type: int
    sql: ${first_purchase_date} - ${created_date} ;;
  }

  dimension: months_til_first_purchase {
    type: int
    sql: ${days_til_first_purchase}/31 ;;
  }

  dimension: days_til_first_purchase_tier {
    type: tier
    sql: ${days_til_first_purchase} ;;
  }

  dimension: lifetime_item_histories {
    type: number
    sql: (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id) ;;
  }

  dimension: lifetime_purchases_tier {
    type: tier
    sql: ${lifetime_purchases} ;;
  }

  dimension: lifetime_revenue {
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL) ;;
  }

  dimension: revenue_first_60_days {
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL
AND ph.date_purchased < ${TABLE}.created_at + interval '60 days') ;;
  }

  dimension: average_revenue_first_60_days {
    type: average
    sql: ${revenue_first_60_days} ;;
  }

  dimension: revenue_first_90_days {
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL
AND ph.date_purchased < ${TABLE}.created_at + interval '90 days') ;;
  }

  dimension: average_revenue_first_90_days {
    type: average
    sql: ${revenue_first_90_days} ;;
  }

  dimension: days_since_first_box_shipped {
    type: number
    sql: CURRENT_DATE - DATE(${TABLE}.membership_start_time) ;;
  }

  dimension: count_of_deliveries {
    type: number
    sql: (select count(*) from deliveries where deliveries.customer_id = ${TABLE}.id) ;;
  }

  dimension: had_a_shipment {
    type: yesno
    sql: ${count_of_deliveries} > 0 ;;
  }

  dimension: days_since_first_box_shipped_tier {
    type: tier
    sql: ${days_since_first_box_shipped} ;;
  }

  dimension: lifer {
    type: yesno
    sql: ${days_since_first_box_shipped} > 90 ;;
  }

  dimension: member_segment {
  }

  dimension: referred_friends {
    type: number
    sql: (SELECT count(*)
FROM transactions c
WHERE c.customer_id = ${TABLE}.id
  AND c.reason = 0
  AND c.status = 1) ;;
  }

  dimension: referred_friend_bool {
    type: yesno
    sql: ${referred_friends} > 0 ;;
  }

  dimension: invites_tier {
    type: tier
    sql: ${referred_friends} ;;
  }

  dimension: days_to_cancel {
    type: number
    sql: ${TABLE}.canceled_date - DATE(${TABLE}.membership_start_time) ;;
  }

  dimension: months_to_cancel {
    type: number
    sql: (${TABLE}.canceled_date - DATE(${TABLE}.membership_start_time))/31 ;;
  }

  dimension: credit_score {
    type: number
  }

  dimension: credit_score_tier {
    type: tier
    sql: ${credit_score} ;;
  }

  dimension: credit_score_present {
    type: yesno
    sql: ${credit_score} is not null ;;
  }

  dimension: utm_source {
  }

  dimension: referring_domain {
  }

  dimension: time_to_cancel_tier {
    type: tier
    sql: ${days_to_cancel} ;;
  }

  dimension: lifetime_revenue_tier {
    type: tier
    sql: ${lifetime_revenue} ;;
  }

  dimension_group: birthday {
    type: time
    timeframes: [time, date]
  }

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

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

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

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

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

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

  measure: share_of_members {
    type: percent_of_total
    sql: ${count} ;;
  }

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

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

  measure: purchaser_count {
    type: count_distinct
    filters: [lifetime_purchases: "1 to"]
    sql: ${id} ;;
  }

  measure: lifer_count {
    type: count_distinct
    filters: [lifer: "True"]
    sql: ${id} ;;
  }

  measure: needs_canceling_count {
    type: count
    filters: [shipping_status_name: "Needs Canceling"]
  }

  measure: average_lifetime_purchases {
    type: avg
    sql: ${lifetime_purchases} ;;
  }

  measure: total_lifetime_purchases {
    type: sum
    sql: ${lifetime_purchases} ;;
  }

  measure: average_item_histories {
    type: avg
    sql: ${lifetime_item_histories} ;;
  }

  measure: total_item_histories {
    type: sum
    sql: ${lifetime_item_histories} ;;
  }

  measure: total_lifetime_revenue {
    type: sum
    sql: ${lifetime_revenue} ;;
  }

  measure: total_lifetime_revenue_per_customer {
    type: number
    sql: ${total_lifetime_revenue} / NULLIF(${count},0) ;;
  }

  measure: average_lifetime_revenue {
    type: avg
    sql: ${lifetime_revenue} ;;
  }

  measure: average_referred_friends {
    type: avg
    sql: ${referred_friends} ;;
  }

  measure: total_referred_friends {
    type: sum
    sql: ${referred_friends} ;;
  }

  measure: count_members_who_referred_friends {
    type: count
    filters: [referred_friend_bool: "True"]
    sql: ${id} ;;
  }

  measure: avg_credit_score {
    type: avg
    sql: ${credit_score} ;;
  }

  measure: average_time_to_cancellation {
    type: avg
    sql: ${days_to_cancel} ;;
  }

  measure: canceled_count {
    type: count_distinct
    filters: [canceled: "True"]
    sql: ${TABLE}.id ;;
  }

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

- view: members
  sql_table_name: members
  fields:
  - dimension: membership_length_in_days
    type: number
    sql: CASE
WHEN ${canceled_date} IS NULL
THEN current_date - ${membership_start_date}
ELSE canceled_date - ${membership_start_date}
END
  - dimension: membership_length_in_month
    type: number
    sql: ${membership_length_in_days}/30
  - dimension: membership_length_in_days_tier
    type: tier
    sql: ${membership_length_in_days}
  - dimension: cancellation_reason
    type: int
  - dimension: cancellation_reason_name
  - dimension: contact_id
    type: int
    hidden: true
  - dimension: customer_notes
  - dimension: email
  - dimension: first_name
  - dimension: from_social_media
  - dimension: from_social_media_binary
    type: yesno
    sql: ${from_social_media} is not null
  - dimension: avatar_file_name
  - dimension: avatar
    type: yesno
    sql: ${avatar_file_name} is not null
  - dimension: id
    type: int
    primary_key: true
  - dimension: last_name
  - dimension: last_shipment_id
    type: int
  - dimension: name
    sql: ${TABLE}.first_name || ' ' || ${TABLE}.last_name
  - dimension: history
    sql: ${TABLE}.id
  - dimension: official_source
    sql: lower(${TABLE}.official_source)
  - dimension: partner_id
    type: int
  - dimension: priority
    type: int
  - dimension: priority_name
  - dimension: purchased_product
    type: yesno
  - dimension: recurly_code
  - dimension: ring_size
    type: number
  - dimension: shipping_status
    type: int
  - dimension: shipping_status_name
  - dimension: sign_in_count
    type: int
  - dimension: sign_in_count_tier
    type: tier
    sql: ${sign_in_count}
  - dimension: signup_coupon_id
    type: int
  - dimension: signup_coupon_name
  - dimension: source
  - dimension: status
    type: int
  - dimension: status_name
  - dimension: survey_id
    type: int
  - dimension: lifetime_purchases
    type: number
    sql: (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id
AND ph.date_purchased IS NOT NULL)
  - dimension: first_purchase
    type: time
    sql: (SELECT ph.date_purchased
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id
AND ph.date_purchased IS NOT NULL
ORDER BY ph.date_purchased ASC
LIMIT 1)
  - dimension: days_til_first_purchase
    type: int
    sql: ${first_purchase_date} - ${created_date}
  - dimension: months_til_first_purchase
    type: int
    sql: ${days_til_first_purchase}/31
  - dimension: days_til_first_purchase_tier
    type: tier
    sql: ${days_til_first_purchase}
  - dimension: lifetime_item_histories
    type: number
    sql: (SELECT COUNT(*)
FROM item_histories ph
WHERE ph.customer_id = ${TABLE}.id)
  - dimension: lifetime_purchases_tier
    type: tier
    sql: ${lifetime_purchases}
  - dimension: lifetime_revenue
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL)
  - dimension: revenue_first_60_days
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL
AND ph.date_purchased < ${TABLE}.created_at + interval '60 days')
  - dimension: average_revenue_first_60_days
    type: average
    sql: ${revenue_first_60_days}
  - dimension: revenue_first_90_days
    type: number
    sql: (SELECT SUM(pd.retail_cents) / 100 *.8
FROM item_histories ph
LEFT JOIN item_descriptors pd
ON ph.item_descriptor_id=pd.id
WHERE ph.customer_id=${TABLE}.id AND ph.date_purchased IS NOT NULL
AND ph.date_purchased < ${TABLE}.created_at + interval '90 days')
  - dimension: average_revenue_first_90_days
    type: average
    sql: ${revenue_first_90_days}
  - dimension: days_since_first_box_shipped
    type: number
    sql: CURRENT_DATE - DATE(${TABLE}.membership_start_time)
  - dimension: count_of_deliveries
    type: number
    sql: (select count(*) from deliveries where deliveries.customer_id = ${TABLE}.id)
  - dimension: had_a_shipment
    type: yesno
    sql: ${count_of_deliveries} > 0
  - dimension: days_since_first_box_shipped_tier
    type: tier
    sql: ${days_since_first_box_shipped}
  - dimension: lifer
    type: yesno
    sql: ${days_since_first_box_shipped} > 90
  - dimension: member_segment
  - dimension: referred_friends
    type: number
    sql: (SELECT count(*)
FROM transactions c
WHERE c.customer_id = ${TABLE}.id
  AND c.reason = 0
  AND c.status = 1)
  - dimension: referred_friend_bool
    type: yesno
    sql: ${referred_friends} > 0
  - dimension: invites_tier
    type: tier
    sql: ${referred_friends}
  - dimension: days_to_cancel
    type: number
    sql: ${TABLE}.canceled_date - DATE(${TABLE}.membership_start_time)
  - dimension: months_to_cancel
    type: number
    sql: (${TABLE}.canceled_date - DATE(${TABLE}.membership_start_time))/31
  - dimension: credit_score
    type: number
  - dimension: credit_score_tier
    type: tier
    sql: ${credit_score}
  - dimension: credit_score_present
    type: yesno
    sql: ${credit_score} is not null
  - dimension: utm_source
  - dimension: referring_domain
  - dimension: time_to_cancel_tier
    type: tier
    sql: ${days_to_cancel}
  - dimension: lifetime_revenue_tier
    type: tier
    sql: ${lifetime_revenue}
  - dimension_group: birthday
    type: time
    timeframes:
    - time
    - date
  - dimension_group: canceled
    type: time
    timeframes:
    - yesno
    - date
    - week
    - month
    sql: ${TABLE}.canceled_date
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: entry
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.entry_time
  - dimension_group: last_sign_in
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.last_sign_in_at
  - dimension_group: membership_start
    type: time
    timeframes:
    - yesno
    - date
    - week
    - month
    sql: ${TABLE}.membership_start_time
  - measure: count
    type: count_distinct
    sql: ${id}
  - measure: share_of_members
    type: percent_of_total
    sql: ${count}
  - measure: lifer_percent
    type: number
    sql: 100.0 * ${lifer_count}/NULLIF(${count},0)
  - measure: purchaser_percent
    type: number
    sql: 100.0 * ${purchaser_count}/NULLIF(${count},0)
  - measure: purchaser_count
    type: count_distinct
    filters:
    - lifetime_purchases: 1 to
    sql: ${id}
  - measure: lifer_count
    type: count_distinct
    filters:
    - lifer: True
    sql: ${id}
  - measure: needs_canceling_count
    type: count
    filters:
    - shipping_status_name: Needs Canceling
  - measure: average_lifetime_purchases
    type: avg
    sql: ${lifetime_purchases}
  - measure: total_lifetime_purchases
    type: sum
    sql: ${lifetime_purchases}
  - measure: average_item_histories
    type: avg
    sql: ${lifetime_item_histories}
  - measure: total_item_histories
    type: sum
    sql: ${lifetime_item_histories}
  - measure: total_lifetime_revenue
    type: sum
    sql: ${lifetime_revenue}
  - measure: total_lifetime_revenue_per_customer
    type: number
    sql: ${total_lifetime_revenue} / NULLIF(${count},0)
  - measure: average_lifetime_revenue
    type: avg
    sql: ${lifetime_revenue}
  - measure: average_referred_friends
    type: avg
    sql: ${referred_friends}
  - measure: total_referred_friends
    type: sum
    sql: ${referred_friends}
  - measure: count_members_who_referred_friends
    type: count
    filters:
    - referred_friend_bool: True
    sql: ${id}
  - measure: avg_credit_score
    type: avg
    sql: ${credit_score}
  - measure: average_time_to_cancellation
    type: avg
    sql: ${days_to_cancel}
  - measure: canceled_count
    type: count_distinct
    filters:
    - canceled: True
    sql: ${TABLE}.id
  - measure: percent_canceled
    type: number
    sql: 100.0*${canceled_count}/NULLIF(${count},0)