Autor: workingtitleecommerce

Preface: It was not successful. 

I would like to implement a small recommendation engine using Google BigQuery ML. We have anonymized order data in Google BigQuery and the idea is:

Find all combinations of product 1 and product 2 of all orders with two or more products purchased.  Let BigQuery ML learn. If we later have a given product 1 let BigQuery suggest some related products 2 to our customers.

Build a BigQuery select statement to find orders with 2 order lines / 2 products purchased.

WITH
  TableFirstProduct AS (
  SELECT
    *
  FROM (
    SELECT
      ol1.IncrementId AS IncrementId1,
      ol1.Sku AS Sku1,
      pc1.ParentSku AS ParentSku1,
      pc1.ParentId AS ParentEntityId1,
      pc1.Price,
      pc1.MyCustomProperty01,
      pc1.MyCustomProperty02,
      pc1.MyCustomProperty03,
      pc1.MyCustomProperty04,
      pc1.Color,
      ROW_NUMBER() OVER (PARTITION BY ol1.IncrementId) AS RowNumber1
    FROM
      `myproject01.mydataset01.order_lines` ol1
    JOIN
      `myproject01.mydataset01.product_childs` pc1
    ON
      ol1.ProductId = pc1.EntityId )
  WHERE
    RowNumber1 = 1),
  TableSecondProduct AS (
  SELECT
    *
  FROM (
    SELECT
      ol2.IncrementId AS IncrementId2,
      ol2.Sku AS Sku2,
      pc2.ParentSku AS ParentSku2,
      pc2.ParentId AS ParentEntityId2,
      ROW_NUMBER() OVER (PARTITION BY ol2.IncrementId) AS RowNumber2
    FROM
      `myproject01.mydataset01.order_lines` ol2
    JOIN
      `myproject01.mydataset01.product_childs` pc2
    ON
      ol2.ProductId = pc2.EntityId )
  WHERE
    RowNumber2 = 2 )
SELECT
  t1.*,
  t2.*
FROM
  TableFirstProduct t1
JOIN
  TableSecondProduct t2
ON
  t1.IncrementId1 = t2.IncrementId2
ORDER BY
  t1.IncrementId1

Saved as view „cart_2_products“.

Create model following this documentation

https://cloud.google.com/bigquery/docs/bigqueryml-analyst-start

#standardSQL
CREATE OR REPLACE MODEL `myproject01.recommandations.recom_product2`
OPTIONS(model_type='logistic_reg') AS
SELECT
  ParentSku2 as label,
  ParentSku1,
  Price,
  MyCustomProperty01,
  MyCustomProperty02,
  MyCustomProperty03,
  MyCustomProperty04,
  Color
FROM
  `myproject01.recommandations.cart_2_products`

Error:

Logistic regression currently only supports binary classification and the label column had 43305 unique labels instead of 2.

Ok, changed to

(model_type='linear_reg')

Error:

'label' column should be numerical.

Fixed using EntityId instead of SKU, I am not sure if this is the correct way to do it.

#standardSQL
CREATE OR REPLACE MODEL `myproject01.recommandations.recom_product2`
OPTIONS
  (model_type='linear_reg') AS
SELECT
  ParentEntityId2 AS label,
  ParentSku1,
  Price,
  MyCustomProperty01,
  MyCustomProperty02,
  MyCustomProperty03,
  MyCustomProperty04,
  Color
FROM
  `myproject01.recommandations.cart_2_products`

Error:

Label column 'label' has NULL values.

Fixing:

IF(ParentEntityId2 IS NULL,
    0,
    ParentEntityId2) AS label,

or full query

#standardSQL
CREATE OR REPLACE MODEL `myproject01.recommandations.recom_product2`
OPTIONS
  (model_type='linear_reg') AS
SELECT
  IF(ParentEntityId2 IS NULL,
    0,
    ParentEntityId2) AS label,
  ParentSku1,
  Price,
  MyCustomProperty01,
  MyCustomProperty02,
  MyCustomProperty03,
  MyCustomProperty04,
  Color
FROM
  `myproject01.recommandations.cart_2_products`

running over 6 mins…

Testing the model via ML.EVALUATE

SELECT
  *
FROM
  ML.EVALUATE(MODEL `myproject01.recommandations.recom_product2`,
    (
    SELECT
      IF(ParentEntityId2 IS NULL,
        0,
        ParentEntityId2) AS label,
      ParentSku1,
      Price,
      MyCustomProperty01,
      MyCustomProperty02,
      MyCustomProperty03,
      MyCustomProperty04,
      Color
    FROM
      `myproject01.recommandations.cart_2_products`
    LIMIT
      10 ))

results to

To be honest:  I have no clue 🙂

Use the model to predict via ML.PREDICT

SELECT
  *
FROM
  ML.PREDICT(MODEL `myproject01.recommandations.recom_product2`,
    (
    SELECT
      IF(ParentEntityId2 IS NULL,
        0,
        ParentEntityId2) AS label,
      ParentSku1,
      Price,
      MyCustomProperty01,
      MyCustomProperty02,
      MyCustomProperty03,
      MyCustomProperty04,
      Color
    FROM
      `myproject01.recommandations.cart_2_products`
    LIMIT
      10 ))

And I see: I does not make sense.  It is the wrong way!

I need exact EntityIds and not probabilities and linear regression model is not aimed for this case.