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 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 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;

markrittman

with
  query_cost as (
  select
    date_trunc(date(creation_time),month) as billing_month,
    user_email,
    query,
    job_type,
    project_id,
    priority,
    concat(destination_table.dataset_id,'.',destination_table.table_id) as destination_table,

markrittman

WITH
  events AS (
  SELECT
    events_true_tstamp_time as event_ts,
    events_user_fingerprint AS user_pseudo_id,
    events_user_id as user_id,
    mkt_network as channel,	
    events_mkt_medium as medium,	
    events_mkt_source as source,	
    events_mkt_campaign as campaign,

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

SELECT
  event_name,
  event_params.key AS event_params_key
FROM
  `ra-development.analytics_277223877.events_*` AS t
  CROSS JOIN UNNEST (t.event_params) AS event_params
  group by 1,2
  order by 1,2

markrittman

  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS session_start_ts,
    CAST(LEAD(TIMESTAMP_MICROS(event_timestamp),1) OVER (PARTITION BY CONCAT(user_pseudo_id)
      ORDER BY
        event_timestamp) AS timestamp) AS session_end_ts,
    (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,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page_path,