dbt Advertising automotive

Generic Organic Referer

Generic Organic Referer model for advertising automotive.

inferred

Blocks

dbt SQL LKML LookML

generic_organic_referer.sql · 42 lines

inferred

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

SELECT
app_id,
domain_userid,
domain_sessionidx,
refr_source,
refr_medium,
refr_urlhost
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(refr_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_source,
  FIRST_VALUE(refr_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_medium,
  FIRST_VALUE(refr_urlhost)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_urlhost
  FROM atomic.events
  WHERE refr_medium <> 'internal'
) t
GROUP BY 1,2,3,4,5,6

# models/schema.yml
version: 2

models:
  - name: generic_organic_referer
    columns:
      - name: application_id
      - name: user_id
      - name: session_index
      - name: referer_source
      - name: referer_medium
      - name: referer_host

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.

generic_organic_referer.sql · 26 lines

source-preserved validation: parses

SELECT
app_id,
domain_userid,
domain_sessionidx,
refr_source,
refr_medium,
refr_urlhost
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(refr_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_source,
  FIRST_VALUE(refr_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_medium,
  FIRST_VALUE(refr_urlhost)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_urlhost
  FROM atomic.events
  WHERE refr_medium <> 'internal'
) t
GROUP BY 1,2,3,4,5,6

generic_organic_referer.view.lkml · 58 lines

inferred

view: generic_organic_referer {
  derived_table: {
    sql: SELECT
app_id,
domain_userid,
domain_sessionidx,
refr_source,
refr_medium,
refr_urlhost
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(refr_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_source,
  FIRST_VALUE(refr_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_medium,
  FIRST_VALUE(refr_urlhost)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_urlhost
  FROM atomic.events
  WHERE refr_medium <> 'internal'
) t
GROUP BY 1,2,3,4,5,6 ;;
  }

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

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

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

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

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

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

  measure: count {
    type: count
  }
}

generic_organic_referer.view.lookml · 43 lines

source-preserved

- view: generic_organic_referer
  derived_table:
    sql: SELECT
app_id,
domain_userid,
domain_sessionidx,
refr_source,
refr_medium,
refr_urlhost
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(refr_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_source,
  FIRST_VALUE(refr_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_medium,
  FIRST_VALUE(refr_urlhost)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS refr_urlhost
  FROM atomic.events
  WHERE refr_medium <> 'internal'
) t
GROUP BY 1,2,3,4,5,6
  fields:
  - dimension: application_id
    sql: ${TABLE}.app_id
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_index
    sql: ${TABLE}.domain_sessionidx
  - dimension: referer_source
    sql: ${TABLE}.refr_source
  - dimension: referer_medium
    sql: ${TABLE}.refr_medium
  - dimension: referer_host
    sql: ${TABLE}.refr_urlhost
  - measure: count
    type: count

generic_organic_referer.sql
generic_organic_referer.sql
generic_organic_referer.view.lkml
generic_organic_referer.view.lookml

Generic Organic Referer

Blocks

Assumptions

Contents