Skip to content

Instantly share code, notes, and snippets.

@mattm
Created April 6, 2018 18:25
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mattm/911cad99afc5dc842eede5af2a117186 to your computer and use it in GitHub Desktop.
Looker Mixpanel Events
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} ;;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment