markrittman

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%'

markrittman

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%'

markrittman

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'
  

markrittman

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,

markrittman

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

markrittman

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

markrittman

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,

markrittman

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,

markrittman

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 ),

markrittman

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,