bqml-ml-explain-predict.sql

#standardSQL
SELECT
  visitorID, #passthrough
  Country,
  Top_feature_attributions, # from ml.explain_predict
  SUM(predicted_label) as total_predicted_purchases,
  label #passthrough
FROM
  ML.EXPLAIN_PREDICT(MODEL `bqml_tutorial.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC