Looker Mixpanel Events

mp_events.lookml

view: mp_events {
  sql_table_name: mp.event ;;

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

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

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

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

  dimension: full_url {
    type: string
    sql: ${TABLE}.current_url ;;
  }

  dimension: has_url_parameters {
    type: yesno
    hidden: yes
    sql: STRPOS(${full_url}, "?") > 0 ;;
  }

  dimension: current_url_without_parameters {
    hidden: yes
    label: "Current URL without Parameters"
    type: string
    sql:
      CASE
        WHEN ${has_url_parameters} THEN SUBSTR(${full_url}, 0, STRPOS(${full_url}, "?") - 1)
        ELSE ${full_url}
      END ;;
  }

  dimension: has_url_hash {
    type: yesno
    hidden: yes
    sql: STRPOS(${current_url_without_parameters}, "#") > 0 ;;
  }

  dimension: current_url {
    type: string
    sql:
     CASE
        WHEN ${has_url_hash} THEN SUBSTR(${current_url_without_parameters}, 0, STRPOS(${current_url_without_parameters}, "#") - 1)
        ELSE ${current_url_without_parameters}
      END ;;
  }

  dimension: current_url_path {
    type: string
    sql: SUBSTR(${current_url}, STRPOS(${current_url}, "helpscout.net") + LENGTH("helpscout.net")) ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension_group: time {
    label: "Event"
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.time ;;
  }

  dimension: utm_source {
    label: "UTM Source"
    type: string
    sql: JSON_EXTRACT_SCALAR(${properties}, "$.utm_source") ;;
  }

  dimension: utm_campaign {
    label: "UTM Campaign"
    type: string
    sql: JSON_EXTRACT_SCALAR(${properties}, "$.utm_campaign") ;;
  }

  dimension: utm_term {
    label: "UTM Term"
    type: string
    sql: JSON_EXTRACT_SCALAR(${properties}, "$.utm_term") ;;
  }

  dimension: utm_medium {
    label: "UTM Medium"
    type: string
    sql: JSON_EXTRACT_SCALAR(${properties}, "$.utm_medium") ;;
  }

  dimension: utm_content {
    label: "UTM Content"
    type: string
    sql: JSON_EXTRACT_SCALAR(${properties}, "$.utm_content") ;;
  }

  measure: count {
    label: "Total Events"
    type: count
    drill_fields: [event_id, name]
  }

  measure: unique_visitors {
    type: count_distinct
    sql: ${distinct_id} ;;
  }
}