Pages

view: pages {
  sql_table_name: live.pages ;;

  dimension: event_id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: context_page_referrer_mapped {
    sql: CASE WHEN ${context_page_referrer} like '%facebook%' THEN 'facebook' WHEN ${context_page_referrer} like '%google%' THEN 'google' WHEN ${context_page_referrer} like '%gliq%' THEN 'gliq' WHEN ${context_page_referrer} like '%moneyandmarkets%' THEN 'money and markets' WHEN ${context_page_referrer} like '%yahoo%' THEN 'yahoo' WHEN ${context_page_referrer} like '%press%' THEN 'weiss press' WHEN ${context_page_referrer} like '%zogbyanalytics%' THEN 'zogbyanalytics' ELSE ${context_page_referrer} END ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: title {
    sql: case when position('- Money and Markets' in ${TABLE}.title) <> 0 then substring(${TABLE}.title, 1, position('- Money and Markets' in ${TABLE}.title)-1) else ${TABLE}.title end ;;
  }

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

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

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

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

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

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

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

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

  measure: count_pageviews {
    type: count
    drill_fields: [received_date, title, user_id, type, context_page_path, id, anonymous_id]
  }

  measure: count_visitors {
    type: count_distinct
    sql: coalesce(${user_id},${aliases_mapping.user_id}, ${anonymous_id}) ;;
  }

  measure: avg_page_view_duration_minutes {
    type: average
    sql: ${page_facts.duration_page_view_seconds}/60.0 ;;
  }

  measure: count_distinct_pageviews {
    type: number
    sql: COUNT(DISTINCT CONCAT(${user_id}, ${url})) ;;
  }
}

- view: pages
  sql_table_name: live.pages
  fields:
  - dimension: event_id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: anonymous_id
    sql: ${TABLE}.anonymous_id
  - dimension: category
    sql: ${TABLE}.category
  - dimension: ccode
    sql: ${TABLE}.ccode
  - dimension: context_campaign_content
    sql: ${TABLE}.context_campaign_content
  - dimension: context_campaign_medium
    sql: ${TABLE}.context_campaign_medium
  - dimension: context_campaign_name
    sql: ${TABLE}.context_campaign_name
  - dimension: context_campaign_source
    sql: ${TABLE}.context_campaign_source
  - dimension: context_campaign_term
    sql: ${TABLE}.context_campaign_term
  - dimension: context_ip
    sql: ${TABLE}.context_ip
  - dimension: context_library_name
    sql: ${TABLE}.context_library_name
  - dimension: context_library_version
    sql: ${TABLE}.context_library_version
  - dimension: context_page_path
    sql: ${TABLE}.context_page_path
  - dimension: context_page_referrer
    sql: ${TABLE}.context_page_referrer
  - dimension: context_page_referrer_mapped
    sql: CASE WHEN ${context_page_referrer} like '%facebook%' THEN 'facebook' WHEN ${context_page_referrer} like '%google%' THEN 'google' WHEN ${context_page_referrer} like '%gliq%' THEN 'gliq' WHEN ${context_page_referrer} like '%moneyandmarkets%' THEN 'money and markets' WHEN ${context_page_referrer} like '%yahoo%' THEN 'yahoo' WHEN ${context_page_referrer} like '%press%' THEN 'weiss press' WHEN ${context_page_referrer} like '%zogbyanalytics%' THEN 'zogbyanalytics' ELSE ${context_page_referrer} END
  - dimension: context_page_search
    sql: ${TABLE}.context_page_search
  - dimension: context_page_title
    sql: ${TABLE}.context_page_title
  - dimension: context_page_url
    sql: ${TABLE}.context_page_url
  - dimension: context_user_agent
    sql: ${TABLE}.context_user_agent
  - dimension: dropdate
    sql: ${TABLE}.dropdate
  - dimension: effort
    sql: ${TABLE}.effort
  - dimension: email
    sql: ${TABLE}.email
  - dimension: name
    sql: ${TABLE}.name
  - dimension: path
    sql: ${TABLE}.path
  - dimension: product
    sql: ${TABLE}.product
  - dimension: referrer
    sql: ${TABLE}.referrer
  - dimension: search
    sql: ${TABLE}.search
  - dimension: source
    sql: ${TABLE}.source
  - dimension: subject
    sql: ${TABLE}.subject
  - dimension: title
    sql: case when position('- Money and Markets' in ${TABLE}.title) <> 0 then substring(${TABLE}.title, 1, position('- Money and Markets' in ${TABLE}.title)-1) else ${TABLE}.title end
  - dimension: type
    sql: ${TABLE}.type
  - dimension: url
    sql: ${TABLE}.url
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: userid
    hidden: true
    sql: ${TABLE}.userid
  - dimension: uuid
    type: int
    sql: ${TABLE}.uuid
  - dimension_group: original_timestamp
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.original_timestamp
  - dimension_group: received
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.received_at
  - dimension_group: sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sent_at
  - measure: count_pageviews
    type: count
    drill_fields:
    - received_date
    - title
    - user_id
    - type
    - context_page_path
    - id
    - anonymous_id
  - measure: count_visitors
    type: count_distinct
    sql: coalesce(${user_id},${aliases_mapping.user_id}, ${anonymous_id})
  - measure: avg_page_view_duration_minutes
    type: average
    sql: ${page_facts.duration_page_view_seconds}/60.0
  - measure: count_distinct_pageviews
    type: number
    sql: COUNT(DISTINCT CONCAT(${user_id}, ${url}))