Customer Order Facts

-- models/customer_order_facts.sql
{{ config(materialized='view') }}

SELECT
  o.user_id
  , u.email
  , u.birth_date
  , o.created_at as created_date
  , AVG(o.order_total) as average_order_total
  , so.promo_code_id
  , IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount
  , MIN(so.delivery_time) as first_delivery_date
  , {% parameter state_2 %}UNT(*) as lifetime_orders
  , SUM(o.order_total) as lifetime_spend
  , {% parameter state_1 %}X(o.time) as most_recent_purchase_date
  , MIN(o.time) as first_purchase_date
  , DATEDIFF({% parameter state_1 %}X(NULLIF(o.time,0)),MIN(NULLIF(o.time,0))) AS days_as_customer
  , DATEDIFF(CURDATE(),{% parameter state_1 %}X(NULLIF(o.time,0))) AS days_since_purchase
  , {% parameter state_2 %}UNT(DISTINCT {% parameter state_8 %}NTH(NULLIF(o.time,0))) AS number_of_distinct_months_with_orders
  , u.market_id AS market_id
  , so.store_id AS store_id
FROM orders o
INNER JOIN orders AS so ON so.order_id = o.id
LEFT JOIN stores AS s ON so.store_id = s.id
LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
LEFT JOIN users AS u ON o.user_id = u.id
WHERE EXISTS (SELECT 1 FROM orders so WHERE so.order_id = o.id and so.status = 6)
GROUP BY user_id

# models/schema.yml
version: 2

models:
  - name: customer_order_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: user_email
      - name: store_id
      - name: age
      - name: age_tier
      - name: average_order_value
      - name: lifetime_orders
      - name: number_of_purchases_tier
      - name: promo_code_discount
      - name: buyer
      - name: lifetime_spend
      - name: first_delivery_date
      - name: first_purchase_delivery_time
      - name: under_40_mins
      - name: between_60_and_90_mins
      - name: greater_than_90_mins
      - name: days_as_customer
      - name: days_as_customer_tiered
      - name: days_since_purchase
      - name: number_of_distinct_months_with_orders
      - name: number_of_distinct_months_with_orders_tier
      - name: is_repeat_customer
      - name: is_first_time_customer
      - name: avg_order_value_is_between_0_and_40
      - name: avg_order_value_is_between_40_and_80
      - name: avg_order_value_is_between_80_and_120
      - name: avg_order_value_is_between_120_and_160
      - name: avg_order_value_is_between_160_and_200
      - name: avg_order_value_is_greater_than_200
      - name: avg_order_value_is_between_0_and_30
      - name: avg_order_value_is_between_30_and_40
      - name: avg_order_value_is_between_40_and_50
      - name: avg_order_value_is_between_50_and_60
      - name: avg_order_value_is_between_60_and_70
      - name: avg_order_value_is_between_70_and_80
      - name: avg_order_value_is_between_80_and_90
      - name: avg_order_value_is_between_90_and_100
      - name: avg_order_value_is_between_100_and_120

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  o.user_id
  , u.email
  , u.birth_date
  , o.created_at as created_date
  , AVG(o.order_total) as average_order_total
  , so.promo_code_id
  , IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount
  , MIN(so.delivery_time) as first_delivery_date
  , {% parameter state_2 %}UNT(*) as lifetime_orders
  , SUM(o.order_total) as lifetime_spend
  , {% parameter state_1 %}X(o.time) as most_recent_purchase_date
  , MIN(o.time) as first_purchase_date
  , DATEDIFF({% parameter state_1 %}X(NULLIF(o.time,0)),MIN(NULLIF(o.time,0))) AS days_as_customer
  , DATEDIFF(CURDATE(),{% parameter state_1 %}X(NULLIF(o.time,0))) AS days_since_purchase
  , {% parameter state_2 %}UNT(DISTINCT {% parameter state_8 %}NTH(NULLIF(o.time,0))) AS number_of_distinct_months_with_orders
  , u.market_id AS market_id
  , so.store_id AS store_id
FROM orders o
INNER JOIN orders AS so ON so.order_id = o.id
LEFT JOIN stores AS s ON so.store_id = s.id
LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
LEFT JOIN users AS u ON o.user_id = u.id
WHERE EXISTS (SELECT 1 FROM orders so WHERE so.order_id = o.id and so.status = 6)
GROUP BY user_id

view: customer_order_facts {
  derived_table: {
    sql: SELECT
  o.user_id
  , u.email
  , u.birth_date
  , o.created_at as created_date
  , AVG(o.order_total) as average_order_total
  , so.promo_code_id
  , IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount
  , MIN(so.delivery_time) as first_delivery_date
  , {% parameter state_2 %}UNT(*) as lifetime_orders
  , SUM(o.order_total) as lifetime_spend
  , {% parameter state_1 %}X(o.time) as most_recent_purchase_date
  , MIN(o.time) as first_purchase_date
  , DATEDIFF({% parameter state_1 %}X(NULLIF(o.time,0)),MIN(NULLIF(o.time,0))) AS days_as_customer
  , DATEDIFF(CURDATE(),{% parameter state_1 %}X(NULLIF(o.time,0))) AS days_since_purchase
  , {% parameter state_2 %}UNT(DISTINCT {% parameter state_8 %}NTH(NULLIF(o.time,0))) AS number_of_distinct_months_with_orders
  , u.market_id AS market_id
  , so.store_id AS store_id
FROM orders o
INNER JOIN orders AS so ON so.order_id = o.id
LEFT JOIN stores AS s ON so.store_id = s.id
LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
LEFT JOIN users AS u ON o.user_id = u.id
WHERE EXISTS (SELECT 1 FROM orders so WHERE so.order_id = o.id and so.status = 6)
GROUP BY user_id ;;
  }

  dimension: user_id {
    type: int
    primary_key: yes
  }

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

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

  dimension: age {
    type: number
    sql: FLOOR(DATEDIFF(CURDATE(),${birth_date})/365) ;;
  }

  dimension: age_tier {
    type: tier
    sql: ${age} ;;
  }

  dimension: average_order_value {
    type: number
    sql: ${TABLE}.average_order_total ;;
  }

  dimension: lifetime_orders {
    label: "USERS Lifetime Orders"
    type: number
  }

  dimension: number_of_purchases_tier {
  }

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

  dimension: buyer {
    type: yesno
    sql: ${lifetime_orders} > 0 ;;
  }

  dimension: lifetime_spend {
    type: number
  }

  dimension: first_delivery_date {
    type: time
    sql: ${TABLE}.first_delivery_date ;;
  }

  dimension: first_purchase_delivery_time {
    type: number
    sql: TIME_TO_SEC(TIMEDIFF(${TABLE}.first_delivery_date, ${TABLE}.first_purchase_date))/60 ;;
  }

  dimension: under_40_mins {
    type: yesno
    sql: ${first_purchase_delivery_time} < 40 ;;
  }

  dimension: between_60_and_90_mins {
    type: yesno
    sql: ${first_purchase_delivery_time} >= 60 AND ${first_purchase_delivery_time} < 90 ;;
  }

  dimension: greater_than_90_mins {
    type: yesno
    sql: ${first_purchase_delivery_time} >= 90 AND ${first_purchase_delivery_time} < 180 ;;
  }

  dimension: days_as_customer {
    type: int
  }

  dimension: days_as_customer_tiered {
    type: tier
    sql: ${days_as_customer} ;;
  }

  dimension: days_since_purchase {
    type: int
  }

  dimension: number_of_distinct_months_with_orders {
    type: int
  }

  dimension: number_of_distinct_months_with_orders_tier {
    type: tier
    sql: ${number_of_distinct_months_with_orders} ;;
  }

  dimension: is_repeat_customer {
    type: yesno
    sql: ${lifetime_orders} > 1 ;;
  }

  dimension: is_first_time_customer {
    type: yesno
    sql: ${lifetime_orders} = 1 ;;
  }

  dimension: avg_order_value_is_between_0_and_40 {
    type: yesno
    sql: ${average_order_value} > 0 AND ${average_order_value} <= 40 ;;
  }

  dimension: avg_order_value_is_between_40_and_80 {
    type: yesno
    sql: ${average_order_value} > 40 AND ${average_order_value} <= 80 ;;
  }

  dimension: avg_order_value_is_between_80_and_120 {
    type: yesno
    sql: ${average_order_value} > 80 AND ${average_order_value} <= 120 ;;
  }

  dimension: avg_order_value_is_between_120_and_160 {
    type: yesno
    sql: ${average_order_value} > 120 AND ${average_order_value} <= 160 ;;
  }

  dimension: avg_order_value_is_between_160_and_200 {
    type: yesno
    sql: ${average_order_value} > 160 AND ${average_order_value} <= 200 ;;
  }

  dimension: avg_order_value_is_greater_than_200 {
    type: yesno
    sql: ${average_order_value} > 200 ;;
  }

  dimension: avg_order_value_is_between_0_and_30 {
    type: yesno
    sql: ${average_order_value} > 0 AND ${average_order_value} <= 30 ;;
  }

  dimension: avg_order_value_is_between_30_and_40 {
    type: yesno
    sql: ${average_order_value} > 30 AND ${average_order_value} <= 40 ;;
  }

  dimension: avg_order_value_is_between_40_and_50 {
    type: yesno
    sql: ${average_order_value} > 40 AND ${average_order_value} <= 50 ;;
  }

  dimension: avg_order_value_is_between_50_and_60 {
    type: yesno
    sql: ${average_order_value} > 50 AND ${average_order_value} <= 60 ;;
  }

  dimension: avg_order_value_is_between_60_and_70 {
    type: yesno
    sql: ${average_order_value} > 60 AND ${average_order_value} <= 70 ;;
  }

  dimension: avg_order_value_is_between_70_and_80 {
    type: yesno
    sql: ${average_order_value} > 70 AND ${average_order_value} <= 80 ;;
  }

  dimension: avg_order_value_is_between_80_and_90 {
    type: yesno
    sql: ${average_order_value} > 80 AND ${average_order_value} <= 90 ;;
  }

  dimension: avg_order_value_is_between_90_and_100 {
    type: yesno
    sql: ${average_order_value} > 90 AND ${average_order_value} <=100 ;;
  }

  dimension: avg_order_value_is_between_100_and_120 {
    type: yesno
    sql: ${average_order_value} > 100 AND ${average_order_value} <=120 ;;
  }

  dimension_group: birth {
    type: time
    timeframes: [date, week, month, year]
    sql: STR_TO_DATE(${TABLE}.birth_date,'%m/%d/%Y') ;;
  }

  dimension_group: most_recent_order {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.most_recent_purchase_date ;;
  }

  dimension_group: order_created_date {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.created_date ;;
  }

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

  measure: avg_days_as_customer {
    type: average
    sql: ${days_as_customer} ;;
  }

  measure: count {
    label: "Lifetime Orders Count"
    type: sum
    sql: ${lifetime_orders} ;;
  }

  measure: order_count {
    type: number
    sql: (
  SELECT {% parameter state_2 %}UNT(o.id)
  FROM orders AS o
  LEFT JOIN orders AS so ON o.id = so.order_id
  WHERE so.status = 6
) ;;
  }

  measure: buyer_count {
    type: count
    filters: [buyer: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count {
    type: count
    filters: [is_repeat_customer: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate {
    type: number
    sql: ${user_purchases.repeat_buyer_count}/ ${user_purchases.buyer_count} * 100 ;;
  }

  measure: buyer_perc_change {
    type: percent_of_previous
    sql: ${buyer_count} ;;
  }

  measure: cumulative_buyer_count {
    type: running_total
    sql: ${buyer_count} ;;
  }

  measure: cumulative_order_count {
    type: running_total
    sql: ${lifetime_orders} ;;
  }

  measure: buyers_over_users {
    type: number
    sql: (${buyer_count}/({% parameter state_2 %}UNT(users.id)))* 100 ;;
  }

  measure: orders_to_buyer {
    type: number
    sql: ((${order_count})/(${buyer_count})) ;;
  }

  measure: total_lifetime_spend {
    type: sum
    sql: ${lifetime_spend} ;;
  }

  measure: perc_change_lifetime_spend {
    type: percent_of_previous
    sql: ${total_lifetime_spend} ;;
  }

  measure: avg_lifetime_spend {
    type: average
    sql: ${lifetime_spend} ;;
  }

  measure: avg_order_value_by_user {
    type: average
    sql: ${average_order_value} ;;
  }

  measure: promo_spend {
    type: sum
    sql: ${promo_code_discount} ;;
  }

  measure: promo_cost_per_buyer {
    type: number
    sql: (${promo_spend}/${buyer_count}) ;;
  }

  measure: buyer_count_0_and_40_avg_orders {
    label: "Buyer Count (AOV, $0-40)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_0_and_40: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_0_and_40_avg_orders {
    label: "Repeat Buyer Count (AOV, $0-40)"
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_between_0_and_40: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_0_and_40_avg_orders {
    label: "Reorder Rate (AOV, $0-40)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_0_and_40_avg_orders}/ ${user_purchases.buyer_count_0_and_40_avg_orders} * 100 ;;
  }

  measure: buyer_count_40_and_80_avg_orders {
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_40_and_80: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_40_and_80_avg_orders {
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_between_40_and_80: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_40_and_80_avg_orders {
    label: "Reorder Rate (AOV, $40-80)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_40_and_80_avg_orders}/ ${user_purchases.buyer_count_40_and_80_avg_orders} * 100 ;;
  }

  measure: buyer_count_80_and_120_avg_orders {
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_80_and_120: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_80_and_120_avg_orders {
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_between_80_and_120: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_80_and_120_avg_orders {
    label: "Reorder Rate (AOV, $80-120)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_80_and_120_avg_orders}/ ${user_purchases.buyer_count_80_and_120_avg_orders} * 100 ;;
  }

  measure: buyer_count_120_and_160_avg_orders {
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_120_and_160: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_120_and_160_avg_orders {
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_between_120_and_160: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_120_and_160_avg_orders {
    label: "Reorder Rate (AOV, $120-160)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_120_and_160_avg_orders}/ ${user_purchases.buyer_count_120_and_160_avg_orders} * 100 ;;
  }

  measure: buyer_count_160_and_200_avg_orders {
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_160_and_200: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_160_and_200_avg_orders {
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_between_160_and_200: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_160_and_200_avg_orders {
    label: "Reorder Rate (AOV, $160-200)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_160_and_200_avg_orders}/ ${user_purchases.buyer_count_160_and_200_avg_orders} * 100 ;;
  }

  measure: buyer_count_200_plus_avg_orders {
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_greater_than_200: "True"]
    sql: ${user_id} ;;
  }

  measure: repeat_buyer_count_200_plus_avg_orders {
    type: count
    filters: [is_repeat_customer: "True"]
    filters: [avg_order_value_is_greater_than_200: "True"]
    sql: ${user_id} ;;
  }

  measure: reorder_rate_greater_than_200_avg_orders {
    label: "Reorder Rate (AOV, $200+)"
    type: number
    sql: ${user_purchases.repeat_buyer_count_200_plus_avg_orders}/ ${user_purchases.buyer_count_200_plus_avg_orders} * 100 ;;
  }

  measure: buyer_count_0_and_30_avg_orders {
    label: "Buyer Count (AOV, $0-30)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_0_and_30: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_30_and_40_avg_orders {
    label: "Buyer Count (AOV, $30-40)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_30_and_40: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_40_and_50_avg_orders {
    label: "Buyer Count (AOV, $40-50)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_40_and_50: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_50_and_60_avg_orders {
    label: "Buyer Count (AOV, $50-60)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_50_and_60: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_60_and_70_avg_orders {
    label: "Buyer Count (AOV, $60-70)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_60_and_70: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_70_and_80_avg_orders {
    label: "Buyer Count (AOV, $70-80)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_70_and_80: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_80_and_90_avg_orders {
    label: "Buyer Count (AOV, $80-90)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_80_and_90: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_90_and_100_avg_orders {
    label: "Buyer Count (AOV, $90-100)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_90_and_100: "True"]
    sql: ${user_id} ;;
  }

  measure: buyer_count_100_and_120_avg_orders {
    label: "Buyer Count (AOV, $100-120)"
    type: count
    filters: [buyer: "True"]
    filters: [avg_order_value_is_between_100_and_120: "True"]
    sql: ${user_id} ;;
  }
}

- view: customer_order_facts
  derived_table:
    sql: SELECT
  o.user_id
  , u.email
  , u.birth_date
  , o.created_at as created_date
  , AVG(o.order_total) as average_order_total
  , so.promo_code_id
  , IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount
  , MIN(so.delivery_time) as first_delivery_date
  , {% parameter state_2 %}UNT(*) as lifetime_orders
  , SUM(o.order_total) as lifetime_spend
  , {% parameter state_1 %}X(o.time) as most_recent_purchase_date
  , MIN(o.time) as first_purchase_date
  , DATEDIFF({% parameter state_1 %}X(NULLIF(o.time,0)),MIN(NULLIF(o.time,0))) AS days_as_customer
  , DATEDIFF(CURDATE(),{% parameter state_1 %}X(NULLIF(o.time,0))) AS days_since_purchase
  , {% parameter state_2 %}UNT(DISTINCT {% parameter state_8 %}NTH(NULLIF(o.time,0))) AS number_of_distinct_months_with_orders
  , u.market_id AS market_id
  , so.store_id AS store_id
FROM orders o
INNER JOIN orders AS so ON so.order_id = o.id
LEFT JOIN stores AS s ON so.store_id = s.id
LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
LEFT JOIN users AS u ON o.user_id = u.id
WHERE EXISTS (SELECT 1 FROM orders so WHERE so.order_id = o.id and so.status = 6)
GROUP BY user_id
  fields:
  - dimension: user_id
    type: int
    primary_key: true
  - dimension: user_email
    sql: ${TABLE}.email
  - dimension: store_id
    type: int
    sql: ${TABLE}.store_id
  - dimension: age
    type: number
    sql: FLOOR(DATEDIFF(CURDATE(),${birth_date})/365)
  - dimension: age_tier
    type: tier
    sql: ${age}
  - dimension: average_order_value
    type: number
    sql: ${TABLE}.average_order_total
  - dimension: lifetime_orders
    type: number
    label: USERS Lifetime Orders
  - dimension: number_of_purchases_tier
  - dimension: promo_code_discount
    type: number
    sql: ${TABLE}.promo_code_discount
  - dimension: buyer
    type: yesno
    sql: ${lifetime_orders} > 0
  - dimension: lifetime_spend
    type: number
  - dimension: first_delivery_date
    type: time
    sql: ${TABLE}.first_delivery_date
  - dimension: first_purchase_delivery_time
    type: number
    sql: TIME_TO_SEC(TIMEDIFF(${TABLE}.first_delivery_date, ${TABLE}.first_purchase_date))/60
  - dimension: under_40_mins
    type: yesno
    sql: ${first_purchase_delivery_time} < 40
  - dimension: between_60_and_90_mins
    type: yesno
    sql: ${first_purchase_delivery_time} >= 60 AND ${first_purchase_delivery_time} < 90
  - dimension: greater_than_90_mins
    type: yesno
    sql: ${first_purchase_delivery_time} >= 90 AND ${first_purchase_delivery_time} < 180
  - dimension: days_as_customer
    type: int
  - dimension: days_as_customer_tiered
    type: tier
    sql: ${days_as_customer}
  - dimension: days_since_purchase
    type: int
  - dimension: number_of_distinct_months_with_orders
    type: int
  - dimension: number_of_distinct_months_with_orders_tier
    type: tier
    sql: ${number_of_distinct_months_with_orders}
  - dimension: is_repeat_customer
    type: yesno
    sql: ${lifetime_orders} > 1
  - dimension: is_first_time_customer
    type: yesno
    sql: ${lifetime_orders} = 1
  - dimension: avg_order_value_is_between_0_and_40
    type: yesno
    sql: ${average_order_value} > 0 AND ${average_order_value} <= 40
  - dimension: avg_order_value_is_between_40_and_80
    type: yesno
    sql: ${average_order_value} > 40 AND ${average_order_value} <= 80
  - dimension: avg_order_value_is_between_80_and_120
    type: yesno
    sql: ${average_order_value} > 80 AND ${average_order_value} <= 120
  - dimension: avg_order_value_is_between_120_and_160
    type: yesno
    sql: ${average_order_value} > 120 AND ${average_order_value} <= 160
  - dimension: avg_order_value_is_between_160_and_200
    type: yesno
    sql: ${average_order_value} > 160 AND ${average_order_value} <= 200
  - dimension: avg_order_value_is_greater_than_200
    type: yesno
    sql: ${average_order_value} > 200
  - dimension: avg_order_value_is_between_0_and_30
    type: yesno
    sql: ${average_order_value} > 0 AND ${average_order_value} <= 30
  - dimension: avg_order_value_is_between_30_and_40
    type: yesno
    sql: ${average_order_value} > 30 AND ${average_order_value} <= 40
  - dimension: avg_order_value_is_between_40_and_50
    type: yesno
    sql: ${average_order_value} > 40 AND ${average_order_value} <= 50
  - dimension: avg_order_value_is_between_50_and_60
    type: yesno
    sql: ${average_order_value} > 50 AND ${average_order_value} <= 60
  - dimension: avg_order_value_is_between_60_and_70
    type: yesno
    sql: ${average_order_value} > 60 AND ${average_order_value} <= 70
  - dimension: avg_order_value_is_between_70_and_80
    type: yesno
    sql: ${average_order_value} > 70 AND ${average_order_value} <= 80
  - dimension: avg_order_value_is_between_80_and_90
    type: yesno
    sql: ${average_order_value} > 80 AND ${average_order_value} <= 90
  - dimension: avg_order_value_is_between_90_and_100
    type: yesno
    sql: ${average_order_value} > 90 AND ${average_order_value} <=100
  - dimension: avg_order_value_is_between_100_and_120
    type: yesno
    sql: ${average_order_value} > 100 AND ${average_order_value} <=120
  - dimension_group: birth
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: STR_TO_DATE(${TABLE}.birth_date,'%m/%d/%Y')
  - dimension_group: most_recent_order
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.most_recent_purchase_date
  - dimension_group: order_created_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.created_date
  - dimension_group: first_purchase_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.first_purchase_date
  - measure: avg_days_as_customer
    type: average
    sql: ${days_as_customer}
  - measure: count
    type: sum
    label: Lifetime Orders Count
    sql: ${lifetime_orders}
  - measure: order_count
    type: number
    sql: (
  SELECT {% parameter state_2 %}UNT(o.id)
  FROM orders AS o
  LEFT JOIN orders AS so ON o.id = so.order_id
  WHERE so.status = 6
)
  - measure: buyer_count
    type: count
    filters:
    - buyer: True
    sql: ${user_id}
  - measure: repeat_buyer_count
    type: count
    filters:
    - is_repeat_customer: True
    sql: ${user_id}
  - measure: reorder_rate
    type: number
    sql: ${user_purchases.repeat_buyer_count}/ ${user_purchases.buyer_count} * 100
  - measure: buyer_perc_change
    type: percent_of_previous
    sql: ${buyer_count}
  - measure: cumulative_buyer_count
    type: running_total
    sql: ${buyer_count}
  - measure: cumulative_order_count
    type: running_total
    sql: ${lifetime_orders}
  - measure: buyers_over_users
    type: number
    sql: (${buyer_count}/({% parameter state_2 %}UNT(users.id)))* 100
  - measure: orders_to_buyer
    type: number
    sql: ((${order_count})/(${buyer_count}))
  - measure: total_lifetime_spend
    type: sum
    sql: ${lifetime_spend}
  - measure: perc_change_lifetime_spend
    type: percent_of_previous
    sql: ${total_lifetime_spend}
  - measure: avg_lifetime_spend
    type: average
    sql: ${lifetime_spend}
  - measure: avg_order_value_by_user
    type: average
    sql: ${average_order_value}
  - measure: promo_spend
    type: sum
    sql: ${promo_code_discount}
  - measure: promo_cost_per_buyer
    type: number
    sql: (${promo_spend}/${buyer_count})
  - measure: buyer_count_0_and_40_avg_orders
    type: count
    label: Buyer Count (AOV, $0-40)
    filters:
    - buyer: True
    - avg_order_value_is_between_0_and_40: True
    sql: ${user_id}
  - measure: repeat_buyer_count_0_and_40_avg_orders
    type: count
    label: Repeat Buyer Count (AOV, $0-40)
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_between_0_and_40: True
    sql: ${user_id}
  - measure: reorder_rate_0_and_40_avg_orders
    type: number
    label: Reorder Rate (AOV, $0-40)
    sql: ${user_purchases.repeat_buyer_count_0_and_40_avg_orders}/ ${user_purchases.buyer_count_0_and_40_avg_orders} * 100
  - measure: buyer_count_40_and_80_avg_orders
    type: count
    filters:
    - buyer: True
    - avg_order_value_is_between_40_and_80: True
    sql: ${user_id}
  - measure: repeat_buyer_count_40_and_80_avg_orders
    type: count
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_between_40_and_80: True
    sql: ${user_id}
  - measure: reorder_rate_40_and_80_avg_orders
    type: number
    label: Reorder Rate (AOV, $40-80)
    sql: ${user_purchases.repeat_buyer_count_40_and_80_avg_orders}/ ${user_purchases.buyer_count_40_and_80_avg_orders} * 100
  - measure: buyer_count_80_and_120_avg_orders
    type: count
    filters:
    - buyer: True
    - avg_order_value_is_between_80_and_120: True
    sql: ${user_id}
  - measure: repeat_buyer_count_80_and_120_avg_orders
    type: count
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_between_80_and_120: True
    sql: ${user_id}
  - measure: reorder_rate_80_and_120_avg_orders
    type: number
    label: Reorder Rate (AOV, $80-120)
    sql: ${user_purchases.repeat_buyer_count_80_and_120_avg_orders}/ ${user_purchases.buyer_count_80_and_120_avg_orders} * 100
  - measure: buyer_count_120_and_160_avg_orders
    type: count
    filters:
    - buyer: True
    - avg_order_value_is_between_120_and_160: True
    sql: ${user_id}
  - measure: repeat_buyer_count_120_and_160_avg_orders
    type: count
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_between_120_and_160: True
    sql: ${user_id}
  - measure: reorder_rate_120_and_160_avg_orders
    type: number
    label: Reorder Rate (AOV, $120-160)
    sql: ${user_purchases.repeat_buyer_count_120_and_160_avg_orders}/ ${user_purchases.buyer_count_120_and_160_avg_orders} * 100
  - measure: buyer_count_160_and_200_avg_orders
    type: count
    filters:
    - buyer: True
    - avg_order_value_is_between_160_and_200: True
    sql: ${user_id}
  - measure: repeat_buyer_count_160_and_200_avg_orders
    type: count
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_between_160_and_200: True
    sql: ${user_id}
  - measure: reorder_rate_160_and_200_avg_orders
    type: number
    label: Reorder Rate (AOV, $160-200)
    sql: ${user_purchases.repeat_buyer_count_160_and_200_avg_orders}/ ${user_purchases.buyer_count_160_and_200_avg_orders} * 100
  - measure: buyer_count_200_plus_avg_orders
    type: count
    filters:
    - buyer: True
    - avg_order_value_is_greater_than_200: True
    sql: ${user_id}
  - measure: repeat_buyer_count_200_plus_avg_orders
    type: count
    filters:
    - is_repeat_customer: True
    - avg_order_value_is_greater_than_200: True
    sql: ${user_id}
  - measure: reorder_rate_greater_than_200_avg_orders
    type: number
    label: Reorder Rate (AOV, $200+)
    sql: ${user_purchases.repeat_buyer_count_200_plus_avg_orders}/ ${user_purchases.buyer_count_200_plus_avg_orders} * 100
  - measure: buyer_count_0_and_30_avg_orders
    type: count
    label: Buyer Count (AOV, $0-30)
    filters:
    - buyer: True
    - avg_order_value_is_between_0_and_30: True
    sql: ${user_id}
  - measure: buyer_count_30_and_40_avg_orders
    type: count
    label: Buyer Count (AOV, $30-40)
    filters:
    - buyer: True
    - avg_order_value_is_between_30_and_40: True
    sql: ${user_id}
  - measure: buyer_count_40_and_50_avg_orders
    type: count
    label: Buyer Count (AOV, $40-50)
    filters:
    - buyer: True
    - avg_order_value_is_between_40_and_50: True
    sql: ${user_id}
  - measure: buyer_count_50_and_60_avg_orders
    type: count
    label: Buyer Count (AOV, $50-60)
    filters:
    - buyer: True
    - avg_order_value_is_between_50_and_60: True
    sql: ${user_id}
  - measure: buyer_count_60_and_70_avg_orders
    type: count
    label: Buyer Count (AOV, $60-70)
    filters:
    - buyer: True
    - avg_order_value_is_between_60_and_70: True
    sql: ${user_id}
  - measure: buyer_count_70_and_80_avg_orders
    type: count
    label: Buyer Count (AOV, $70-80)
    filters:
    - buyer: True
    - avg_order_value_is_between_70_and_80: True
    sql: ${user_id}
  - measure: buyer_count_80_and_90_avg_orders
    type: count
    label: Buyer Count (AOV, $80-90)
    filters:
    - buyer: True
    - avg_order_value_is_between_80_and_90: True
    sql: ${user_id}
  - measure: buyer_count_90_and_100_avg_orders
    type: count
    label: Buyer Count (AOV, $90-100)
    filters:
    - buyer: True
    - avg_order_value_is_between_90_and_100: True
    sql: ${user_id}
  - measure: buyer_count_100_and_120_avg_orders
    type: count
    label: Buyer Count (AOV, $100-120)
    filters:
    - buyer: True
    - avg_order_value_is_between_100_and_120: True
    sql: ${user_id}