SQL Advertising

Impressions Clicks Conversions

Impressions Clicks Conversions model for advertising.

source-preserved

Blocks

dbt SQL LKML LookML

impressions_clicks_conversions.sql · 45 lines

inferred

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

SELECT 'impression' AS type
  , partner_id
  , campaign_id
  , DATE(impression_gmt_timestamp) AS event
  , SUM(bidder_total_spend)::FLOAT AS bidder_spend
  , COUNT(*) AS occurrences
FROM impressions_30d
GROUP BY 1,2,3,4

UNION ALL

SELECT 'click' AS type
  , partner_id
  , campaign_id
  , DATE(click_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM clicks
GROUP BY 1,2,3,4

UNION ALL

SELECT 'conversion' AS type
  , partner_id
  , campaign_id
  , DATE(conversion_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM attributed_merit
GROUP BY 1,2,3,4

# models/schema.yml
version: 2

models:
  - name: impressions_clicks_conversions
    columns:
      - name: type
      - name: partner_id
      - name: campaign_id
      - name: bidder_spend
      - name: occurrences

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.

impressions_clicks_conversions.sql · 30 lines

source-preserved validation: parses

SELECT 'impression' AS type
  , partner_id
  , campaign_id
  , DATE(impression_gmt_timestamp) AS event
  , SUM(bidder_total_spend)::FLOAT AS bidder_spend
  , COUNT(*) AS occurrences
FROM impressions_30d
GROUP BY 1,2,3,4

UNION ALL

SELECT 'click' AS type
  , partner_id
  , campaign_id
  , DATE(click_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM clicks
GROUP BY 1,2,3,4

UNION ALL

SELECT 'conversion' AS type
  , partner_id
  , campaign_id
  , DATE(conversion_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM attributed_merit
GROUP BY 1,2,3,4

impressions_clicks_conversions.view.lkml · 111 lines

inferred

view: impressions_clicks_conversions {
  derived_table: {
    sql: SELECT 'impression' AS type
  , partner_id
  , campaign_id
  , DATE(impression_gmt_timestamp) AS event
  , SUM(bidder_total_spend)::FLOAT AS bidder_spend
  , COUNT(*) AS occurrences
FROM impressions_30d
GROUP BY 1,2,3,4

UNION ALL

SELECT 'click' AS type
  , partner_id
  , campaign_id
  , DATE(click_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM clicks
GROUP BY 1,2,3,4

UNION ALL

SELECT 'conversion' AS type
  , partner_id
  , campaign_id
  , DATE(conversion_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM attributed_merit
GROUP BY 1,2,3,4 ;;
  }

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

  dimension: partner_id {
    type: int
    hidden: yes
    sql: ${TABLE}.partner_id ;;
  }

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

  dimension: bidder_spend {
    type: number
    sql: ${TABLE}.bidder_spend ;;
  }

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

  dimension_group: event {
    type: time
    timeframes: [date, week]
    sql: ${TABLE}.event ;;
  }

  measure: total_impressions {
    type: sum
    filters: [type: "impression"]
    drill_fields: [detail*]
    sql: ${occurrences} ;;
  }

  measure: total_clicks {
    type: sum
    filters: [type: "click"]
    drill_fields: [detail*]
    sql: ${occurrences} ;;
  }

  measure: total_conversions {
    type: sum
    filters: [type: "conversion"]
    drill_fields: [detail*]
    sql: ${occurrences} ;;
  }

  measure: total_bidder_spend {
    type: sum
    sql: ${bidder_spend} ;;
  }

  measure: cpm {
    label: "Impressions Clicks Conversions CPM"
    type: number
    sql: ${total_bidder_spend} / NULLIF(${total_impressions}, 0) ;;
  }

  measure: cpc {
    label: "Impressions Clicks Conversions CPC"
    type: number
    sql: ${total_bidder_spend} / NULLIF((${total_clicks} * 1000), 0) ;;
  }

  measure: cpa {
    label: "Impressions Clicks Conversions CPA"
    type: number
    sql: ${total_bidder_spend} / NULLIF((${total_conversions} * 1000), 0) ;;
  }
}

impressions_clicks_conversions.view.lookml · 92 lines

source-preserved

- view: impressions_clicks_conversions
  derived_table:
    sql: SELECT 'impression' AS type
  , partner_id
  , campaign_id
  , DATE(impression_gmt_timestamp) AS event
  , SUM(bidder_total_spend)::FLOAT AS bidder_spend
  , COUNT(*) AS occurrences
FROM impressions_30d
GROUP BY 1,2,3,4

UNION ALL

SELECT 'click' AS type
  , partner_id
  , campaign_id
  , DATE(click_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM clicks
GROUP BY 1,2,3,4

UNION ALL

SELECT 'conversion' AS type
  , partner_id
  , campaign_id
  , DATE(conversion_gmt_timestamp) AS event
  , NULL AS bidder_spend
  , COUNT(*) AS occurrences
FROM attributed_merit
GROUP BY 1,2,3,4
  fields:
  - dimension: type
    sql: ${TABLE}.type
  - dimension: partner_id
    type: int
    hidden: true
    sql: ${TABLE}.partner_id
  - dimension: campaign_id
    type: int
    hidden: true
    sql: ${TABLE}.campaign_id
  - dimension: bidder_spend
    type: number
    sql: ${TABLE}.bidder_spend
  - dimension: occurrences
    type: number
    hidden: true
    sql: ${TABLE}.occurrences
  - dimension_group: event
    type: time
    timeframes:
    - date
    - week
    sql: ${TABLE}.event
  - measure: total_impressions
    type: sum
    drill_fields:
    - detail*
    filters:
    - type: impression
    sql: ${occurrences}
  - measure: total_clicks
    type: sum
    drill_fields:
    - detail*
    filters:
    - type: click
    sql: ${occurrences}
  - measure: total_conversions
    type: sum
    drill_fields:
    - detail*
    filters:
    - type: conversion
    sql: ${occurrences}
  - measure: total_bidder_spend
    type: sum
    sql: ${bidder_spend}
  - measure: cpm
    type: number
    label: Impressions Clicks Conversions CPM
    sql: ${total_bidder_spend} / NULLIF(${total_impressions}, 0)
  - measure: cpc
    type: number
    label: Impressions Clicks Conversions CPC
    sql: ${total_bidder_spend} / NULLIF((${total_clicks} * 1000), 0)
  - measure: cpa
    type: number
    label: Impressions Clicks Conversions CPA
    sql: ${total_bidder_spend} / NULLIF((${total_conversions} * 1000), 0)

impressions_clicks_conversions.sql
impressions_clicks_conversions.sql
impressions_clicks_conversions.view.lkml
impressions_clicks_conversions.view.lookml

Impressions Clicks Conversions

Blocks

Assumptions

Contents