Visitor Viewed Services

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

SELECT
v.domain_userid,
s.visit_first_happened,
s.ts_first_happened,
CASE WHEN s.visited_services_page = 1 THEN 1 ELSE 0 END AS visited_services_page
FROM (
  SELECT
  domain_userid
  FROM {{ this }}.events
  GROUP BY 1
) v LEFT JOIN (
  SELECT
  domain_userid,
  1 AS visited_services_page,
  MIN(domain_sessionidx) AS visit_first_happened,
  MIN(collector_tstamp) AS ts_first_happened
  FROM {{ this }}.events
  WHERE page_urlpath LIKE '%/services%'
  GROUP BY 1 ) s
ON v.domain_userid = s.domain_userid

# models/schema.yml
version: 2

models:
  - name: visitor_viewed_services
    columns:
      - name: domain_user_id
        tests:
          - unique
          - not_null
      - name: visited_services_page
      - name: timestamp
      - name: visit
      - name: first_visit
      - name: first_visit_tiered

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
v.domain_userid,
s.visit_first_happened,
s.ts_first_happened,
CASE WHEN s.visited_services_page = 1 THEN 1 ELSE 0 END AS visited_services_page
FROM (
  SELECT
  domain_userid
  FROM ${TABLE}.events
  GROUP BY 1
) v LEFT JOIN (
  SELECT
  domain_userid,
  1 AS visited_services_page,
  MIN(domain_sessionidx) AS visit_first_happened,
  MIN(collector_tstamp) AS ts_first_happened
  FROM ${TABLE}.events
  WHERE page_urlpath LIKE '%/services%'
  GROUP BY 1 ) s
ON v.domain_userid = s.domain_userid

view: visitor_viewed_services {
  derived_table: {
    sql: SELECT
v.domain_userid,
s.visit_first_happened,
s.ts_first_happened,
CASE WHEN s.visited_services_page = 1 THEN 1 ELSE 0 END AS visited_services_page
FROM (
  SELECT
  domain_userid
  FROM ${TABLE}.events
  GROUP BY 1
) v LEFT JOIN (
  SELECT
  domain_userid,
  1 AS visited_services_page,
  MIN(domain_sessionidx) AS visit_first_happened,
  MIN(collector_tstamp) AS ts_first_happened
  FROM ${TABLE}.events
  WHERE page_urlpath LIKE '%/services%'
  GROUP BY 1 ) s
ON v.domain_userid = s.domain_userid ;;
  }

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

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

  dimension: timestamp {
    type: time
    sql: ${TABLE}.first_happened ;;
  }

  dimension: visit {
    sql: ${TABLE}.visit_first_happened ;;
  }

  dimension: first_visit {
    sql: ${TABLE}.visit_first_happened ;;
  }

  dimension: first_visit_tiered {
    type: tier
    sql: ${TABLE}.visit_first_happened ;;
  }

  measure: visitors_count {
    type: count_distinct
    hidden: yes
    sql: ${domain_user_id} ;;
  }

  measure: visitors_viewed_services_count {
    type: count_distinct
    filters: [visited_services_page: "True"]
    sql: ${domain_user_id} ;;
  }

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

- view: visitor_viewed_services
  derived_table:
    sql: SELECT
v.domain_userid,
s.visit_first_happened,
s.ts_first_happened,
CASE WHEN s.visited_services_page = 1 THEN 1 ELSE 0 END AS visited_services_page
FROM (
  SELECT
  domain_userid
  FROM ${TABLE}.events
  GROUP BY 1
) v LEFT JOIN (
  SELECT
  domain_userid,
  1 AS visited_services_page,
  MIN(domain_sessionidx) AS visit_first_happened,
  MIN(collector_tstamp) AS ts_first_happened
  FROM ${TABLE}.events
  WHERE page_urlpath LIKE '%/services%'
  GROUP BY 1 ) s
ON v.domain_userid = s.domain_userid
  fields:
  - dimension: domain_user_id
    primary_key: true
    sql: ${TABLE}.domain_userid
  - dimension: visited_services_page
    type: yesno
    sql: ${TABLE}.visited_services_page = 1
  - dimension: timestamp
    type: time
    sql: ${TABLE}.first_happened
  - dimension: visit
    sql: ${TABLE}.visit_first_happened
  - dimension: first_visit
    sql: ${TABLE}.visit_first_happened
  - dimension: first_visit_tiered
    type: tier
    sql: ${TABLE}.visit_first_happened
  - measure: visitors_count
    type: count_distinct
    sql: ${domain_user_id}
  - measure: visitors_viewed_services_count
    type: count_distinct
    filters:
    - visited_services_page: True
    sql: ${domain_user_id}
  - measure: fraction_visitors_viewed_services
    type: number
    sql: NULLIF(${visitors_viewed_services_count},0)/NULLIF(${visitors_count},0)::REAL