data.ml
SQL Advertising technology

Audience Houseconstructionyear Distribution Conversions

Audience Houseconstructionyear Distribution Conversions model for advertising technology.

source-preserved

Blocks

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

with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
    conversiondata
  JOIN
      analytics_data df2
  ON
    conversiondata.auctionid = df2.auctionid
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  group by dc.geoid, df2.insertionorderid
  HAVING count(distinct df2.adnxsuid) >= 30)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_houseconstructionyear
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)

# models/schema.yml
version: 2

models:
  - name: audience_houseconstructionyear_distribution_conversions
    columns:
      - name: insertionorder_id
      - name: house_construction_year

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.
audience_houseconstructionyear_distribution_conversions.sql · 34 lines
source-preserved validation: parses 
with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
    conversiondata
  JOIN
      analytics_data df2
  ON
    conversiondata.auctionid = df2.auctionid
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  group by dc.geoid, df2.insertionorderid
  HAVING count(distinct df2.adnxsuid) >= 30)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_houseconstructionyear
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)
audience_houseconstructionyear_distribution_conversions.view.lkml · 51 lines
inferred 
view: audience_houseconstructionyear_distribution_conversions {
  derived_table: {
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
    conversiondata
  JOIN
      analytics_data df2
  ON
    conversiondata.auctionid = df2.auctionid
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  group by dc.geoid, df2.insertionorderid
  HAVING count(distinct df2.adnxsuid) >= 30)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_houseconstructionyear
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
) ;;
  }

  dimension: insertionorder_id {
    sql: ${TABLE}.insertionorderid ;;
  }

  dimension: house_construction_year {
    sql: ${TABLE}.key ;;
  }

  measure: ratio {
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report ;;
  }
}
audience_houseconstructionyear_distribution_conversions.view.lookml · 44 lines
source-preserved 
- view: audience_houseconstructionyear_distribution_conversions
  derived_table:
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
    conversiondata
  JOIN
      analytics_data df2
  ON
    conversiondata.auctionid = df2.auctionid
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  group by dc.geoid, df2.insertionorderid
  HAVING count(distinct df2.adnxsuid) >= 30)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_houseconstructionyear
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)
  fields:
  - dimension: insertionorder_id
    sql: ${TABLE}.insertionorderid
  - dimension: house_construction_year
    sql: ${TABLE}.key
  - measure: ratio
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report

Contents

  • audience_houseconstructionyear_distribution_conversions.sql
  • audience_houseconstructionyear_distribution_conversions.sql
  • audience_houseconstructionyear_distribution_conversions.view.lkml
  • audience_houseconstructionyear_distribution_conversions.view.lookml