Fact Click Stream V2

view: fact_click_stream_v2 {
  sql_table_name: FactClickStream_V2 ;;

  dimension: basket_id {
    sql: ${TABLE}.BasketID ;;
  }

  dimension: basket_idhas_order {
    sql: ${TABLE}.BasketIDHasOrder ;;
  }

  dimension: basket_idwith_order {
    sql: ${TABLE}.BasketIDWithOrder ;;
  }

  dimension: basket_qty {
    type: int
    sql: ${TABLE}.BasketQty ;;
  }

  dimension: converted {
    type: yesno
    sql: ${basket_qty} > 0 ;;
  }

  dimension: basket_value {
    type: int
    sql: ${TABLE}.BasketValue ;;
  }

  dimension: checkout_type {
    type: int
    sql: ${TABLE}.CheckoutType ;;
  }

  dimension: cookie_id {
    sql: ${TABLE}.CookieID ;;
  }

  dimension: country_id {
    type: int
    sql: ${TABLE}.CountryId ;;
  }

  dimension: dim_page_number_last_listing_page_visited {
    sql: ${TABLE}.DimPageNumberLastListingPageVisited ;;
  }

  dimension: distance_clicks_session_start {
    type: int
    sql: ${TABLE}.DistanceClicksSessionStart ;;
  }

  dimension: distance_clicks_session_start_description {
    sql: ${TABLE}.DistanceClicksSessionStartDescription ;;
  }

  dimension: duration {
    type: number
    sql: ${TABLE}.Duration ;;
  }

  dimension: duration_inactive {
    type: number
    sql: ${TABLE}.DurationInactive ;;
  }

  dimension: duration_with_user_actions {
    type: number
    sql: ${TABLE}.DurationWithUserActions ;;
  }

  dimension: entrance {
    sql: ${TABLE}.Entrance ;;
  }

  dimension: entrance_bounce {
    sql: ${TABLE}.EntranceBounce ;;
  }

  dimension: entry_page {
    sql: ${TABLE}.EntryPage ;;
  }

  dimension: exit_page {
    sql: ${TABLE}.ExitPage ;;
  }

  dimension: geo_subfolder {
    sql: ${TABLE}.GeoSubfolder ;;
  }

  dimension: is_flat_rate_ship {
    sql: ${TABLE}.IsFlatRateShip ;;
  }

  dimension: is_logged {
    sql: ${TABLE}.isLogged ;;
  }

  dimension: is_new_visit {
    sql: ${TABLE}.IsNewVisit ;;
  }

  dimension: is_registered_user {
    sql: ${TABLE}.IsRegisteredUser ;;
  }

  dimension: is_using_translate_content_sites {
    sql: ${TABLE}.IsUsingTranslateContentSites ;;
  }

  dimension: multi_select_was_used {
    sql: ${TABLE}.MultiSelectWasUsed ;;
  }

  dimension: navegate_by_listing_page {
    sql: ${TABLE}.NavegateByListingPage ;;
  }

  dimension: navigation_type {
    type: int
    sql: ${TABLE}.NavigationType ;;
  }

  dimension: non_bounce_visit {
    sql: ${TABLE}.NonBounceVisit ;;
  }

  dimension: number_items_loaded {
    type: int
    sql: ${TABLE}.NumberItemsLoaded ;;
  }

  dimension: number_of_sizes_available {
    type: int
    sql: ${TABLE}.NumberOfSizesAvailable ;;
  }

  dimension: order_from_portal {
    sql: ${TABLE}.orderFromPortal ;;
  }

  dimension: page_depth {
    type: int
    sql: ${TABLE}.PageDepth ;;
  }

  dimension: page_depth_description {
    sql: ${TABLE}.PageDepthDescription ;;
  }

  dimension: page_value_auxiliary {
    type: number
    sql: ${TABLE}.PageValueAuxiliary ;;
  }

  dimension: portal_ff {
    sql: ${TABLE}.PortalPlaceholder ;;
  }

  dimension: position_list {
    type: int
    sql: ${TABLE}.PositionList ;;
  }

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

  dimension: product_outof_stock {
    sql: ${TABLE}.ProductOutofStock ;;
  }

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

  dimension: session_id_final {
    sql: ${TABLE}.SessionIdFinal ;;
  }

  dimension: session_idnew_visit {
    sql: ${TABLE}.SessionIDNewVisit ;;
  }

  dimension: sizes_available {
    sql: ${TABLE}.SizesAvailable ;;
  }

  dimension: sk_begin_date {
    type: int
    sql: ${TABLE}.SK_BeginDate ;;
  }

  dimension: sk_begin_time {
    type: int
    sql: ${TABLE}.SK_BeginTime ;;
  }

  dimension: sk_campaign_first {
    type: int
    sql: ${TABLE}.SK_CampaignFirst ;;
  }

  dimension: sk_click_stream {
    type: number
    sql: ${TABLE}.SK_ClickStream ;;
  }

  dimension: sk_dim_product_performance_attributes {
    sql: ${TABLE}.SK_DimProductPerformanceAttributes ;;
  }

  dimension: sk_dim_site_content_attributes {
    sql: ${TABLE}.SK_DimSiteContentAttributes ;;
  }

  dimension: sk_entry_page {
    type: int
    sql: ${TABLE}.SK_EntryPage ;;
  }

  dimension: sk_exit_page {
    type: int
    sql: ${TABLE}.SK_ExitPage ;;
  }

  dimension: sk_geo_subfolder {
    type: int
    sql: ${TABLE}.SK_GeoSubfolder ;;
  }

  dimension: sk_geography_bill {
    type: int
    sql: ${TABLE}.SK_GeographyBill ;;
  }

  dimension: sk_geography_ship {
    type: int
    sql: ${TABLE}.SK_GeographyShip ;;
  }

  dimension: sk_keyword {
    type: int
    sql: ${TABLE}.SK_Keyword ;;
  }

  dimension: sk_next_page {
    type: int
    sql: ${TABLE}.SK_NextPage ;;
  }

  dimension: sk_page {
    type: int
    sql: ${TABLE}.SK_Page ;;
  }

  dimension: sk_page_view_date {
    type: int
    sql: ${TABLE}.SK_PageViewDate ;;
  }

  dimension: sk_page_view_date_tzuser {
    type: int
    sql: ${TABLE}.SK_PageViewDate_TZUser ;;
  }

  dimension: sk_page_view_time {
    type: int
    sql: ${TABLE}.SK_PageViewTime ;;
  }

  dimension: sk_page_view_time_tzuser {
    type: int
    sql: ${TABLE}.SK_PageViewTime_TZUser ;;
  }

  dimension: sk_previous_page {
    type: int
    sql: ${TABLE}.SK_PreviousPage ;;
  }

  dimension: sk_product {
    type: int
    sql: ${TABLE}.SK_Product ;;
  }

  dimension: sk_referral {
    type: int
    sql: ${TABLE}.SK_Referral ;;
  }

  dimension: sk_session_traffic_source_first {
    type: int
    sql: ${TABLE}.SK_SessionTrafficSourceFirst ;;
  }

  dimension: sk_store {
    type: int
    sql: ${TABLE}.SK_Store ;;
  }

  dimension: sk_user {
    type: int
    sql: ${TABLE}.SK_User ;;
  }

  dimension: sk_user_action_urlreference {
    type: int
    sql: ${TABLE}.SK_UserActionURLReference ;;
  }

  dimension: sk_user_agent {
    type: int
    sql: ${TABLE}.SK_UserAgent ;;
  }

  dimension: sk_user_country_first {
    type: int
    sql: ${TABLE}.SK_UserCountryFirst ;;
  }

  dimension: sk_user_country_last {
    type: int
    sql: ${TABLE}.SK_UserCountryLast ;;
  }

  dimension: sk_user_session {
    type: number
    sql: ${TABLE}.SK_UserSession ;;
  }

  dimension: sk_user_subfolder {
    type: int
    sql: ${TABLE}.SK_UserSubfolder ;;
  }

  dimension: sk_visit_attributes {
    sql: ${TABLE}.SK_VisitAttributes ;;
  }

  dimension: sk_visitor_engagement_page_depth {
    type: int
    sql: ${TABLE}.SK_VisitorEngagementPageDepth ;;
  }

  dimension: sk_visitor_engagement_recency {
    type: int
    sql: ${TABLE}.SK_VisitorEngagementRecency ;;
  }

  dimension: sk_visitor_engagement_visit_duration {
    type: int
    sql: ${TABLE}.SK_VisitorEngagementVisitDuration ;;
  }

  dimension: sk_visitor_time_to_purchase {
    type: int
    sql: ${TABLE}.SK_VisitorTimeToPurchase ;;
  }

  dimension: sk_visitor_visits_to_purchase {
    type: int
    sql: ${TABLE}.SK_VisitorVisitsToPurchase ;;
  }

  dimension: stock_qty_end_of_day {
    type: number
    sql: ${TABLE}.StockQtyEndOfDay ;;
  }

  dimension: subfolder {
    sql: ${TABLE}.Subfolder ;;
  }

  dimension: total_clicks {
    type: int
    sql: ${TABLE}.TotalClicks ;;
  }

  dimension: unique_user_actions {
    type: int
    sql: ${TABLE}.UniqueUserActions ;;
  }

  dimension: unit_full_price_gbp {
    sql: ${TABLE}.UnitFullPrice_GBP ;;
  }

  dimension: unit_price_gbp {
    sql: ${TABLE}.UnitPrice_GBP ;;
  }

  dimension: user_id {
    type: int
    sql: ${TABLE}.UserID ;;
  }

  dimension: user_ididentified {
    type: int
    sql: ${TABLE}.UserIDIdentified ;;
  }

  dimension: user_subfolder {
    sql: ${TABLE}.UserSubfolder ;;
  }

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: visits {
    type: count_distinct
    sql: ${session_id_final} ;;
  }

  measure: converted_visits {
    type: count_distinct
    filters: [converted: "True"]
    sql: ${session_id_final} ;;
  }

  measure: conversion_rate {
    type: number
    value_format: "##.00%"
    sql: 1.0*${converted_visits}/${visits} ;;
  }
}

- view: fact_click_stream_v2
  sql_table_name: FactClickStream_V2
  fields:
  - dimension: basket_id
    sql: ${TABLE}.BasketID
  - dimension: basket_idhas_order
    sql: ${TABLE}.BasketIDHasOrder
  - dimension: basket_idwith_order
    sql: ${TABLE}.BasketIDWithOrder
  - dimension: basket_qty
    type: int
    sql: ${TABLE}.BasketQty
  - dimension: converted
    type: yesno
    sql: ${basket_qty} > 0
  - dimension: basket_value
    type: int
    sql: ${TABLE}.BasketValue
  - dimension: checkout_type
    type: int
    sql: ${TABLE}.CheckoutType
  - dimension: cookie_id
    sql: ${TABLE}.CookieID
  - dimension: country_id
    type: int
    sql: ${TABLE}.CountryId
  - dimension: dim_page_number_last_listing_page_visited
    sql: ${TABLE}.DimPageNumberLastListingPageVisited
  - dimension: distance_clicks_session_start
    type: int
    sql: ${TABLE}.DistanceClicksSessionStart
  - dimension: distance_clicks_session_start_description
    sql: ${TABLE}.DistanceClicksSessionStartDescription
  - dimension: duration
    type: number
    sql: ${TABLE}.Duration
  - dimension: duration_inactive
    type: number
    sql: ${TABLE}.DurationInactive
  - dimension: duration_with_user_actions
    type: number
    sql: ${TABLE}.DurationWithUserActions
  - dimension: entrance
    sql: ${TABLE}.Entrance
  - dimension: entrance_bounce
    sql: ${TABLE}.EntranceBounce
  - dimension: entry_page
    sql: ${TABLE}.EntryPage
  - dimension: exit_page
    sql: ${TABLE}.ExitPage
  - dimension: geo_subfolder
    sql: ${TABLE}.GeoSubfolder
  - dimension: is_flat_rate_ship
    sql: ${TABLE}.IsFlatRateShip
  - dimension: is_logged
    sql: ${TABLE}.isLogged
  - dimension: is_new_visit
    sql: ${TABLE}.IsNewVisit
  - dimension: is_registered_user
    sql: ${TABLE}.IsRegisteredUser
  - dimension: is_using_translate_content_sites
    sql: ${TABLE}.IsUsingTranslateContentSites
  - dimension: multi_select_was_used
    sql: ${TABLE}.MultiSelectWasUsed
  - dimension: navegate_by_listing_page
    sql: ${TABLE}.NavegateByListingPage
  - dimension: navigation_type
    type: int
    sql: ${TABLE}.NavigationType
  - dimension: non_bounce_visit
    sql: ${TABLE}.NonBounceVisit
  - dimension: number_items_loaded
    type: int
    sql: ${TABLE}.NumberItemsLoaded
  - dimension: number_of_sizes_available
    type: int
    sql: ${TABLE}.NumberOfSizesAvailable
  - dimension: order_from_portal
    sql: ${TABLE}.orderFromPortal
  - dimension: page_depth
    type: int
    sql: ${TABLE}.PageDepth
  - dimension: page_depth_description
    sql: ${TABLE}.PageDepthDescription
  - dimension: page_value_auxiliary
    type: number
    sql: ${TABLE}.PageValueAuxiliary
  - dimension: portal_ff
    sql: ${TABLE}.PortalPlaceholder
  - dimension: position_list
    type: int
    sql: ${TABLE}.PositionList
  - dimension: product_id
    type: int
    sql: ${TABLE}.ProductID
  - dimension: product_outof_stock
    sql: ${TABLE}.ProductOutofStock
  - dimension: session_id
    sql: ${TABLE}.SessionId
  - dimension: session_id_final
    sql: ${TABLE}.SessionIdFinal
  - dimension: session_idnew_visit
    sql: ${TABLE}.SessionIDNewVisit
  - dimension: sizes_available
    sql: ${TABLE}.SizesAvailable
  - dimension: sk_begin_date
    type: int
    sql: ${TABLE}.SK_BeginDate
  - dimension: sk_begin_time
    type: int
    sql: ${TABLE}.SK_BeginTime
  - dimension: sk_campaign_first
    type: int
    sql: ${TABLE}.SK_CampaignFirst
  - dimension: sk_click_stream
    type: number
    sql: ${TABLE}.SK_ClickStream
  - dimension: sk_dim_product_performance_attributes
    sql: ${TABLE}.SK_DimProductPerformanceAttributes
  - dimension: sk_dim_site_content_attributes
    sql: ${TABLE}.SK_DimSiteContentAttributes
  - dimension: sk_entry_page
    type: int
    sql: ${TABLE}.SK_EntryPage
  - dimension: sk_exit_page
    type: int
    sql: ${TABLE}.SK_ExitPage
  - dimension: sk_geo_subfolder
    type: int
    sql: ${TABLE}.SK_GeoSubfolder
  - dimension: sk_geography_bill
    type: int
    sql: ${TABLE}.SK_GeographyBill
  - dimension: sk_geography_ship
    type: int
    sql: ${TABLE}.SK_GeographyShip
  - dimension: sk_keyword
    type: int
    sql: ${TABLE}.SK_Keyword
  - dimension: sk_next_page
    type: int
    sql: ${TABLE}.SK_NextPage
  - dimension: sk_page
    type: int
    sql: ${TABLE}.SK_Page
  - dimension: sk_page_view_date
    type: int
    sql: ${TABLE}.SK_PageViewDate
  - dimension: sk_page_view_date_tzuser
    type: int
    sql: ${TABLE}.SK_PageViewDate_TZUser
  - dimension: sk_page_view_time
    type: int
    sql: ${TABLE}.SK_PageViewTime
  - dimension: sk_page_view_time_tzuser
    type: int
    sql: ${TABLE}.SK_PageViewTime_TZUser
  - dimension: sk_previous_page
    type: int
    sql: ${TABLE}.SK_PreviousPage
  - dimension: sk_product
    type: int
    sql: ${TABLE}.SK_Product
  - dimension: sk_referral
    type: int
    sql: ${TABLE}.SK_Referral
  - dimension: sk_session_traffic_source_first
    type: int
    sql: ${TABLE}.SK_SessionTrafficSourceFirst
  - dimension: sk_store
    type: int
    sql: ${TABLE}.SK_Store
  - dimension: sk_user
    type: int
    sql: ${TABLE}.SK_User
  - dimension: sk_user_action_urlreference
    type: int
    sql: ${TABLE}.SK_UserActionURLReference
  - dimension: sk_user_agent
    type: int
    sql: ${TABLE}.SK_UserAgent
  - dimension: sk_user_country_first
    type: int
    sql: ${TABLE}.SK_UserCountryFirst
  - dimension: sk_user_country_last
    type: int
    sql: ${TABLE}.SK_UserCountryLast
  - dimension: sk_user_session
    type: number
    sql: ${TABLE}.SK_UserSession
  - dimension: sk_user_subfolder
    type: int
    sql: ${TABLE}.SK_UserSubfolder
  - dimension: sk_visit_attributes
    sql: ${TABLE}.SK_VisitAttributes
  - dimension: sk_visitor_engagement_page_depth
    type: int
    sql: ${TABLE}.SK_VisitorEngagementPageDepth
  - dimension: sk_visitor_engagement_recency
    type: int
    sql: ${TABLE}.SK_VisitorEngagementRecency
  - dimension: sk_visitor_engagement_visit_duration
    type: int
    sql: ${TABLE}.SK_VisitorEngagementVisitDuration
  - dimension: sk_visitor_time_to_purchase
    type: int
    sql: ${TABLE}.SK_VisitorTimeToPurchase
  - dimension: sk_visitor_visits_to_purchase
    type: int
    sql: ${TABLE}.SK_VisitorVisitsToPurchase
  - dimension: stock_qty_end_of_day
    type: number
    sql: ${TABLE}.StockQtyEndOfDay
  - dimension: subfolder
    sql: ${TABLE}.Subfolder
  - dimension: total_clicks
    type: int
    sql: ${TABLE}.TotalClicks
  - dimension: unique_user_actions
    type: int
    sql: ${TABLE}.UniqueUserActions
  - dimension: unit_full_price_gbp
    sql: ${TABLE}.UnitFullPrice_GBP
  - dimension: unit_price_gbp
    sql: ${TABLE}.UnitPrice_GBP
  - dimension: user_id
    type: int
    sql: ${TABLE}.UserID
  - dimension: user_ididentified
    type: int
    sql: ${TABLE}.UserIDIdentified
  - dimension: user_subfolder
    sql: ${TABLE}.UserSubfolder
  - dimension_group: date_last_user_action_on_page
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateLastUserActionOnPage
  - dimension_group: page_view
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.PageViewDate
  - dimension_group: row_active
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.RowActiveDate
  - dimension_group: row_exclude
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.RowExcludeDate
  - dimension_group: row_update
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.RowUpdateDate
  - measure: count
    type: count
  - measure: visits
    type: count_distinct
    sql: ${session_id_final}
  - measure: converted_visits
    type: count_distinct
    filters:
    - converted: True
    sql: ${session_id_final}
  - measure: conversion_rate
    type: number
    value_format: '##.00%'
    sql: 1.0*${converted_visits}/${visits}