Spree Orders Monthly Cohort

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

SELECT
  DATE_FORMAT(CONVERT_TZ(u.first_order,'UTC','America/Los_Angeles'),'%Y-%m') AS created_month
  , COUNT(*) as cohort_size

FROM
-- {{ ref('user_order_facts') }} u
  (select
    user_id
  , min(completed_at) as first_order
  FROM spree_orders
  WHERE locked_at > 0
    AND user_id > 0
  GROUP BY user_id) as u
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: spree_orders_monthly_cohort
    columns:
      - name: created_month
        tests:
          - unique
          - not_null
      - name: cohort_size
      - name: total_revenue_over_total_cohort_size

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• 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
  DATE_FORMAT(CONVERT_TZ(u.first_order,'UTC','America/Los_Angeles'),'%Y-%m') AS created_month
  , COUNT(*) as cohort_size

FROM
-- ${user_order_facts.SQL_TABLE_NAME} u
  (select
    user_id
  , min(completed_at) as first_order
  FROM spree_orders
  WHERE locked_at > 0
    AND user_id > 0
  GROUP BY user_id) as u
GROUP BY 1

view: spree_orders_monthly_cohort {
  derived_table: {
    sql: SELECT
  DATE_FORMAT(CONVERT_TZ(u.first_order,'UTC','America/Los_Angeles'),'%Y-%m') AS created_month
  , COUNT(*) as cohort_size

FROM
-- ${user_order_facts.SQL_TABLE_NAME} u
  (select
    user_id
  , min(completed_at) as first_order
  FROM spree_orders
  WHERE locked_at > 0
    AND user_id > 0
  GROUP BY user_id) as u
GROUP BY 1 ;;
  }

  dimension: created_month {
    primary_key: yes
  }

  dimension: cohort_size {
    type: int
    hidden: yes
  }

  dimension: total_revenue_over_total_cohort_size {
    type: number
    sql: ${spree_orders.total_revenue} / ${total_cohort_size} ;;
  }

  measure: total_cohort_size {
    type: sum
    sql: ${cohort_size} ;;
  }
}

- view: spree_orders_monthly_cohort
  derived_table:
    sql: SELECT
  DATE_FORMAT(CONVERT_TZ(u.first_order,'UTC','America/Los_Angeles'),'%Y-%m') AS created_month
  , COUNT(*) as cohort_size

FROM
-- ${user_order_facts.SQL_TABLE_NAME} u
  (select
    user_id
  , min(completed_at) as first_order
  FROM spree_orders
  WHERE locked_at > 0
    AND user_id > 0
  GROUP BY user_id) as u
GROUP BY 1
  fields:
  - dimension: created_month
    primary_key: true
  - dimension: cohort_size
    type: int
    hidden: true
  - dimension: total_revenue_over_total_cohort_size
    type: number
    sql: ${spree_orders.total_revenue} / ${total_cohort_size}
  - measure: total_cohort_size
    type: sum
    sql: ${cohort_size}