<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Overview
This notebook provides code to populate a dashboard that compares audience behavior based on the GA360 BQ Export. This is particularly useful for customers interested in understanding behavior prior to an observed event, which is useful for behavior-based segmentation, site optimization, or as inputs for a predictive model.
In addition to a sound GA360 tagging implementation, you will need access to the source dataset as a viewer, and access to run BQ jobs on a GCP project.
Dataset
This notebook is meant be a scalable solution that works with any GA360 BQ Export. This particular example utilizes the GA360 data from the Google Merchandise Store, publicly available here. Due to the limited nature of the Merchandise Store Data, not all aspects of this notebook will produce results; try it on your own (corporate) data!
Objective
The resulting dashboard provides a quick solution to visualize differences in audience behavior based on the Google Analytics 360 BigQuery Export. Without customization, this default to comparing the general population vs. the behavior of a particular audience of interest, e.g. users who make a purchase online, or who purchase above a certain dollar amount. These insights can be used in a variety of ways, which include (but are not limited to)
Step1:  Authenticate your GCP account
If you are using AI Platform Notebooks, your environment is already
authenticated. Skip this step.
If you are using Colab, run the cell below and follow the instructions
when prompted to authenticate your account via oAuth.
Otherwise, follow these steps
Step2:  Create a BigQuery dataset
If you already have a dataset ready to save tables to, skip this step.
Set the name of your BigQuery dataset below. Dataset IDs
must be alphanumeric (plus underscores) and must be at most 1024 characters
long.
Step3:  Validate that your dataset created successfully (this will throw an error if there is no dataset)
Step4:  Import libraries and define parameters
PROJECT_ID_BILLING is where querying costs will be billed to
GA_* fields are where the GA360 BQ Export is stored
START_DATE and END_DATE note the date range for analysis
UTC_ADJUSTMENT adjusts for timezone for the appropriate fields*
*Note that the GA360 BQ Export has all timestamps in POSIX time
Step7:  Define target audience and filters
user_label_query is used to segment the GA360 BQ Export between your target audience and general population.
query_filter is used to further define all data that is aggregated
Step10:  Query custom dimensions to isolate fields with fewer unique values, which will be visualized
Start with session-level custom dimensions
Step13:  Repeat for hit level custom dimensions
Step18:  Programmatically write a query that pulls distinct users, by class, for features and every custom dimension (session & hit level).
If you want to view the query, set View_Query to True in the cell below.
Step19:  Save results to BQ. As-is, only writes if there is no table that already exists.
Step20:  Visualize results with a pre-built Data Studio dashboard
<ASSISTANT_TASK:>
Python Code:
PROJECT_ID_BILLING = "" # Set the project ID
! gcloud config set project $PROJECT_ID_BILLING
Explanation: Overview
This notebook provides code to populate a dashboard that compares audience behavior based on the GA360 BQ Export. This is particularly useful for customers interested in understanding behavior prior to an observed event, which is useful for behavior-based segmentation, site optimization, or as inputs for a predictive model.
In addition to a sound GA360 tagging implementation, you will need access to the source dataset as a viewer, and access to run BQ jobs on a GCP project.
Dataset
This notebook is meant be a scalable solution that works with any GA360 BQ Export. This particular example utilizes the GA360 data from the Google Merchandise Store, publicly available here. Due to the limited nature of the Merchandise Store Data, not all aspects of this notebook will produce results; try it on your own (corporate) data!
Objective
The resulting dashboard provides a quick solution to visualize differences in audience behavior based on the Google Analytics 360 BigQuery Export. Without customization, this default to comparing the general population vs. the behavior of a particular audience of interest, e.g. users who make a purchase online, or who purchase above a certain dollar amount. These insights can be used in a variety of ways, which include (but are not limited to):
  - Provide guidance to create rules-based audiences
  - Recommend potential ways to optimize check-out flow or site design
  - Highlight potential features for a propensity model
Costs
This tutorial uses billable components of Google Cloud Platform (GCP):
BigQuery
Learn about BigQuery pricing, and use the Pricing
Calculator
to generate a cost estimate based on your projected usage.
Details
The insights and analysis offered by GA360 are numerous, and this notebook does not intend to cover all of them. Here is a list of features included in this example:
- Traffic source (trafficSource.medium)
- DMA
- Time visited by daypart
- Time visited by day
- Device category (device.deviceCategory)
- Page path level 1 (hits.page.pagePathLevel1)
- Ecommerce action (hits.eCommerceAction.action_type)
- Product engagement (hits.product.v2ProductCategory)
- Browser (device.browser)
- total sessions 
- page views
- average time per page
- average session depth (page views per session)
- distinct DMAs (for users on mobile, signifies if they are traveling or not)
- session & hit level custom dimensions
Notes on data output:
Continuous variables generate histograms and cut off the top 0.5% of data
Custom dimensions will only populate if they are setup on the GA360 implementation, and are treated as categorical features
As-is, only custom dimension indices 50 or lower will be visualized; you will need to edit the dashboard to look at distribution of indices above 50. All custom dimensions will be evaluated by the query, so will be present in the underlying dataset.
Set up your GCP project
If you are not already a GCP customer with GA360 and its BQ Export enabled, follow the steps below. If you want to simply implement this on you already-existing dataset, skip to "Import libraries and define parameters".
Select or create a GCP project.. When you first create an account, you get a $300 free credit towards your compute/storage costs.
Make sure that billing is enabled for your project.
Enable the BigQuery API.
Enter your project ID in the cell below. Then run the  cell to make sure the
Cloud SDK uses the right project for all the commands in this notebook.
Note: Jupyter runs lines prefixed with ! as shell commands, and it interpolates Python variables prefixed with $ into these commands.
End of explanation
import sys
# If you are running this notebook in Colab, run this cell and follow the
# instructions to authenticate your GCP account. This provides access to your
# Cloud Storage bucket and lets you submit training jobs and prediction
# requests.
if 'google.colab' in sys.modules:
  from google.colab import auth
  auth.authenticate_user()
# If you are running this notebook locally, replace the string below with the
# path to your service account key and run this cell to authenticate your GCP
# account.
else:
  %env GOOGLE_APPLICATION_CREDENTIALS ''
Explanation: Authenticate your GCP account
If you are using AI Platform Notebooks, your environment is already
authenticated. Skip this step.
If you are using Colab, run the cell below and follow the instructions
when prompted to authenticate your account via oAuth.
Otherwise, follow these steps:
In the GCP Console, go to the Create service account key
   page.
From the Service account drop-down list, select New service account.
In the Service account name field, enter a name.
From the Role drop-down list, select
   Machine Learning Engine > AI Platform Admin and
   Storage > Storage Object Admin.
Click Create. A JSON file that contains your key downloads to your
local environment.
Enter the path to your service account key as the
GOOGLE_APPLICATION_CREDENTIALS variable in the cell below and run the cell.
End of explanation
DATASET_NAME = "" # Name the dataset you'd like to save the output to
LOCATION = "US"
! bq mk --location=$LOCATION --dataset $PROJECT_ID_BILLING:$DATASET_NAME
Explanation: Create a BigQuery dataset
If you already have a dataset ready to save tables to, skip this step.
Set the name of your BigQuery dataset below. Dataset IDs
must be alphanumeric (plus underscores) and must be at most 1024 characters
long.
End of explanation
! bq show --format=prettyjson $PROJECT_ID_BILLING:$DATASET_NAME
Explanation: Validate that your dataset created successfully (this will throw an error if there is no dataset)
End of explanation
# Import libraries
import numpy as np
import pandas as pd
# Colab tools & bigquery library
from google.cloud import bigquery
bigquery.USE_LEGACY_SQL = False
pd.options.display.float_format = '{:.5f}'.format
GA_PROJECT_ID = "bigquery-public-data" 
GA_DATASET_ID = "google_analytics_sample" 
GA_TABLE_ID = "ga_sessions_*" 
START_DATE = "20170501" # Format is YYYYMMDD, for GA360 BQ Export
END_DATE = "20170801" 
UTC_ADJUSTMENT = -5 
client = bigquery.Client(project=PROJECT_ID_BILLING)
Explanation: Import libraries and define parameters
PROJECT_ID_BILLING is where querying costs will be billed to
GA_* fields are where the GA360 BQ Export is stored
START_DATE and END_DATE note the date range for analysis
UTC_ADJUSTMENT adjusts for timezone for the appropriate fields*
*Note that the GA360 BQ Export has all timestamps in POSIX time
End of explanation
# Define the query to identify your target audience with label 
# (1 for target, 0 for general population)
user_label_query = f
SELECT 
  fullvisitorId, 
  max(case when totals.transactions = 1 then 1 else 0 end) as label,
  min(case when totals.transactions = 1 then visitStartTime end) as event_session
FROM 
  `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}`
WHERE 
  _TABLE_SUFFIX BETWEEN '{START_DATE}' AND '{END_DATE}'
  AND geoNetwork.Country="United States"
GROUP BY 
  fullvisitorId
# query_filter -- Change this if you want to adjust WHERE clause in 
# the query. This will be inserted after all clauses selecting from 
# the GA360 BQ Export.
query_filter = f
WHERE (
  _TABLE_SUFFIX BETWEEN '{START_DATE}' AND '{END_DATE}'
  AND geoNetwork.Country="United States"
  AND (a.visitStartTime < IFNULL(event_session, 0)
      or event_session is null) )
Explanation: Define target audience and filters
user_label_query is used to segment the GA360 BQ Export between your target audience and general population.
query_filter is used to further define all data that is aggregated:
Removes behavior during or after the session in which the target event occurs
Subset to only the United States
Specify start and end date for analysis
End of explanation
# Set cut off for session-level custom dimensions, 
# then query BQ Export to pull relevant indices
sessions_cut_off =  20 # Max number of distinct values in custom dimensions
# By default, assume there will be custom dimensions at the session and hit level.
# Further down, set these to False if no appropriate CDs are found.
query_session_cd = True
# Unnest session-level custom dimensions a count values for each index
sessions_cd = f
SELECT index, count(distinct value) as dist_values
FROM (SELECT cd.index, cd.value, count(*) as sessions
    FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}`,
    UNNEST(customDimensions) as cd
    WHERE _TABLE_SUFFIX BETWEEN '{START_DATE}' AND '{END_DATE}'
    GROUP BY 1, 2
    ORDER BY 1, 2)
GROUP BY index
try:
  # Run a Standard SQL query with the project set explicitly
  sessions_custom_dimensions = client.query(sessions_cd, 
    project=PROJECT_ID_BILLING).to_dataframe()
  # Create list of session-level CDs to visualize
  session_index_list = sessions_custom_dimensions.loc[
    sessions_custom_dimensions.dist_values <= sessions_cut_off, 'index'].values
  session_index_exclude = sessions_custom_dimensions.loc[
    sessions_custom_dimensions.dist_values > sessions_cut_off, 'index'].values
  if len(session_index_list) == 0:
    query_session_cd = False
    print("No session-level indices found.")
  else:   
    print(fPrinting visualizations for the following session-level indices: \
    {session_index_list};\n
    Excluded the following custom dimension indices because they had more than \
    {sessions_cut_off} possible values: {session_index_exclude}\n \n)
except:
  query_session_cd = False
Explanation: Query custom dimensions to isolate fields with fewer unique values, which will be visualized
Start with session-level custom dimensions:
End of explanation
# Set cut off for hit-level custom dimensions, 
# then query BQ Export to pull relevant indices
hit_cut_off = 20 
# By default, assume there will be custom dimensions at the session and hit level.
# Further down, set these to False if no appropriate CDs are found.
query_hit_cd = True
hits_cd = f
SELECT index, count(distinct value) as dist_values
FROM (
  SELECT cd.index, cd.value, count(*) as hits
  FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}`,
  UNNEST(hits) as ht,
  UNNEST(ht.customDimensions) as cd
  WHERE _TABLE_SUFFIX BETWEEN '{START_DATE}' AND '{END_DATE}'
  GROUP BY 1, 2
  ORDER BY 1, 2 )
GROUP BY index
try:
  hits_custom_dimensions = client.query(hits_cd, project=PROJECT_ID_BILLING).to_dataframe()
  # Create list of hit-level CDs to visualize
  hit_index_list = hits_custom_dimensions.loc[hits_custom_dimensions.dist_values <= hit_cut_off, 'index'].values
  hit_index_exclude = hits_custom_dimensions.loc[hits_custom_dimensions.dist_values > hit_cut_off, 'index'].values
  if len(hit_index_list) == 0:
    query_hit_cd = False
    print("No hit-level indices found.")
  else:
    print(fPrinting visualizations for the following hit-level cds: \
    {hit_index_list};\n
    Excluded the following custom dimension indices because they had more than \
    {hit_cut_off} possible values: {hit_index_exclude}\n \n)
except:
  print("No hit-level custom dimensions found!")
  query_hit_cd = False
Explanation: Repeat for hit level custom dimensions:
End of explanation
# Write a big query that aggregates data to be used as dashboard input
# Set to True if you want to print the final query after it's generated
View_Query = False
final_query = f
WITH users_labeled as (
{user_label_query}
),
trafficSource_medium AS (
SELECT count(distinct CASE WHEN label = 1 THEN  fullvisitorId END) AS count_1_users,
count(distinct CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
trafficSource_medium AS trafficSource_medium,
'trafficSource_medium' AS type
FROM (
    SELECT a.fullvisitorId, 
    trafficSource.medium AS trafficSource_medium,
    label
    FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
    unnest (hits) as hits
    LEFT JOIN users_labeled b USING(fullvisitorId)
    {query_filter}
      GROUP BY 1,2,3)
GROUP BY trafficSource_medium),
dma_staging AS (
  SELECT a.fullvisitorId, 
    geoNetwork.metro AS metro,
    label,
    COUNT(*) AS visits
    FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a
    LEFT JOIN users_labeled b USING(fullvisitorId)
    {query_filter}
      GROUP BY 1,2,3),
--- Finds the dma with the most visits for each user. If it's a tie, arbitrarily picks one.
visitor_dma AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
metro AS dma,
'dma' AS type
FROM (
  SELECT fullvisitorId,
  metro, 
  label,
  ROW_NUMBER() OVER (PARTITION BY fullvisitorId ORDER BY visits DESC) AS row_num
  FROM dma_staging)
WHERE row_num = 1 
GROUP BY metro, type),
distinct_dma AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
distinct_dma AS distinct_dma,
'distinct_dma' AS type
FROM (
  SELECT COUNT(DISTINCT metro) as distinct_dma,
  fullvisitorId,
  label
  FROM dma_staging
  GROUP BY fullvisitorId, label)
GROUP BY distinct_dma),
  
-- Finds the daypart with the most pageviews for each user; adjusts for timezones and daylight savings time, loosely
visitor_common_daypart AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
'day_part' AS type,
daypart
FROM (
  SELECT fullvisitorId, daypart, label, ROW_NUMBER() OVER (PARTITION BY fullvisitorId ORDER BY pageviews DESC) AS row_num
  FROM (
    SELECT
      fullvisitorId,
      label,
      CASE WHEN hour_of_day >= 1 AND hour_of_day < 6 THEN '1_night_1_6' 
      WHEN hour_of_day >= 6 AND hour_of_day < 11 THEN '2_morning_6_11' 
      WHEN hour_of_day >= 11 AND hour_of_day < 14 THEN '3_lunch_11_14' 
      WHEN hour_of_day >= 14 AND hour_of_day < 17 THEN '4_afternoon_14_17' 
      WHEN hour_of_day >= 17 AND hour_of_day < 19 THEN '5_dinner_17_19' 
      WHEN hour_of_day >= 19 AND hour_of_day < 22 THEN '6_evening_19_23' 
      WHEN hour_of_day >= 22 OR hour_of_day = 0 THEN '7_latenight_23_1'
      END AS daypart, SUM(pageviews) AS pageviews
    FROM (
      SELECT a.fullvisitorId, b.label, EXTRACT(HOUR
        FROM TIMESTAMP_ADD(TIMESTAMP_SECONDS(visitStartTime), INTERVAL {UTC_ADJUSTMENT} HOUR)) AS hour_of_day,
        totals.pageviews AS pageviews
      FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a
      LEFT JOIN users_labeled b USING(fullvisitorId)
      {query_filter}
       )
    GROUP BY 1,2,3) )
WHERE row_num = 1 
GROUP BY type, daypart),
-- Finds the most common day based on pageviews
visitor_common_day AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
'DoW' AS type,
case when day = 1 then "1_Sunday"
when day = 2 then "2_Monday"
when day = 3 then "3_Tuesday"
when day = 4 then "4_Wednesday"
when day = 5 then "5_Thursday"
when day = 6 then "6_Friday"
when day = 7 then "7_Saturday" end as day
FROM (
  SELECT fullvisitorId, day, label, ROW_NUMBER() OVER (PARTITION BY fullvisitorId ORDER BY pages_viewed DESC) AS row_num
  FROM (
    SELECT a.fullvisitorId, 
    EXTRACT(DAYOFWEEK FROM PARSE_DATE('%Y%m%d',date)) AS day, 
    SUM(totals.pageviews) AS pages_viewed,
    b.label
    FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a
    LEFT JOIN users_labeled b USING(fullvisitorId)
    {query_filter}
    GROUP BY 1,2,4 ) )
WHERE row_num = 1 
GROUP BY type, day),
  
technology AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
deviceCategory AS deviceCategory,
browser AS browser,
'technology' AS type
FROM (
  SELECT fullvisitorId,
  deviceCategory,
  browser,
  label,
  ROW_NUMBER() OVER (PARTITION BY fullvisitorId ORDER BY visits DESC) AS row_num
  FROM (
    SELECT a.fullvisitorId, 
    device.deviceCategory AS deviceCategory,
    CASE WHEN device.browser LIKE 'Chrome%' THEN device.browser WHEN device.browser LIKE 'Safari%' THEN device.browser ELSE 'Other browser' END AS browser,
    b.label,
    COUNT(*) AS visits
    FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a
    LEFT JOIN users_labeled b USING(fullvisitorId)
    {query_filter}
      GROUP BY 1,2,3,4))
  WHERE row_num = 1 
GROUP BY deviceCategory,browser,type),
PPL1 AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
PPL1 AS PPL1,
'PPL1' AS type
FROM (
    SELECT a.fullvisitorId, 
    hits.page.pagePathLevel1 AS PPL1,
    b.label
    FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
    unnest (hits) as hits
    LEFT JOIN users_labeled b USING(fullvisitorId)
    {query_filter}
    GROUP BY 1,2,3)
GROUP BY PPL1),
ecomm_action AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
CASE WHEN ecomm_action = '1' THEN '1_Click product list'
WHEN ecomm_action = '2' THEN '2_Product detail view'
WHEN ecomm_action = '3' THEN '3_Add to cart'
WHEN ecomm_action = '4' THEN '4_Remove from cart'
WHEN ecomm_action = '5' THEN '5_Start checkout'
WHEN ecomm_action = '6' THEN '6_Checkout complete'
WHEN ecomm_action = '7' THEN '7_Refund'
WHEN ecomm_action = '8' THEN '8_Checkout options'
ELSE '9_No_ecomm_action'
END AS ecomm_action,
'ecomm_action' AS type
FROM (
    SELECT a.fullvisitorId, 
    hits.eCommerceAction.action_type AS ecomm_action,
    b.label
    FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
    unnest (hits) as hits
    LEFT JOIN users_labeled b USING(fullvisitorId)
  {query_filter}
  GROUP BY 1,2,3)
GROUP BY ecomm_action),
prod_cat AS (
SELECT COUNT(DISTINCT CASE WHEN label = 1 THEN fullvisitorId END) AS count_1_users,
COUNT(DISTINCT CASE WHEN label = 0 THEN fullvisitorId END) AS count_0_users,
prod_cat AS prod_cat,
'prod_cat' AS type
FROM (
  SELECT a.fullvisitorId, 
  prod.v2ProductCategory AS prod_cat,
  b.label
  FROM`{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
  unnest (hits) as hits,
  UNNEST (hits.product) AS prod
  LEFT JOIN users_labeled b USING(fullvisitorId)
  {query_filter}
  GROUP BY 1,2,3)
GROUP BY prod_cat),
agg_metrics AS (
SELECT fullvisitorId,
  CASE WHEN label IS NULL then 0 else label end as label,
  count(distinct visitId) as total_sessions,
  sum(totals.pageviews) as pageviews,
  count(totals.bounces)/count(distinct VisitID) as bounce_rate,
  sum(totals.timeonSite)/sum(totals.pageviews) as time_per_page,
  sum(totals.pageviews) / count(distinct VisitID) as avg_session_depth
FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a
LEFT JOIN users_labeled b
USING (fullvisitorId)
{query_filter}
GROUP BY 1,2
),
Agg_sessions AS (
SELECT fullvisitorId, label, total_sessions 
FROM agg_metrics),
Agg_pageviews AS (
SELECT fullvisitorId, label, pageviews 
FROM agg_metrics),
Agg_time_per_page AS (
SELECT fullvisitorId, label, time_per_page
FROM agg_metrics),
Agg_avg_session_depth AS (
SELECT fullvisitorId, label, avg_session_depth
FROM agg_metrics),
hist_sessions AS (
SELECT 
  ROUND(min+max/2) as avg_sessions,
  COUNT(distinct case when label = 1 then fullvisitorId end) as count_1_users,
  COUNT(distinct case when label = 0 or label is null then fullvisitorId end) as count_0_users,
  'stats_sessions' as type
FROM Agg_sessions
JOIN (SELECT min+step*i min, min+step*(i+1)max
  FROM (
    SELECT max-min diff, min, max, (max-min)/20 step, GENERATE_ARRAY(0, 20, 1) i
    FROM (
      SELECT MIN(total_sessions) min, MAX(total_sessions) max
      FROM Agg_sessions
      JOIN (select APPROX_QUANTILES(total_sessions, 200 IGNORE NULLS)[OFFSET(199)] as trimmer FROM Agg_sessions) b
      ON agg_sessions.total_sessions <= b.trimmer
    )
  ), UNNEST(i) i) stats_sessions
ON Agg_sessions.total_sessions >= stats_sessions.min 
AND Agg_sessions.total_sessions < stats_sessions.max
GROUP BY min, max
ORDER BY min),
hist_pageviews AS (
SELECT 
  ROUND(min+max/2) as avg_pageviews,
  COUNT(distinct case when label = 1 then fullvisitorId end) as count_1_users,
  COUNT(distinct case when label = 0 or label is null then fullvisitorId end) as count_0_users,
  'stats_pageviews' as type
FROM Agg_pageviews
JOIN (SELECT min+step*i min, min+step*(i+1)max
  FROM (
    SELECT max-min diff, min, max, (max-min)/20 step, GENERATE_ARRAY(0, 20, 1) i
    FROM (
      SELECT MIN(pageviews) min, MAX(pageviews) max
      FROM Agg_pageviews
      JOIN (select APPROX_QUANTILES(pageviews, 200 IGNORE NULLS)[OFFSET(199)] as trimmer FROM Agg_pageviews) b
      ON agg_pageviews.pageviews <= b.trimmer
    )
  ), UNNEST(i) i) stats_pageviews
ON Agg_pageviews.pageviews >= stats_pageviews.min 
AND Agg_pageviews.pageviews < stats_pageviews.max
GROUP BY min, max
ORDER BY min),
hist_time_per_page AS (
SELECT 
  ROUND(min+max/2) as avg_time_per_page,
  COUNT(distinct case when label = 1 then fullvisitorId end) as count_1_users,
  COUNT(distinct case when label = 0 or label is null then fullvisitorId end) as count_0_users,
  'stats_time_per_page' as type
FROM Agg_time_per_page
JOIN (SELECT min+step*i min, min+step*(i+1)max
  FROM (
    SELECT max-min diff, min, max, (max-min)/20 step, GENERATE_ARRAY(0, 20, 1) i
    FROM (
      SELECT MIN(time_per_page) min, MAX(time_per_page) max
      FROM Agg_time_per_page
      JOIN (select APPROX_QUANTILES(time_per_page, 200 IGNORE NULLS)[OFFSET(199)] as trimmer FROM Agg_time_per_page) b
      ON agg_time_per_page.time_per_page <= b.trimmer
    )
  ), UNNEST(i) i) stats_time_per_page
ON Agg_time_per_page.time_per_page >= stats_time_per_page.min 
AND Agg_time_per_page.time_per_page < stats_time_per_page.max
GROUP BY min, max
ORDER BY min),
hist_avg_session_depth AS (
SELECT 
  ROUND(min+max/2) as avg_avg_session_depth,
  COUNT(distinct case when label = 1 then fullvisitorId end) as count_1_users,
  COUNT(distinct case when label = 0 or label is null then fullvisitorId end) as count_0_users,
  'stats_avg_session_depth' as type
FROM Agg_avg_session_depth
JOIN (SELECT min+step*i min, min+step*(i+1)max
  FROM (
    SELECT max-min diff, min, max, (max-min)/20 step, GENERATE_ARRAY(0, 20, 1) i
    FROM (
      SELECT MIN(avg_session_depth) min, MAX(avg_session_depth) max
      FROM Agg_avg_session_depth
      JOIN (select APPROX_QUANTILES(avg_session_depth, 200 IGNORE NULLS)[OFFSET(199)] as trimmer FROM Agg_avg_session_depth) b
      ON agg_avg_session_depth.avg_session_depth <= b.trimmer
    )
  ), UNNEST(i) i) stats_avg_session_depth
ON Agg_avg_session_depth.avg_session_depth >= stats_avg_session_depth.min 
AND Agg_avg_session_depth.avg_session_depth < stats_avg_session_depth.max
GROUP BY min, max
ORDER BY min)
if query_session_cd:
  session_cd_query = ",\nsession_cds AS (SELECT * FROM ("
  counter = len(session_index_list)
  start = 1
  for ind in session_index_list:
    ind_num = ind
    session_custom_dimension_query_base = fSELECT
    "session_dim_{ind_num}" as type,
    count(distinct case when label = 1 then a.fullvisitorId end) as count_1_users,
    count(distinct case when label = 0 then a.fullvisitorId end) as count_0_users,
    cd.value as session_dim_{ind_num}_value
    FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
    UNNEST(customDimensions) as cd
    LEFT JOIN users_labeled b
    ON a.fullvisitorId = b.fullvisitorId
    {query_filter}
    AND cd.index = {ind_num}
    GROUP BY type, cd.value)
    query_add = session_custom_dimension_query_base
    session_cd_query += query_add
    if start > 1:
      session_cd_query += "USING (type, count_1_users, count_0_users)"
    if start < counter:
      session_cd_query += "\nFULL OUTER JOIN\n("
      start+=1
  session_cd_query+=")\n"
  final_query += session_cd_query
# Query hits 
if query_hit_cd:
  hit_cd_query = ",\nhits_cds AS (SELECT * FROM ("
  counter = len(hit_index_list)
  start = 1
  for ind in hit_index_list:
    ind_num = ind
    hit_cust_d_query_base = fSELECT
    "hit_dim_{ind_num}" as type,
    count(distinct case when label = 1 then a.fullvisitorId end) as count_1_users,
    count(distinct case when label = 0 then a.fullvisitorId end) as count_0_users,
    cd.value as hit_dim_{ind_num}_value
    FROM `{GA_PROJECT_ID}.{GA_DATASET_ID}.{GA_TABLE_ID}` a,
    UNNEST(hits) as ht,
    UNNEST(ht.customDimensions) as cd
    LEFT JOIN users_labeled b
    ON a.fullvisitorId = b.fullvisitorId
    {query_filter}
    AND cd.index = {ind_num}
    GROUP BY type, cd.value)
    
    query_add = hit_cust_d_query_base
    hit_cd_query += query_add
    if start > 1:
      hit_cd_query += "USING (type, count_1_users, count_0_users)"
    if start < counter:
      hit_cd_query += "\nFULL OUTER JOIN\n("
      start+=1
  hit_cd_query+=")\n"
  final_query += hit_cd_query
final_query += SELECT *, count_1_users/(count_1_users+count_0_users) as conv_rate FROM trafficSource_medium
FULL OUTER JOIN visitor_dma USING (type,count_1_users,count_0_users)
FULL OUTER JOIN distinct_dma USING (type,count_1_users,count_0_users)
FULL OUTER JOIN visitor_common_daypart USING (type,count_1_users,count_0_users)
FULL OUTER JOIN visitor_common_day USING (type,count_1_users,count_0_users)
FULL OUTER JOIN technology USING (type,count_1_users,count_0_users)
FULL OUTER JOIN PPL1 USING (type,count_1_users,count_0_users)
FULL OUTER JOIN ecomm_action USING (type,count_1_users,count_0_users)
FULL OUTER JOIN prod_cat USING (type,count_1_users,count_0_users)
FULL OUTER JOIN hist_sessions USING (type, count_1_users, count_0_users)
FULL OUTER JOIN hist_pageviews USING (type, count_1_users, count_0_users)
FULL OUTER JOIN hist_time_per_page USING (type, count_1_users, count_0_users)
FULL OUTER JOIN hist_avg_session_depth USING (type, count_1_users, count_0_users)
if query_hit_cd:
  final_query+="FULL OUTER JOIN hits_cds USING (type,count_1_users,count_0_users)"
  
if query_session_cd:
  final_query+="FULL OUTER JOIN session_cds USING (type,count_1_users,count_0_users)"
if (View_Query):
  print(final_query)
Explanation: Programmatically write a query that pulls distinct users, by class, for features and every custom dimension (session & hit level).
If you want to view the query, set View_Query to True in the cell below.
End of explanation
# Set the destination for your query results.
# This will be your data source for the Data Studio dashboard.
DESTINATION = f"{PROJECT_ID_BILLING}.{DATASET_NAME}.ga360_gazer_output"
job_config = bigquery.QueryJobConfig(destination=DESTINATION, 
                                     writeDisposition="WRITE_EMPTY")
# Start the query, passing in the extra configuration.
query_job = client.query(final_query, job_config=job_config)
query_job.result()
print("Query results loaded to the table {}".format(DESTINATION))
Explanation: Save results to BQ. As-is, only writes if there is no table that already exists.
End of explanation
# Delete the dataset and all contents within
! bq rm -r $PROJECT_ID_BILLING:$DATASET_NAME
Explanation: Visualize results with a pre-built Data Studio dashboard:
Open the templated dashboard here 
Make a copy with the button in the top menu bar. When making a copy:
Accept the terms and conditions, if it's your first time using Data Studio
Create a new data source
Select BigQuery (you will need to grant permissions again)
Under Project, select your project specified by PROJECT_ID_BILLING
Under Dataset, select the dataset you specified as DATASET_NAME
Under Table, select "ga360_gazer_output" (unless you changed the name)
Click "Connect"
You will see a list of fields - click "ADD TO REPORT" on the top right
You will be prompted to make a copy of the original report with your new data source - click "Copy Report"
Page through the pages to view insights
Cleaning up
To clean up all GCP resources used in this project, you can delete the GCP
project you used for the tutorial.
End of explanation
<END_TASK>