dbt Marketing digital

Event Duration

Event Duration model for marketing digital.

inferred

Blocks

dbt SQL LKML LookML

event_duration.sql · 41 lines

inferred

-- models/event_duration.sql
{{ config(materialized='table') }}

SELECT
  event_id,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  page_urlhost,
  page_urlpath,
  page_urlfragment,
  LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp ) AS event_endts,
  EXTRACT(EPOCH FROM (
    (LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp)) - dvce_tstamp
  )) duration_s
FROM
  ATOMIC.events

# models/schema.yml
version: 2

models:
  - name: event_duration
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: visit_id
      - name: page_urlhost
      - name: page_urlpath
      - name: page_urlfragment
      - name: event_end
      - name: event_duration_seconds
      - name: page_type_model
      - name: page_type_up
      - name: page_type_golf
      - name: page_type_polo
      - name: page_type_beetle
      - name: page_type_jetta
      - name: page_type_passat
      - name: page_type_touran
      - name: page_type_tiguan

Assumptions

materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

event_duration.sql · 12 lines

source-preserved validation: parses

SELECT
  event_id,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  page_urlhost,
  page_urlpath,
  page_urlfragment,
  LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp ) AS event_endts,
  EXTRACT(EPOCH FROM (
    (LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp)) - dvce_tstamp
  )) duration_s
FROM
  ATOMIC.events

event_duration.view.lkml · 191 lines

inferred

view: event_duration {
  derived_table: {
    sql: SELECT
  event_id,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  page_urlhost,
  page_urlpath,
  page_urlfragment,
  LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp ) AS event_endts,
  EXTRACT(EPOCH FROM (
    (LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp)) - dvce_tstamp
  )) duration_s
FROM
  ATOMIC.events ;;
    persist_for: "6 hour"
  }

  dimension: event_id {
    primary_key: yes
    sql: ${TABLE}.event_id ;;
  }

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

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

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

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

  dimension: event_end {
    hidden: yes
    sql: ${TABLE}.event_endts ;;
  }

  dimension: event_duration_seconds {
    sql: ${TABLE}.duration_s ;;
  }

  dimension: page_type_model {
    type: yesno
    sql: ${page_urlpath} LIKE '%models%' ;;
  }

  dimension: page_type_up {
    type: yesno
    sql: ${page_urlpath} LIKE '%up-%' ;;
  }

  dimension: page_type_golf {
    type: yesno
    sql: ${page_urlpath} LIKE '%golf%' ;;
  }

  dimension: page_type_polo {
    type: yesno
    sql: ${page_urlpath} LIKE '%polo%' ;;
  }

  dimension: page_type_beetle {
    type: yesno
    sql: ${page_urlpath} LIKE '%beetle%' ;;
  }

  dimension: page_type_jetta {
    type: yesno
    sql: ${page_urlpath} LIKE '%jetta%' ;;
  }

  dimension: page_type_passat {
    type: yesno
    sql: ${page_urlpath} LIKE '%passat%' ;;
  }

  dimension: page_type_touran {
    type: yesno
    sql: ${page_urlpath} LIKE '%touran%' ;;
  }

  dimension: page_type_tiguan {
    type: yesno
    sql: ${page_urlpath} LIKE '%tiguan%' ;;
  }

  measure: visits_count {
    type: count_distinct
    hidden: yes
    sql: ${TABLE}.visit_id ;;
  }

  measure: total_duration_s {
    type: sum
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_model_page {
    type: sum
    filters: [page_type_model: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_model_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_golf_page {
    type: sum
    filters: [page_type_golf: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_golf_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_up_page {
    type: sum
    filters: [page_type_up: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_up_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_polo_page {
    type: sum
    filters: [page_type_polo: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_polo_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_beetle_page {
    type: sum
    filters: [page_type_beetle: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_beetle_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_jetta_page {
    type: sum
    filters: [page_type_jetta: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_jetta_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_passat_page {
    type: sum
    filters: [page_type_passat: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_vehicle_page_per_visit {
    type: number
    sql: ${seconds_on_passat_page} / NULLIF(${visits_count},0)::REAL ;;
  }

  measure: seconds_on_touran_page {
    type: sum
    filters: [page_type_touran: "True"]
    sql: ${event_duration_seconds} ;;
  }

  measure: seconds_on_touran_page_per_visit {
    type: sum
    sql: ${seconds_on_touran_page} / NULLIF(${visits_count},0)::REAL ;;
  }
}

event_duration.view.lookml · 129 lines

source-preserved

- view: event_duration
  derived_table:
    sql: SELECT
  event_id,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  page_urlhost,
  page_urlpath,
  page_urlfragment,
  LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp ) AS event_endts,
  EXTRACT(EPOCH FROM (
    (LEAD (dvce_tstamp) OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp)) - dvce_tstamp
  )) duration_s
FROM
  ATOMIC.events
    persist_for: 6 hour
  fields:
  - dimension: event_id
    primary_key: true
    sql: ${TABLE}.event_id
  - dimension: visit_id
    sql: ${TABLE}.visit_id
  - dimension: page_urlhost
    sql: ${TABLE}.page_urlhost
  - dimension: page_urlpath
    sql: ${TABLE}.page_urlpath
  - dimension: page_urlfragment
    sql: ${TABLE}.page_urlfragment
  - dimension: event_end
    hidden: true
    sql: ${TABLE}.event_endts
  - dimension: event_duration_seconds
    sql: ${TABLE}.duration_s
  - dimension: page_type_model
    type: yesno
    sql: ${page_urlpath} LIKE '%models%'
  - dimension: page_type_up
    type: yesno
    sql: ${page_urlpath} LIKE '%up-%'
  - dimension: page_type_golf
    type: yesno
    sql: ${page_urlpath} LIKE '%golf%'
  - dimension: page_type_polo
    type: yesno
    sql: ${page_urlpath} LIKE '%polo%'
  - dimension: page_type_beetle
    type: yesno
    sql: ${page_urlpath} LIKE '%beetle%'
  - dimension: page_type_jetta
    type: yesno
    sql: ${page_urlpath} LIKE '%jetta%'
  - dimension: page_type_passat
    type: yesno
    sql: ${page_urlpath} LIKE '%passat%'
  - dimension: page_type_touran
    type: yesno
    sql: ${page_urlpath} LIKE '%touran%'
  - dimension: page_type_tiguan
    type: yesno
    sql: ${page_urlpath} LIKE '%tiguan%'
  - measure: visits_count
    type: count_distinct
    sql: ${TABLE}.visit_id
  - measure: total_duration_s
    type: sum
    sql: ${event_duration_seconds}
  - measure: seconds_on_model_page
    type: sum
    filters:
    - page_type_model: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_model_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_golf_page
    type: sum
    filters:
    - page_type_golf: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_golf_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_up_page
    type: sum
    filters:
    - page_type_up: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_up_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_polo_page
    type: sum
    filters:
    - page_type_polo: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_polo_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_beetle_page
    type: sum
    filters:
    - page_type_beetle: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_beetle_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_jetta_page
    type: sum
    filters:
    - page_type_jetta: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_jetta_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_passat_page
    type: sum
    filters:
    - page_type_passat: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_vehicle_page_per_visit
    type: number
    sql: ${seconds_on_passat_page} / NULLIF(${visits_count},0)::REAL
  - measure: seconds_on_touran_page
    type: sum
    filters:
    - page_type_touran: True
    sql: ${event_duration_seconds}
  - measure: seconds_on_touran_page_per_visit
    type: sum
    sql: ${seconds_on_touran_page} / NULLIF(${visits_count},0)::REAL

event_duration.sql
event_duration.sql
event_duration.view.lkml
event_duration.view.lookml

Event Duration

Blocks

Assumptions

Contents