SQL Clothing app

User Session

User Session model for clothing app.

source-preserved

Blocks

dbt SQL LKML LookML

user_session.sql · 86 lines

inferred

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

with
sessions_pre as (
  select domain_userid || domain_sessionidx || '_' || resessionidx as session_pkey
    , min(collector_tstamp) as start_at
    , max(collector_tstamp) as last_event_at
    , min(dvce_created_tstamp) AS dvce_min_tstamp
    , max(dvce_created_tstamp) AS dvce_max_tstamp
    , count(1) as number_of_events
    , count(distinct(floor(extract(epoch from dvce_created_tstamp)/30)))/2::float AS time_engaged_with_minutes
  FROM
    generic_schema.events as events,
    {{ ref('snowplow_resession') }} as snowplow_resession

where
  events.event_id = snowplow_resession.event_id
    and domain_userid is not null
    and domain_sessionidx is not null
    and domain_userid != ''
    and dvce_created_tstamp IS NOT NULL
    and dvce_created_tstamp > '2000-01-01' -- Prevent SQL errors
    and dvce_created_tstamp < '2030-01-01' -- Prevent SQL errors
  GROUP BY 1
)

select
  start_at
  , least(last_event_at + interval '1 minute', lead(start_at) over (partition by domain_userid order by domain_sessionidx, resessionidx)) as end_at
  , number_of_events
  , time_engaged_with_minutes
  , b.*
from sessions_pre
  inner join {{ ref('user_session_prep') }} as b
    on sessions_pre.session_pkey = b.session_pkey

# models/schema.yml
version: 2

models:
  - name: user_session
    columns:
      - name: session_pkey
        tests:
          - unique
          - not_null
      - name: domain_user_id
      - name: domain_session_index
      - name: resession_index
      - name: number_of_events
      - name: duration_minutes
      - name: time_engaged_with_minutes
      - name: bounced
      - name: is_first_session
      - name: new_vs_returning_visitor
      - name: geography_country
      - name: geography_region
      - name: geography_city
      - name: landing_page_urlhost
      - name: landing_page_urlpath
      - name: exit_page_urlhost
      - name: exit_page_urlpath
      - name: browser
      - name: browser_family
      - name: browser_version
      - name: browser_type
      - name: browser_renderengine
      - name: browser_language
      - name: browser_has_director_plugin
      - name: browser_has_flash_plugin
      - name: browser_has_gears_plugin
      - name: browser_has_java_plugin
      - name: browser_has_pdf_plugin
      - name: browser_has_quicktime_plugin
      - name: browser_has_realplayer_plugin
      - name: browser_has_silverlight_plugin
      - name: browser_has_windowsmedia_plugin
      - name: browser_supports_cookies
      - name: operating_system
      - name: operating_system_family
      - name: operating_system_manufacturer
      - name: device_type
      - name: device_is_mobile
      - name: device_screen_width
      - name: device_screen_height

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.

user_session.sql · 33 lines

source-preserved validation: not checked

with
sessions_pre as (
  select domain_userid || domain_sessionidx || '_' || resessionidx as session_pkey
    , min(collector_tstamp) as start_at
    , max(collector_tstamp) as last_event_at
    , min(dvce_created_tstamp) AS dvce_min_tstamp
    , max(dvce_created_tstamp) AS dvce_max_tstamp
    , count(1) as number_of_events
    , count(distinct(floor(extract(epoch from dvce_created_tstamp)/30)))/2::float AS time_engaged_with_minutes
  FROM
    generic_schema.events as events,
    ${snowplow_resession.SQL_TABLE_NAME} as snowplow_resession

where
  events.event_id = snowplow_resession.event_id
    and domain_userid is not null
    and domain_sessionidx is not null
    and domain_userid != ''
    and dvce_created_tstamp IS NOT NULL
    and dvce_created_tstamp > '2000-01-01' -- Prevent SQL errors
    and dvce_created_tstamp < '2030-01-01' -- Prevent SQL errors
  GROUP BY 1
)

select
  start_at
  , least(last_event_at + interval '1 minute', lead(start_at) over (partition by domain_userid order by domain_sessionidx, resessionidx)) as end_at
  , number_of_events
  , time_engaged_with_minutes
  , b.*
from sessions_pre
  inner join ${user_session_prep.SQL_TABLE_NAME} as b
    on sessions_pre.session_pkey = b.session_pkey

user_session.view.lkml · 335 lines

inferred

view: user_session {
  derived_table: {
    sql: with
sessions_pre as (
  select domain_userid || domain_sessionidx || '_' || resessionidx as session_pkey
    , min(collector_tstamp) as start_at
    , max(collector_tstamp) as last_event_at
    , min(dvce_created_tstamp) AS dvce_min_tstamp
    , max(dvce_created_tstamp) AS dvce_max_tstamp
    , count(1) as number_of_events
    , count(distinct(floor(extract(epoch from dvce_created_tstamp)/30)))/2::float AS time_engaged_with_minutes
  FROM
    generic_schema.events as events,
    ${snowplow_resession.SQL_TABLE_NAME} as snowplow_resession

where
  events.event_id = snowplow_resession.event_id
    and domain_userid is not null
    and domain_sessionidx is not null
    and domain_userid != ''
    and dvce_created_tstamp IS NOT NULL
    and dvce_created_tstamp > '2000-01-01' -- Prevent SQL errors
    and dvce_created_tstamp < '2030-01-01' -- Prevent SQL errors
  GROUP BY 1
)

select
  start_at
  , least(last_event_at + interval '1 minute', lead(start_at) over (partition by domain_userid order by domain_sessionidx, resessionidx)) as end_at
  , number_of_events
  , time_engaged_with_minutes
  , b.*
from sessions_pre
  inner join ${user_session_prep.SQL_TABLE_NAME} as b
    on sessions_pre.session_pkey = b.session_pkey ;;
  }

  dimension: session_pkey {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.session_pkey ;;
  }

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

  dimension: domain_session_index {
    type: number
    sql: ${TABLE}.domain_sessionidx ;;
  }

  dimension: resession_index {
    type: number
    sql: ${TABLE}.resessionidx ;;
  }

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

  dimension: duration_minutes {
    type: number
    sql: DATEDIFF(MINUTES, ${TABLE}.start_at, ${TABLE}.end_at) ;;
  }

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

  dimension: bounced {
    type: yesno
    sql: ${number_of_events} = 1 ;;
  }

  dimension: is_first_session {
    type: yesno
    sql: ${domain_session_index} = 1 ;;
  }

  dimension: new_vs_returning_visitor {
  }

  dimension: geography_country {
    sql: ${TABLE}.geo_country ;;
  }

  dimension: geography_region {
    sql: ${TABLE}.geo_region ;;
  }

  dimension: geography_city {
    sql: ${TABLE}.geo_city ;;
  }

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

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

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

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

  dimension: browser {
    sql: ${TABLE}.br_name ;;
  }

  dimension: browser_family {
    sql: ${TABLE}.br_family ;;
  }

  dimension: browser_version {
    sql: ${TABLE}.br_version ;;
  }

  dimension: browser_type {
    sql: ${TABLE}.br_type ;;
  }

  dimension: browser_renderengine {
    sql: ${TABLE}.br_renderengine ;;
  }

  dimension: browser_language {
    sql: ${TABLE}.br_lang ;;
  }

  dimension: browser_has_director_plugin {
    type: yesno
    sql: ${TABLE}.br_features_director ;;
  }

  dimension: browser_has_flash_plugin {
    type: yesno
    sql: ${TABLE}.br_features_flash ;;
  }

  dimension: browser_has_gears_plugin {
    type: yesno
    sql: ${TABLE}.br_features_gears ;;
  }

  dimension: browser_has_java_plugin {
    type: yesno
    sql: ${TABLE}.br_features_java ;;
  }

  dimension: browser_has_pdf_plugin {
    type: yesno
    sql: ${TABLE}.br_features_pdf ;;
  }

  dimension: browser_has_quicktime_plugin {
    type: yesno
    sql: ${TABLE}.br_features_quicktime ;;
  }

  dimension: browser_has_realplayer_plugin {
    type: yesno
    sql: ${TABLE}.br_features_realplayer ;;
  }

  dimension: browser_has_silverlight_plugin {
    type: yesno
    sql: ${TABLE}.br_features_silverlight ;;
  }

  dimension: browser_has_windowsmedia_plugin {
    type: yesno
    sql: ${TABLE}.br_features_windowsmedia ;;
  }

  dimension: browser_supports_cookies {
    type: yesno
    sql: ${TABLE}.br_cookies ;;
  }

  dimension: operating_system {
    sql: ${TABLE}.os_name ;;
  }

  dimension: operating_system_family {
    sql: ${TABLE}.os_family ;;
  }

  dimension: operating_system_manufacturer {
    sql: ${TABLE}.os_manufacturer ;;
  }

  dimension: device_type {
    sql: ${TABLE}.dvce_type ;;
  }

  dimension: device_is_mobile {
    type: yesno
    sql: ${TABLE}.dvce_ismobile ;;
  }

  dimension: device_screen_width {
    sql: ${TABLE}.dvce_screenwidth ;;
  }

  dimension: device_screen_height {
    sql: ${TABLE}.dvce_screenheight ;;
  }

  dimension: referrer_medium {
  }

  dimension: referrer_source {
    sql: ${TABLE}.refr_source ;;
  }

  dimension: referrer_term {
    sql: ${TABLE}.refr_term ;;
  }

  dimension: referrer_url_host {
    sql: ${TABLE}.refr_urlhost ;;
  }

  dimension: referrer_url_path {
    sql: ${TABLE}.refr_urlpath ;;
  }

  dimension: campaign_medium {
    sql: ${TABLE}.mkt_medium ;;
  }

  dimension: campaign_source {
    sql: ${TABLE}.mkt_source ;;
  }

  dimension: campaign_term {
    sql: ${TABLE}.mkt_term ;;
  }

  dimension: campaign_name {
    sql: ${TABLE}.mkt_campaign ;;
  }

  dimension: campaign_content {
    sql: ${TABLE}.mkt_content ;;
  }

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

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

  measure: session_count {
    type: count_distinct
    sql: ${session_pkey} ;;
  }

  measure: count {
    type: count
    drill_fields: [count_drill*]
  }

  measure: bounced_session_count {
    type: count
    drill_fields: [count_drill*]
  }

  measure: bounce_rate {
    type: number
    sql: ${bounced_session_count}::float/NULLIF(${count},0) ;;
  }

  measure: average_number_of_events {
    type: average
    sql: ${number_of_events} ;;
  }

  measure: average_duration_minutes {
    type: average
    sql: ${duration_minutes} ;;
  }

  measure: sessions_per_user {
    type: number
    sql: ${count}::float/NULLIF(${user_cookie.count},0) ;;
  }

  measure: user_cookie.count {
    type: count_distinct
    drill_fields: [user_cookie.domain_user_id, user_cookie.id, user_cookie.ip_address, location.city, location.country]
    sql: ${domain_user_id} ;;
  }

  measure: average_time_engaged_minutes {
    type: average
    sql: ${time_engaged_with_minutes} ;;
  }

  measure: sessions_from_new_visitors_count {
    type: count
    filters: [domain_session_index: "1"]
    drill_fields: [count_drill*]
  }

  measure: sessions_from_returning_visitors_count {
    type: count
    drill_fields: [count_drill*]
  }

  measure: percent_new_visitor_sessions {
    type: number
    value_format: "#.00%"
    sql: ${sessions_from_new_visitors_count}::float/NULLIF(${count},0) ;;
  }

  measure: percent_returning_visitor_sessions {
    type: number
    value_format: "#.00%"
    sql: ${sessions_from_returning_visitors_count}::float/NULLIF(${count},0) ;;
  }
}

user_session.view.lookml · 222 lines

source-preserved

- view: user_session
  derived_table:
    sql: with
sessions_pre as (
  select domain_userid || domain_sessionidx || '_' || resessionidx as session_pkey
    , min(collector_tstamp) as start_at
    , max(collector_tstamp) as last_event_at
    , min(dvce_created_tstamp) AS dvce_min_tstamp
    , max(dvce_created_tstamp) AS dvce_max_tstamp
    , count(1) as number_of_events
    , count(distinct(floor(extract(epoch from dvce_created_tstamp)/30)))/2::float AS time_engaged_with_minutes
  FROM
    generic_schema.events as events,
    ${snowplow_resession.SQL_TABLE_NAME} as snowplow_resession

where
  events.event_id = snowplow_resession.event_id
    and domain_userid is not null
    and domain_sessionidx is not null
    and domain_userid != ''
    and dvce_created_tstamp IS NOT NULL
    and dvce_created_tstamp > '2000-01-01' -- Prevent SQL errors
    and dvce_created_tstamp < '2030-01-01' -- Prevent SQL errors
  GROUP BY 1
)

select
  start_at
  , least(last_event_at + interval '1 minute', lead(start_at) over (partition by domain_userid order by domain_sessionidx, resessionidx)) as end_at
  , number_of_events
  , time_engaged_with_minutes
  , b.*
from sessions_pre
  inner join ${user_session_prep.SQL_TABLE_NAME} as b
    on sessions_pre.session_pkey = b.session_pkey
  fields:
  - dimension: session_pkey
    primary_key: true
    hidden: true
    sql: ${TABLE}.session_pkey
  - dimension: domain_user_id
    sql: ${TABLE}.domain_userid
  - dimension: domain_session_index
    type: number
    sql: ${TABLE}.domain_sessionidx
  - dimension: resession_index
    type: number
    sql: ${TABLE}.resessionidx
  - dimension: number_of_events
    type: number
    sql: ${TABLE}.number_of_events
  - dimension: duration_minutes
    type: number
    sql: DATEDIFF(MINUTES, ${TABLE}.start_at, ${TABLE}.end_at)
  - dimension: time_engaged_with_minutes
    sql: ${TABLE}.time_engaged_with_minutes
  - dimension: bounced
    type: yesno
    sql: ${number_of_events} = 1
  - dimension: is_first_session
    type: yesno
    sql: ${domain_session_index} = 1
  - dimension: new_vs_returning_visitor
  - dimension: geography_country
    sql: ${TABLE}.geo_country
  - dimension: geography_region
    sql: ${TABLE}.geo_region
  - dimension: geography_city
    sql: ${TABLE}.geo_city
  - dimension: landing_page_urlhost
    sql: ${TABLE}.landing_page_urlhost
  - dimension: landing_page_urlpath
    sql: ${TABLE}.landing_page_urlpath
  - dimension: exit_page_urlhost
    sql: ${TABLE}.exit_page_urlhost
  - dimension: exit_page_urlpath
    sql: ${TABLE}.exit_page_urlpath
  - dimension: browser
    sql: ${TABLE}.br_name
  - dimension: browser_family
    sql: ${TABLE}.br_family
  - dimension: browser_version
    sql: ${TABLE}.br_version
  - dimension: browser_type
    sql: ${TABLE}.br_type
  - dimension: browser_renderengine
    sql: ${TABLE}.br_renderengine
  - dimension: browser_language
    sql: ${TABLE}.br_lang
  - dimension: browser_has_director_plugin
    type: yesno
    sql: ${TABLE}.br_features_director
  - dimension: browser_has_flash_plugin
    type: yesno
    sql: ${TABLE}.br_features_flash
  - dimension: browser_has_gears_plugin
    type: yesno
    sql: ${TABLE}.br_features_gears
  - dimension: browser_has_java_plugin
    type: yesno
    sql: ${TABLE}.br_features_java
  - dimension: browser_has_pdf_plugin
    type: yesno
    sql: ${TABLE}.br_features_pdf
  - dimension: browser_has_quicktime_plugin
    type: yesno
    sql: ${TABLE}.br_features_quicktime
  - dimension: browser_has_realplayer_plugin
    type: yesno
    sql: ${TABLE}.br_features_realplayer
  - dimension: browser_has_silverlight_plugin
    type: yesno
    sql: ${TABLE}.br_features_silverlight
  - dimension: browser_has_windowsmedia_plugin
    type: yesno
    sql: ${TABLE}.br_features_windowsmedia
  - dimension: browser_supports_cookies
    type: yesno
    sql: ${TABLE}.br_cookies
  - dimension: operating_system
    sql: ${TABLE}.os_name
  - dimension: operating_system_family
    sql: ${TABLE}.os_family
  - dimension: operating_system_manufacturer
    sql: ${TABLE}.os_manufacturer
  - dimension: device_type
    sql: ${TABLE}.dvce_type
  - dimension: device_is_mobile
    type: yesno
    sql: ${TABLE}.dvce_ismobile
  - dimension: device_screen_width
    sql: ${TABLE}.dvce_screenwidth
  - dimension: device_screen_height
    sql: ${TABLE}.dvce_screenheight
  - dimension: referrer_medium
  - dimension: referrer_source
    sql: ${TABLE}.refr_source
  - dimension: referrer_term
    sql: ${TABLE}.refr_term
  - dimension: referrer_url_host
    sql: ${TABLE}.refr_urlhost
  - dimension: referrer_url_path
    sql: ${TABLE}.refr_urlpath
  - dimension: campaign_medium
    sql: ${TABLE}.mkt_medium
  - dimension: campaign_source
    sql: ${TABLE}.mkt_source
  - dimension: campaign_term
    sql: ${TABLE}.mkt_term
  - dimension: campaign_name
    sql: ${TABLE}.mkt_campaign
  - dimension: campaign_content
    sql: ${TABLE}.mkt_content
  - dimension_group: start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.start_at
  - dimension_group: end
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.end_at
  - measure: session_count
    type: count_distinct
    sql: ${session_pkey}
  - measure: count
    type: count
    drill_fields:
    - count_drill*
  - measure: bounced_session_count
    type: count
    drill_fields:
    - count_drill*
  - measure: bounce_rate
    type: number
    sql: ${bounced_session_count}::float/NULLIF(${count},0)
  - measure: average_number_of_events
    type: average
    sql: ${number_of_events}
  - measure: average_duration_minutes
    type: average
    sql: ${duration_minutes}
  - measure: sessions_per_user
    type: number
    sql: ${count}::float/NULLIF(${user_cookie.count},0)
  - measure: user_cookie.count
    type: count_distinct
    drill_fields:
    - user_cookie.domain_user_id
    - user_cookie.id
    - user_cookie.ip_address
    - location.city
    - location.country
    sql: ${domain_user_id}
  - measure: average_time_engaged_minutes
    type: average
    sql: ${time_engaged_with_minutes}
  - measure: sessions_from_new_visitors_count
    type: count
    drill_fields:
    - count_drill*
    filters:
    - domain_session_index: 1
  - measure: sessions_from_returning_visitors_count
    type: count
    drill_fields:
    - count_drill*
  - measure: percent_new_visitor_sessions
    type: number
    value_format: '#.00%'
    sql: ${sessions_from_new_visitors_count}::float/NULLIF(${count},0)
  - measure: percent_returning_visitor_sessions
    type: number
    value_format: '#.00%'
    sql: ${sessions_from_returning_visitors_count}::float/NULLIF(${count},0)

user_session.sql
user_session.sql
user_session.view.lkml
user_session.view.lookml

User Session

Blocks

Assumptions

Contents