data.ml
dbt Dating platform

Visitors

Visitors model for dating platform.

inferred

Blocks

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

SELECT
  v.app_id,
  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,
  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
FROM
  {{ this }}.visitors_basic AS v
  LEFT JOIN {{ this }}.sessions_landing_page AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN {{ this }}.sessions_source AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1

# models/schema.yml
version: 2

models:
  - name: visitors
    columns:
      - name: app_id
      - name: user_id
      - 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_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

  • 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
  v.app_id,
  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,
  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
FROM
  ${TABLE}.visitors_basic AS v
  LEFT JOIN ${TABLE}.sessions_landing_page AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${TABLE}.sessions_source AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
visitors.view.lkml · 219 lines
source-preserved 
view: visitors {
  derived_table: {
    sql: SELECT
  v.app_id,
  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,
  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
FROM
  ${TABLE}.visitors_basic AS v
  LEFT JOIN ${TABLE}.sessions_landing_page AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${TABLE}.sessions_source AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1 ;;
  }

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

  dimension: user_id {
    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_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 · 140 lines
inferred 
- view: visitors
  derived_table:
    sql: SELECT
  v.app_id,
  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,
  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
FROM
  ${TABLE}.visitors_basic AS v
  LEFT JOIN ${TABLE}.sessions_landing_page AS l
  ON v.domain_userid = l.domain_userid
  AND l.domain_sessionidx = 1
  LEFT JOIN ${TABLE}.sessions_source AS s
  ON v.domain_userid = s.domain_userid
  AND s.domain_sessionidx = 1
  fields:
  - dimension: app_id
    sql: ${TABLE}.app_id
  - dimension: user_id
    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_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