Landing Page

-- models/landing_page.sql
{{ config(materialized='table') }}

SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || domain_sessionidx AS visit_id,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS "rank_asc"
   FROM {{ this }}.events
   WHERE event = 'page_view') AS a
 WHERE rank_asc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                    -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)

# models/schema.yml
version: 2

models:
  - name: landing_page
    columns:
      - name: visit_id
        tests:
          - unique
          - not_null
      - name: domain_userid
      - name: domain_sessionidx
      - name: host
      - name: path
      - name: landing_page

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || domain_sessionidx AS visit_id,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS "rank_asc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_asc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                    -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)

view: landing_page {
  derived_table: {
    sql: SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || domain_sessionidx AS visit_id,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS "rank_asc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_asc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                    -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp) ;;
    persist_for: "6 hour"
  }

  dimension: visit_id {
    primary_key: yes
    sql: ${TABLE}.visit_id ;;
  }

  dimension: domain_userid {
    hidden: yes
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: domain_sessionidx {
    type: int
    hidden: yes
    sql: ${TABLE}.domain_sessionidx ;;
  }

  dimension: host {
    sql: ${TABLE}.page_urlhost ;;
  }

  dimension: path {
    sql: ${TABLE}.page_urlpath ;;
  }

  dimension: landing_page {
    sql: ${TABLE}.page_urlhost || ${TABLE}.page_urlpath ;;
  }

  measure: landing_page_count {
    type: count_distinct
    sql: ${landing_page} ;;
  }
}

- view: landing_page
  derived_table:
    sql: SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || domain_sessionidx AS visit_id,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS "rank_asc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_asc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                    -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
    persist_for: 6 hour
  fields:
  - dimension: visit_id
    primary_key: true
    sql: ${TABLE}.visit_id
  - dimension: domain_userid
    hidden: true
    sql: ${TABLE}.domain_userid
  - dimension: domain_sessionidx
    type: int
    hidden: true
    sql: ${TABLE}.domain_sessionidx
  - dimension: host
    sql: ${TABLE}.page_urlhost
  - dimension: path
    sql: ${TABLE}.page_urlpath
  - dimension: landing_page
    sql: ${TABLE}.page_urlhost || ${TABLE}.page_urlpath
  - measure: landing_page_count
    type: count_distinct
    sql: ${landing_page}