SQL Insurance vehicle

Visitors

Visitors model for insurance vehicle.

source-preserved

Blocks

dbt SQL LKML LookML

visitors.sql · 77 lines

inferred

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

SELECT
  v.domain_userid,
  v.first_touch,
  v.last_touch,
  v.number_of_events,
  v.distinct_pages_viewed,
  v.number_of_sessions,
  l.page_urlhost,
  l.page_urlpath,
  l.page_title,
  s.mkt_source,
  s.mkt_medium,
  s.mkt_campaign,
  s.mkt_term,
  s.refr_source,
  s.refr_medium,
  s.refr_term,
  s.refr_urlhost,
  s.refr_urlpath,
  c.mkt_medium AS first_campaign_touch_medium,
  c.mkt_source AS first_campaign_touch_source,
  c.mkt_term AS first_campaign_touch_term,
  c.mkt_campaign AS first_campaign_touch_campaign,
  c.mkt_content AS first_campaign_touch_content
FROM
  {{ ref('visitors_basic') }} AS v
  LEFT JOIN {{ ref('landing_page_sessions') }} AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN {{ ref('source_sessions') }} AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
  LEFT JOIN {{ ref('campaign_first_touches') }} AS c
  ON v.domain_userid = c.domain_userid

# models/schema.yml
version: 2

models:
  - name: visitors
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: first_touch
      - name: last_touch
      - name: events_during_lifetime
      - name: events_during_lifetime_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_title
      - 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
      - name: first_campaign_touch_medium
      - name: first_campaign_touch_source
      - name: first_campaign_touch_term
      - name: first_campaign_touch_campaign
      - name: first_campaign_touch_content

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 · 34 lines

source-preserved validation: not checked

SELECT
  v.domain_userid,
  v.first_touch,
  v.last_touch,
  v.number_of_events,
  v.distinct_pages_viewed,
  v.number_of_sessions,
  l.page_urlhost,
  l.page_urlpath,
  l.page_title,
  s.mkt_source,
  s.mkt_medium,
  s.mkt_campaign,
  s.mkt_term,
  s.refr_source,
  s.refr_medium,
  s.refr_term,
  s.refr_urlhost,
  s.refr_urlpath,
  c.mkt_medium AS first_campaign_touch_medium,
  c.mkt_source AS first_campaign_touch_source,
  c.mkt_term AS first_campaign_touch_term,
  c.mkt_campaign AS first_campaign_touch_campaign,
  c.mkt_content AS first_campaign_touch_content
FROM
  ${visitors_basic.SQL_TABLE_NAME} AS v
  LEFT JOIN ${landing_page_sessions.SQL_TABLE_NAME} AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${source_sessions.SQL_TABLE_NAME} AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
  LEFT JOIN ${campaign_first_touches.SQL_TABLE_NAME} AS c
  ON v.domain_userid = c.domain_userid

visitors.view.lkml · 247 lines

inferred

view: visitors {
  derived_table: {
    sql: SELECT
  v.domain_userid,
  v.first_touch,
  v.last_touch,
  v.number_of_events,
  v.distinct_pages_viewed,
  v.number_of_sessions,
  l.page_urlhost,
  l.page_urlpath,
  l.page_title,
  s.mkt_source,
  s.mkt_medium,
  s.mkt_campaign,
  s.mkt_term,
  s.refr_source,
  s.refr_medium,
  s.refr_term,
  s.refr_urlhost,
  s.refr_urlpath,
  c.mkt_medium AS first_campaign_touch_medium,
  c.mkt_source AS first_campaign_touch_source,
  c.mkt_term AS first_campaign_touch_term,
  c.mkt_campaign AS first_campaign_touch_campaign,
  c.mkt_content AS first_campaign_touch_content
FROM
  ${visitors_basic.SQL_TABLE_NAME} AS v
  LEFT JOIN ${landing_page_sessions.SQL_TABLE_NAME} AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${source_sessions.SQL_TABLE_NAME} AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
  LEFT JOIN ${campaign_first_touches.SQL_TABLE_NAME} AS c
  ON v.domain_userid = c.domain_userid ;;
  }

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

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

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

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

  dimension: events_during_lifetime_tiered {
    type: tier
    sql: ${TABLE}.number_of_events ;;
  }

  dimension: bounce {
    type: yesno
    sql: ${TABLE}.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_title {
    sql: ${TABLE}.page_title ;;
  }

  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: first_campaign_touch_medium {
    sql: ${TABLE}.first_campaign_touch_medium ;;
  }

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

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

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

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

  dimension_group: first_touch {
    type: time
    timeframes: [time, hour, 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 · 159 lines

source-preserved

- view: visitors
  derived_table:
    sql: SELECT
  v.domain_userid,
  v.first_touch,
  v.last_touch,
  v.number_of_events,
  v.distinct_pages_viewed,
  v.number_of_sessions,
  l.page_urlhost,
  l.page_urlpath,
  l.page_title,
  s.mkt_source,
  s.mkt_medium,
  s.mkt_campaign,
  s.mkt_term,
  s.refr_source,
  s.refr_medium,
  s.refr_term,
  s.refr_urlhost,
  s.refr_urlpath,
  c.mkt_medium AS first_campaign_touch_medium,
  c.mkt_source AS first_campaign_touch_source,
  c.mkt_term AS first_campaign_touch_term,
  c.mkt_campaign AS first_campaign_touch_campaign,
  c.mkt_content AS first_campaign_touch_content
FROM
  ${visitors_basic.SQL_TABLE_NAME} AS v
  LEFT JOIN ${landing_page_sessions.SQL_TABLE_NAME} AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${source_sessions.SQL_TABLE_NAME} AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
  LEFT JOIN ${campaign_first_touches.SQL_TABLE_NAME} AS c
  ON v.domain_userid = c.domain_userid
  fields:
  - dimension: user_id
    primary_key: true
    sql: ${TABLE}.domain_userid
  - dimension: first_touch
    sql: ${TABLE}.first_touch
  - dimension: last_touch
    type: time
    sql: ${TABLE}.last_touch
  - dimension: events_during_lifetime
    type: int
    sql: ${TABLE}.number_of_events
  - dimension: events_during_lifetime_tiered
    type: tier
    sql: ${TABLE}.number_of_events
  - dimension: bounce
    type: yesno
    sql: ${TABLE}.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_title
    sql: ${TABLE}.page_title
  - 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: first_campaign_touch_medium
    sql: ${TABLE}.first_campaign_touch_medium
  - dimension: first_campaign_touch_source
    sql: ${TABLE}.first_campaign_touch_source
  - dimension: first_campaign_touch_term
    sql: ${TABLE}.first_campaign_touch_term
  - dimension: first_campaign_touch_campaign
    sql: ${TABLE}.first_campaign_touch_campaign
  - dimension: first_campaign_touch_content
    sql: ${TABLE}.first_campaign_touch_content
  - dimension_group: first_touch
    type: time
    timeframes:
    - time
    - hour
    - 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.sql
visitors.sql
visitors.view.lkml
visitors.view.lookml

Visitors

Blocks

Assumptions

Contents