data.ml
SQL Advertising automotive

Paid Campaign List Calc

Paid Campaign List Calc model for advertising automotive.

source-preserved

Blocks

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

SELECT
app_id,
domain_userid,
domain_sessionidx,
mkt_source,
mkt_medium,
mkt_campaign
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(mkt_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_source,
  FIRST_VALUE(mkt_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_medium,
  FIRST_VALUE(mkt_campaign)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_campaign
  FROM atomic.events
  WHERE mkt_source IS NOT NULL
) t
GROUP BY 1,2,3,4,5,6

# models/schema.yml
version: 2

models:
  - name: paid_campaign_list_calc
    columns:
      - name: application_id
      - name: user_id
      - name: session_index

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.
paid_campaign_list_calc.sql · 28 lines
source-preserved validation: parses 
SELECT
app_id,
domain_userid,
domain_sessionidx,
mkt_source,
mkt_medium,
mkt_campaign
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(mkt_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_source,
  FIRST_VALUE(mkt_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_medium,
  FIRST_VALUE(mkt_campaign)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_campaign
  FROM atomic.events
  WHERE mkt_source IS NOT NULL
) t
GROUP BY 1,2,3,4,5,6
paid_campaign_list_calc.view.lkml · 48 lines
inferred 
view: paid_campaign_list_calc {
  derived_table: {
    sql: SELECT
app_id,
domain_userid,
domain_sessionidx,
mkt_source,
mkt_medium,
mkt_campaign
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(mkt_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_source,
  FIRST_VALUE(mkt_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_medium,
  FIRST_VALUE(mkt_campaign)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_campaign
  FROM atomic.events
  WHERE mkt_source IS NOT NULL
) 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 ;;
  }

  measure: count {
    type: count
  }
}
paid_campaign_list_calc.view.lookml · 39 lines
source-preserved 
- view: paid_campaign_list_calc
  derived_table:
    sql: SELECT
app_id,
domain_userid,
domain_sessionidx,
mkt_source,
mkt_medium,
mkt_campaign
FROM (
  SELECT
  app_id,
  domain_userid,
  domain_sessionidx,
  FIRST_VALUE(mkt_source)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_source,
  FIRST_VALUE(mkt_medium)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_medium,
  FIRST_VALUE(mkt_campaign)
    OVER (PARTITION BY app_id, domain_userid, domain_sessionidx
    ORDER BY dvce_tstamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mkt_campaign
  FROM atomic.events
  WHERE mkt_source IS NOT NULL
) 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
  - measure: count
    type: count

Contents

  • paid_campaign_list_calc.sql
  • paid_campaign_list_calc.sql
  • paid_campaign_list_calc.view.lkml
  • paid_campaign_list_calc.view.lookml