SQL Advertising technology

All Metrics Data

All Metrics Data model for advertising technology.

source-preserved

Blocks

dbt SQL LKML LookML

all_metrics_data.sql · 108 lines

inferred

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

SELECT
  sellsidedata_net.day,
  sellsidedata_net.country as countryname,
  sellsidedomains.domainname,
  members.membername,
  members.masteraccountid,
  sellsidedata_net.partnerid,
  sellsidedata_net.foldposition,
  0 as zoneid,
  '' as zone,
  sellsidedata_net.adsize,
  SUM(sellsidedata_net.impressions) as impressions,
  SUM(sellsidedata_net.gross_revenue_usd) as grossrevenue,
  SUM(sellsidedata_net.net_earnings_usd) as netearnings,
  SUM(sellsidedata_net.net_revenue_usd) as netrevenue,
  SUM(sellsidedata_net.adserving_fees_usd) as adservingfees,
  SUM(sellsidedata_net.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(sellsidedata_net.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(sellsidedata_net.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(sellsidedata_net.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B2' as source
FROM sellsidedata_net
JOIN sellsidedomains ON sellsidedata_net.domainid = sellsidedomains.domainid
JOIN sellsidepublishers ON sellsidedomains.publisherid = sellsidepublishers.publisherid
JOIN members ON sellsidepublishers.memberid = members.memberid
JOIN (select * from currencies where currency = 'CAD') currencies ON sellsidedata_net.day = currencies.day
GROUP BY
    sellsidedata_net.day,
    sellsidedata_net.country,
    sellsidedomains.domainname,
    members.membername,
    members.masteraccountid,
    sellsidedata_net.partnerid,
    sellsidedata_net.foldposition,
    zoneid,
    zone,
    sellsidedata_net.adsize
UNION
SELECT
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount as masteraccountid,
  reporting_data.partnerid,
  RIGHT(zones_data.zonename,3) as foldposition,
  reporting_data.zoneid,
  zones_data.zonename as zone,
  reporting_data.adsize,
  SUM(reporting_data.impressions) as impressions,
  SUM(reporting_data.gross_revenue_usd) as grossrevenue,
  SUM(reporting_data.net_earnings_usd) as netearnings,
  SUM(reporting_data.net_revenue_usd) as netrevenue,
  SUM(reporting_data.adserving_fees_usd) as adservingfees,
  SUM(reporting_data.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(reporting_data.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(reporting_data.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(reporting_data.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B3' as source
FROM reporting_data
JOIN countries ON reporting_data.country = countries.countrycode
JOIN
  platform_junction
  ON reporting_data.memberid = platform_junction.memberid
  AND reporting_data.sellerid = platform_junction.appnexusseatid
  AND reporting_data.placement_id = platform_junction.placement_id
  AND reporting_data.publisher_id = platform_junction.publisher_id
  AND reporting_data.zoneid = platform_junction.zoneid
  AND reporting_data.adsize = platform_junction.adsize
  AND reporting_data.scope = platform_junction.scope
JOIN members_data ON reporting_data.memberid = members_data.memberid
LEFT JOIN zones_data ON
  platform_junction.zoneid=zones_data.zoneid
  AND platform_junction.memberid=zones_data.memberid
  AND platform_junction.domainid=zones_data.domainid
JOIN (select * from currencies where currency = 'CAD') currencies ON reporting_data.day = currencies.day
GROUP BY
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount,
  reporting_data.partnerid,
  foldposition,
  reporting_data.zoneid,
  zones_data.zonename,
  reporting_data.adsize

# models/schema.yml
version: 2

models:
  - name: all_metrics_data
    columns:
      - name: source
      - name: country_name
      - name: member_name
      - name: domain_name
      - name: masteraccount_id
      - name: fold_position
      - name: zone_id
      - name: zone
      - name: ad_size
      - name: is_before_mtd
      - name: is_before_ytd

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.

all_metrics_data.sql · 87 lines

source-preserved validation: parses

SELECT
  sellsidedata_net.day,
  sellsidedata_net.country as countryname,
  sellsidedomains.domainname,
  members.membername,
  members.masteraccountid,
  sellsidedata_net.partnerid,
  sellsidedata_net.foldposition,
  0 as zoneid,
  '' as zone,
  sellsidedata_net.adsize,
  SUM(sellsidedata_net.impressions) as impressions,
  SUM(sellsidedata_net.gross_revenue_usd) as grossrevenue,
  SUM(sellsidedata_net.net_earnings_usd) as netearnings,
  SUM(sellsidedata_net.net_revenue_usd) as netrevenue,
  SUM(sellsidedata_net.adserving_fees_usd) as adservingfees,
  SUM(sellsidedata_net.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(sellsidedata_net.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(sellsidedata_net.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(sellsidedata_net.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B2' as source
FROM sellsidedata_net
JOIN sellsidedomains ON sellsidedata_net.domainid = sellsidedomains.domainid
JOIN sellsidepublishers ON sellsidedomains.publisherid = sellsidepublishers.publisherid
JOIN members ON sellsidepublishers.memberid = members.memberid
JOIN (select * from currencies where currency = 'CAD') currencies ON sellsidedata_net.day = currencies.day
GROUP BY
    sellsidedata_net.day,
    sellsidedata_net.country,
    sellsidedomains.domainname,
    members.membername,
    members.masteraccountid,
    sellsidedata_net.partnerid,
    sellsidedata_net.foldposition,
    zoneid,
    zone,
    sellsidedata_net.adsize
UNION
SELECT
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount as masteraccountid,
  reporting_data.partnerid,
  RIGHT(zones_data.zonename,3) as foldposition,
  reporting_data.zoneid,
  zones_data.zonename as zone,
  reporting_data.adsize,
  SUM(reporting_data.impressions) as impressions,
  SUM(reporting_data.gross_revenue_usd) as grossrevenue,
  SUM(reporting_data.net_earnings_usd) as netearnings,
  SUM(reporting_data.net_revenue_usd) as netrevenue,
  SUM(reporting_data.adserving_fees_usd) as adservingfees,
  SUM(reporting_data.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(reporting_data.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(reporting_data.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(reporting_data.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B3' as source
FROM reporting_data
JOIN countries ON reporting_data.country = countries.countrycode
JOIN
  platform_junction
  ON reporting_data.memberid = platform_junction.memberid
  AND reporting_data.sellerid = platform_junction.appnexusseatid
  AND reporting_data.placement_id = platform_junction.placement_id
  AND reporting_data.publisher_id = platform_junction.publisher_id
  AND reporting_data.zoneid = platform_junction.zoneid
  AND reporting_data.adsize = platform_junction.adsize
  AND reporting_data.scope = platform_junction.scope
JOIN members_data ON reporting_data.memberid = members_data.memberid
LEFT JOIN zones_data ON
  platform_junction.zoneid=zones_data.zoneid
  AND platform_junction.memberid=zones_data.memberid
  AND platform_junction.domainid=zones_data.domainid
JOIN (select * from currencies where currency = 'CAD') currencies ON reporting_data.day = currencies.day
GROUP BY
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount,
  reporting_data.partnerid,
  foldposition,
  reporting_data.zoneid,
  zones_data.zonename,
  reporting_data.adsize

all_metrics_data.view.lkml · 290 lines

inferred

view: all_metrics_data {
  derived_table: {
    sql: SELECT
  sellsidedata_net.day,
  sellsidedata_net.country as countryname,
  sellsidedomains.domainname,
  members.membername,
  members.masteraccountid,
  sellsidedata_net.partnerid,
  sellsidedata_net.foldposition,
  0 as zoneid,
  '' as zone,
  sellsidedata_net.adsize,
  SUM(sellsidedata_net.impressions) as impressions,
  SUM(sellsidedata_net.gross_revenue_usd) as grossrevenue,
  SUM(sellsidedata_net.net_earnings_usd) as netearnings,
  SUM(sellsidedata_net.net_revenue_usd) as netrevenue,
  SUM(sellsidedata_net.adserving_fees_usd) as adservingfees,
  SUM(sellsidedata_net.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(sellsidedata_net.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(sellsidedata_net.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(sellsidedata_net.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B2' as source
FROM sellsidedata_net
JOIN sellsidedomains ON sellsidedata_net.domainid = sellsidedomains.domainid
JOIN sellsidepublishers ON sellsidedomains.publisherid = sellsidepublishers.publisherid
JOIN members ON sellsidepublishers.memberid = members.memberid
JOIN (select * from currencies where currency = 'CAD') currencies ON sellsidedata_net.day = currencies.day
GROUP BY
    sellsidedata_net.day,
    sellsidedata_net.country,
    sellsidedomains.domainname,
    members.membername,
    members.masteraccountid,
    sellsidedata_net.partnerid,
    sellsidedata_net.foldposition,
    zoneid,
    zone,
    sellsidedata_net.adsize
UNION
SELECT
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount as masteraccountid,
  reporting_data.partnerid,
  RIGHT(zones_data.zonename,3) as foldposition,
  reporting_data.zoneid,
  zones_data.zonename as zone,
  reporting_data.adsize,
  SUM(reporting_data.impressions) as impressions,
  SUM(reporting_data.gross_revenue_usd) as grossrevenue,
  SUM(reporting_data.net_earnings_usd) as netearnings,
  SUM(reporting_data.net_revenue_usd) as netrevenue,
  SUM(reporting_data.adserving_fees_usd) as adservingfees,
  SUM(reporting_data.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(reporting_data.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(reporting_data.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(reporting_data.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B3' as source
FROM reporting_data
JOIN countries ON reporting_data.country = countries.countrycode
JOIN
  platform_junction
  ON reporting_data.memberid = platform_junction.memberid
  AND reporting_data.sellerid = platform_junction.appnexusseatid
  AND reporting_data.placement_id = platform_junction.placement_id
  AND reporting_data.publisher_id = platform_junction.publisher_id
  AND reporting_data.zoneid = platform_junction.zoneid
  AND reporting_data.adsize = platform_junction.adsize
  AND reporting_data.scope = platform_junction.scope
JOIN members_data ON reporting_data.memberid = members_data.memberid
LEFT JOIN zones_data ON
  platform_junction.zoneid=zones_data.zoneid
  AND platform_junction.memberid=zones_data.memberid
  AND platform_junction.domainid=zones_data.domainid
JOIN (select * from currencies where currency = 'CAD') currencies ON reporting_data.day = currencies.day
GROUP BY
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount,
  reporting_data.partnerid,
  foldposition,
  reporting_data.zoneid,
  zones_data.zonename,
  reporting_data.adsize ;;
  }

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

  dimension: country_name {
    sql: ${TABLE}.countryname ;;
  }

  dimension: member_name {
    sql: ${TABLE}.membername ;;
  }

  dimension: domain_name {
    sql: ${TABLE}.domainname ;;
  }

  dimension: masteraccount_id {
    sql: ${TABLE}.masteraccountid ;;
  }

  dimension: fold_position {
    sql: ${TABLE}.foldposition ;;
  }

  dimension: zone_id {
    type: int
    sql: ${TABLE}.zoneid ;;
  }

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

  dimension: ad_size {
    sql: ${TABLE}.adsize ;;
  }

  dimension: is_before_mtd {
    type: yesno
    sql: EXTRACT(DAY FROM ${TABLE}.day) < EXTRACT(DAY FROM GETDATE()) ;;
  }

  dimension: is_before_ytd {
    type: yesno
    sql: EXTRACT(DOY FROM ${TABLE}.day) < EXTRACT(DOY FROM GETDATE()) ;;
  }

  dimension_group: day {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
  }

  measure: impressions {
    type: sum
    sql: ${TABLE}.impressions ;;
  }

  measure: revenue {
    type: sum
    sql: ${TABLE}.grossrevenue ;;
  }

  measure: net_earnings {
    type: sum
    sql: ${TABLE}.netearnings ;;
  }

  measure: net_revenue {
    type: sum
    sql: ${TABLE}.netrevenue ;;
  }

  measure: adserving_fees {
    type: sum
    sql: ${TABLE}.adservingfees ;;
  }

  measure: gross_ecpm {
    type: number
    sql: ROUND(1000.00 * ${revenue} / NULLIF(${impressions} ,0), 2) ;;
  }

  measure: net_ecpm {
    type: number
    sql: ROUND(1000.00 * ${net_revenue} / NULLIF(${impressions} ,0), 2) ;;
  }

  measure: pct_growth_revenue {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${revenue} ;;
  }

  measure: pct_growth_net_revenue {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_revenue} ;;
  }

  measure: pct_growth_impressions {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${impressions} ;;
  }

  measure: pct_growth_net_earnings {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_earnings} ;;
  }

  measure: pct_growth_ecpm {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${gross_ecpm} ;;
  }

  measure: pct_growth_net_ecpm {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_ecpm} ;;
  }

  measure: revenue_cad {
    type: sum
    sql: ${TABLE}.grossrevenue_cad ;;
  }

  measure: net_earnings_cad {
    type: sum
    sql: ${TABLE}.netearnings_cad ;;
  }

  measure: net_revenue_cad {
    type: sum
    sql: ${TABLE}.netrevenue_cad ;;
  }

  measure: adserving_fees_cad {
    type: sum
    sql: ${TABLE}.adservingfees_cad ;;
  }

  measure: gross_ecpm_cad {
    type: number
    sql: ROUND(1000.00 * ${revenue_cad} / NULLIF(${impressions} ,0), 2) ;;
  }

  measure: net_ecpm_cad {
    type: number
    sql: ROUND(1000.00 * ${net_revenue_cad} / NULLIF(${impressions} ,0), 2) ;;
  }

  measure: pct_growth_revenue_cad {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${revenue_cad} ;;
  }

  measure: pct_growth_net_revenue_cad {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_revenue_cad} ;;
  }

  measure: pct_growth_net_earnings_cad {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_earnings_cad} ;;
  }

  measure: pct_growth_ecpm_cad {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${gross_ecpm_cad} ;;
  }

  measure: pct_growth_net_ecpm_cad {
    type: percent_of_previous
    value_format: "#.00\%"
    sql: ${net_ecpm_cad} ;;
  }

  measure: percent_of_total_impressions {
    type: percent_of_total
    sql: ${impressions} ;;
  }

  measure: percent_of_total_revenue {
    type: percent_of_total
    sql: ${revenue} ;;
  }

  measure: cpm_company {
    type: number
    sql: ${gross_ecpm} - ${net_ecpm} ;;
  }
}

all_metrics_data.view.lookml · 214 lines

source-preserved

- view: all_metrics_data
  derived_table:
    sql: SELECT
  sellsidedata_net.day,
  sellsidedata_net.country as countryname,
  sellsidedomains.domainname,
  members.membername,
  members.masteraccountid,
  sellsidedata_net.partnerid,
  sellsidedata_net.foldposition,
  0 as zoneid,
  '' as zone,
  sellsidedata_net.adsize,
  SUM(sellsidedata_net.impressions) as impressions,
  SUM(sellsidedata_net.gross_revenue_usd) as grossrevenue,
  SUM(sellsidedata_net.net_earnings_usd) as netearnings,
  SUM(sellsidedata_net.net_revenue_usd) as netrevenue,
  SUM(sellsidedata_net.adserving_fees_usd) as adservingfees,
  SUM(sellsidedata_net.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(sellsidedata_net.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(sellsidedata_net.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(sellsidedata_net.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B2' as source
FROM sellsidedata_net
JOIN sellsidedomains ON sellsidedata_net.domainid = sellsidedomains.domainid
JOIN sellsidepublishers ON sellsidedomains.publisherid = sellsidepublishers.publisherid
JOIN members ON sellsidepublishers.memberid = members.memberid
JOIN (select * from currencies where currency = 'CAD') currencies ON sellsidedata_net.day = currencies.day
GROUP BY
    sellsidedata_net.day,
    sellsidedata_net.country,
    sellsidedomains.domainname,
    members.membername,
    members.masteraccountid,
    sellsidedata_net.partnerid,
    sellsidedata_net.foldposition,
    zoneid,
    zone,
    sellsidedata_net.adsize
UNION
SELECT
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount as masteraccountid,
  reporting_data.partnerid,
  RIGHT(zones_data.zonename,3) as foldposition,
  reporting_data.zoneid,
  zones_data.zonename as zone,
  reporting_data.adsize,
  SUM(reporting_data.impressions) as impressions,
  SUM(reporting_data.gross_revenue_usd) as grossrevenue,
  SUM(reporting_data.net_earnings_usd) as netearnings,
  SUM(reporting_data.net_revenue_usd) as netrevenue,
  SUM(reporting_data.adserving_fees_usd) as adservingfees,
  SUM(reporting_data.gross_revenue_usd*currencies.rate) as grossrevenue_cad,
  SUM(reporting_data.net_earnings_usd*currencies.rate) as netearnings_cad,
  SUM(reporting_data.net_revenue_usd*currencies.rate) as netrevenue_cad,
  SUM(reporting_data.adserving_fees_usd*currencies.rate) as adservingfees_cad,
  'source_B3' as source
FROM reporting_data
JOIN countries ON reporting_data.country = countries.countrycode
JOIN
  platform_junction
  ON reporting_data.memberid = platform_junction.memberid
  AND reporting_data.sellerid = platform_junction.appnexusseatid
  AND reporting_data.placement_id = platform_junction.placement_id
  AND reporting_data.publisher_id = platform_junction.publisher_id
  AND reporting_data.zoneid = platform_junction.zoneid
  AND reporting_data.adsize = platform_junction.adsize
  AND reporting_data.scope = platform_junction.scope
JOIN members_data ON reporting_data.memberid = members_data.memberid
LEFT JOIN zones_data ON
  platform_junction.zoneid=zones_data.zoneid
  AND platform_junction.memberid=zones_data.memberid
  AND platform_junction.domainid=zones_data.domainid
JOIN (select * from currencies where currency = 'CAD') currencies ON reporting_data.day = currencies.day
GROUP BY
  reporting_data.day,
  countries.countryname,
  platform_junction.domainname,
  members_data.membername,
  members_data.crmaccount,
  reporting_data.partnerid,
  foldposition,
  reporting_data.zoneid,
  zones_data.zonename,
  reporting_data.adsize
  fields:
  - dimension: source
    sql: ${TABLE}.source
  - dimension: country_name
    sql: ${TABLE}.countryname
  - dimension: member_name
    sql: ${TABLE}.membername
  - dimension: domain_name
    sql: ${TABLE}.domainname
  - dimension: masteraccount_id
    sql: ${TABLE}.masteraccountid
  - dimension: fold_position
    sql: ${TABLE}.foldposition
  - dimension: zone_id
    type: int
    sql: ${TABLE}.zoneid
  - dimension: zone
    sql: ${TABLE}.zone
  - dimension: ad_size
    sql: ${TABLE}.adsize
  - dimension: is_before_mtd
    type: yesno
    sql: EXTRACT(DAY FROM ${TABLE}.day) < EXTRACT(DAY FROM GETDATE())
  - dimension: is_before_ytd
    type: yesno
    sql: EXTRACT(DOY FROM ${TABLE}.day) < EXTRACT(DOY FROM GETDATE())
  - dimension_group: day
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
  - measure: impressions
    type: sum
    sql: ${TABLE}.impressions
  - measure: revenue
    type: sum
    sql: ${TABLE}.grossrevenue
  - measure: net_earnings
    type: sum
    sql: ${TABLE}.netearnings
  - measure: net_revenue
    type: sum
    sql: ${TABLE}.netrevenue
  - measure: adserving_fees
    type: sum
    sql: ${TABLE}.adservingfees
  - measure: gross_ecpm
    type: number
    sql: ROUND(1000.00 * ${revenue} / NULLIF(${impressions} ,0), 2)
  - measure: net_ecpm
    type: number
    sql: ROUND(1000.00 * ${net_revenue} / NULLIF(${impressions} ,0), 2)
  - measure: pct_growth_revenue
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${revenue}
  - measure: pct_growth_net_revenue
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_revenue}
  - measure: pct_growth_impressions
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${impressions}
  - measure: pct_growth_net_earnings
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_earnings}
  - measure: pct_growth_ecpm
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${gross_ecpm}
  - measure: pct_growth_net_ecpm
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_ecpm}
  - measure: revenue_cad
    type: sum
    sql: ${TABLE}.grossrevenue_cad
  - measure: net_earnings_cad
    type: sum
    sql: ${TABLE}.netearnings_cad
  - measure: net_revenue_cad
    type: sum
    sql: ${TABLE}.netrevenue_cad
  - measure: adserving_fees_cad
    type: sum
    sql: ${TABLE}.adservingfees_cad
  - measure: gross_ecpm_cad
    type: number
    sql: ROUND(1000.00 * ${revenue_cad} / NULLIF(${impressions} ,0), 2)
  - measure: net_ecpm_cad
    type: number
    sql: ROUND(1000.00 * ${net_revenue_cad} / NULLIF(${impressions} ,0), 2)
  - measure: pct_growth_revenue_cad
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${revenue_cad}
  - measure: pct_growth_net_revenue_cad
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_revenue_cad}
  - measure: pct_growth_net_earnings_cad
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_earnings_cad}
  - measure: pct_growth_ecpm_cad
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${gross_ecpm_cad}
  - measure: pct_growth_net_ecpm_cad
    type: percent_of_previous
    value_format: '#.00\%'
    sql: ${net_ecpm_cad}
  - measure: percent_of_total_impressions
    type: percent_of_total
    sql: ${impressions}
  - measure: percent_of_total_revenue
    type: percent_of_total
    sql: ${revenue}
  - measure: cpm_company
    type: number
    sql: ${gross_ecpm} - ${net_ecpm}

all_metrics_data.sql
all_metrics_data.sql
all_metrics_data.view.lkml
all_metrics_data.view.lookml

All Metrics Data

Blocks

Assumptions

Contents