Session Pg Trk Facts

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

select s.session_id
  , s.looker_visitor_id
  , first_referrer
  , least(max(t2s.sent_at) + interval '30 minutes', min(s.next_session_start_at)) as end_at
  , count(case when t2s.event_source = 'tracks' then 1 else null end) as tracks_count
  , count(case when t2s.event = 'viewed_email' then event_id else null end) as cnt_viewed_email
  , count(case when t2s.event = 'viewed_page' then event_id else null end) as cnt_viewed_page
  , count(case when t2s.event = 'viewed_product' then event_id else null end) as cnt_viewed_product
  , count(case when t2s.event = 'completed_order' then event_id else null end) as cnt_completed_order
  from {{ ref('sessions_pg_trk') }} as s
  inner join {{ ref('event_facts') }} as t2s
  using(session_id)
  group by 1,2,3

# models/schema.yml
version: 2

models:
  - name: session_pg_trk_facts
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: first_referrer
      - name: first_referrer_domain
      - name: first_referrer_domain_mapped
      - name: tracks_count
      - name: referrer
      - name: tracks_count_tier
      - name: is_bounced_session
      - name: session_duration_minutes
      - name: session_duration_minutes_tiered
      - name: viewed_email
      - name: viewed_page
      - name: viewed_product
      - name: completed_order

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.

select s.session_id
  , s.looker_visitor_id
  , first_referrer
  , least(max(t2s.sent_at) + interval '30 minutes', min(s.next_session_start_at)) as end_at
  , count(case when t2s.event_source = 'tracks' then 1 else null end) as tracks_count
  , count(case when t2s.event = 'viewed_email' then event_id else null end) as cnt_viewed_email
  , count(case when t2s.event = 'viewed_page' then event_id else null end) as cnt_viewed_page
  , count(case when t2s.event = 'viewed_product' then event_id else null end) as cnt_viewed_product
  , count(case when t2s.event = 'completed_order' then event_id else null end) as cnt_completed_order
  from ${sessions_pg_trk.SQL_TABLE_NAME} as s
  inner join ${event_facts.SQL_TABLE_NAME} as t2s
  using(session_id)
  group by 1,2,3

view: session_pg_trk_facts {
  derived_table: {
    sql: select s.session_id
  , s.looker_visitor_id
  , first_referrer
  , least(max(t2s.sent_at) + interval '30 minutes', min(s.next_session_start_at)) as end_at
  , count(case when t2s.event_source = 'tracks' then 1 else null end) as tracks_count
  , count(case when t2s.event = 'viewed_email' then event_id else null end) as cnt_viewed_email
  , count(case when t2s.event = 'viewed_page' then event_id else null end) as cnt_viewed_page
  , count(case when t2s.event = 'viewed_product' then event_id else null end) as cnt_viewed_product
  , count(case when t2s.event = 'completed_order' then event_id else null end) as cnt_completed_order
  from ${sessions_pg_trk.SQL_TABLE_NAME} as s
  inner join ${event_facts.SQL_TABLE_NAME} as t2s
  using(session_id)
  group by 1,2,3 ;;
  }

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

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

  dimension: first_referrer_domain {
    sql: split_part(${first_referrer},'/',3) ;;
  }

  dimension: first_referrer_domain_mapped {
    sql: CASE WHEN ${first_referrer} like '%facebook%' THEN 'facebook' WHEN ${first_referrer} like '%google%' THEN 'google' WHEN ${first_referrer} like '%gliq%' THEN 'gliq' WHEN ${first_referrer} like '%moneyandmarkets%' THEN 'money and markets' WHEN ${first_referrer} like '%yahoo%' THEN 'yahoo' WHEN ${first_referrer} like '%press%' THEN 'weiss press' WHEN ${first_referrer} like '%zogbyanalytics%' THEN 'zogbyanalytics' ELSE ${first_referrer} END ;;
  }

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

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

  dimension: tracks_count_tier {
    type: tier
    sql: ${tracks_count} ;;
  }

  dimension: is_bounced_session {
    sql: CASE WHEN ${tracks_count} = 1 THEN 'Bounced Session'
ELSE 'Not Bounced Session' END ;;
  }

  dimension: session_duration_minutes {
    type: number
    sql: datediff(minutes, ${sessions_pg_trk.session_start_at_time}::timestamp, ${end_time}::timestamp) ;;
  }

  dimension: session_duration_minutes_tiered {
    type: tier
    sql: ${session_duration_minutes} ;;
  }

  dimension: viewed_email {
    type: yesno
    sql: ${TABLE}.cnt_viewed_email > 0 ;;
  }

  dimension: viewed_page {
    type: yesno
    sql: ${TABLE}.cnt_viewed_page > 0 ;;
  }

  dimension: viewed_product {
    type: yesno
    sql: ${TABLE}.cnt_viewed_product > 0 ;;
  }

  dimension: completed_order {
    type: yesno
    sql: ${TABLE}.cnt_completed_order > 0 ;;
  }

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

  measure: avg_session_duration_minutes {
    type: average
    sql: ${session_duration_minutes}::float ;;
  }

  measure: avg_tracks_per_session {
    type: average
    sql: ${tracks_count}::float ;;
  }

  measure: viewed_email_count {
    type: count
  }

  measure: viewed_page_count {
    type: count
  }

  measure: viewed_product_count {
    type: count
  }

  measure: completed_order_count {
    type: count
  }
}

- view: session_pg_trk_facts
  derived_table:
    sql: select s.session_id
  , s.looker_visitor_id
  , first_referrer
  , least(max(t2s.sent_at) + interval '30 minutes', min(s.next_session_start_at)) as end_at
  , count(case when t2s.event_source = 'tracks' then 1 else null end) as tracks_count
  , count(case when t2s.event = 'viewed_email' then event_id else null end) as cnt_viewed_email
  , count(case when t2s.event = 'viewed_page' then event_id else null end) as cnt_viewed_page
  , count(case when t2s.event = 'viewed_product' then event_id else null end) as cnt_viewed_product
  , count(case when t2s.event = 'completed_order' then event_id else null end) as cnt_completed_order
  from ${sessions_pg_trk.SQL_TABLE_NAME} as s
  inner join ${event_facts.SQL_TABLE_NAME} as t2s
  using(session_id)
  group by 1,2,3
  fields:
  - dimension: session_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: first_referrer
    sql: ${TABLE}.first_referrer
  - dimension: first_referrer_domain
    sql: split_part(${first_referrer},'/',3)
  - dimension: first_referrer_domain_mapped
    sql: CASE WHEN ${first_referrer} like '%facebook%' THEN 'facebook' WHEN ${first_referrer} like '%google%' THEN 'google' WHEN ${first_referrer} like '%gliq%' THEN 'gliq' WHEN ${first_referrer} like '%moneyandmarkets%' THEN 'money and markets' WHEN ${first_referrer} like '%yahoo%' THEN 'yahoo' WHEN ${first_referrer} like '%press%' THEN 'weiss press' WHEN ${first_referrer} like '%zogbyanalytics%' THEN 'zogbyanalytics' ELSE ${first_referrer} END
  - dimension: tracks_count
    type: number
    sql: ${TABLE}.tracks_count
  - dimension: referrer
    type: number
    sql: ${TABLE}.referrer
  - dimension: tracks_count_tier
    type: tier
    sql: ${tracks_count}
  - dimension: is_bounced_session
    sql: CASE WHEN ${tracks_count} = 1 THEN 'Bounced Session'
ELSE 'Not Bounced Session' END
  - dimension: session_duration_minutes
    type: number
    sql: datediff(minutes, ${sessions_pg_trk.session_start_at_time}::timestamp, ${end_time}::timestamp)
  - dimension: session_duration_minutes_tiered
    type: tier
    sql: ${session_duration_minutes}
  - dimension: viewed_email
    type: yesno
    sql: ${TABLE}.cnt_viewed_email > 0
  - dimension: viewed_page
    type: yesno
    sql: ${TABLE}.cnt_viewed_page > 0
  - dimension: viewed_product
    type: yesno
    sql: ${TABLE}.cnt_viewed_product > 0
  - dimension: completed_order
    type: yesno
    sql: ${TABLE}.cnt_completed_order > 0
  - dimension_group: end
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.end_at
  - measure: avg_session_duration_minutes
    type: average
    sql: ${session_duration_minutes}::float
  - measure: avg_tracks_per_session
    type: average
    sql: ${tracks_count}::float
  - measure: viewed_email_count
    type: count
  - measure: viewed_page_count
    type: count
  - measure: viewed_product_count
    type: count
  - measure: completed_order_count
    type: count