data.ml
SQL Advertising

Campaign Revenue Looker

Campaign Revenue Looker model for advertising.

source-preserved

Blocks

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

select
  `interval`,
   case
    when ex.campaign_id = 191281 then 'India - Display Ads Initial Engagement'
    when ex.campaign_id = 193943 then 'Australia - Display Ads Initial Engagement'
    when ex.campaign_id = 202802 then 'Turkey - Display Ads Initial Engagement'
    when ex.campaign_id = 205894 then 'Turkey PMP'
    when ex.campaign_id = 193637 then 'Turkey - Social Social Media Exchange'
    when ex.campaign_id = 193409 then 'Brazil - Display Ads Retargeting'
    when ex.campaign_id = 191285 then 'India - Display Ads Retargeting'
    when ex.campaign_id = 191414 then 'India - Social Social Media Exchange'
    when ex.campaign_id = 194481 then 'Australia - Social Social Media Exchange'
    when ex.campaign_id = 191282 then 'US - Display Ads Initial Engagement'
    when ex.campaign_id = 193412 then 'Brazil - Display Ads Initial Engagement'
    when ex.campaign_id = 193424 then 'UK - Display Ads Initial Engagement'
    when ex.campaign_id = 191279 then 'CA - Display Ads Initial Engagement'
    when ex.campaign_id = 194508 then 'US Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 193945 then 'Australia - Display Ads Retargeting'
    when ex.campaign_id = 191286 then 'US - Display Ads Retargeting'
    when ex.campaign_id = 195060 then 'US Web Pro - Display Ads Initial Engagement'
    when ex.campaign_id = 205052 then 'Turkey - Display Ads Retargeting'
    when ex.campaign_id = 193411 then 'Mexico - Display Ads Retargeting'
    when ex.campaign_id = 193413 then 'Mexico - Display Ads Initial Engagement'
    when ex.campaign_id = 195225 then 'White Pages PMP'
    when ex.campaign_id = 199099 then 'India PMP'
    when ex.campaign_id = 191413 then 'US - Social Social Media Exchange'
    when ex.campaign_id = 194443 then 'Mexico - Social Social Media Exchange'
    when ex.campaign_id = 191283 then 'Canada - Display Ads Retargeting'
    when ex.campaign_id = 205782 then 'UK PMP'
    when ex.campaign_id = 195057 then 'US Web Pro - Display Ads Retargeting'
    when ex.campaign_id = 193422 then 'UK - Display Ads Retargeting'
    when ex.campaign_id = 199100 then 'Australia PMP'
    when ex.campaign_id = 191409 then 'UK - Social Social Media Exchange'
    when ex.campaign_id = 200507 then 'CA Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 200493 then 'IN Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 194476 then 'Brazil - Social Social Media Exchange'
    when ex.campaign_id = 199095 then 'US PMP'
    when ex.campaign_id = 199096 then 'Brazil PMP'
    when ex.campaign_id = 199075 then 'Canada PMP'
    when ex.campaign_id = 191407 then 'Canada - Social Social Media Exchange'
    when ex.campaign_id = 199097 then 'Mexico PMP'
    when ex.campaign_id = 202182 then 'UK Sublime Skinz'
    when ex.campaign_id = 200509 then 'UK Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 199213 then 'O365 - Social Social Media Exchange'
    when ex.campaign_id = 193785 then 'India - Social FBM'
    when ex.campaign_id = 200613 then 'CA Web Pro - Social FBM'
    when ex.campaign_id = 200612 then 'IN Web Pro - Social FBM'
    when ex.campaign_id = 200611 then 'UK Web Pro - Social FBM'
    when ex.campaign_id = 199225 then 'O365 - Social FBM'
    when ex.campaign_id = 194678 then 'Australia - Social FBM'
    when ex.campaign_id = 194676 then 'Brazil - Social FBM'
    when ex.campaign_id = 193786 then 'Canada - Social FBM'
    when ex.campaign_id = 194680 then 'Mexico - Social FBM'
    when ex.campaign_id = 193787 then 'Turkey - Social FBM'
    when ex.campaign_id = 193788 then 'UK - Social FBM'
    when ex.campaign_id = 193784 then 'US - Social FBM'
    when ex.campaign_id = 208549 then 'London Initial Engagement'
    when ex.campaign_id = 208545 then 'London Retargeting'
    when ex.campaign_id = 208649 then 'London FBM'
    when ex.campaign_id = 194682 then 'US Web Pro - Social FBM' else
    ex.main_campaign end as campaign_name,
    revenue as advertising_spend
    from campaign_revenue r
    inner join campaign_meta_exch ex
    on ex.campaign_id = r.campaign_id
    where ex.campaign_id in
    (191279,
    191283,
    191281,
    191285,
    191282,
    191286,
    191413,
    191414,
    191409,
    191407,
    193424,
    193422,
    193411,
    193413,
    193637,
    193412,
    193409,
    193784,
    193786,
    193788,
    193785,
    193787,
    193943,
    193945,
    194508,
    194481,
    194476,
    194443,
    194676,
    194680,
    194678,
    194682,
    195057,
    195225,
    195060,
    196067,
    198491,
    199097,
    199075,
    199096,
    199100,
    199099,
    199095,
    199213,
    199225,
    200613,
    200612,
    200611,
    200509,
    200507,
    200493,
    202149,
    202182,
    202802,
    205052,
    205782,
    205894,
    208549,
    208545,
    208649)

# models/schema.yml
version: 2

models:
  - name: campaign_revenue_looker
    columns:
      - name: campaign_name

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.
campaign_revenue_looker.sql · 126 lines
source-preserved validation: parses 
select
  `interval`,
   case
    when ex.campaign_id = 191281 then 'India - Display Ads Initial Engagement'
    when ex.campaign_id = 193943 then 'Australia - Display Ads Initial Engagement'
    when ex.campaign_id = 202802 then 'Turkey - Display Ads Initial Engagement'
    when ex.campaign_id = 205894 then 'Turkey PMP'
    when ex.campaign_id = 193637 then 'Turkey - Social Social Media Exchange'
    when ex.campaign_id = 193409 then 'Brazil - Display Ads Retargeting'
    when ex.campaign_id = 191285 then 'India - Display Ads Retargeting'
    when ex.campaign_id = 191414 then 'India - Social Social Media Exchange'
    when ex.campaign_id = 194481 then 'Australia - Social Social Media Exchange'
    when ex.campaign_id = 191282 then 'US - Display Ads Initial Engagement'
    when ex.campaign_id = 193412 then 'Brazil - Display Ads Initial Engagement'
    when ex.campaign_id = 193424 then 'UK - Display Ads Initial Engagement'
    when ex.campaign_id = 191279 then 'CA - Display Ads Initial Engagement'
    when ex.campaign_id = 194508 then 'US Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 193945 then 'Australia - Display Ads Retargeting'
    when ex.campaign_id = 191286 then 'US - Display Ads Retargeting'
    when ex.campaign_id = 195060 then 'US Web Pro - Display Ads Initial Engagement'
    when ex.campaign_id = 205052 then 'Turkey - Display Ads Retargeting'
    when ex.campaign_id = 193411 then 'Mexico - Display Ads Retargeting'
    when ex.campaign_id = 193413 then 'Mexico - Display Ads Initial Engagement'
    when ex.campaign_id = 195225 then 'White Pages PMP'
    when ex.campaign_id = 199099 then 'India PMP'
    when ex.campaign_id = 191413 then 'US - Social Social Media Exchange'
    when ex.campaign_id = 194443 then 'Mexico - Social Social Media Exchange'
    when ex.campaign_id = 191283 then 'Canada - Display Ads Retargeting'
    when ex.campaign_id = 205782 then 'UK PMP'
    when ex.campaign_id = 195057 then 'US Web Pro - Display Ads Retargeting'
    when ex.campaign_id = 193422 then 'UK - Display Ads Retargeting'
    when ex.campaign_id = 199100 then 'Australia PMP'
    when ex.campaign_id = 191409 then 'UK - Social Social Media Exchange'
    when ex.campaign_id = 200507 then 'CA Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 200493 then 'IN Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 194476 then 'Brazil - Social Social Media Exchange'
    when ex.campaign_id = 199095 then 'US PMP'
    when ex.campaign_id = 199096 then 'Brazil PMP'
    when ex.campaign_id = 199075 then 'Canada PMP'
    when ex.campaign_id = 191407 then 'Canada - Social Social Media Exchange'
    when ex.campaign_id = 199097 then 'Mexico PMP'
    when ex.campaign_id = 202182 then 'UK Sublime Skinz'
    when ex.campaign_id = 200509 then 'UK Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 199213 then 'O365 - Social Social Media Exchange'
    when ex.campaign_id = 193785 then 'India - Social FBM'
    when ex.campaign_id = 200613 then 'CA Web Pro - Social FBM'
    when ex.campaign_id = 200612 then 'IN Web Pro - Social FBM'
    when ex.campaign_id = 200611 then 'UK Web Pro - Social FBM'
    when ex.campaign_id = 199225 then 'O365 - Social FBM'
    when ex.campaign_id = 194678 then 'Australia - Social FBM'
    when ex.campaign_id = 194676 then 'Brazil - Social FBM'
    when ex.campaign_id = 193786 then 'Canada - Social FBM'
    when ex.campaign_id = 194680 then 'Mexico - Social FBM'
    when ex.campaign_id = 193787 then 'Turkey - Social FBM'
    when ex.campaign_id = 193788 then 'UK - Social FBM'
    when ex.campaign_id = 193784 then 'US - Social FBM'
    when ex.campaign_id = 208549 then 'London Initial Engagement'
    when ex.campaign_id = 208545 then 'London Retargeting'
    when ex.campaign_id = 208649 then 'London FBM'
    when ex.campaign_id = 194682 then 'US Web Pro - Social FBM' else
    ex.main_campaign end as campaign_name,
    revenue as advertising_spend
    from campaign_revenue r
    inner join campaign_meta_exch ex
    on ex.campaign_id = r.campaign_id
    where ex.campaign_id in
    (191279,
    191283,
    191281,
    191285,
    191282,
    191286,
    191413,
    191414,
    191409,
    191407,
    193424,
    193422,
    193411,
    193413,
    193637,
    193412,
    193409,
    193784,
    193786,
    193788,
    193785,
    193787,
    193943,
    193945,
    194508,
    194481,
    194476,
    194443,
    194676,
    194680,
    194678,
    194682,
    195057,
    195225,
    195060,
    196067,
    198491,
    199097,
    199075,
    199096,
    199100,
    199099,
    199095,
    199213,
    199225,
    200613,
    200612,
    200611,
    200509,
    200507,
    200493,
    202149,
    202182,
    202802,
    205052,
    205782,
    205894,
    208549,
    208545,
    208649)
campaign_revenue_looker.view.lkml · 151 lines
inferred 
view: campaign_revenue_looker {
  derived_table: {
    sql: select
  `interval`,
   case
    when ex.campaign_id = 191281 then 'India - Display Ads Initial Engagement'
    when ex.campaign_id = 193943 then 'Australia - Display Ads Initial Engagement'
    when ex.campaign_id = 202802 then 'Turkey - Display Ads Initial Engagement'
    when ex.campaign_id = 205894 then 'Turkey PMP'
    when ex.campaign_id = 193637 then 'Turkey - Social Social Media Exchange'
    when ex.campaign_id = 193409 then 'Brazil - Display Ads Retargeting'
    when ex.campaign_id = 191285 then 'India - Display Ads Retargeting'
    when ex.campaign_id = 191414 then 'India - Social Social Media Exchange'
    when ex.campaign_id = 194481 then 'Australia - Social Social Media Exchange'
    when ex.campaign_id = 191282 then 'US - Display Ads Initial Engagement'
    when ex.campaign_id = 193412 then 'Brazil - Display Ads Initial Engagement'
    when ex.campaign_id = 193424 then 'UK - Display Ads Initial Engagement'
    when ex.campaign_id = 191279 then 'CA - Display Ads Initial Engagement'
    when ex.campaign_id = 194508 then 'US Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 193945 then 'Australia - Display Ads Retargeting'
    when ex.campaign_id = 191286 then 'US - Display Ads Retargeting'
    when ex.campaign_id = 195060 then 'US Web Pro - Display Ads Initial Engagement'
    when ex.campaign_id = 205052 then 'Turkey - Display Ads Retargeting'
    when ex.campaign_id = 193411 then 'Mexico - Display Ads Retargeting'
    when ex.campaign_id = 193413 then 'Mexico - Display Ads Initial Engagement'
    when ex.campaign_id = 195225 then 'White Pages PMP'
    when ex.campaign_id = 199099 then 'India PMP'
    when ex.campaign_id = 191413 then 'US - Social Social Media Exchange'
    when ex.campaign_id = 194443 then 'Mexico - Social Social Media Exchange'
    when ex.campaign_id = 191283 then 'Canada - Display Ads Retargeting'
    when ex.campaign_id = 205782 then 'UK PMP'
    when ex.campaign_id = 195057 then 'US Web Pro - Display Ads Retargeting'
    when ex.campaign_id = 193422 then 'UK - Display Ads Retargeting'
    when ex.campaign_id = 199100 then 'Australia PMP'
    when ex.campaign_id = 191409 then 'UK - Social Social Media Exchange'
    when ex.campaign_id = 200507 then 'CA Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 200493 then 'IN Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 194476 then 'Brazil - Social Social Media Exchange'
    when ex.campaign_id = 199095 then 'US PMP'
    when ex.campaign_id = 199096 then 'Brazil PMP'
    when ex.campaign_id = 199075 then 'Canada PMP'
    when ex.campaign_id = 191407 then 'Canada - Social Social Media Exchange'
    when ex.campaign_id = 199097 then 'Mexico PMP'
    when ex.campaign_id = 202182 then 'UK Sublime Skinz'
    when ex.campaign_id = 200509 then 'UK Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 199213 then 'O365 - Social Social Media Exchange'
    when ex.campaign_id = 193785 then 'India - Social FBM'
    when ex.campaign_id = 200613 then 'CA Web Pro - Social FBM'
    when ex.campaign_id = 200612 then 'IN Web Pro - Social FBM'
    when ex.campaign_id = 200611 then 'UK Web Pro - Social FBM'
    when ex.campaign_id = 199225 then 'O365 - Social FBM'
    when ex.campaign_id = 194678 then 'Australia - Social FBM'
    when ex.campaign_id = 194676 then 'Brazil - Social FBM'
    when ex.campaign_id = 193786 then 'Canada - Social FBM'
    when ex.campaign_id = 194680 then 'Mexico - Social FBM'
    when ex.campaign_id = 193787 then 'Turkey - Social FBM'
    when ex.campaign_id = 193788 then 'UK - Social FBM'
    when ex.campaign_id = 193784 then 'US - Social FBM'
    when ex.campaign_id = 208549 then 'London Initial Engagement'
    when ex.campaign_id = 208545 then 'London Retargeting'
    when ex.campaign_id = 208649 then 'London FBM'
    when ex.campaign_id = 194682 then 'US Web Pro - Social FBM' else
    ex.main_campaign end as campaign_name,
    revenue as advertising_spend
    from campaign_revenue r
    inner join campaign_meta_exch ex
    on ex.campaign_id = r.campaign_id
    where ex.campaign_id in
    (191279,
    191283,
    191281,
    191285,
    191282,
    191286,
    191413,
    191414,
    191409,
    191407,
    193424,
    193422,
    193411,
    193413,
    193637,
    193412,
    193409,
    193784,
    193786,
    193788,
    193785,
    193787,
    193943,
    193945,
    194508,
    194481,
    194476,
    194443,
    194676,
    194680,
    194678,
    194682,
    195057,
    195225,
    195060,
    196067,
    198491,
    199097,
    199075,
    199096,
    199100,
    199099,
    199095,
    199213,
    199225,
    200613,
    200612,
    200611,
    200509,
    200507,
    200493,
    202149,
    202182,
    202802,
    205052,
    205782,
    205894,
    208549,
    208545,
    208649) ;;
  }

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: advertising_spend {
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.advertising_spend ;;
  }
}
campaign_revenue_looker.view.lookml · 147 lines
source-preserved 
- view: campaign_revenue_looker
  derived_table:
    sql: select
  `interval`,
   case
    when ex.campaign_id = 191281 then 'India - Display Ads Initial Engagement'
    when ex.campaign_id = 193943 then 'Australia - Display Ads Initial Engagement'
    when ex.campaign_id = 202802 then 'Turkey - Display Ads Initial Engagement'
    when ex.campaign_id = 205894 then 'Turkey PMP'
    when ex.campaign_id = 193637 then 'Turkey - Social Social Media Exchange'
    when ex.campaign_id = 193409 then 'Brazil - Display Ads Retargeting'
    when ex.campaign_id = 191285 then 'India - Display Ads Retargeting'
    when ex.campaign_id = 191414 then 'India - Social Social Media Exchange'
    when ex.campaign_id = 194481 then 'Australia - Social Social Media Exchange'
    when ex.campaign_id = 191282 then 'US - Display Ads Initial Engagement'
    when ex.campaign_id = 193412 then 'Brazil - Display Ads Initial Engagement'
    when ex.campaign_id = 193424 then 'UK - Display Ads Initial Engagement'
    when ex.campaign_id = 191279 then 'CA - Display Ads Initial Engagement'
    when ex.campaign_id = 194508 then 'US Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 193945 then 'Australia - Display Ads Retargeting'
    when ex.campaign_id = 191286 then 'US - Display Ads Retargeting'
    when ex.campaign_id = 195060 then 'US Web Pro - Display Ads Initial Engagement'
    when ex.campaign_id = 205052 then 'Turkey - Display Ads Retargeting'
    when ex.campaign_id = 193411 then 'Mexico - Display Ads Retargeting'
    when ex.campaign_id = 193413 then 'Mexico - Display Ads Initial Engagement'
    when ex.campaign_id = 195225 then 'White Pages PMP'
    when ex.campaign_id = 199099 then 'India PMP'
    when ex.campaign_id = 191413 then 'US - Social Social Media Exchange'
    when ex.campaign_id = 194443 then 'Mexico - Social Social Media Exchange'
    when ex.campaign_id = 191283 then 'Canada - Display Ads Retargeting'
    when ex.campaign_id = 205782 then 'UK PMP'
    when ex.campaign_id = 195057 then 'US Web Pro - Display Ads Retargeting'
    when ex.campaign_id = 193422 then 'UK - Display Ads Retargeting'
    when ex.campaign_id = 199100 then 'Australia PMP'
    when ex.campaign_id = 191409 then 'UK - Social Social Media Exchange'
    when ex.campaign_id = 200507 then 'CA Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 200493 then 'IN Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 194476 then 'Brazil - Social Social Media Exchange'
    when ex.campaign_id = 199095 then 'US PMP'
    when ex.campaign_id = 199096 then 'Brazil PMP'
    when ex.campaign_id = 199075 then 'Canada PMP'
    when ex.campaign_id = 191407 then 'Canada - Social Social Media Exchange'
    when ex.campaign_id = 199097 then 'Mexico PMP'
    when ex.campaign_id = 202182 then 'UK Sublime Skinz'
    when ex.campaign_id = 200509 then 'UK Web Pro - Social Social Media Exchange'
    when ex.campaign_id = 199213 then 'O365 - Social Social Media Exchange'
    when ex.campaign_id = 193785 then 'India - Social FBM'
    when ex.campaign_id = 200613 then 'CA Web Pro - Social FBM'
    when ex.campaign_id = 200612 then 'IN Web Pro - Social FBM'
    when ex.campaign_id = 200611 then 'UK Web Pro - Social FBM'
    when ex.campaign_id = 199225 then 'O365 - Social FBM'
    when ex.campaign_id = 194678 then 'Australia - Social FBM'
    when ex.campaign_id = 194676 then 'Brazil - Social FBM'
    when ex.campaign_id = 193786 then 'Canada - Social FBM'
    when ex.campaign_id = 194680 then 'Mexico - Social FBM'
    when ex.campaign_id = 193787 then 'Turkey - Social FBM'
    when ex.campaign_id = 193788 then 'UK - Social FBM'
    when ex.campaign_id = 193784 then 'US - Social FBM'
    when ex.campaign_id = 208549 then 'London Initial Engagement'
    when ex.campaign_id = 208545 then 'London Retargeting'
    when ex.campaign_id = 208649 then 'London FBM'
    when ex.campaign_id = 194682 then 'US Web Pro - Social FBM' else
    ex.main_campaign end as campaign_name,
    revenue as advertising_spend
    from campaign_revenue r
    inner join campaign_meta_exch ex
    on ex.campaign_id = r.campaign_id
    where ex.campaign_id in
    (191279,
    191283,
    191281,
    191285,
    191282,
    191286,
    191413,
    191414,
    191409,
    191407,
    193424,
    193422,
    193411,
    193413,
    193637,
    193412,
    193409,
    193784,
    193786,
    193788,
    193785,
    193787,
    193943,
    193945,
    194508,
    194481,
    194476,
    194443,
    194676,
    194680,
    194678,
    194682,
    195057,
    195225,
    195060,
    196067,
    198491,
    199097,
    199075,
    199096,
    199100,
    199099,
    199095,
    199213,
    199225,
    200613,
    200612,
    200611,
    200509,
    200507,
    200493,
    202149,
    202182,
    202802,
    205052,
    205782,
    205894,
    208549,
    208545,
    208649)
  fields:
  - dimension: campaign_name
    sql: ${TABLE}.campaign_name
  - dimension_group: interval
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.`interval`
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: advertising_spend
    type: sum
    value_format: '$#,##0.00'
    sql: ${TABLE}.advertising_spend

Contents

  • campaign_revenue_looker.sql
  • campaign_revenue_looker.sql
  • campaign_revenue_looker.view.lkml
  • campaign_revenue_looker.view.lookml