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,

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 
    user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    replace(lower(event_name),' ','_') event_name, 
    count(*) events
  FROM `ra-development.analytics_277223877.events_*`
  GROUP BY 
    1, 2, 3, 4),
session_events_pivoted as (

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,

markrittman

with
  meeting_bookings as # meetings have names and email addresses, but no anonymous_id
  (
  select
    # union of three events that are logically the same but were recorded as three different events historically
    id,
    timestamp,
    email as anonymous_id,
    cast(null as string) as context_ip,
    cast(null as string) as context_page_path,

markrittman

use role accountadmin;

create or replace role loader;
create or replace role transformer;
create or replace role reporter;
create or replace role looker_role;
grant role loader to role sysadmin;
grant role transformer to role sysadmin;
grant role reporter to role sysadmin;
grant role looker_role to role sysadmin;