dbt Media publishing

Visit New

Visit New model for media publishing.

inferred

Blocks

dbt SQL LKML LookML

visit_new.sql · 82 lines

inferred

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

SELECT
  visit.*,
  visit_channel.app_version AS "app_version",
  visit_channel.channel AS "channel",
  visit_channel.first_channel  AS "first_channel",
  visit_channel.first_desktop  AS "first_desktop",
  visit_channel.first_html5 AS "first_html5",
  visit_channel.first_html5_android AS "first_html5_android",
  visit_channel.first_html5_ios AS "first_html5_ios",
  visit_channel.first_ipad AS "first_ipad",
  visit_channel.first_iphone AS "first_iphone",
  visit_channel.manufacturer AS "manufacturer",
  visit_channel.model AS "visit_channel.model",
  visit_general.attraction_type1 AS "attraction_type1",
  visit_general.attraction_type2 AS "attraction_type2",
  visit_general.cohort AS "cohort",
  visit_general.first_sub_type AS "first_sub_type",
  visit_general.last_sub_type AS "last_sub_type",
  visit_general.sub_cohort AS "sub_cohort",
  visit_general.used_guest_pass AS "used_guest_pass",
  visit_geo.country_name,
  visit_geo.region,
  visit_geo.geo_source
FROM sanitized_db.si_cs_visit_recent AS visit
LEFT JOIN sanitized_db.sanitized_visit_channel AS visit_channel ON visit.visit_id = visit_channel.visit_id
LEFT JOIN sanitized_db.sanitized_visit_general AS visit_general ON visit.visit_id = visit_general.visit_id
LEFT JOIN sanitized_db.sanitized_merged_geoip AS visit_geo ON visit.visit_id = visit_geo.visit_id
WHERE
  ((visit.start_time) >= (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ))) AND (visit.start_time) < (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,91, DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ) ))))

# models/schema.yml
version: 2

models:
  - name: visit_new
    columns:
      - name: visit_id
        tests:
          - unique
          - not_null
      - name: parent_visitor_id
      - name: first_request
      - name: last_request
      - name: referral_url
      - name: site_visited
      - name: visit_index
      - name: is_first_visit
      - name: days_since_first_visit
      - name: days_since_prev_visit
      - name: visit_duration
      - name: num_requests
      - name: num_pages
      - name: unique_pages
      - name: match_type
      - name: is_abce_visit
      - name: is_abce_session
      - name: has_expired
      - name: is_first_site_visit
      - name: is_flirt
      - name: visit_value
      - name: visit_frequency
      - name: visit_date
      - name: app_version
      - name: channel
      - name: is_first_channel
      - name: is_first_desktop
      - name: is_first_html5
      - name: is_first_html5_android
      - name: is_first_html5_ios
      - name: is_first_ipad
      - name: is_first_iphone
      - name: manufacturer
      - name: model
      - name: attraction_type1
      - name: attraction_type2
      - name: first_sub_type
      - name: last_sub_type
      - name: sub_cohort
      - name: used_guest_pass

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.

visit_new.sql · 29 lines

source-preserved validation: parses

SELECT
  visit.*,
  visit_channel.app_version AS "app_version",
  visit_channel.channel AS "channel",
  visit_channel.first_channel  AS "first_channel",
  visit_channel.first_desktop  AS "first_desktop",
  visit_channel.first_html5 AS "first_html5",
  visit_channel.first_html5_android AS "first_html5_android",
  visit_channel.first_html5_ios AS "first_html5_ios",
  visit_channel.first_ipad AS "first_ipad",
  visit_channel.first_iphone AS "first_iphone",
  visit_channel.manufacturer AS "manufacturer",
  visit_channel.model AS "visit_channel.model",
  visit_general.attraction_type1 AS "attraction_type1",
  visit_general.attraction_type2 AS "attraction_type2",
  visit_general.cohort AS "cohort",
  visit_general.first_sub_type AS "first_sub_type",
  visit_general.last_sub_type AS "last_sub_type",
  visit_general.sub_cohort AS "sub_cohort",
  visit_general.used_guest_pass AS "used_guest_pass",
  visit_geo.country_name,
  visit_geo.region,
  visit_geo.geo_source
FROM sanitized_db.si_cs_visit_recent AS visit
LEFT JOIN sanitized_db.sanitized_visit_channel AS visit_channel ON visit.visit_id = visit_channel.visit_id
LEFT JOIN sanitized_db.sanitized_visit_general AS visit_general ON visit.visit_id = visit_general.visit_id
LEFT JOIN sanitized_db.sanitized_merged_geoip AS visit_geo ON visit.visit_id = visit_geo.visit_id
WHERE
  ((visit.start_time) >= (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ))) AND (visit.start_time) < (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,91, DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ) ))))

visit_new.view.lkml · 283 lines

inferred

view: visit_new {
  derived_table: {
    sql: SELECT
  visit.*,
  visit_channel.app_version AS "app_version",
  visit_channel.channel AS "channel",
  visit_channel.first_channel  AS "first_channel",
  visit_channel.first_desktop  AS "first_desktop",
  visit_channel.first_html5 AS "first_html5",
  visit_channel.first_html5_android AS "first_html5_android",
  visit_channel.first_html5_ios AS "first_html5_ios",
  visit_channel.first_ipad AS "first_ipad",
  visit_channel.first_iphone AS "first_iphone",
  visit_channel.manufacturer AS "manufacturer",
  visit_channel.model AS "visit_channel.model",
  visit_general.attraction_type1 AS "attraction_type1",
  visit_general.attraction_type2 AS "attraction_type2",
  visit_general.cohort AS "cohort",
  visit_general.first_sub_type AS "first_sub_type",
  visit_general.last_sub_type AS "last_sub_type",
  visit_general.sub_cohort AS "sub_cohort",
  visit_general.used_guest_pass AS "used_guest_pass",
  visit_geo.country_name,
  visit_geo.region,
  visit_geo.geo_source
FROM sanitized_db.si_cs_visit_recent AS visit
LEFT JOIN sanitized_db.sanitized_visit_channel AS visit_channel ON visit.visit_id = visit_channel.visit_id
LEFT JOIN sanitized_db.sanitized_visit_general AS visit_general ON visit.visit_id = visit_general.visit_id
LEFT JOIN sanitized_db.sanitized_merged_geoip AS visit_geo ON visit.visit_id = visit_geo.visit_id
WHERE
  ((visit.start_time) >= (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ))) AND (visit.start_time) < (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,91, DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ) )))) ;;
  }

  dimension: visit_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.visit_id ;;
  }

  dimension: parent_visitor_id {
    type: int
    sql: ${TABLE}.parent_visitor ;;
  }

  dimension: first_request {
    type: int
    hidden: yes
    sql: ${TABLE}.first_request ;;
  }

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

  dimension: referral_url {
    type: int
    hidden: yes
    sql: ${TABLE}.referral_url ;;
  }

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

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

  dimension: is_first_visit {
    type: yesno
    sql: ${TABLE}.is_first_visit ;;
  }

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

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

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

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

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

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

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

  dimension: is_abce_visit {
    hidden: yes
    sql: ${TABLE}.is_abce_visit ;;
  }

  dimension: is_abce_session {
    hidden: yes
    sql: ${TABLE}.is_abce_session ;;
  }

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

  dimension: is_first_site_visit {
    type: yesno
    sql: ${TABLE}.is_first_site_visit ;;
  }

  dimension: is_flirt {
    hidden: yes
    sql: ${TABLE}.is_flirt ;;
  }

  dimension: visit_value {
    type: int
    hidden: yes
    sql: ${TABLE}.visit_value ;;
  }

  dimension: visit_frequency {
    type: number
    hidden: yes
    sql: ${TABLE}.visit_frequency ;;
  }

  dimension: visit_date {
    type: number
    hidden: yes
    sql: ${TABLE}.visit_date ;;
  }

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

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

  dimension: is_first_channel {
    type: yesno
    sql: ${TABLE}.first_channel ;;
  }

  dimension: is_first_desktop {
    type: yesno
    sql: ${TABLE}.first_desktop ;;
  }

  dimension: is_first_html5 {
    type: yesno
    sql: ${TABLE}.first_html5 ;;
  }

  dimension: is_first_html5_android {
    type: yesno
    sql: ${TABLE}.first_html5_android ;;
  }

  dimension: is_first_html5_ios {
    type: yesno
    sql: ${TABLE}.first_html5_ios ;;
  }

  dimension: is_first_ipad {
    type: yesno
    sql: ${TABLE}.first_ipad ;;
  }

  dimension: is_first_iphone {
    sql: ${TABLE}.first_iphone ;;
  }

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

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

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

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

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

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

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

  dimension: used_guest_pass {
    type: yesno
    sql: ${TABLE}.used_guest_pass ;;
  }

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

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

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

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

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

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

  measure: count_ln {
    type: number
    drill_fields: [count]
    sql: ln(${count}) ;;
  }

  measure: first_visit_count {
    type: count
    filters: [is_first_site_visit: "True"]
    drill_fields: [count]
  }

  measure: expired_count {
    type: count
    filters: [has_expired: "True"]
    drill_fields: [count]
  }

  measure: percent_first_visit {
    type: number
    value_format: "0.00\%"
    drill_fields: [count]
    sql: 100.0 * ${first_visit_count} / ${count} ;;
  }
}

visit_new.view.lookml · 197 lines

source-preserved

- view: visit_new
  derived_table:
    sql: SELECT
  visit.*,
  visit_channel.app_version AS "app_version",
  visit_channel.channel AS "channel",
  visit_channel.first_channel  AS "first_channel",
  visit_channel.first_desktop  AS "first_desktop",
  visit_channel.first_html5 AS "first_html5",
  visit_channel.first_html5_android AS "first_html5_android",
  visit_channel.first_html5_ios AS "first_html5_ios",
  visit_channel.first_ipad AS "first_ipad",
  visit_channel.first_iphone AS "first_iphone",
  visit_channel.manufacturer AS "manufacturer",
  visit_channel.model AS "visit_channel.model",
  visit_general.attraction_type1 AS "attraction_type1",
  visit_general.attraction_type2 AS "attraction_type2",
  visit_general.cohort AS "cohort",
  visit_general.first_sub_type AS "first_sub_type",
  visit_general.last_sub_type AS "last_sub_type",
  visit_general.sub_cohort AS "sub_cohort",
  visit_general.used_guest_pass AS "used_guest_pass",
  visit_geo.country_name,
  visit_geo.region,
  visit_geo.geo_source
FROM sanitized_db.si_cs_visit_recent AS visit
LEFT JOIN sanitized_db.sanitized_visit_channel AS visit_channel ON visit.visit_id = visit_channel.visit_id
LEFT JOIN sanitized_db.sanitized_visit_general AS visit_general ON visit.visit_id = visit_general.visit_id
LEFT JOIN sanitized_db.sanitized_merged_geoip AS visit_geo ON visit.visit_id = visit_geo.visit_id
WHERE
  ((visit.start_time) >= (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ))) AND (visit.start_time) < (CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(day,91, DATEADD(day,-90, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())) ) ))))
  fields:
  - dimension: visit_id
    type: int
    primary_key: true
    sql: ${TABLE}.visit_id
  - dimension: parent_visitor_id
    type: int
    sql: ${TABLE}.parent_visitor
  - dimension: first_request
    type: int
    hidden: true
    sql: ${TABLE}.first_request
  - dimension: last_request
    type: int
    sql: ${TABLE}.last_request
  - dimension: referral_url
    type: int
    hidden: true
    sql: ${TABLE}.referral_url
  - dimension: site_visited
    sql: ${TABLE}.site_visited
  - dimension: visit_index
    type: number
    sql: ${TABLE}.visit_index
  - dimension: is_first_visit
    type: yesno
    sql: ${TABLE}.is_first_visit
  - dimension: days_since_first_visit
    type: number
    sql: ${TABLE}.days_since_first_visit
  - dimension: days_since_prev_visit
    type: number
    sql: ${TABLE}.days_since_prev_visit
  - dimension: visit_duration
    type: number
    sql: ${TABLE}.visit_duration
  - dimension: num_requests
    type: number
    sql: ${TABLE}.num_requests
  - dimension: num_pages
    type: number
    sql: ${TABLE}.num_pages
  - dimension: unique_pages
    type: number
    sql: ${TABLE}.unique_pages
  - dimension: match_type
    sql: ${TABLE}.match_type
  - dimension: is_abce_visit
    hidden: true
    sql: ${TABLE}.is_abce_visit
  - dimension: is_abce_session
    hidden: true
    sql: ${TABLE}.is_abce_session
  - dimension: has_expired
    sql: ${TABLE}.has_expired
  - dimension: is_first_site_visit
    type: yesno
    sql: ${TABLE}.is_first_site_visit
  - dimension: is_flirt
    hidden: true
    sql: ${TABLE}.is_flirt
  - dimension: visit_value
    type: int
    hidden: true
    sql: ${TABLE}.visit_value
  - dimension: visit_frequency
    type: number
    hidden: true
    sql: ${TABLE}.visit_frequency
  - dimension: visit_date
    type: number
    hidden: true
    sql: ${TABLE}.visit_date
  - dimension: app_version
    sql: ${TABLE}.app_version
  - dimension: channel
    sql: ${TABLE}.channel
  - dimension: is_first_channel
    type: yesno
    sql: ${TABLE}.first_channel
  - dimension: is_first_desktop
    type: yesno
    sql: ${TABLE}.first_desktop
  - dimension: is_first_html5
    type: yesno
    sql: ${TABLE}.first_html5
  - dimension: is_first_html5_android
    type: yesno
    sql: ${TABLE}.first_html5_android
  - dimension: is_first_html5_ios
    type: yesno
    sql: ${TABLE}.first_html5_ios
  - dimension: is_first_ipad
    type: yesno
    sql: ${TABLE}.first_ipad
  - dimension: is_first_iphone
    sql: ${TABLE}.first_iphone
  - dimension: manufacturer
    sql: ${TABLE}.manufacturer
  - dimension: model
    sql: ${TABLE}.model
  - dimension: attraction_type1
    sql: ${TABLE}.attraction_type1
  - dimension: attraction_type2
    sql: ${TABLE}.attraction_type2
  - dimension: first_sub_type
    type: number
    sql: ${TABLE}.first_sub_type
  - dimension: last_sub_type
    type: number
    sql: ${TABLE}.last_sub_type
  - dimension: sub_cohort
    type: number
    sql: ${TABLE}.sub_cohort
  - dimension: used_guest_pass
    type: yesno
    sql: ${TABLE}.used_guest_pass
  - dimension: country_name
    sql: ${TABLE}.country_name
  - dimension: region
    sql: ${TABLE}.region
  - dimension: geo_source
    sql: ${TABLE}.geo_source
  - dimension_group: start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.start_time
  - dimension_group: local_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.local_time
  - measure: count
    type: count
    drill_fields:
    - count
  - measure: count_ln
    type: number
    drill_fields:
    - count
    sql: ln(${count})
  - measure: first_visit_count
    type: count
    drill_fields:
    - count
    filters:
    - is_first_site_visit: True
  - measure: expired_count
    type: count
    drill_fields:
    - count
    filters:
    - has_expired: True
  - measure: percent_first_visit
    type: number
    value_format: '0.00\%'
    drill_fields:
    - count
    sql: 100.0 * ${first_visit_count} / ${count}

visit_new.sql
visit_new.sql
visit_new.view.lkml
visit_new.view.lookml

Visit New

Blocks

Assumptions

Contents