data.ml
dbt Video editing

Company Events

Company Events model for video editing.

inferred

Blocks

company_events.sql · 98 lines
inferred 
-- models/company_events.sql
{{ config(materialized='table') }}

SELECT
domain_userid,
domain_sessionidx,
event_id,
event,
collector_tstamp,
dvce_tstamp,
page_title,
page_urlscheme,
page_urlhost,
page_urlpath,
page_urlport,
page_urlquery,
page_urlfragment,
refr_medium,
refr_source,
refr_term,
refr_urlhost,
refr_urlpath,
pp_xoffset_max,
pp_xoffset_min,
pp_yoffset_max,
pp_yoffset_min,
se_category,
se_action,
se_label,
se_property,
se_value,
doc_height,
doc_width,
tr_orderid,
tr_affiliation,
tr_total,
tr_tax,
tr_city,
tr_state,
tr_country,
ti_orderid,
ti_category,
ti_sku,
ti_name,
ti_price,
ti_quantity
FROM company_analytics.events
WHERE domain_userid IS NOT NULL

# models/schema.yml
version: 2

models:
  - name: company_events
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: event_type
      - name: timestamp
      - name: session_index
      - name: user_id
      - name: session_id
      - name: page_title
      - name: page_url_scheme
      - name: page_url_host
      - name: page_url_path
      - name: page_url_port
      - name: page_url_query
      - name: page_url_fragment
      - name: refr_medium
      - name: first_event_in_session
      - name: first_event_for_visitor
      - name: refr_source
      - name: refr_term
      - name: refr_url_host
      - name: refr_url_path
      - name: x_offset
      - name: pp_xoffset_min
      - name: y_offset
      - name: pp_yoffset_min
      - name: structured_event_action
      - name: structured_event_category
      - name: structured_event_label
      - name: structured_event_property
      - name: structured_event_value
      - name: user_agent
      - name: page_height
      - name: page_width
      - name: transaction_order_id
      - name: transaction_affiliation
      - name: transaction_value
      - name: transaction_tax
      - name: transaction_address_city
      - name: transaction_address_state
      - name: transaction_address_country
      - name: transaction_item_order_id

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.
company_events.sql · 45 lines
source-preserved validation: parses 
SELECT
domain_userid,
domain_sessionidx,
event_id,
event,
collector_tstamp,
dvce_tstamp,
page_title,
page_urlscheme,
page_urlhost,
page_urlpath,
page_urlport,
page_urlquery,
page_urlfragment,
refr_medium,
refr_source,
refr_term,
refr_urlhost,
refr_urlpath,
pp_xoffset_max,
pp_xoffset_min,
pp_yoffset_max,
pp_yoffset_min,
se_category,
se_action,
se_label,
se_property,
se_value,
doc_height,
doc_width,
tr_orderid,
tr_affiliation,
tr_total,
tr_tax,
tr_city,
tr_state,
tr_country,
ti_orderid,
ti_category,
ti_sku,
ti_name,
ti_price,
ti_quantity
FROM company_analytics.events
WHERE domain_userid IS NOT NULL
company_events.view.lkml · 377 lines
inferred 
view: company_events {
  derived_table: {
    sql: SELECT
domain_userid,
domain_sessionidx,
event_id,
event,
collector_tstamp,
dvce_tstamp,
page_title,
page_urlscheme,
page_urlhost,
page_urlpath,
page_urlport,
page_urlquery,
page_urlfragment,
refr_medium,
refr_source,
refr_term,
refr_urlhost,
refr_urlpath,
pp_xoffset_max,
pp_xoffset_min,
pp_yoffset_max,
pp_yoffset_min,
se_category,
se_action,
se_label,
se_property,
se_value,
doc_height,
doc_width,
tr_orderid,
tr_affiliation,
tr_total,
tr_tax,
tr_city,
tr_state,
tr_country,
ti_orderid,
ti_category,
ti_sku,
ti_name,
ti_price,
ti_quantity
FROM company_analytics.events
WHERE domain_userid IS NOT NULL ;;
    persist_for: "24 hours"
  }

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

  dimension: event_type {
    sql: ${TABLE}.event ;;
  }

  dimension: timestamp {
    sql: ${TABLE}.collector_tstamp ;;
  }

  dimension: session_index {
    type: number
    sql: ${TABLE}.domain_sessionidx ;;
  }

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

  dimension: session_id {
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx ;;
  }

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

  dimension: page_url_scheme {
    sql: ${TABLE}.page_urlscheme ;;
  }

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

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

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

  dimension: page_url_query {
    hidden: yes
    sql: ${TABLE}.page_urlquery ;;
  }

  dimension: page_url_fragment {
    hidden: yes
    sql: ${TABLE}.page_urlfragment ;;
  }

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

  dimension: first_event_in_session {
    type: yesno
    sql: ${TABLE}.refr_medium != 'internal' ;;
  }

  dimension: first_event_for_visitor {
    type: yesno
    sql: ${TABLE}.refr_medium != 'internal' AND ${TABLE}.domain_sessionidx = 1 ;;
  }

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

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

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

  dimension: refr_url_path {
    sql: ${TABLE}.refr_urlpath ;;
  }

  dimension: x_offset {
    type: int
    sql: ${TABLE}.pp_xoffset_max ;;
  }

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

  dimension: y_offset {
    type: int
    sql: ${TABLE}.pp_yoffset_max ;;
  }

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

  dimension: structured_event_action {
    sql: ${TABLE}.se_action ;;
  }

  dimension: structured_event_category {
    sql: ${TABLE}.se_category ;;
  }

  dimension: structured_event_label {
    sql: ${TABLE}.se_label ;;
  }

  dimension: structured_event_property {
    sql: ${TABLE}.se_property ;;
  }

  dimension: structured_event_value {
    type: number
    sql: ${TABLE}.se_value ;;
  }

  dimension: user_agent {
    hidden: yes
    sql: ${TABLE}.useragent ;;
  }

  dimension: page_height {
    type: int
    sql: ${TABLE}.doc_height ;;
  }

  dimension: page_width {
    type: int
    sql: ${TABLE}.doc_width ;;
  }

  dimension: transaction_order_id {
    sql: ${TABLE}.tr_orderid ;;
  }

  dimension: transaction_affiliation {
    sql: ${TABLE}.tr_affiliation ;;
  }

  dimension: transaction_value {
    type: number
    sql: ${TABLE}.tr_total ;;
  }

  dimension: transaction_tax {
    type: number
    sql: ${TABLE}.tr_tax ;;
  }

  dimension: transaction_address_city {
    sql: ${TABLE}.tr_city ;;
  }

  dimension: transaction_address_state {
    sql: ${TABLE}.tr_state ;;
  }

  dimension: transaction_address_country {
    sql: ${TABLE}.tr_country ;;
  }

  dimension: transaction_item_order_id {
    sql: ${TABLE}.ti_orderid ;;
  }

  dimension: transaction_item_category {
    sql: ${TABLE}.ti_category ;;
  }

  dimension: transaction_item_sku {
    sql: ${TABLE}.ti_sku ;;
  }

  dimension: transaction_item_name {
    sql: ${TABLE}.ti_name ;;
  }

  dimension: transaction_item_price {
    type: number
    sql: ${TABLE}.ti_price ;;
  }

  dimension: transaction_item_quantity {
    type: int
    sql: ${TABLE}.ti_quantity ;;
  }

  dimension: transaction_items_list {
    sql: ${transaction_order_id} ;;
  }

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

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

  measure: count {
    type: count
  }

  measure: page_pings_count {
    type: count
    filters: [event_type: "page_ping"]
  }

  measure: page_views_count {
    type: count
    filters: [event_type: "page_view"]
  }

  measure: transactions_count {
    type: count_distinct
    filters: [event_type: "transaction"]
    sql: ${transaction_order_id} ;;
  }

  measure: distinct_pages_viewed_count {
    type: count_distinct
    sql: ${page_url_path} ;;
  }

  measure: sessions_count {
    type: count_distinct
    sql: ${session_id} ;;
  }

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

  measure: page_pings_per_visitor {
    type: number
    sql: NULLIF(${page_pings_count},0)/NULLIF(${visitors_count},0)::REAL ;;
  }

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

  measure: transactions_per_visitor {
    type: number
    sql: NULLIF(${transactions_count},0)/NULLIF(${visitors_count},0)::REAL ;;
  }

  measure: visitors_count {
    type: count_distinct
    hidden: yes
    sql: ${user_id} ;;
  }

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

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

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

  measure: page_views_per_visitor {
    type: number
    sql: ${page_views_count}/NULLIF(${visitors_count},0)::REAL ;;
  }

  measure: landing_page_views_count {
    type: count
    filters: [event_type: "page_view"]
    filters: [first_event_in_session: "True"]
  }

  measure: internal_page_views_count {
    type: count
    filters: [event_type: "page_view"]
    filters: [first_event_in_session: "False"]
  }

  measure: approx_user_usage_in_minutes {
    type: number
    sql: APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPOCH FROM ${TABLE}.collector_tstamp)/10), ${TABLE}.domain_userid) ) /6 ;;
  }

  measure: approx_usage_per_visitor_in_seconds {
    type: number
    sql: ${approx_user_usage_in_minutes}/NULLIF(${visitors_count}, 0)::REAL ;;
  }

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

  measure: transactions_value {
    type: sum
    filters: [event_type: "transaction"]
    sql: ${transaction_value} ;;
  }
}
company_events.view.lookml · 251 lines
source-preserved 
- view: company_events
  derived_table:
    sql: SELECT
domain_userid,
domain_sessionidx,
event_id,
event,
collector_tstamp,
dvce_tstamp,
page_title,
page_urlscheme,
page_urlhost,
page_urlpath,
page_urlport,
page_urlquery,
page_urlfragment,
refr_medium,
refr_source,
refr_term,
refr_urlhost,
refr_urlpath,
pp_xoffset_max,
pp_xoffset_min,
pp_yoffset_max,
pp_yoffset_min,
se_category,
se_action,
se_label,
se_property,
se_value,
doc_height,
doc_width,
tr_orderid,
tr_affiliation,
tr_total,
tr_tax,
tr_city,
tr_state,
tr_country,
ti_orderid,
ti_category,
ti_sku,
ti_name,
ti_price,
ti_quantity
FROM company_analytics.events
WHERE domain_userid IS NOT NULL
    persist_for: 24 hours
  fields:
  - dimension: event_id
    primary_key: true
    sql: ${TABLE}.event_id
  - dimension: event_type
    sql: ${TABLE}.event
  - dimension: timestamp
    sql: ${TABLE}.collector_tstamp
  - dimension: session_index
    type: number
    sql: ${TABLE}.domain_sessionidx
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_id
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx
  - dimension: page_title
    sql: ${TABLE}.page_title
  - dimension: page_url_scheme
    sql: ${TABLE}.page_urlscheme
  - dimension: page_url_host
    sql: ${TABLE}.page_urlhost
  - dimension: page_url_path
    sql: ${TABLE}.page_urlpath
  - dimension: page_url_port
    type: int
    hidden: true
    sql: ${TABLE}.page_urlport
  - dimension: page_url_query
    hidden: true
    sql: ${TABLE}.page_urlquery
  - dimension: page_url_fragment
    hidden: true
    sql: ${TABLE}.page_urlfragment
  - dimension: refr_medium
    sql: ${TABLE}.refr_medium
  - dimension: first_event_in_session
    type: yesno
    sql: ${TABLE}.refr_medium != 'internal'
  - dimension: first_event_for_visitor
    type: yesno
    sql: ${TABLE}.refr_medium != 'internal' AND ${TABLE}.domain_sessionidx = 1
  - dimension: refr_source
    sql: ${TABLE}.refr_source
  - dimension: refr_term
    sql: ${TABLE}.refr_term
  - dimension: refr_url_host
    sql: ${TABLE}.refr_urlhost
  - dimension: refr_url_path
    sql: ${TABLE}.refr_urlpath
  - dimension: x_offset
    type: int
    sql: ${TABLE}.pp_xoffset_max
  - dimension: pp_xoffset_min
    type: int
    hidden: true
    sql: ${TABLE}.pp_xoffset_min
  - dimension: y_offset
    type: int
    sql: ${TABLE}.pp_yoffset_max
  - dimension: pp_yoffset_min
    type: int
    hidden: true
    sql: ${TABLE}.pp_yoffset_min
  - dimension: structured_event_action
    sql: ${TABLE}.se_action
  - dimension: structured_event_category
    sql: ${TABLE}.se_category
  - dimension: structured_event_label
    sql: ${TABLE}.se_label
  - dimension: structured_event_property
    sql: ${TABLE}.se_property
  - dimension: structured_event_value
    type: number
    sql: ${TABLE}.se_value
  - dimension: user_agent
    hidden: true
    sql: ${TABLE}.useragent
  - dimension: page_height
    type: int
    sql: ${TABLE}.doc_height
  - dimension: page_width
    type: int
    sql: ${TABLE}.doc_width
  - dimension: transaction_order_id
    sql: ${TABLE}.tr_orderid
  - dimension: transaction_affiliation
    sql: ${TABLE}.tr_affiliation
  - dimension: transaction_value
    type: number
    sql: ${TABLE}.tr_total
  - dimension: transaction_tax
    type: number
    sql: ${TABLE}.tr_tax
  - dimension: transaction_address_city
    sql: ${TABLE}.tr_city
  - dimension: transaction_address_state
    sql: ${TABLE}.tr_state
  - dimension: transaction_address_country
    sql: ${TABLE}.tr_country
  - dimension: transaction_item_order_id
    sql: ${TABLE}.ti_orderid
  - dimension: transaction_item_category
    sql: ${TABLE}.ti_category
  - dimension: transaction_item_sku
    sql: ${TABLE}.ti_sku
  - dimension: transaction_item_name
    sql: ${TABLE}.ti_name
  - dimension: transaction_item_price
    type: number
    sql: ${TABLE}.ti_price
  - dimension: transaction_item_quantity
    type: int
    sql: ${TABLE}.ti_quantity
  - dimension: transaction_items_list
    sql: ${transaction_order_id}
  - dimension_group: timestamp
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.collector_tstamp
  - dimension_group: device_timestamp
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.dvce_tstamp
  - measure: count
    type: count
  - measure: page_pings_count
    type: count
    filters:
    - event_type: page_ping
  - measure: page_views_count
    type: count
    filters:
    - event_type: page_view
  - measure: transactions_count
    type: count_distinct
    filters:
    - event_type: transaction
    sql: ${transaction_order_id}
  - measure: distinct_pages_viewed_count
    type: count_distinct
    sql: ${page_url_path}
  - measure: sessions_count
    type: count_distinct
    sql: ${session_id}
  - measure: page_pings_per_session
    type: number
    sql: NULLIF(${page_pings_count},0)/NULLIF(${sessions_count},0)::REAL
  - measure: page_pings_per_visitor
    type: number
    sql: NULLIF(${page_pings_count},0)/NULLIF(${visitors_count},0)::REAL
  - measure: transactions_per_session
    type: number
    sql: NULLIF(${transactions_count},0)/NULLIF(${sessions_count},0)::REAL
  - measure: transactions_per_visitor
    type: number
    sql: NULLIF(${transactions_count},0)/NULLIF(${visitors_count},0)::REAL
  - measure: visitors_count
    type: count_distinct
    sql: ${user_id}
  - measure: events_per_session
    type: number
    sql: ${count}/NULLIF(${sessions_count},0)::REAL
  - measure: events_per_visitor
    type: number
    sql: ${count}/NULLIF(${visitors_count},0)::REAL
  - measure: page_views_per_sessions
    type: number
    sql: ${page_views_count}/NULLIF(${sessions_count},0)::REAL
  - measure: page_views_per_visitor
    type: number
    sql: ${page_views_count}/NULLIF(${visitors_count},0)::REAL
  - measure: landing_page_views_count
    type: count
    filters:
    - event_type: page_view
    - first_event_in_session: True
  - measure: internal_page_views_count
    type: count
    filters:
    - event_type: page_view
    - first_event_in_session: False
  - measure: approx_user_usage_in_minutes
    type: number
    sql: APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPOCH FROM ${TABLE}.collector_tstamp)/10), ${TABLE}.domain_userid) ) /6
  - measure: approx_usage_per_visitor_in_seconds
    type: number
    sql: ${approx_user_usage_in_minutes}/NULLIF(${visitors_count}, 0)::REAL
  - measure: approx_usage_per_visit_in_seconds
    type: number
    sql: ${approx_user_usage_in_minutes}/NULLIF(${sessions_count}, 0)::REAL
  - measure: transactions_value
    type: sum
    filters:
    - event_type: transaction
    sql: ${transaction_value}

Contents

  • company_events.sql
  • company_events.sql
  • company_events.view.lkml
  • company_events.view.lookml