google bigquery how to create machine learning model using sql

1-- standardSQL to build the model
2CREATE OR REPLACE MODEL `bqml_lab.sample_model`
3OPTIONS(model_type='logistic_reg') AS
4SELECT
5  IF(totals.transactions IS NULL, 0, 1) AS label,
6  IFNULL(device.operatingSystem, "") AS os,
7  device.isMobile AS is_mobile,
8  IFNULL(geoNetwork.country, "") AS country,
9  IFNULL(totals.pageviews, 0) AS pageviews
10FROM
11  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
12WHERE
13  _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'
14LIMIT 100000;
15
16SELECT
17  *
18FROM
19  ml.EVALUATE(MODEL `bqml_lab.sample_model`, (
20SELECT
21  IF(totals.transactions IS NULL, 0, 1) AS label,
22  IFNULL(device.operatingSystem, "") AS os,
23  device.isMobile AS is_mobile,
24  IFNULL(geoNetwork.country, "") AS country,
25  IFNULL(totals.pageviews, 0) AS pageviews
26FROM
27  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
28WHERE
29  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'));
30
31-- We used logistic regression for this model, hence, this query returns
32-- values such as precision, recall, accuracy, f1_score, log_loss, roc_auc.
33-- If it was linear regression, we'd have seen values such as mean_absolute_error, 
34-- mean_squared_error, mean_squared_log_error, median_absolute_error, 
35-- r2_score, explained_variance.
36  
37-- output in Json/Grid 
38[
39  {
40    "precision": "0.43601895734597157",
41    "recall": "0.0856610800744879",
42    "accuracy": "0.9851952452667814",
43    "f1_score": "0.1431906614785992",
44    "log_loss": "0.04735969966406397",
45    "roc_auc": "0.982000999000999"
46  }
47]  
48