Dsm Data Raw

view: dsm_data_raw {
  sql_table_name: revenue.dsm_data_raw ;;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: site {
    sql: substring(${TABLE}.proposal_name, 0, 3) ;;
  }

  dimension: is_podcast {
    sql: strpos(lower(product_name), 'podcast') > 0 ;;
  }

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

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

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

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

  measure: billable_net_revenue {
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.billable_net_revenue / 1000000 ;;
  }

  measure: contracted_net_revenue {
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.contracted_net_revenue / 1000000 ;;
  }

  measure: reconciled_revenue {
    type: sum
    value_format: "$#,##0.00"
    sql: cast (regexp_replace(${TABLE}.reconciled_revenue, '[^\\.0-9]+') as float) ;;
  }

  measure: count {
    type: count
    drill_fields: [product_name, proposal_agency_name, proposal_line_item_name, proposal_name]
  }
}

- view: dsm_data_raw
  sql_table_name: revenue.dsm_data_raw
  fields:
  - dimension: month_and_year
    sql: ${TABLE}.month_and_year
  - dimension: product_id
    type: int
    sql: ${TABLE}.product_id
  - dimension: product_name
    sql: ${TABLE}.product_name
  - dimension: proposal_advertiser
    sql: ${TABLE}.proposal_advertiser
  - dimension: proposal_advertiser_id
    type: int
    sql: ${TABLE}.proposal_advertiser_id
  - dimension: proposal_agencies
    sql: ${TABLE}.proposal_agencies
  - dimension: proposal_agency_id
    type: int
    sql: ${TABLE}.proposal_agency_id
  - dimension: proposal_agency_ids
    type: int
    sql: ${TABLE}.proposal_agency_ids
  - dimension: proposal_agency_name
    sql: ${TABLE}.proposal_agency_name
  - dimension: proposal_approval_stage
    sql: ${TABLE}.proposal_approval_stage
  - dimension: proposal_billing_cap
    sql: ${TABLE}.proposal_billing_cap
  - dimension: proposal_billing_schedule
    sql: ${TABLE}.proposal_billing_schedule
  - dimension: proposal_billing_source
    sql: ${TABLE}.proposal_billing_source
  - dimension: proposal_currency
    sql: ${TABLE}.proposal_currency
  - dimension: proposal_id
    type: int
    sql: ${TABLE}.proposal_id
  - dimension: proposal_line_item_comments
    sql: ${TABLE}.proposal_line_item_comments
  - dimension: proposal_line_item_id
    type: int
    sql: ${TABLE}.proposal_line_item_id
  - dimension: proposal_line_item_name
    sql: ${TABLE}.proposal_line_item_name
  - dimension: proposal_line_item_type_and_priority
    sql: ${TABLE}.proposal_line_item_type_and_priority
  - dimension: proposal_name
    sql: ${TABLE}.proposal_name
  - dimension: proposal_pricing_model
    sql: ${TABLE}.proposal_pricing_model
  - dimension: proposal_primary_salesperson
    sql: ${TABLE}.proposal_primary_salesperson
  - dimension: proposal_secondary_salesperson
    sql: ${TABLE}.proposal_secondary_salesperson
  - dimension: proposal_status
    sql: ${TABLE}.proposal_status
  - dimension: site
    sql: substring(${TABLE}.proposal_name, 0, 3)
  - dimension: is_podcast
    sql: strpos(lower(product_name), 'podcast') > 0
  - dimension_group: proposal_end_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.proposal_end_date_time
  - dimension_group: proposal_line_item_end_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.proposal_line_item_end_date_time
  - dimension_group: proposal_line_item_start_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.proposal_line_item_start_date_time
  - dimension_group: proposal_start_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.proposal_start_date_time
  - measure: billable_net_revenue
    type: sum
    value_format: '$#,##0.00'
    sql: ${TABLE}.billable_net_revenue / 1000000
  - measure: contracted_net_revenue
    type: sum
    value_format: '$#,##0.00'
    sql: ${TABLE}.contracted_net_revenue / 1000000
  - measure: reconciled_revenue
    type: sum
    value_format: '$#,##0.00'
    sql: cast (regexp_replace(${TABLE}.reconciled_revenue, '[^\\.0-9]+') as float)
  - measure: count
    type: count
    drill_fields:
    - product_name
    - proposal_agency_name
    - proposal_line_item_name
    - proposal_name