This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with segment_pageviews as ( | |
select | |
timestamp_trunc(p.received_at,day) as day_day, | |
context_page_title as page_title, | |
count(distinct id) as page_views | |
from | |
`ra-development.company_website.pages_view` p | |
where | |
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01' | |
and context_page_url not like '%info.rittmananalytics.com%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with segment_pageviews as ( | |
select | |
timestamp_trunc(p.received_at,day) as day_day, | |
context_page_title as page_title, | |
count(distinct id) as page_views | |
from | |
`ra-development.company_website.pages_view` p | |
where | |
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01' | |
and context_page_url not like '%info.rittmananalytics.com%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with segment_pageviews as ( | |
select | |
'segment' as source, id, timestamp_trunc(timestamp,HOUR) as timestamp_minute, path, context_page_title, anonymous_id, user_id, context_ip | |
from | |
`ra-development.company_website.pages_view` p | |
where | |
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-10' | |
and context_page_title = 'Adding Looker Regression Tests to the dbtCloud CI/CD Pipeline using Spectacles — Rittman Analytics' | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
events AS ( | |
SELECT | |
TIMESTAMP_MICROS(event_timestamp) AS event_ts, | |
CONCAT(user_pseudo_id,'-',event_name,'-',CAST(event_timestamp AS STRING)) AS event_id, | |
user_pseudo_id AS user_pseudo_id, | |
user_id, | |
traffic_source.name AS utm_channel, | |
traffic_source.medium AS utm_medium, | |
traffic_source.source AS utm_source, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH wordpress_posts AS (SELECT * FROM | |
( | |
SELECT | |
p.post_title AS Post_Title, | |
'Content' AS post_type, | |
t.name AS Category, | |
p.post_date AS Post_Date, | |
p.post_name AS Post_Name, | |
p.comment_count AS comment_count, | |
p.post_excerpt |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
view: google_search_console_weekly_stats { | |
derived_table: { | |
sql: WITH | |
page_keyword_performance AS ( | |
SELECT | |
CASE | |
WHEN page = 'https://rittmananalytics.com/' THEN 'home' | |
ELSE | |
COALESCE(LOWER(ARRAY_REVERSE( SPLIT(RTRIM(page,'/'),'/'))[SAFE_OFFSET(0)]),'home') | |
END |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
view: wordpress_posts { | |
derived_table: { | |
sql: SELECT * FROM | |
( | |
SELECT | |
p.post_title AS Post_Title, | |
'Content' AS post_type, | |
t.name AS Category, | |
p.post_date AS Post_Date, | |
p.post_name AS Post_Name, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
view: kpi_scorecard { | |
derived_table: { | |
sql: with delivery_kpi_1 as ( | |
( | |
SELECT | |
timestamp(date_trunc(contact_utilization_fact.forecast_week,MONTH)) as kpi_month, | |
'Utilization' as kpi_name, | |
max(0.5) as category_weighting_pct, | |
COALESCE(SUM(contact_utilization_fact.actual_billable_hours ), | |
0) / COALESCE(SUM(contact_utilization_fact.target_billable_capacity ), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with events as ( | |
select | |
anonymous_id, | |
user_id, | |
cast(null as string) as name, | |
cast(null as string) as email, | |
timestamp, | |
'page_view' as event_type, | |
concat(split(context_ip,'.')[safe_offset(0)],'.***.***.',split(context_ip,'.')[safe_offset(3)]) as context_ip, | |
context_page_path, |
NewerOlder