Trial Customer Facts

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

SELECT user.id AS user_id,
  min(DATE(user_history_facts.first_order_date)) AS first_order_date,
  min(datediff(curdate(), user_history_facts.first_order_date)) as days_since_purchase
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN order_consolidated AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN {{ ref('user_history_facts') }} AS user_history_facts ON `order`.user_id = user_history_facts.user_id

WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
  AND `order`.completed_at = user_history_facts.first_order_date AND
  `order`.completed_at is not null
  group by 1

# models/schema.yml
version: 2

models:
  - name: trial_customer_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: days_since_purchase

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 user.id AS user_id,
  min(DATE(user_history_facts.first_order_date)) AS first_order_date,
  min(datediff(curdate(), user_history_facts.first_order_date)) as days_since_purchase
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN order_consolidated AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN ${user_history_facts.SQL_TABLE_NAME} AS user_history_facts ON `order`.user_id = user_history_facts.user_id

WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
  AND `order`.completed_at = user_history_facts.first_order_date AND
  `order`.completed_at is not null
  group by 1

view: trial_customer_facts {
  derived_table: {
    sql: SELECT user.id AS user_id,
  min(DATE(user_history_facts.first_order_date)) AS first_order_date,
  min(datediff(curdate(), user_history_facts.first_order_date)) as days_since_purchase
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN order_consolidated AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN ${user_history_facts.SQL_TABLE_NAME} AS user_history_facts ON `order`.user_id = user_history_facts.user_id

WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
  AND `order`.completed_at = user_history_facts.first_order_date AND
  `order`.completed_at is not null
  group by 1 ;;
  }

  dimension: user_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.user_id ;;
  }

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

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

  measure: count {
    label: "Initial Purchase User Count"
    type: count
    drill_fields: [detail*]
  }
}

- view: trial_customer_facts
  derived_table:
    sql: SELECT user.id AS user_id,
  min(DATE(user_history_facts.first_order_date)) AS first_order_date,
  min(datediff(curdate(), user_history_facts.first_order_date)) as days_since_purchase
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN order_consolidated AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN ${user_history_facts.SQL_TABLE_NAME} AS user_history_facts ON `order`.user_id = user_history_facts.user_id

WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
  AND `order`.completed_at = user_history_facts.first_order_date AND
  `order`.completed_at is not null
  group by 1
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: days_since_purchase
    type: number
    sql: ${TABLE}.days_since_purchase
  - dimension_group: first_order
    type: time
    timeframes:
    - date
    - year
    - month
    sql: ${TABLE}.first_order_date
  - measure: count
    type: count
    label: Initial Purchase User Count
    drill_fields:
    - detail*