Skip to content

Instantly share code, notes, and snippets.

@markrittman
Created December 2, 2022 20:24
Show Gist options
  • Save markrittman/4687428d095092213b26f06dfaa31b75 to your computer and use it in GitHub Desktop.
Save markrittman/4687428d095092213b26f06dfaa31b75 to your computer and use it in GitHub Desktop.
LookML view that aggregates Rudderstack page traffic data and formats the page URL for joining to Wordpress wp_posts.post_name column
view: rudderstack_weekly_page_stats {
derived_table: {
sql: SELECT
title,
CASE
WHEN path = '/' THEN 'home'
ELSE
COALESCE(LOWER(ARRAY_REVERSE( SPLIT(RTRIM(path,'/'),'/'))[SAFE_OFFSET(0)]),'home')
END
AS page_name,
DATE_TRUNC(DATE(timestamp),week) AS traffic_week,
COUNT(DISTINCT anonymous_id) total_unique_visitors,
COUNT(*) AS total_page_views,
SUM(CASE
WHEN referring_domain IS NOT NULL AND referring_domain NOT LIKE '%rittmananalytics.com%' THEN 1
ELSE
0
END
) AS referral_page_views
FROM
`ra-development.rudderstack_ra_website.pages`
GROUP BY
1,
2,
3
;;
}
dimension: title {
type: string
sql: ${TABLE}.title ;;
}
dimension: page_name {
hidden: yes
type: string
sql: ${TABLE}.page_name ;;
}
dimension_group: traffic {
type: time
timeframes: [raw,week,quarter,year]
datatype: timestamp
sql: timestamp(${TABLE}.traffic_week) ;;
}
dimension: pk {
type: string
sql: concat(${page_name},${traffic_raw}) ;;
}
dimension: unique_visitors {
hidden: yes
type: number
sql: ${TABLE}.total_unique_visitors ;;
}
dimension: page_views {
hidden: yes
type: number
sql: ${TABLE}.total_page_views ;;
}
dimension: referral_page_views {
hidden: yes
type: number
sql: ${TABLE}.referral_page_views ;;
}
measure: total_page_views {
type: sum
sql: ${page_views} ;;
}
measure: total_unique_visitors {
type: sum
sql: ${unique_visitors} ;;
}
measure: total_referral_page_views {
type: sum
sql: ${referral_page_views} ;;
}
set: detail {
fields: [
title,
page_name,
traffic_week,
total_unique_visitors,
total_page_views,
referral_page_views
]
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment