dbt Advertising automotive

Page Views

Page Views model for advertising automotive.

inferred

Blocks

dbt SQL LKML LookML

page_views.sql · 39 lines

inferred

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

SELECT
  e.app_id,
  domain_userid,
  domain_sessionidx,
  event_id,
  CASE WHEN t.app_id IS NULL THEN collector_tstamp ELSE convert_timezone('UTC', t.timezone_offset, collector_tstamp) END AS collector_tstamp,
  FIRST_VALUE (collector_tstamp) OVER (
    PARTITION BY e.app_id, domain_userid, domain_sessionidx
    ORDER BY collector_tstamp
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  )  AS next_page_view_tstamp,
  page_urlhost,
  page_urlpath,
  page_urlhost || page_urlpath AS page_url,
  timezone_offset
FROM atomic.events e
LEFT JOIN reference_data.timezones t
ON e.app_id = t.app_id
WHERE event LIKE 'page_view'

# models/schema.yml
version: 2

models:
  - name: page_views
    columns:
      - name: application_id
      - name: user_id
      - name: session_index
      - name: page_url
      - name: url_host
      - name: url_path
      - name: page_view_id
      - name: timestamp
      - name: next_page_view_timestamp
      - name: duration

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.

page_views.sql · 19 lines

source-preserved validation: parses

SELECT
  e.app_id,
  domain_userid,
  domain_sessionidx,
  event_id,
  CASE WHEN t.app_id IS NULL THEN collector_tstamp ELSE convert_timezone('UTC', t.timezone_offset, collector_tstamp) END AS collector_tstamp,
  FIRST_VALUE (collector_tstamp) OVER (
    PARTITION BY e.app_id, domain_userid, domain_sessionidx
    ORDER BY collector_tstamp
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  )  AS next_page_view_tstamp,
  page_urlhost,
  page_urlpath,
  page_urlhost || page_urlpath AS page_url,
  timezone_offset
FROM atomic.events e
LEFT JOIN reference_data.timezones t
ON e.app_id = t.app_id
WHERE event LIKE 'page_view'

page_views.view.lkml · 89 lines

inferred

view: page_views {
  derived_table: {
    sql: SELECT
  e.app_id,
  domain_userid,
  domain_sessionidx,
  event_id,
  CASE WHEN t.app_id IS NULL THEN collector_tstamp ELSE convert_timezone('UTC', t.timezone_offset, collector_tstamp) END AS collector_tstamp,
  FIRST_VALUE (collector_tstamp) OVER (
    PARTITION BY e.app_id, domain_userid, domain_sessionidx
    ORDER BY collector_tstamp
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  )  AS next_page_view_tstamp,
  page_urlhost,
  page_urlpath,
  page_urlhost || page_urlpath AS page_url,
  timezone_offset
FROM atomic.events e
LEFT JOIN reference_data.timezones t
ON e.app_id = t.app_id
WHERE event LIKE 'page_view' ;;
  }

  dimension: application_id {
    sql: ${TABLE}.app_id ;;
  }

  dimension: user_id {
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: session_index {
    sql: ${TABLE}.domain_sessionidx ;;
  }

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

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

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

  dimension: page_view_id {
    sql: ${TABLE}.event_id ;;
  }

  dimension: timestamp {
    sql: ${TABLE}.collector_tstamp ;;
  }

  dimension: next_page_view_timestamp {
    sql: convert_timezone('UTC', ${TABLE}.timezone_offset, ${TABLE}.next_page_view_tstamp) ;;
  }

  dimension: duration {
    sql: EXTRACT(EPOCH FROM ${next_page_view_timestamp} - ${timestamp}) ;;
  }

  dimension_group: timestamp {
    type: time
    timeframes: [time, hour, date, week, month]
    sql: ${TABLE}.collector_tstamp ;;
  }

  measure: page_views_count {
    type: count_distinct
    sql: ${page_view_id} ;;
  }

  measure: distinct_pages_count {
    type: count_distinct
    sql: ${page_url} ;;
  }

  measure: visitors_count {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: sessions_count {
    type: count_distinct
    sql: (${user_id} || ${session_index}) ;;
  }
}

page_views.view.lookml · 63 lines

source-preserved

- view: page_views
  derived_table:
    sql: SELECT
  e.app_id,
  domain_userid,
  domain_sessionidx,
  event_id,
  CASE WHEN t.app_id IS NULL THEN collector_tstamp ELSE convert_timezone('UTC', t.timezone_offset, collector_tstamp) END AS collector_tstamp,
  FIRST_VALUE (collector_tstamp) OVER (
    PARTITION BY e.app_id, domain_userid, domain_sessionidx
    ORDER BY collector_tstamp
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  )  AS next_page_view_tstamp,
  page_urlhost,
  page_urlpath,
  page_urlhost || page_urlpath AS page_url,
  timezone_offset
FROM atomic.events e
LEFT JOIN reference_data.timezones t
ON e.app_id = t.app_id
WHERE event LIKE 'page_view'
  fields:
  - dimension: application_id
    sql: ${TABLE}.app_id
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_index
    sql: ${TABLE}.domain_sessionidx
  - dimension: page_url
    sql: ${TABLE}.page_url
  - dimension: url_host
    sql: ${TABLE}.page_urlhost
  - dimension: url_path
    sql: ${TABLE}.page_urlpath
  - dimension: page_view_id
    sql: ${TABLE}.event_id
  - dimension: timestamp
    sql: ${TABLE}.collector_tstamp
  - dimension: next_page_view_timestamp
    sql: convert_timezone('UTC', ${TABLE}.timezone_offset, ${TABLE}.next_page_view_tstamp)
  - dimension: duration
    sql: EXTRACT(EPOCH FROM ${next_page_view_timestamp} - ${timestamp})
  - dimension_group: timestamp
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.collector_tstamp
  - measure: page_views_count
    type: count_distinct
    sql: ${page_view_id}
  - measure: distinct_pages_count
    type: count_distinct
    sql: ${page_url}
  - measure: visitors_count
    type: count_distinct
    sql: ${user_id}
  - measure: sessions_count
    type: count_distinct
    sql: (${user_id} || ${session_index})

page_views.sql
page_views.sql
page_views.view.lkml
page_views.view.lookml

Page Views

Blocks

Assumptions

Contents