project_id = "my_project" #@param {type:"string"}
example_dataset = "my_schema" #@param {type:"string"}

from google.colab import auth
auth.authenticate_user()

"""
Explanation: Disclaimer: The following code demonstrates a sample BQML model creation based on GA-BQ export. This is meant for inspiration only. We expect analysts/data scientists to identify the right set of features to create retargeted audiences based on their business needs. 
Change Params and Authenticate
End of explanation
"""


%%bigquery --project=$project_id df
WITH sample_raw_data AS (
  SELECT CAST(CEIL(RAND() * 100) AS INT64) AS clientId, * EXCEPT (clientId) FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` LIMIT 1000
),
visit_data AS (
  SELECT clientId, SUM(totals.visits) AS all_visits, CAST(ROUND(RAND() * 1) AS INT64) AS converted
  FROM sample_raw_data
  GROUP BY clientId
)
SELECT *
FROM visit_data

df.head()

import math
training_data_size = math.ceil(df.shape[0] * 0.7)

training_data = df[:training_data_size]
test_data = df[training_data_size:]

training_data.to_csv('training.csv', index=False)
test_data.to_csv('test.csv', index=False)

!bq load --project_id $project_id --autodetect --source_format='CSV' $example_dataset".training_data" training.csv
!bq load --project_id $project_id --autodetect --source_format='CSV' $example_dataset".test_data" test.csv

"""
Explanation: Generate and load sample GA dataset
Based on an anonymized public GA dataset
End of explanation
"""


%%bigquery --project=$project_id
CREATE OR REPLACE MODEL `my_schema.test_model` 
OPTIONS (model_type='logistic_reg', input_label_cols=['converted'], auto_class_weights=True) AS
(
  SELECT all_visits, converted 
  FROM `my_schema.training_data`
)

"""
Explanation: Create a BQML model
Creating a logistic regression model that tries to predict if a user converts based on total number of visits.
End of explanation
"""


%%bigquery --project=$project_id
SELECT * 
FROM ML.EVALUATE(MODEL `my_schema.test_model`, 
                 (SELECT * FROM `my_schema.test_data`))

"""
Explanation: Evaluate a BQML model
End of explanation
"""


%%bigquery --project=$project_id predicted_df
SELECT clientId AS ga_userId, NTILE(10) OVER (ORDER BY prob DESC) AS ga_dimension1 
FROM ML.PREDICT(MODEL `my_schema.test_model`, 
                (SELECT * FROM `my_schema.test_data`)),
     UNNEST(predicted_converted_probs)

predicted_df.head()

"""
Explanation: Predict using a BQML model
We'd need this query for automating audience import in GA.
End of explanation
"""


