Events Raw Generic

view: events_raw_generic {
  sql_table_name: public.events_raw ;;

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

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

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

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

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

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

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

  dimension: hotel_id {
    label: "Hotel_id"
    type: string
    sql: ${TABLE}.properties ->> 'hotel_id' ;;
  }

  dimension: hotel_name {
    label: "Hotel Name"
    type: string
    sql: ${TABLE}.properties ->> 'hotel_name' ;;
  }

  dimension: hotel_total_price {
    label: "Hotel Total Price"
    type: int
    sql: (Cast (${TABLE}.properties ->> 'hotel_price_per_night' as INT)/100) ;;
  }

  dimension: hotel_position {
    label: "Hotel Position"
    type: int
    sql: Cast (${TABLE}.properties ->> 'hotel_position' as INT) ;;
  }

  dimension: search_lag {
    label: "Lag Days"
    type: int
    sql: Cast(${TABLE}.properties ->> 'search_lag' as Int) ;;
  }

  dimension: adults {
    label: "Adults"
    type: int
    sql: Cast (${TABLE}.properties ->> 'search_adults' as INT) ;;
  }

  dimension: children {
    label: "Children"
    type: int
    sql: Cast (${TABLE}.properties ->> 'search_children' as INT) ;;
  }

  dimension: nights {
    label: "Nights"
    type: int
    sql: Cast (${TABLE}.properties ->> 'search_nights' as INT) ;;
  }

  dimension: sort_order {
    label: "Sort Order"
    type: string
    sql: ${TABLE}.properties ->> 'sort_order' ;;
  }

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

  dimension_group: time_2 {
    label: "Date"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.time ;;
  }

  measure: count {
    type: count
    drill_fields: [id, name, users.user_id, sessions.session_id, variants.variant_id]
  }

  measure: visitors {
    label: "Visitors"
    type: count_distinct
    sql: ${TABLE}.user_id ;;
  }

  measure: visitor_percent_of_total {
    label: "Percent of Total Visitors"
    type: percent_of_total
    sql: ${visitors} ;;
  }

  measure: homepage_views_count {
    label: "Homepage Views"
    type: count
    filters: [event_name: "homepage_view"]
  }

  measure: hotel_book_count {
    label: "Bookings"
    type: count
    filters: [event_name: "hotel_book"]
  }

  measure: hotel_click_count {
    label: "Hotel Clicks"
    type: count
    filters: [event_name: "hotel_detail_view"]
  }

  measure: hotel_index_view_count {
    label: "Hotel Views"
    type: count
    filters: [event_name: "hotel_index_view"]
  }

  measure: ctr_rate {
    label: "Ctr"
    type: number
    sql: 100*${hotel_click_count}/nullif(${hotel_index_view_count},0) ;;
  }

  measure: conversion_rate {
    label: "Conversion rate"
    type: number
    sql: 100*${hotel_book_count}/nullif(${hotel_index_view_count},0) ;;
  }

  measure: avg_price_per_night {
    label: "Avg Price Per Night"
    type: avg
    value_format: "#,##0"
    sql: ${hotel_total_price}/nullif(${nights},0) ;;
  }

  measure: avg_position {
    label: "Avg Position"
    type: avg
    value_format: "0.0"
    sql: CAST(${hotel_position} AS REAL) ;;
  }

  measure: position_rank {
    label: "Highest Display Position"
    type: number
    sql: RANK() OVER(ORDER BY ${avg_position} ASC) ;;
  }

  measure: ctr_rank {
    label: "Highest CTR"
    type: number
    sql: RANK() OVER(ORDER BY ${ctr_rate} DESC) ;;
  }

  measure: click_rank {
    label: "Most Clicked"
    type: number
    sql: RANK() OVER(ORDER BY ${hotel_click_count} DESC) ;;
  }

  measure: book_rank {
    label: "Best Sellers"
    type: number
    sql: RANK() OVER(ORDER BY ${hotel_book_count} DESC) ;;
  }

  measure: view_rank {
    label: "Most Viewed"
    type: number
    sql: RANK() OVER(ORDER BY ${hotel_index_view_count} DESC) ;;
  }

  measure: price_rank {
    label: "Rank Price Per Night (highest)"
    type: number
    sql: RANK() OVER(ORDER BY ${avg_price_per_night} DESC) ;;
  }

  measure: conversion_rank {
    label: "Highest Converters"
    type: number
    sql: RANK() OVER(ORDER BY ${conversion_rate} DESC) ;;
  }
}

- view: events_raw_generic
  sql_table_name: public.events_raw
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: account_id
    type: int
    sql: ${TABLE}.account_id
  - dimension: event_name
    sql: ${TABLE}.name
  - dimension: properties
    sql: ${TABLE}.properties
  - dimension: session_id
    sql: ${TABLE}.session_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: variants_id
    type: int
    sql: ${TABLE}.variants_id
  - dimension: hotel_id
    type: string
    label: Hotel_id
    sql: ${TABLE}.properties ->> 'hotel_id'
  - dimension: hotel_name
    type: string
    label: Hotel Name
    sql: ${TABLE}.properties ->> 'hotel_name'
  - dimension: hotel_total_price
    type: int
    label: Hotel Total Price
    sql: (Cast (${TABLE}.properties ->> 'hotel_price_per_night' as INT)/100)
  - dimension: hotel_position
    type: int
    label: Hotel Position
    sql: Cast (${TABLE}.properties ->> 'hotel_position' as INT)
  - dimension: search_lag
    type: int
    label: Lag Days
    sql: Cast(${TABLE}.properties ->> 'search_lag' as Int)
  - dimension: adults
    type: int
    label: Adults
    sql: Cast (${TABLE}.properties ->> 'search_adults' as INT)
  - dimension: children
    type: int
    label: Children
    sql: Cast (${TABLE}.properties ->> 'search_children' as INT)
  - dimension: nights
    type: int
    label: Nights
    sql: Cast (${TABLE}.properties ->> 'search_nights' as INT)
  - dimension: sort_order
    type: string
    label: Sort Order
    sql: ${TABLE}.properties ->> 'sort_order'
  - dimension_group: time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.time
  - dimension_group: time_2
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.time
  - measure: count
    type: count
    drill_fields:
    - id
    - name
    - users.user_id
    - sessions.session_id
    - variants.variant_id
  - measure: visitors
    type: count_distinct
    label: Visitors
    sql: ${TABLE}.user_id
  - measure: visitor_percent_of_total
    type: percent_of_total
    label: Percent of Total Visitors
    sql: ${visitors}
  - measure: homepage_views_count
    type: count
    label: Homepage Views
    filters:
    - event_name: homepage_view
  - measure: hotel_book_count
    type: count
    label: Bookings
    filters:
    - event_name: hotel_book
  - measure: hotel_click_count
    type: count
    label: Hotel Clicks
    filters:
    - event_name: hotel_detail_view
  - measure: hotel_index_view_count
    type: count
    label: Hotel Views
    filters:
    - event_name: hotel_index_view
  - measure: ctr_rate
    type: number
    label: Ctr
    sql: 100*${hotel_click_count}/nullif(${hotel_index_view_count},0)
  - measure: conversion_rate
    type: number
    label: Conversion rate
    sql: 100*${hotel_book_count}/nullif(${hotel_index_view_count},0)
  - measure: avg_price_per_night
    type: avg
    label: Avg Price Per Night
    value_format: '#,##0'
    sql: ${hotel_total_price}/nullif(${nights},0)
  - measure: avg_position
    type: avg
    label: Avg Position
    value_format: 0.0
    sql: CAST(${hotel_position} AS REAL)
  - measure: position_rank
    type: number
    label: Highest Display Position
    sql: RANK() OVER(ORDER BY ${avg_position} ASC)
  - measure: ctr_rank
    type: number
    label: Highest CTR
    sql: RANK() OVER(ORDER BY ${ctr_rate} DESC)
  - measure: click_rank
    type: number
    label: Most Clicked
    sql: RANK() OVER(ORDER BY ${hotel_click_count} DESC)
  - measure: book_rank
    type: number
    label: Best Sellers
    sql: RANK() OVER(ORDER BY ${hotel_book_count} DESC)
  - measure: view_rank
    type: number
    label: Most Viewed
    sql: RANK() OVER(ORDER BY ${hotel_index_view_count} DESC)
  - measure: price_rank
    type: number
    label: Rank Price Per Night (highest)
    sql: RANK() OVER(ORDER BY ${avg_price_per_night} DESC)
  - measure: conversion_rank
    type: number
    label: Highest Converters
    sql: RANK() OVER(ORDER BY ${conversion_rate} DESC)