data.ml
SQL Advertising technology

Audience Gender Distribution

Audience Gender Distribution model for advertising technology.

source-preserved

Blocks

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

with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  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_gender
          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_gender_distribution
    columns:
      - name: insertionorder_id
      - name: gender

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_gender_distribution.sql · 31 lines
source-preserved validation: parses 
with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  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_gender
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)
audience_gender_distribution.view.lkml · 48 lines
inferred 
view: audience_gender_distribution {
  derived_table: {
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  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_gender
          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: gender {
    sql: ${TABLE}.key ;;
  }

  measure: ratio {
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report ;;
  }
}
audience_gender_distribution.view.lookml · 41 lines
source-preserved 
- view: audience_gender_distribution
  derived_table:
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  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_gender
          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: gender
    sql: ${TABLE}.key
  - measure: ratio
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report

Contents

  • audience_gender_distribution.sql
  • audience_gender_distribution.sql
  • audience_gender_distribution.view.lkml
  • audience_gender_distribution.view.lookml