data.ml
dbt Media large poc

Visitors

Visitors model for media large poc.

inferred

Blocks

visitors.sql · 64 lines
inferred 
-- models/visitors.sql
{{ config(materialized='view') }}

SELECT
  b.best_userid,
  b.first_touch,
  b.last_touch,
  b.number_of_events,
  b.distinct_pages_viewed,
  b.number_of_sessions,
  s.start_event_id AS first_event_id,
  s.mkt_medium,
  s.mkt_source,
  s.mkt_term,
  s.mkt_campaign,
  s.mkt_content,
  s.refr_urlhost,
  s.refr_urlpath,
  s.refr_medium,
  s.refr_source,
  s.refr_term,
  s.page_urlhost,
  s.page_urlpath,
  s.page_urlquery,
  s.page_urlfragment,
  s.app_id
FROM {{ ref('visitors_basic') }} AS b
LEFT JOIN {{ ref('sessions') }} AS s
ON b.best_userid = s.best_userid
WHERE s.session_index = 1

# models/schema.yml
version: 2

models:
  - name: visitors
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: first_touch
      - name: last_touch
      - name: number_of_events
      - name: number_of_events_tiered
      - name: bounce
      - name: distinct_pages_viewed
      - name: distinct_pages_viewed_tiered
      - name: number_of_sessions
      - name: number_of_sessions_tiered
      - name: session_stream
      - name: event_stream
      - name: landing_page_url_host
      - name: landing_page_url_path
      - name: landing_page_url
      - name: referer_medium
      - name: referer_source
      - name: referer_term
      - name: referer_url_host
      - name: referer_url_path
      - name: campaign_medium
      - name: campaign_source
      - name: campaign_term
      - name: campaign_name

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.
visitors.sql · 27 lines
source-preserved validation: not checked 
SELECT
  b.best_userid,
  b.first_touch,
  b.last_touch,
  b.number_of_events,
  b.distinct_pages_viewed,
  b.number_of_sessions,
  s.start_event_id AS first_event_id,
  s.mkt_medium,
  s.mkt_source,
  s.mkt_term,
  s.mkt_campaign,
  s.mkt_content,
  s.refr_urlhost,
  s.refr_urlpath,
  s.refr_medium,
  s.refr_source,
  s.refr_term,
  s.page_urlhost,
  s.page_urlpath,
  s.page_urlquery,
  s.page_urlfragment,
  s.app_id
FROM ${visitors_basic.SQL_TABLE_NAME} AS b
LEFT JOIN ${sessions.SQL_TABLE_NAME} AS s
ON b.best_userid = s.best_userid
WHERE s.session_index = 1
visitors.view.lkml · 216 lines
inferred 
view: visitors {
  derived_table: {
    sql: SELECT
  b.best_userid,
  b.first_touch,
  b.last_touch,
  b.number_of_events,
  b.distinct_pages_viewed,
  b.number_of_sessions,
  s.start_event_id AS first_event_id,
  s.mkt_medium,
  s.mkt_source,
  s.mkt_term,
  s.mkt_campaign,
  s.mkt_content,
  s.refr_urlhost,
  s.refr_urlpath,
  s.refr_medium,
  s.refr_source,
  s.refr_term,
  s.page_urlhost,
  s.page_urlpath,
  s.page_urlquery,
  s.page_urlfragment,
  s.app_id
FROM ${visitors_basic.SQL_TABLE_NAME} AS b
LEFT JOIN ${sessions.SQL_TABLE_NAME} AS s
ON b.best_userid = s.best_userid
WHERE s.session_index = 1 ;;
  }

  dimension: user_id {
    primary_key: yes
    sql: ${TABLE}.best_userid ;;
  }

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

  dimension: last_touch {
    type: time
    sql: ${TABLE}.last_touch ;;
  }

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

  dimension: number_of_events_tiered {
    type: tier
    sql: ${number_of_events} ;;
  }

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

  dimension: distinct_pages_viewed {
    type: int
    sql: ${TABLE}.distinct_pages_viewed ;;
  }

  dimension: distinct_pages_viewed_tiered {
    type: tier
    sql: ${distinct_pages_viewed} ;;
  }

  dimension: number_of_sessions {
    type: int
    sql: ${TABLE}.number_of_sessions ;;
  }

  dimension: number_of_sessions_tiered {
    type: tier
    sql: ${number_of_sessions} ;;
  }

  dimension: session_stream {
    sql: ${user_id} ;;
  }

  dimension: event_stream {
    sql: ${user_id} ;;
  }

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

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

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

  dimension: referer_medium {
  }

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

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

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

  dimension: referer_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_group: first_touch {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_touch ;;
  }

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

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

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

  measure: events_count {
    type: sum
    sql: ${TABLE}.number_of_events ;;
  }

  measure: events_per_visitor {
    type: number
    sql: ${events_count}/NULLIF(${count},0)::REAL ;;
  }

  measure: sessions_count {
    type: sum
    sql: ${TABLE}.number_of_sessions ;;
  }

  measure: sessions_per_visitor {
    type: number
    sql: ${sessions_count}/NULLIF(${count},0)::REAL ;;
  }

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

  measure: campaign_medium_count {
    type: count_distinct
    sql: ${campaign_medium} ;;
  }

  measure: campaign_source_count {
    type: count_distinct
    sql: ${campaign_source} ;;
  }

  measure: campaign_term_count {
    type: count_distinct
    sql: ${campaign_term} ;;
  }

  measure: campaign_count {
    type: count_distinct
    sql: ${campaign_name} ;;
  }

  measure: referer_medium_count {
    type: count_distinct
    sql: ${referer_medium} ;;
  }

  measure: referer_source_count {
    type: count_distinct
    sql: ${referer_source} ;;
  }

  measure: referer_term_count {
    type: count_distinct
    sql: ${referer_term} ;;
  }
}
visitors.view.lookml · 139 lines
source-preserved 
- view: visitors
  derived_table:
    sql: SELECT
  b.best_userid,
  b.first_touch,
  b.last_touch,
  b.number_of_events,
  b.distinct_pages_viewed,
  b.number_of_sessions,
  s.start_event_id AS first_event_id,
  s.mkt_medium,
  s.mkt_source,
  s.mkt_term,
  s.mkt_campaign,
  s.mkt_content,
  s.refr_urlhost,
  s.refr_urlpath,
  s.refr_medium,
  s.refr_source,
  s.refr_term,
  s.page_urlhost,
  s.page_urlpath,
  s.page_urlquery,
  s.page_urlfragment,
  s.app_id
FROM ${visitors_basic.SQL_TABLE_NAME} AS b
LEFT JOIN ${sessions.SQL_TABLE_NAME} AS s
ON b.best_userid = s.best_userid
WHERE s.session_index = 1
  fields:
  - dimension: user_id
    primary_key: true
    sql: ${TABLE}.best_userid
  - dimension: first_touch
    sql: ${TABLE}.first_touch
  - dimension: last_touch
    type: time
    sql: ${TABLE}.last_touch
  - dimension: number_of_events
    type: int
    sql: ${TABLE}.number_of_events
  - dimension: number_of_events_tiered
    type: tier
    sql: ${number_of_events}
  - dimension: bounce
    type: yesno
    sql: ${number_of_events} = 1
  - dimension: distinct_pages_viewed
    type: int
    sql: ${TABLE}.distinct_pages_viewed
  - dimension: distinct_pages_viewed_tiered
    type: tier
    sql: ${distinct_pages_viewed}
  - dimension: number_of_sessions
    type: int
    sql: ${TABLE}.number_of_sessions
  - dimension: number_of_sessions_tiered
    type: tier
    sql: ${number_of_sessions}
  - dimension: session_stream
    sql: ${user_id}
  - dimension: event_stream
    sql: ${user_id}
  - dimension: landing_page_url_host
    sql: ${TABLE}.page_urlhost
  - dimension: landing_page_url_path
    sql: ${TABLE}.page_urlpath
  - dimension: landing_page_url
    sql: ${TABLE}.page_urlhost || ${TABLE}.page_urlpath
  - dimension: referer_medium
  - dimension: referer_source
    sql: ${TABLE}.refr_source
  - dimension: referer_term
    sql: ${TABLE}.refr_term
  - dimension: referer_url_host
    sql: ${TABLE}.refr_urlhost
  - dimension: referer_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_group: first_touch
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_touch
  - measure: count
    type: count_distinct
    sql: ${user_id}
  - measure: bounced_visitor_count
    type: count_distinct
    sql: ${user_id}
  - measure: bounce_rate
    type: number
    sql: ${bounced_visitor_count}/NULLIF(${count},0)::REAL
  - measure: events_count
    type: sum
    sql: ${TABLE}.number_of_events
  - measure: events_per_visitor
    type: number
    sql: ${events_count}/NULLIF(${count},0)::REAL
  - measure: sessions_count
    type: sum
    sql: ${TABLE}.number_of_sessions
  - measure: sessions_per_visitor
    type: number
    sql: ${sessions_count}/NULLIF(${count},0)::REAL
  - measure: landing_page_count
    type: count_distinct
    sql: ${landing_page_url}
  - measure: campaign_medium_count
    type: count_distinct
    sql: ${campaign_medium}
  - measure: campaign_source_count
    type: count_distinct
    sql: ${campaign_source}
  - measure: campaign_term_count
    type: count_distinct
    sql: ${campaign_term}
  - measure: campaign_count
    type: count_distinct
    sql: ${campaign_name}
  - measure: referer_medium_count
    type: count_distinct
    sql: ${referer_medium}
  - measure: referer_source_count
    type: count_distinct
    sql: ${referer_source}
  - measure: referer_term_count
    type: count_distinct
    sql: ${referer_term}

Contents

  • visitors.sql
  • visitors.sql
  • visitors.view.lkml
  • visitors.view.lookml