SQL Advertising

Impression Data Attributed Events

Impression Data Attributed Events model for advertising.

source-preserved

Blocks

dbt SQL LKML LookML

impression_data_attributed_events.sql · 49 lines

inferred

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

select
  case when imps.partner_id is null then convs.partner_id else imps.partner_id end as partner_id,
  case when imps.strategy_id is null then convs.strategy_id else imps.strategy_id end as strategy_id,
  case when imps.campaign_id is null then convs.campaign_id else imps.campaign_id end as campaign_id,
  case when imps.date is null then convs.date else imps.date end as date,
  imps.bidder_total_spend,
  imps.impressions,
  convs.conversions
 from
   (
  select
    partner_id,
    campaign_id,
    strategy_id,
    timestamp_gmt::date as date,
    sum(total_spend_cpm / 1000) as bidder_total_spend,
    count(*) as impressions
  from
    impression_data
  group by 1,2,3,4
  ) imps
  full outer join
  (
  select
    partner_id,
    strategy_id,
    campaign_id,
    event_report_timestamp::date as date,
    count(*) as conversions
  from
    advertising_attributed_events
  where event_type = 'conversion'
  group by 1,2,3,4
  ) convs on convs.strategy_id = imps.strategy_id and convs.date = imps.date

# models/schema.yml
version: 2

models:
  - name: impression_data_attributed_events
    columns:
      - name: campaign_id
      - name: strategy_id
      - name: bidder_total_spend
      - name: impressions
      - name: conversions

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.

impression_data_attributed_events.sql · 34 lines

source-preserved validation: parses

select
  case when imps.partner_id is null then convs.partner_id else imps.partner_id end as partner_id,
  case when imps.strategy_id is null then convs.strategy_id else imps.strategy_id end as strategy_id,
  case when imps.campaign_id is null then convs.campaign_id else imps.campaign_id end as campaign_id,
  case when imps.date is null then convs.date else imps.date end as date,
  imps.bidder_total_spend,
  imps.impressions,
  convs.conversions
 from
   (
  select
    partner_id,
    campaign_id,
    strategy_id,
    timestamp_gmt::date as date,
    sum(total_spend_cpm / 1000) as bidder_total_spend,
    count(*) as impressions
  from
    impression_data
  group by 1,2,3,4
  ) imps
  full outer join
  (
  select
    partner_id,
    strategy_id,
    campaign_id,
    event_report_timestamp::date as date,
    count(*) as conversions
  from
    advertising_attributed_events
  where event_type = 'conversion'
  group by 1,2,3,4
  ) convs on convs.strategy_id = imps.strategy_id and convs.date = imps.date

impression_data_attributed_events.view.lkml · 100 lines

inferred

view: impression_data_attributed_events {
  derived_table: {
    sql: select
  case when imps.partner_id is null then convs.partner_id else imps.partner_id end as partner_id,
  case when imps.strategy_id is null then convs.strategy_id else imps.strategy_id end as strategy_id,
  case when imps.campaign_id is null then convs.campaign_id else imps.campaign_id end as campaign_id,
  case when imps.date is null then convs.date else imps.date end as date,
  imps.bidder_total_spend,
  imps.impressions,
  convs.conversions
 from
   (
  select
    partner_id,
    campaign_id,
    strategy_id,
    timestamp_gmt::date as date,
    sum(total_spend_cpm / 1000) as bidder_total_spend,
    count(*) as impressions
  from
    impression_data
  group by 1,2,3,4
  ) imps
  full outer join
  (
  select
    partner_id,
    strategy_id,
    campaign_id,
    event_report_timestamp::date as date,
    count(*) as conversions
  from
    advertising_attributed_events
  where event_type = 'conversion'
  group by 1,2,3,4
  ) convs on convs.strategy_id = imps.strategy_id and convs.date = imps.date ;;
  }

  dimension: campaign_id {
    type: int
    sql: ${TABLE}.campaign_id ;;
  }

  dimension: strategy_id {
    type: int
    sql: ${TABLE}.strategy_id ;;
  }

  dimension: bidder_total_spend {
    type: number
    hidden: yes
    sql: ${TABLE}.bidder_total_spend ;;
  }

  dimension: impressions {
    type: number
    hidden: yes
    sql: ${TABLE}.impressions ;;
  }

  dimension: conversions {
    type: number
    hidden: yes
    sql: ${TABLE}.conversions ;;
  }

  dimension_group: date {
    type: time
    timeframes: [date]
    convert_tz: no
    sql: ${TABLE}.date ;;
  }

  measure: total_bidder_total_spend {
    type: sum
    sql: ${bidder_total_spend} ;;
  }

  measure: total_conversions {
    type: sum
    sql: ${conversions} ;;
  }

  measure: total_impressions {
    type: sum
    sql: ${impressions} ;;
  }

  measure: cpm {
    label: "cpm"
    type: number
    sql: ${total_bidder_total_spend} / NULLIF(${total_impressions}, 0) * 1000 ;;
  }

  measure: cpa {
    label: "cpa"
    type: number
    sql: ${total_bidder_total_spend} /  NULLIF(${total_conversions},0) ;;
  }
}

impression_data_attributed_events.view.lookml · 78 lines

source-preserved

- view: impression_data_attributed_events
  derived_table:
    sql: select
  case when imps.partner_id is null then convs.partner_id else imps.partner_id end as partner_id,
  case when imps.strategy_id is null then convs.strategy_id else imps.strategy_id end as strategy_id,
  case when imps.campaign_id is null then convs.campaign_id else imps.campaign_id end as campaign_id,
  case when imps.date is null then convs.date else imps.date end as date,
  imps.bidder_total_spend,
  imps.impressions,
  convs.conversions
 from
   (
  select
    partner_id,
    campaign_id,
    strategy_id,
    timestamp_gmt::date as date,
    sum(total_spend_cpm / 1000) as bidder_total_spend,
    count(*) as impressions
  from
    impression_data
  group by 1,2,3,4
  ) imps
  full outer join
  (
  select
    partner_id,
    strategy_id,
    campaign_id,
    event_report_timestamp::date as date,
    count(*) as conversions
  from
    advertising_attributed_events
  where event_type = 'conversion'
  group by 1,2,3,4
  ) convs on convs.strategy_id = imps.strategy_id and convs.date = imps.date
  fields:
  - dimension: campaign_id
    type: int
    sql: ${TABLE}.campaign_id
  - dimension: strategy_id
    type: int
    sql: ${TABLE}.strategy_id
  - dimension: bidder_total_spend
    type: number
    hidden: true
    sql: ${TABLE}.bidder_total_spend
  - dimension: impressions
    type: number
    hidden: true
    sql: ${TABLE}.impressions
  - dimension: conversions
    type: number
    hidden: true
    sql: ${TABLE}.conversions
  - dimension_group: date
    type: time
    timeframes:
    - date
    convert_tz: false
    sql: ${TABLE}.date
  - measure: total_bidder_total_spend
    type: sum
    sql: ${bidder_total_spend}
  - measure: total_conversions
    type: sum
    sql: ${conversions}
  - measure: total_impressions
    type: sum
    sql: ${impressions}
  - measure: cpm
    type: number
    label: cpm
    sql: ${total_bidder_total_spend} / NULLIF(${total_impressions}, 0) * 1000
  - measure: cpa
    type: number
    label: cpa
    sql: ${total_bidder_total_spend} /  NULLIF(${total_conversions},0)

impression_data_attributed_events.sql
impression_data_attributed_events.sql
impression_data_attributed_events.view.lkml
impression_data_attributed_events.view.lookml

Impression Data Attributed Events

Blocks

Assumptions

Contents