!pip install google-cloud-bigquery

# Automatically restart kernel after installs
import IPython
app = IPython.Application.instance()
app.kernel.do_shutdown(True)  

"""
Explanation: <table align="left">
  <td>
    <a href="https://colab.research.google.com/github/GoogleCloudPlatform/analytics-componentized-patterns/blob/master/notebooks/bqml_matrix_factorization_retail_ecommerce.ipynb">
      <img src="https://cloud.google.com/ml-engine/images/colab-logo-32px.png" alt="Colab logo"> Run in Colab
    </a>
  </td>
  <td>
    <a href="https://console.cloud.google.com/ai-platform/notebooks/deploy-notebook?name=BigQuery%20ML%20-%20Retail%20Recommendation%20System&download_url=https%3A%2F%2Fgithub.com%2FGoogleCloudPlatform%2Fanalytics-componentized-patterns%2Fraw%2Fmaster%2Fretail%2Frecommendation-system%2Fbqml%2Fbqml_retail_recommendation_system.ipynb&url=https%3A%2F%2Fgithub.com%2FGoogleCloudPlatform%2Fanalytics-componentized-patterns%2Fblob%2Fmaster%2Fretail%2Frecommendation-system%2Fbqml%2Fbqml_retail_recommendation_system.ipynb">
      <img src="https://cloud.google.com/images/products/ai/ai-solutions-icon.svg" alt="AI Platform Notebooks">Run on AI Platform Notebooks</a>
  </td>
  <td>
    <a href="https://github.com/GoogleCloudPlatform/analytics-componentized-patterns/blob/master/notebooks/bqml_matrix_factorization_retail_ecommerce.ipynb">
      <img src="https://cloud.google.com/ml-engine/images/github-logo-32px.png" alt="GitHub logo">
      View on GitHub
    </a>
  </td>
</table>

Overview
In this notebook, you’ll learn how to build a product recommendation system in a retail scenario using matrix factorization, and how to use the predicted recommendations to drive marketing activation.
Why are recommendation systems so important?
The majority of consumers today expect personalization — to see products and services relevant to their interests. Naturally, they can help businesses too. By learning from user behaviours and preferences, businesses can deliver their recommendations in a variety of ways, including personalized coupons, marketing emails, and search results, or targeted ads. Ultimately, this enables businesses to attract more customer spending with targeted cross-selling or upselling, while reducing unnecessary costs by marketing irrelevant products.


"Companies that fail to show customers they know them and their buying preferences risk losing business to competitors who are more attuned to what their customers want."
Harvard Business Review. “The Age of Personalization”. September 2018


How does matrix factorization work?
Based on user preferences, matrix factorization (collaborative filtering) is one of the most common and effective methods of creating recommendation systems. For more information about how they work, see this introduction to recommendation systems here. 
What is BigQuery ML?
BigQuery ML enables users to create and execute machine learning models in BigQuery by using standard SQL queries. This means, if your data is already in BigQuery, you don’t need to export your data to train and deploy machine learning models — by training, you’re also deploying in the same step. Combined with BigQuery’s auto-scaling of compute resources, you won’t have to worry about spinning up a cluster or building a model training and deployment pipeline. This means you’ll be saving time building your machine learning pipeline, enabling your business to focus more on the value of machine learning instead of spending time setting up the infrastructure.
You may have also heard of Recommendations AI, a Google Cloud product purpose-built for real-time recommendations on a website using state-of-the-art deep learning models. Matrix factorization with BigQuery ML, on the other hand, is a more generic ML algorithm that can be used for offline and online recommendations (e.g. personalized e-mail campaigns).
Scope of this notebook
Dataset
The Google Analytics Sample dataset, which is hosted publicly on BigQuery, is a dataset that provides 12 months (August 2016 to August 2017) of obfuscated Google Analytics 360 data from the Google Merchandise Store, a real e-commerce store that sells Google-branded merchandise.
Objective
By the end of this notebook, you will know how to:
* pre-process data into the correct format needed to create a recommender system using BigQuery ML
* train (and deploy) the matrix factorization model in BigQuery ML
* evaluate the model
* make predictions using the model
* take action on the predicted recommendations:
  * for activation via Google Ads, Display & Video 360 and Search Ads 360
  * for activation via emails
  * export predictions to a pandas dataframe
  * export predictions into Google Cloud Storage
Costs
This tutorial uses billable components of Google Cloud Platform (GCP):

BigQuery
BigQuery ML

Learn about BigQuery pricing, BigQuery ML
pricing and use the Pricing
Calculator
to generate a cost estimate based on your projected usage.
PIP Install Packages and dependencies
End of explanation
"""


PROJECT_ID = "your_project_id"
REGION = 'US'

"""
Explanation: Set up your GCP project
The following steps are required, regardless of your notebook environment.


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.


Enter your project ID and region 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.
End of explanation
"""


from google.cloud import bigquery
import time
import pandas as pd

pd.set_option('display.float_format', lambda x: '%.3f' % x)

"""
Explanation: Import libraries and define constants
End of explanation
"""


!bq mk --location=$REGION --dataset $PROJECT_ID:bqml

"""
Explanation: Creating a BigQuery dataset
In this notebook, you will need to create a dataset in your project called bqml. To create it, run the following cell:
End of explanation
"""


%%bigquery --project $PROJECT_ID
## follows the Google Analytics schema: 
#https://support.google.com/analytics/answer/3437719?hl=en

SELECT 
  CONCAT(fullVisitorID,'-',CAST(visitNumber AS STRING)) AS visitorId,
  hitNumber,
  time,
  page.pageTitle,
  type,
  productSKU,
  v2ProductName,
  v2ProductCategory,
  productPrice/1000000 as productPrice_USD

FROM 
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`, 
  UNNEST(hits) AS hits,
  UNNEST(hits.product) AS hits_product
LIMIT 5

"""
Explanation: Raw data
Before beginning, take a look at the raw data:
Note: Jupyter runs cells starting with %%bigquery as SQL queries
End of explanation
"""


%%bigquery --project $PROJECT_ID
## follows schema from https://support.google.com/analytics/answer/3437719?hl=en&ref_topic=3416089
CREATE OR REPLACE TABLE bqml.aggregate_web_stats AS (
  WITH
    durations AS (
      --calculate pageview durations
      SELECT
        CONCAT(fullVisitorID,'-', 
             CAST(visitNumber AS STRING),'-', 
             CAST(hitNumber AS STRING) ) AS visitorId_session_hit,
        LEAD(time, 1) OVER (
          PARTITION BY CONCAT(fullVisitorID,'-',CAST(visitNumber AS STRING))
          ORDER BY
          time ASC ) - time AS pageview_duration
      FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_2016*`,
        UNNEST(hits) AS hit 
    ),
      
    prodview_durations AS (
      --filter for product detail pages only
      SELECT
        CONCAT(fullVisitorID,'-',CAST(visitNumber AS STRING)) AS visitorId,
        productSKU AS itemId,
        IFNULL(dur.pageview_duration,
          1) AS pageview_duration,
      FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_2016*` t,
        UNNEST(hits) AS hits,
        UNNEST(hits.product) AS hits_product
      JOIN
        durations dur
      ON
        CONCAT(fullVisitorID,'-',
               CAST(visitNumber AS STRING),'-',
               CAST(hitNumber AS STRING)) = dur.visitorId_session_hit
      WHERE
      #action_type: Product detail views = 2
      eCommerceAction.action_type = "2" 
    ),
    
    aggregate_web_stats AS(
      --sum pageview durations by visitorId, itemId
      SELECT
        visitorId,
        itemId,
        SUM(pageview_duration) AS session_duration
      FROM
        prodview_durations
      GROUP BY
        visitorId,
        itemId )
    SELECT
      *
    FROM
      aggregate_web_stats
);
-- Show table
SELECT
  *
FROM
  bqml.aggregate_web_stats
LIMIT
  10

"""
Explanation: Pre-process the data
With collaborative filtering (matrix factorization), the dataset must indicate a user's preference for a product, like a rating between 1 and 5 stars. However, in the retail industry, there is usually no or insufficient explicit feedback on how much a user liked a product. Thus, other behavioral metrics need to be used to infer their implicit "rating".  One way to infer user interest in a product is to look at the total time spent on a product detail page (e.g., session duration).
With matrix factorization, in order to train the model, you will need a table with userId, itemId, and the rating. In this notebook example, session duration will be used as the implicit rating. If you have other metrics (e.g., frequency of pageviews), you can simply combine the metrics together using a weighted sum to compute a rating value.
|userId|itemId|rating|
|-|-|-|
|visitor1|productSKU_1|3000|
|visitor1|productSKU_4|15000|
|visitor1|productSKU_9|920|
|visitor2|productSKU_12|0|
Notice how every row is a unique combination of userId and itemId, along with the (implicit) rating.
The query below will pre-process the data by calculating the total pageview duration per product per user, and materialize the data in a new table, aggregate_web_stats.
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  bqml.aggregate_web_stats
LIMIT 
  10

"""
Explanation: The training data
With the data stored in an output table in the correct format for matrix factorization, the data is now ready for training a matrix factorization model.
End of explanation
"""


%%bigquery --project $PROJECT_ID

CREATE OR REPLACE MODEL bqml.retail_recommender
OPTIONS(model_type='matrix_factorization', 
        user_col='visitorId', 
        item_col='itemId',
        rating_col='session_duration',
        feedback_type='implicit'
        )
AS
SELECT * FROM bqml.aggregate_web_stats

"""
Explanation: Train the matrix factorization model
To train the matrix factorization model (with implicit feedback), you will need to set the options:
* model_type: 'matrix_factorization' 
* user_col: \<user column name>
* item_col: \<item column name>
* rating_col: \<rating column name>
* feedback_type: 'implicit' (default is 'explicit')
To learn more about the parameters when training a model, read the documentation on the CREATE MODEL statement for Matrix Factorization.
Note: You may need to setup slot reservations. For more information, you can read up on how to set up flex slots programmatically or via the BigQuery UI.
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqml.retail_recommender)

"""
Explanation: Model Evaluation
Inspect the resulting metrics from model evaluation.
For more information on these metrics, read the ML.EVALUATE documentation here.
End of explanation
"""


%%bigquery --project $PROJECT_ID

#check for a single visitor
DECLARE MY_VISITORID STRING DEFAULT "0824461277962362623-1";

SELECT
  *
FROM
  ML.RECOMMEND(MODEL `bqml.retail_recommender`,
      (SELECT MY_VISITORID as visitorID)
              )
ORDER BY predicted_session_duration_confidence DESC
LIMIT 5

"""
Explanation: Hyperparameter Tuning
If you want to improve your model,  some of the hyperparameters you can tune are:
* NUM_FACTORS: Specifies the number of latent factors to use for matrix factorization models (int64_value)
* L2_REG: The amount of L2 regularization applied (float64_value)
* WALS_ALPHA: A hyperparameter for 'IMPLICIT' matrix factorization model (float64_value)
See the official documentation on CREATE MODEL (matrix factorization) for more information on hyperparameter tuning.
Make predictions
Inspect the predicted recommendations for a single user
What are the top 5 items you could recommend to a specific visitorId?
End of explanation
"""


%%bigquery --project $PROJECT_ID

DECLARE MY_VISITORID STRING DEFAULT "6499749315992064304-2";

WITH product_details AS(
    SELECT 
        productSKU,
        v2ProductName,
    FROM 
        `bigquery-public-data.google_analytics_sample.ga_sessions_2016*`,
        UNNEST(hits) AS hits,
        UNNEST(hits.product) AS hits_product
    GROUP BY 2,1 
)

SELECT
  r.*,
  d.v2ProductName
FROM
  ML.RECOMMEND(MODEL `bqml.retail_recommender`,
      (
      SELECT
       MY_VISITORID as visitorId)) r
JOIN
  product_details d
ON
  r.itemId = d.productSKU
ORDER BY predicted_session_duration_confidence DESC
LIMIT 5

"""
Explanation: What are the names of the recommended products? Discover the product names by joining the resulting productSKU recommendations back with the product names:
End of explanation
"""


%%bigquery --project $PROJECT_ID

-- Create output table
CREATE OR REPLACE TABLE bqml.prod_recommendations AS (
WITH predictions AS (
    SELECT 
      visitorId, 
      ARRAY_AGG(STRUCT(itemId, 
                       predicted_session_duration_confidence)
                ORDER BY 
                  predicted_session_duration_confidence DESC
                LIMIT 5) as recommended
    FROM ML.RECOMMEND(MODEL bqml.retail_recommender)
    GROUP BY visitorId
)
    

SELECT
  visitorId,
  itemId,
  predicted_session_duration_confidence
FROM
  predictions p,
  UNNEST(recommended)
);

-- Show table
SELECT
  *
FROM
  bqml.prod_recommendations
ORDER BY 
  visitorId
LIMIT
  20

"""
Explanation: Batch predictions for all users
To retrieve the top 5 recommended products for all existing users, run the following query. As the result can be large (num_users * num_products * top N), this also outputs the recommendations to a separate table.
End of explanation
"""


%%bigquery --project $PROJECT_ID
WITH predictions AS (
    SELECT 
      visitorId, 
      ARRAY_AGG(STRUCT(itemId, 
                       predicted_session_duration_confidence)
                ORDER BY 
                  predicted_session_duration_confidence) as recommended
    FROM ML.RECOMMEND(MODEL bqml.retail_recommender)
    WHERE itemId = "GGOEYOLR018699"
    GROUP BY visitorId
)
  
SELECT
  visitorId,
  ML.MIN_MAX_SCALER(
    predicted_session_duration_confidence
    ) OVER() as GGOEYOLR018699
FROM
  predictions p,
  UNNEST(recommended)
ORDER BY GGOEYOLR018699 DESC

"""
Explanation: Using the predicted recommendations in production
Once you have the recommendations, plugging into your production pipeline will depend on your use case. 
Here are a few possible ways to help you get started:
1. Export recommendations for marketing activation:
    1. For activation via Google Ads, Display & Video 360 and Search Ads 360
    1. For activation via emails
1. Other ways to export recommendations from BigQuery
    1. BigQuery to pandas dataframes
    1. Export the predictions to Google Cloud Storage
<a id="export_ga360"></a>
1-1. Export recommendations to Google Analytics 360 (Google Marketing Platform)
By exporting the resulting predictions from BigQuery ML back to Google Analytics, you will be able to generate custom remarketing audiences and target customers more effectively with ads, search, or email activation.
Formatting the data for Google Analytics 360
You may need to format the data output into something that Google Analytics, for example:
|clientId | LikelyToBuyProductA |
|-|-|
| 123 |  0.70 |
| 345 |  0.90 |  
Here's a sample query for an itemId "GGOEYOLR018699", that normalizes the confidence scores between 0 and 1, using ML.MIN_MAX_SCALER:
End of explanation
"""


%%bigquery df --project $PROJECT_ID
SELECT
  *
FROM 
  bqml.prod_recommendations
LIMIT 100

df.head()

"""
Explanation: To create a column per product, you can use the pivot() function as described in this blogpost.
For Google Analytics Data Import, it's recommended that you use  clientId as the key, along with individual columns that show some propensity score. In other words, you may need to create a new column for each product that you are interested in recommending, and create a custom dimension in Google Analytics that can be then used to build your audiences. It's also likely best to ensure that you have one row per clientId. If you know you will be exporting predictions to Google Analytics, it's recommended that you train your models using clientId directly instead of visitorId.
Exporting the data from BigQuery into Google Analytics 360
The easiest way to export your BigQuery ML predictions from a BigQuery table to Google Analytics 360 is to use the MoDeM (Model Deployment for Marketing) reference implementation. MoDeM helps you load data into Google Analytics for eventual activation in Google Ads, Display & Video 360 and Search Ads 360.  
To export to Google Analytics 360 from BigQuery:
- Follow the step-by-step instructions here to build your ETL pipeline from BigQuery ML to Google Analytics using MoDeM. You can also view the interactive instructions in this notebook.
1-2. Email activation using Salesforce Marketing Cloud
As Google Analytics does not contain email addresses, you may need to integrate with a 3rd-party platform like Salesforce Marketing Cloud for email activations.
Google Analytics 360 customers can  activate their Analytics 360 audiences in Marketing Cloud on Salesforce direct marketing channels (email and SMS). This enables your marketing team to build audiences based on online web behavior and engage with those customers via emails and SMS.
Follow the step-by-step instructions here to integrate Google Analytics 360 with Salesforce Marketing Cloud, or learn more about Audience Activation through Salesforce Trailhead.
<a id="export_other"></a>
2. Other ways to export recommendations from BigQuery
If you want to use the predicted recommendations in other services, two ways to leverage the results are to export the data from BigQuery as a pandas dataframe, or if you want to store the result on Google Cloud Storage, you can also export the table directly as a CSV file.
2-1. Read from the predictions directly from BigQuery
With the predictions stored in a separate table, you can export the data into a Pandas dataframe using the BigQuery Storage API (see documentation and code samples). You can also use other BigQuery client libraries. 
Alternatively you can also export directly into pandas in a notebook using the %%bigquery &lt;variable name&gt; as in:
End of explanation
"""


%%bigquery --project $PROJECT_ID

EXPORT DATA OPTIONS (
uri="gs://mybucket/myfile/recommendations_*.csv", 
  format=CSV
) AS 
SELECT
  *
FROM 
  bqml.prod_recommendations

"""
Explanation: 2-2. Export predictions table to Google Cloud Storage
There are several ways to export the predictions table to Google Cloud Storage (GCS), so that you can use them in a separate service. Perhaps the easiest way is to export directly to GCS using SQL (documentation).
End of explanation
"""


