Visitors

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

SELECT
  ae.domain_userid,
  max(x.domain_userid) customer_id,
  MIN(ae.collector_tstamp) AS first_touch_ts,
  MAX(ae.collector_tstamp) AS last_touch_ts,
  COUNT(distinct (ae.event_id)) AS number_of_events,
  COUNT(DISTINCT case when event = 'page_view' then ae.page_title end) AS distinct_pages_viewed,
  count(distinct case when event = 'transaction' then tr_orderid end) distinct_orders_placed,
  count(distinct case when event = 'transaction_item' then ti_category end) distinct_category_ordered,
  count(distinct case when event = 'transaction_item' then ti_sku end) distinct_skus_ordered,

  MAX(ae.domain_sessionidx) AS number_of_visits,
  max(x.ltv) ltv
FROM {{ this }}.events ae left join (select y.domain_userid, sum(y.tr_total) ltv from
                                        (select domain_userid, tr_total, row_number() over (partition by tr_orderid order by collector_tstamp asc) rn
                                          FROM {{ this }}.events
                                          WHERE event = 'transaction') y where y.rn = 1 group by 1
                                ) x  on x.domain_userid = ae.domain_userid
WHERE ae.domain_userid IS NOT NULL
AND ae.domain_userid <> ''
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: visitors
    columns:
      - name: domain_user_id
        tests:
          - unique
          - not_null
      - name: customer_id
      - name: lifetime_value
      - name: lifetime_value_tier
      - name: lifetime_events
      - name: events_tiered
      - name: bounce
      - name: lifetime_visits
      - name: lifetime_visits_tiered
      - name: lifetime_distinct_pages_viewed
      - name: distinct_orders_placed
      - name: distinct_sku_ordered
      - name: distinct_category_ordered
      - name: lifetime_distinct_pages_viewed_tiered
      - name: visit_history
      - name: event_history

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
  ae.domain_userid,
  max(x.domain_userid) customer_id,
  MIN(ae.collector_tstamp) AS first_touch_ts,
  MAX(ae.collector_tstamp) AS last_touch_ts,
  COUNT(distinct (ae.event_id)) AS number_of_events,
  COUNT(DISTINCT case when event = 'page_view' then ae.page_title end) AS distinct_pages_viewed,
  count(distinct case when event = 'transaction' then tr_orderid end) distinct_orders_placed,
  count(distinct case when event = 'transaction_item' then ti_category end) distinct_category_ordered,
  count(distinct case when event = 'transaction_item' then ti_sku end) distinct_skus_ordered,

  MAX(ae.domain_sessionidx) AS number_of_visits,
  max(x.ltv) ltv
FROM ${TABLE}.events ae left join (select y.domain_userid, sum(y.tr_total) ltv from
                                        (select domain_userid, tr_total, row_number() over (partition by tr_orderid order by collector_tstamp asc) rn
                                          FROM ${TABLE}.events
                                          WHERE event = 'transaction') y where y.rn = 1 group by 1
                                ) x  on x.domain_userid = ae.domain_userid
WHERE ae.domain_userid IS NOT NULL
AND ae.domain_userid <> ''
GROUP BY 1

view: visitors {
  derived_table: {
    sql: SELECT
  ae.domain_userid,
  max(x.domain_userid) customer_id,
  MIN(ae.collector_tstamp) AS first_touch_ts,
  MAX(ae.collector_tstamp) AS last_touch_ts,
  COUNT(distinct (ae.event_id)) AS number_of_events,
  COUNT(DISTINCT case when event = 'page_view' then ae.page_title end) AS distinct_pages_viewed,
  count(distinct case when event = 'transaction' then tr_orderid end) distinct_orders_placed,
  count(distinct case when event = 'transaction_item' then ti_category end) distinct_category_ordered,
  count(distinct case when event = 'transaction_item' then ti_sku end) distinct_skus_ordered,

  MAX(ae.domain_sessionidx) AS number_of_visits,
  max(x.ltv) ltv
FROM ${TABLE}.events ae left join (select y.domain_userid, sum(y.tr_total) ltv from
                                        (select domain_userid, tr_total, row_number() over (partition by tr_orderid order by collector_tstamp asc) rn
                                          FROM ${TABLE}.events
                                          WHERE event = 'transaction') y where y.rn = 1 group by 1
                                ) x  on x.domain_userid = ae.domain_userid
WHERE ae.domain_userid IS NOT NULL
AND ae.domain_userid <> ''
GROUP BY 1 ;;
  }

  dimension: domain_user_id {
    primary_key: yes
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: customer_id {
    sql: ${TABLE}.customer_id ;;
  }

  dimension: lifetime_value {
    type: number
    sql: ${TABLE}.ltv ;;
  }

  dimension: lifetime_value_tier {
    type: tier
    sql: ${lifetime_value} ;;
  }

  dimension: lifetime_events {
    type: int
    sql: ${TABLE}.number_of_events ;;
  }

  dimension: events_tiered {
    type: tier
    sql: ${TABLE}.number_of_events ;;
  }

  dimension: bounce {
    type: yesno
    sql: ${TABLE}.number_of_events = 1 ;;
  }

  dimension: lifetime_visits {
    type: int
    sql: ${TABLE}.number_of_visits ;;
  }

  dimension: lifetime_visits_tiered {
    type: tier
    sql: ${TABLE}.number_of_visits ;;
  }

  dimension: lifetime_distinct_pages_viewed {
    type: int
    sql: ${TABLE}.distinct_pages_viewed ;;
  }

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

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

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

  dimension: lifetime_distinct_pages_viewed_tiered {
    type: tier
    sql: ${TABLE}.distinct_pages_viewed ;;
  }

  dimension: visit_history {
    sql: ${domain_user_id} ;;
  }

  dimension: event_history {
    sql: ${domain_user_id} ;;
  }

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

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

  measure: visitors_count {
    type: count
  }

  measure: customers_count {
    type: count_distinct
    sql: ${TABLE}.customer_id ;;
  }

  measure: events_count {
    type: sum
    sql: ${TABLE}.number_of_events ;;
  }

  measure: events_per_visitor {
    type: number
    sql: ${events_count} / NULLIF(${visitors_count}, 0)::REAL ;;
  }

  measure: visits_count {
    type: sum
    sql: ${lifetime_visits} ;;
  }

  measure: events_per_visit {
    type: number
    sql: ${events_count} / NULLIF(${visits_count}, 0)::REAL ;;
  }

  measure: bounced_visitors_count {
    type: count
    filters: [bounce: "True"]
  }

  measure: bounce_rate {
    type: number
    sql: ${bounced_visitors_count}/${visitors_count}::REAL ;;
  }
}

- view: visitors
  derived_table:
    sql: SELECT
  ae.domain_userid,
  max(x.domain_userid) customer_id,
  MIN(ae.collector_tstamp) AS first_touch_ts,
  MAX(ae.collector_tstamp) AS last_touch_ts,
  COUNT(distinct (ae.event_id)) AS number_of_events,
  COUNT(DISTINCT case when event = 'page_view' then ae.page_title end) AS distinct_pages_viewed,
  count(distinct case when event = 'transaction' then tr_orderid end) distinct_orders_placed,
  count(distinct case when event = 'transaction_item' then ti_category end) distinct_category_ordered,
  count(distinct case when event = 'transaction_item' then ti_sku end) distinct_skus_ordered,

  MAX(ae.domain_sessionidx) AS number_of_visits,
  max(x.ltv) ltv
FROM ${TABLE}.events ae left join (select y.domain_userid, sum(y.tr_total) ltv from
                                        (select domain_userid, tr_total, row_number() over (partition by tr_orderid order by collector_tstamp asc) rn
                                          FROM ${TABLE}.events
                                          WHERE event = 'transaction') y where y.rn = 1 group by 1
                                ) x  on x.domain_userid = ae.domain_userid
WHERE ae.domain_userid IS NOT NULL
AND ae.domain_userid <> ''
GROUP BY 1
  fields:
  - dimension: domain_user_id
    primary_key: true
    sql: ${TABLE}.domain_userid
  - dimension: customer_id
    sql: ${TABLE}.customer_id
  - dimension: lifetime_value
    type: number
    sql: ${TABLE}.ltv
  - dimension: lifetime_value_tier
    type: tier
    sql: ${lifetime_value}
  - dimension: lifetime_events
    type: int
    sql: ${TABLE}.number_of_events
  - dimension: events_tiered
    type: tier
    sql: ${TABLE}.number_of_events
  - dimension: bounce
    type: yesno
    sql: ${TABLE}.number_of_events = 1
  - dimension: lifetime_visits
    type: int
    sql: ${TABLE}.number_of_visits
  - dimension: lifetime_visits_tiered
    type: tier
    sql: ${TABLE}.number_of_visits
  - dimension: lifetime_distinct_pages_viewed
    type: int
    sql: ${TABLE}.distinct_pages_viewed
  - dimension: distinct_orders_placed
    type: int
    sql: ${TABLE}.distinct_orders_placed
  - dimension: distinct_sku_ordered
    type: int
    sql: ${TABLE}.distinct_sku_ordered
  - dimension: distinct_category_ordered
    type: int
    sql: ${TABLE}.distinct_category_ordered
  - dimension: lifetime_distinct_pages_viewed_tiered
    type: tier
    sql: ${TABLE}.distinct_pages_viewed
  - dimension: visit_history
    sql: ${domain_user_id}
  - dimension: event_history
    sql: ${domain_user_id}
  - dimension_group: first_touch_timestamp
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_touch_ts
  - dimension_group: last_touch
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.last_touch_ts
  - measure: visitors_count
    type: count
  - measure: customers_count
    type: count_distinct
    sql: ${TABLE}.customer_id
  - measure: events_count
    type: sum
    sql: ${TABLE}.number_of_events
  - measure: events_per_visitor
    type: number
    sql: ${events_count} / NULLIF(${visitors_count}, 0)::REAL
  - measure: visits_count
    type: sum
    sql: ${lifetime_visits}
  - measure: events_per_visit
    type: number
    sql: ${events_count} / NULLIF(${visits_count}, 0)::REAL
  - measure: bounced_visitors_count
    type: count
    filters:
    - bounce: True
  - measure: bounce_rate
    type: number
    sql: ${bounced_visitors_count}/${visitors_count}::REAL