data.ml
LKML Manufacturing sanitary

Web Hourly Metrics

Web Hourly Metrics model for manufacturing sanitary.

inferred

Blocks

web_hourly_metrics.view.lkml · 323 lines
inferred 
view: web_hourly_metrics {
  sql_table_name: dwh.web_hourly_metrics_aggr_ts ;;

  dimension: day {
    hidden: yes
    sql: CAST("${TABLE}.day" AS STRING) ;;
  }

  dimension: referring_domain {
    sql: ${TABLE}.elem1 ;;
  }

  dimension: elem1url {
    hidden: yes
    sql: ${TABLE}.elem1url ;;
  }

  dimension: search_engine {
    sql: ${TABLE}.elem2 ;;
  }

  dimension: elem2url {
    hidden: yes
    sql: ${TABLE}.elem2url ;;
  }

  dimension: elem3 {
    hidden: yes
    sql: ${TABLE}.elem3 ;;
  }

  dimension: elem3url {
    hidden: yes
    sql: ${TABLE}.elem3url ;;
  }

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

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

  dimension: is_before_current_month {
    type: yesno
    sql: ${event_month_num} < EXTRACT(month from CURRENT_DATE) ;;
  }

  dimension: periodname {
    hidden: yes
    sql: ${TABLE}.periodname ;;
  }

  dimension: reportsuite {
    hidden: yes
    sql: ${TABLE}.reportsuite ;;
  }

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

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

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

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

  dimension: year {
    hidden: yes
    sql: CAST("${TABLE}.year" AS STRING) ;;
  }

  dimension_group: event {
    type: time
    timeframes: [date, month_num, month, year, time]
    sql: ${TABLE}.tmstamp ;;
  }

  measure: row_count {
    type: count
    drill_fields: [periodname]
  }

  measure: total_visits {
    type: sum
    sql: ${metricvalue} ;;
  }

  measure: visits_this_month {
    type: sum
    sql: ${metricvalue} ;;
  }

  measure: visits_last_month {
    type: sum
    filters: [metricid: "visits"]
    filters: [event_date: "2 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: visits_last_month_last_year {
    type: sum
    filters: [metricid: "visits"]
    filters: [event_date: "14 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: visit_growth_last_month {
    description: "Last month's total compared to last month a year ago"
    type: number
    sql: 100 * (${visits_last_month} - ${visits_last_month_last_year}) / NULLIF(${visits_last_month_last_year},0) ;;
  }

  measure: visits_year_to_date {
    type: sum
    filters: [metricid: "visits"]
    filters: [event_year: "this year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: visits_last_year_to_date {
    type: sum
    filters: [metricid: "visits"]
    filters: [event_year: "last year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: visit_growth_year_to_date {
    description: "This YTD (as of last month) compared to last year's YTD"
    type: number
    sql: 100 * (${visits_year_to_date} - ${visits_last_year_to_date}) / NULLIF(${visits_last_year_to_date},0) ;;
  }

  measure: total_visits_percent_of_previous {
    type: percent_of_previous
    sql: ${total_visits} ;;
  }

  measure: total_visitors {
    type: sum
    filters: [metricid: "uniquevisitors"]
    sql: ${metricvalue} ;;
  }

  measure: visitors_this_month {
    type: sum
    filters: [metricid: "uniquevisitors"]
    filters: [event_date: "1 month ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: visitors_last_month {
    type: sum
    filters: [metricid: "uniquevisitors"]
    filters: [event_date: "2 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: visitors_last_month_last_year {
    type: sum
    filters: [metricid: "uniquevisitors"]
    filters: [event_date: "14 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: visitor_growth_last_month {
    type: number
    sql: 100 * (${visitors_last_month} - ${visitors_last_month_last_year}) / NULLIF(${visitors_last_month_last_year},0) ;;
  }

  measure: visitors_year_to_date {
    type: sum
    filters: [metricid: "uniquevisitors"]
    filters: [is_before_current_month: "True"]
    filters: [event_year: "this year"]
    sql: ${metricvalue} ;;
  }

  measure: visitors_last_year_to_date {
    type: sum
    filters: [metricid: "uniquevisitors"]
    filters: [is_before_current_month: "True"]
    filters: [event_year: "last year"]
    sql: ${metricvalue} ;;
  }

  measure: visitors_growth_year_to_date {
    type: number
    sql: 100 * (${visitors_year_to_date} - ${visitors_last_year_to_date}) / NULLIF(${visitors_last_year_to_date},0) ;;
  }

  measure: total_visitors_percent_of_previous {
    type: percent_of_previous
    sql: ${total_visitors} ;;
  }

  measure: total_where_to_buy_searches {
    type: sum
    filters: [metricid: "event9"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_this_month {
    type: sum
    filters: [metricid: "event9"]
    filters: [event_date: "1 month ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_last_month {
    type: sum
    filters: [metricid: "event9"]
    filters: [event_date: "2 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_last_month_last_year {
    type: sum
    filters: [metricid: "event9"]
    filters: [event_date: "14 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_growth_last_month {
    type: number
    sql: 100 * (${where_to_buy_searches_last_month} - ${where_to_buy_searches_last_month_last_year}) / NULLIF(${where_to_buy_searches_last_month_last_year},0) ;;
  }

  measure: where_to_buy_searches_year_to_date {
    type: sum
    filters: [metricid: "event9"]
    filters: [event_year: "this year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_last_year_to_date {
    type: sum
    filters: [metricid: "event9"]
    filters: [event_year: "last year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: where_to_buy_searches_growth_year_to_date {
    type: number
    sql: 100 * (${where_to_buy_searches_year_to_date} - ${where_to_buy_searches_last_year_to_date}) / NULLIF(${where_to_buy_searches_last_year_to_date},0) ;;
  }

  measure: percent_of_previous_total_where_to_buy_searches {
    type: percent_of_previous
    sql: ${total_where_to_buy_searches} ;;
  }

  measure: total_pages_emailed {
    type: sum
    filters: [metricid: "totalevent11"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_this_month {
    type: sum
    filters: [metricid: "totalevent11"]
    filters: [event_date: "1 month ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_last_month {
    type: sum
    filters: [metricid: "totalevent11"]
    filters: [event_date: "2 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_last_month_last_year {
    type: sum
    filters: [metricid: "totalevent11"]
    filters: [event_date: "13 months ago for 1 month"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_growth_last_month {
    type: number
    sql: 100 * (${pages_emailed_last_month} - ${pages_emailed_last_month_last_year}) / NULLIF(${pages_emailed_last_month_last_year},0) ;;
  }

  measure: percent_of_previous_total_pages_emailed {
    type: percent_of_previous
    sql: ${total_pages_emailed} ;;
  }

  measure: pages_emailed_year_to_date {
    type: sum
    filters: [metricid: "totalevent11"]
    filters: [event_year: "this year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_last_year_to_date {
    type: sum
    filters: [metricid: "totalevent11"]
    filters: [event_year: "last year"]
    filters: [is_before_current_month: "True"]
    sql: ${metricvalue} ;;
  }

  measure: pages_emailed_growth_year_to_date {
    type: number
    sql: 100 * (${pages_emailed_year_to_date} - ${pages_emailed_last_year_to_date}) / NULLIF(${pages_emailed_last_year_to_date},0) ;;
  }
}
web_hourly_metrics.view.lookml · 241 lines
source-preserved 
- view: web_hourly_metrics
  sql_table_name: dwh.web_hourly_metrics_aggr_ts
  fields:
  - dimension: day
    hidden: true
    sql: CAST("${TABLE}.day" AS STRING)
  - dimension: referring_domain
    sql: ${TABLE}.elem1
  - dimension: elem1url
    hidden: true
    sql: ${TABLE}.elem1url
  - dimension: search_engine
    sql: ${TABLE}.elem2
  - dimension: elem2url
    hidden: true
    sql: ${TABLE}.elem2url
  - dimension: elem3
    hidden: true
    sql: ${TABLE}.elem3
  - dimension: elem3url
    hidden: true
    sql: ${TABLE}.elem3url
  - dimension: metricid
    sql: ${TABLE}.metricid
  - dimension: metricvalue
    sql: ${TABLE}.metricvalue
  - dimension: is_before_current_month
    type: yesno
    sql: ${event_month_num} < EXTRACT(month from CURRENT_DATE)
  - dimension: periodname
    hidden: true
    sql: ${TABLE}.periodname
  - dimension: reportsuite
    hidden: true
    sql: ${TABLE}.reportsuite
  - dimension: runseconds
    sql: ${TABLE}.runseconds
  - dimension: totalperiod
    sql: ${TABLE}.totalperiod
  - dimension: type
    hidden: true
    sql: ${TABLE}.type
  - dimension: waitseconds
    sql: ${TABLE}.waitseconds
  - dimension: year
    hidden: true
    sql: CAST("${TABLE}.year" AS STRING)
  - dimension_group: event
    type: time
    timeframes:
    - date
    - month_num
    - month
    - year
    - time
    sql: ${TABLE}.tmstamp
  - measure: row_count
    type: count
    drill_fields:
    - periodname
  - measure: total_visits
    type: sum
    sql: ${metricvalue}
  - measure: visits_this_month
    type: sum
    sql: ${metricvalue}
  - measure: visits_last_month
    type: sum
    filters:
    - metricid: visits
    - event_date: 2 months ago for 1 month
    sql: ${metricvalue}
  - measure: visits_last_month_last_year
    type: sum
    filters:
    - metricid: visits
    - event_date: 14 months ago for 1 month
    sql: ${metricvalue}
  - measure: visit_growth_last_month
    type: number
    description: 'Last month''s total compared to last month a year ago'
    sql: 100 * (${visits_last_month} - ${visits_last_month_last_year}) / NULLIF(${visits_last_month_last_year},0)
  - measure: visits_year_to_date
    type: sum
    filters:
    - metricid: visits
    - event_year: this year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: visits_last_year_to_date
    type: sum
    filters:
    - metricid: visits
    - event_year: last year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: visit_growth_year_to_date
    type: number
    description: 'This YTD (as of last month) compared to last year''s YTD'
    sql: 100 * (${visits_year_to_date} - ${visits_last_year_to_date}) / NULLIF(${visits_last_year_to_date},0)
  - measure: total_visits_percent_of_previous
    type: percent_of_previous
    sql: ${total_visits}
  - measure: total_visitors
    type: sum
    filters:
    - metricid: uniquevisitors
    sql: ${metricvalue}
  - measure: visitors_this_month
    type: sum
    filters:
    - metricid: uniquevisitors
    - event_date: 1 month ago for 1 month
    sql: ${metricvalue}
  - measure: visitors_last_month
    type: sum
    filters:
    - metricid: uniquevisitors
    - event_date: 2 months ago for 1 month
    sql: ${metricvalue}
  - measure: visitors_last_month_last_year
    type: sum
    filters:
    - metricid: uniquevisitors
    - event_date: 14 months ago for 1 month
    sql: ${metricvalue}
  - measure: visitor_growth_last_month
    type: number
    sql: 100 * (${visitors_last_month} - ${visitors_last_month_last_year}) / NULLIF(${visitors_last_month_last_year},0)
  - measure: visitors_year_to_date
    type: sum
    filters:
    - metricid: uniquevisitors
    - is_before_current_month: True
    - event_year: this year
    sql: ${metricvalue}
  - measure: visitors_last_year_to_date
    type: sum
    filters:
    - metricid: uniquevisitors
    - is_before_current_month: True
    - event_year: last year
    sql: ${metricvalue}
  - measure: visitors_growth_year_to_date
    type: number
    sql: 100 * (${visitors_year_to_date} - ${visitors_last_year_to_date}) / NULLIF(${visitors_last_year_to_date},0)
  - measure: total_visitors_percent_of_previous
    type: percent_of_previous
    sql: ${total_visitors}
  - measure: total_where_to_buy_searches
    type: sum
    filters:
    - metricid: event9
    sql: ${metricvalue}
  - measure: where_to_buy_searches_this_month
    type: sum
    filters:
    - metricid: event9
    - event_date: 1 month ago for 1 month
    sql: ${metricvalue}
  - measure: where_to_buy_searches_last_month
    type: sum
    filters:
    - metricid: event9
    - event_date: 2 months ago for 1 month
    sql: ${metricvalue}
  - measure: where_to_buy_searches_last_month_last_year
    type: sum
    filters:
    - metricid: event9
    - event_date: 14 months ago for 1 month
    sql: ${metricvalue}
  - measure: where_to_buy_searches_growth_last_month
    type: number
    sql: 100 * (${where_to_buy_searches_last_month} - ${where_to_buy_searches_last_month_last_year}) / NULLIF(${where_to_buy_searches_last_month_last_year},0)
  - measure: where_to_buy_searches_year_to_date
    type: sum
    filters:
    - metricid: event9
    - event_year: this year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: where_to_buy_searches_last_year_to_date
    type: sum
    filters:
    - metricid: event9
    - event_year: last year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: where_to_buy_searches_growth_year_to_date
    type: number
    sql: 100 * (${where_to_buy_searches_year_to_date} - ${where_to_buy_searches_last_year_to_date}) / NULLIF(${where_to_buy_searches_last_year_to_date},0)
  - measure: percent_of_previous_total_where_to_buy_searches
    type: percent_of_previous
    sql: ${total_where_to_buy_searches}
  - measure: total_pages_emailed
    type: sum
    filters:
    - metricid: totalevent11
    sql: ${metricvalue}
  - measure: pages_emailed_this_month
    type: sum
    filters:
    - metricid: totalevent11
    - event_date: 1 month ago for 1 month
    sql: ${metricvalue}
  - measure: pages_emailed_last_month
    type: sum
    filters:
    - metricid: totalevent11
    - event_date: 2 months ago for 1 month
    sql: ${metricvalue}
  - measure: pages_emailed_last_month_last_year
    type: sum
    filters:
    - metricid: totalevent11
    - event_date: 13 months ago for 1 month
    sql: ${metricvalue}
  - measure: pages_emailed_growth_last_month
    type: number
    sql: 100 * (${pages_emailed_last_month} - ${pages_emailed_last_month_last_year}) / NULLIF(${pages_emailed_last_month_last_year},0)
  - measure: percent_of_previous_total_pages_emailed
    type: percent_of_previous
    sql: ${total_pages_emailed}
  - measure: pages_emailed_year_to_date
    type: sum
    filters:
    - metricid: totalevent11
    - event_year: this year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: pages_emailed_last_year_to_date
    type: sum
    filters:
    - metricid: totalevent11
    - event_year: last year
    - is_before_current_month: True
    sql: ${metricvalue}
  - measure: pages_emailed_growth_year_to_date
    type: number
    sql: 100 * (${pages_emailed_year_to_date} - ${pages_emailed_last_year_to_date}) / NULLIF(${pages_emailed_last_year_to_date},0)

Contents

  • web_hourly_metrics.view.lkml
  • web_hourly_metrics.view.lookml