markrittman

if [ "$#" -ne 4 ]
then
  echo "Usage: ./patchbv client_id tracking_id view_name nested_flatted_flag i.e. patchbv 37028 dnata_travel experience false"
  exit 1
fi
echo "patching qubit-client-$1 $2 $3"
gcloud auth login mark.rittman@qubit.com
gcloud config set project qubit-client-"$1"
echo "deleting existing objects for $2"
bq rm -f -t qubit-client-"$1":qubit_bi_"$2".qp_bi_"$3"_all_time_up_to_yesterday

markrittman

if [ "$#" -ne 1 ]
then
  echo "Usage: ./check_materialization_logs.sh tracking_id i.e. check_materialization_logs.sh dnata_travel"
  exit 1
fi
echo "checking status of tracking_id $1"
gcloud auth login mark.rittman@qubit.com
gcloud config set project qubit-datateam
bq query "SELECT * from [qubit-datateam:livetap.bi_data_completeness_streamed_log] where meta_trackingId = '"${1}"' and ts = (select max(ts) from livetap.bi_data_completeness_streamed_log where meta_trackingId = '"${1}"') order by ts desc"
bq query "SELECT ts,unique_id, tracking_id, processed_date, status FROM [qubit-datateam:livetap.bi_data_materialisation_streamed_log] WHERE tracking_id = '"${1}"' order by ts desc limit 15"

markrittman

##Additional LookML view (transaction_sequence.view.lkml)##

view: transaction_sequence {
  view_label: "Product Interactions and Sales"
  derived_table: {
    sql: WITH view AS (SELECT
        * EXCEPT(event_number)
      FROM
        (SELECT
          TIMESTAMP_ADD(meta_serverTs, INTERVAL 0 MICROSECOND) AS property_event_ts,

markrittman

SELECT
	date_trunc('hours'::text, session_start_ts) AS session_start_hour,
	session_start_ts,
	date_diff ('seconds'::text,
		pg_catalog.lead(session_start_ts, 1) OVER (PARTITION BY user_id ORDER BY session_start_ts DESC),
		session_start_ts) AS secs_between_sessions,
	user_id,
	global_session_id,
	user_session_id,
	page_views_in_session,

markrittman

WITH
  actuals AS (
  SELECT
    shopify_shop,
    CASE
      WHEN billing_address__country_code = 'GB' THEN 'UK'
      WHEN billing_address__country_code = 'US' THEN 'US'
    ELSE
    'RoW'
  END

markrittman

with page_views as (
SELECT *,
       case when split(context_page_path,'/')[safe_offset(1)] = 'products' then 'Product Category Page'
            when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 'Product Details Page'
            when split(context_page_path,'/')[safe_offset(1)] = 'search-products' then 'Search Page'
            else 'Home Page' end as page_type,
       case when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 
            replace(replace(replace(replace(replace(split(split(context_page_path,'/')[safe_offset(2)],'_')[safe_offset(0)],
              '-white',''),'-blush-pink',''),'-black',''),'-black-black',''),'-',' ') end as product_name,
       case when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 

markrittman

WITH
  history AS (
  SELECT
    *
  FROM (
    SELECT
      h.query_id,
      h.history_created_date AS query_ts,
      h.user_id AS user_id,
      h.history_query_run_count AS query_count,

markrittman

SELECT
  TABLE_SCHEMA, 
  count(distinct table_name) as table_count,
  CASE
    WHEN IS_SYSTEM_DEFINED = 'YES' THEN 'Ingestion-Time'
    WHEN data_type = 'TIMESTAMP' THEN concat('Time-unit column')
    WHEN data_type = 'INT64' THEN concat('Integer Range')
END
  AS partitioning_type
FROM

markrittman

https://CLIENT_DOMAIN.looker.com/explore/system__activity/history?fields=look.title,history.completed_week,history.query_run_count,history.average_runtime&pivots=history.completed_week&fill_fields=history.completed_week&f[history.completed_week]=4+weeks&f[look.title]=-EMPTY&sorts=history.query_run_count+desc+0,history.completed_week&limit=500&vis=%7B%22show_view_names%22%3Afalse%2C%22show_row_numbers%22%3Atrue%2C%22transpose%22%3Afalse%2C%22truncate_text%22%3Atrue%2C%22hide_totals%22%3Afalse%2C%22hide_row_totals%22%3Afalse%2C%22size_to_fit%22%3Atrue%2C%22table_theme%22%3A%22white%22%2C%22limit_displayed_rows%22%3Afalse%2C%22enable_conditional_formatting%22%3Atrue%2C%22header_text_alignment%22%3A%22left%22%2C%22header_font_size%22%3A%2212%22%2C%22rows_font_size%22%3A%2212%22%2C%22conditional_formatting_include_totals%22%3Afalse%2C%22conditional_formatting_include_nulls%22%3Afalse%2C%22show_sql_query_menu_options%22%3Afalse%2C%22show_totals%22%3Atrue%2C%22show_row_totals%22%3Atrue%2C%22series_cell_visualization

markrittman

https://LOOKER_DOMAIN.looker.com/explore/system__activity/history?fields=history.dashboard_run_count,history.average_runtime,history.completed_week,dashboard.title&pivots=history.completed_week&fill_fields=history.completed_week&f[history.dashboard_session]=-NULL&f[history.completed_week]=4+weeks&f[dashboard.title]=-NULL&sorts=history.completed_week+desc,impact+desc+1&limit=500&vis=%7B%22show_view_names%22%3Afalse%2C%22show_row_numbers%22%3Atrue%2C%22transpose%22%3Afalse%2C%22truncate_text%22%3Atrue%2C%22hide_totals%22%3Afalse%2C%22hide_row_totals%22%3Afalse%2C%22size_to_fit%22%3Atrue%2C%22table_theme%22%3A%22white%22%2C%22limit_displayed_rows%22%3Afalse%2C%22enable_conditional_formatting%22%3Atrue%2C%22header_text_alignment%22%3A%22left%22%2C%22header_font_size%22%3A%2212%22%2C%22rows_font_size%22%3A%2212%22%2C%22conditional_formatting_include_totals%22%3Afalse%2C%22conditional_formatting_include_nulls%22%3Afalse%2C%22color_application%22%3A%7B%22collection_id%22%3A%22legacy%22%2C%22palette_id%22%3A%22looker