BigQuery MLの使い方｜SQLで訪問数予測・異常検知・購入予測を試す

こんにちは、中都です。

GA4や広告のデータをBigQueryに集めて、昨日のセッション数や今月の購入数を可視化する。そこまでできても、「来月はどのくらい集客できそうか」「普段と違う日はどこか」と聞かれると、もう一段の分析が必要になります。

僕がBigQuery MLを面白いと思うのは、データを外へ持ち出さず、いつものSQLでモデルの作成・評価・予測まで進められるところです。今回は公開サンプルを使い、訪問数の予測、異常な日の抽出、購入見込みの判定を順番に試します。

集計した数字を、次の施策を考える材料にしたい方へ

「先月どうだったか」の先で困る場面

毎月レポートを作っていても、次のような判断に使うには、過去の合計だけでは足りません。

• 流入の見通し：今の傾向が続いた場合、来月の訪問数はどの程度になりそうか。
• 変化の発見：アクセスが多かった日は、曜日による通常の波なのか、それとも調べる価値のある変化なのか。
• 購入の傾向：購入したセッションと購入しなかったセッションには、どんな違いがあるのか。

僕は、集計やグラフ化で終わっている企業は多いのではないかと感じています。数字を眺めるだけでなく、目標に届かなそうな兆しを見つけて、次の施策を考えるところまで進めたいんですよね。

この記事でできるようになること

• 30日先までの訪問数：中心の予測値と、上下の幅を表・グラフで見る。
• 異常検知：過去の訪問数から、普段のパターンを外れた日を抽出する。
• 購入見込みの評価：購入候補をスコア化し、取りこぼしと誤検知を混同行列で確かめる。

BigQueryでSQLを実行したことがある方向けです。モデルが保存された画面、予測の表とグラフ、購入予測の評価結果を実演画像とともに載せています。以下のSQLは、ハンズオン資料の12本を掲載したものです。各「SQLを開く」からコード全体をコピーし、上から順番に実行できます。

準備：USのデータセットを作り、料金と上書き先を確認する

BigQuery MLは、BigQuery上のデータに対してSQLで機械学習モデルを作る機能です。今回使うのは、日付と訪問数から予測するARIMA_PLUSと、購入したかどうかを分類するロジスティック回帰です。

1. Google CloudのBigQueryを開き、学習に使うプロジェクトを選びます。
2. 請求先と、クエリ実行・テーブル作成・モデル作成の権限を確認します。組織の環境なら管理者に確認してください。必要なロールはGoogle公式のモデル作成手順に記載されています。
3. エクスプローラでプロジェクトのメニューからデータセットを作成を開きます。データセットIDをbqml_tutorial、ロケーションをUSにします。確定前にIDと場所を確認してください。
4. データセットが左側に表示されたら、新しいクエリを開きます。クエリの処理ロケーションを明示する場合もUSにそろえます。

今回の公開サンプルはbigquery-public-data.google_analytics_sample.ga_sessions_*です。旧Google Analytics（Universal Analytics）のセッション形式であり、GA4のevents_*形式とは異なります。自社GA4へ応用する場合は、セッションや購入の定義に合わせて前処理から作り直します。自社データの蓄積方法は、GA4とBigQueryの連携手順で説明しています。

クエリ・学習・保存には費用が発生する場合があります。ARIMA_PLUSの自動探索は複数の候補モデルを学習するため、読み取るデータ量だけから学習料金を決めつけないでください。BigQueryの料金表でモデル作成の項目を確認し、最初はこの公開サンプルで試しましょう。

以下のCREATE OR REPLACEは、同名のテーブルやモデルがあると置き換えます。追記ではありません。既存業務で使っている同名のものがないことを確認してください。複数のSQLを一括実行せず、1本ずつ完了を確認すると、途中で失敗した場所を追いやすくなります。

1．セッション単位と日別のテーブルを用意する

公開サンプルを自分のデータセットへコピーする

クエリエディタに最初のSQLを貼り付け、実行を押します。ga_sessions_sampleが作成されたら、テーブルを開いてプレビューします。デバイス、流入元、ページビュー、購入有無などが入っています。

CREATE OR REPLACE TABLE `bqml_tutorial.ga_sessions_sample` AS
SELECT
  PARSE_DATE('%Y%m%d', date) AS session_date,
  fullVisitorId AS visitor_id,
  visitId AS visit_id,
  IFNULL(device.deviceCategory, 'unknown') AS device_category,
  IFNULL(channelGrouping, 'unknown') AS channel_grouping,
  IFNULL(trafficSource.source, 'unknown') AS source,
  IFNULL(trafficSource.medium, 'unknown') AS medium,
  IFNULL(geoNetwork.country, 'unknown') AS country,
  IFNULL(totals.visits, 0) AS visits,
  IFNULL(totals.transactions, 0) AS transactions,
  IF(IFNULL(totals.transactions, 0) > 0, TRUE, FALSE) AS purchased,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(totals.newVisits, 0) AS new_visit,
  IFNULL(totals.timeOnSite, 0) AS time_on_site,
  IFNULL(totals.transactionRevenue, 0) / 1000000 AS revenue,
  EXTRACT(DAYOFWEEK FROM PARSE_DATE('%Y%m%d', date)) AS day_of_week
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`;

SQLはtotals.transactionsが0より大きいセッションをpurchased = TRUEにしています。購入回数そのものと、購入したセッションの件数を分けて扱います。

日別の訪問数に集計する

次のSQLを新しいクエリで実行すると、ga_daily_visitsができます。時系列モデルには、この表のparsed_dateとtotal_visitsを使います。total_visitsは元データのtotals.visitsの合計で、行数を数えるsessionsとは列を分けています。

CREATE OR REPLACE TABLE `bqml_tutorial.ga_daily_visits` AS
SELECT
  TIMESTAMP(session_date) AS parsed_date,
  session_date,
  SUM(visits) AS total_visits,
  COUNT(*) AS sessions,
  COUNT(DISTINCT visitor_id) AS users,
  SUM(pageviews) AS pageviews,
  SUM(transactions) AS transactions,
  SUM(revenue) AS revenue
FROM `bqml_tutorial.ga_sessions_sample`
GROUP BY parsed_date, session_date
ORDER BY session_date;

元データの日付をDATEへ変換し、さらにTIMESTAMPへ変換しています。TIMESTAMP(session_date)の変換はUTC基準です。自社データへ置き換えるときは、集計日のタイムゾーンをそろえてください。また、日別ユーザー数を足しても期間全体のユニークユーザー数にはなりません。

件数・期間・購入率を先に見る

SELECT
  COUNT(*) AS sessions,
  MIN(session_date) AS start_date,
  MAX(session_date) AS end_date,
  COUNTIF(purchased) AS purchased_sessions,
  ROUND(SAFE_DIVIDE(COUNTIF(purchased), COUNT(*)), 4) AS purchase_rate
FROM `bqml_tutorial.ga_sessions_sample`;

僕の実演では、903,653行、期間は2016年8月1日〜2017年8月1日、購入したセッションは11,552件でした。購入率は約1.28％です。こういう購入者の少ないデータは、どれだけ正解したかだけを見ると危ないんです。

例えば、100人中1人が購入するデータで全員を「買わない」と予測すると、99人分は正解になります。でも、見つけたかった購入者は1人も拾えていません。購入候補を探すなら、正解率の高さより「購入者をどれだけ拾い、どれだけ外したか」を確かめましょう。

自分の実行結果が0件なら、この先の学習へ進まず、参照先と期間を確認してください。処理途中に失敗した場合も、前のSQLで作った表やモデルは残ります。後続の処理を進める前に、今回作成したものが使われているか確認します。

2．ARIMA_PLUSで30日先の訪問数を予測する

日付と訪問数からモデルを作る

次のSQLを実行し、ga_arima_modelが作成されるまで待ちます。ARIMA_PLUSは時系列を扱うモデルです。今回は自動でモデルを選ぶ設定と、後で予測の内訳を見るための分解設定を使います。

CREATE OR REPLACE MODEL `bqml_tutorial.ga_arima_model`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'parsed_date',
  time_series_data_col = 'total_visits',
  auto_arima = TRUE,
  data_frequency = 'AUTO_FREQUENCY',
  decompose_time_series = TRUE
) AS
SELECT
  parsed_date,
  total_visits
FROM `bqml_tutorial.ga_daily_visits`;

ここは僕が面白いと感じたところです。モデルがPythonのファイルとして別の場所へ出ていくのではなく、テーブルやビューと同じデータセットに保存されます。データを集めている場所で、予測モデルも分析資産として管理できます。

中心値と予測区間をセットで表示する

ML.FORECASTで、学習データの最終日から30日先までの予測を取り出します。今日から30日先ではありません。このサンプルでは2017年8月2日からの予測です。

SELECT
  DATE(forecast_timestamp) AS forecast_date,
  ROUND(forecast_value, 0) AS forecast_visits,
  ROUND(prediction_interval_lower_bound, 0) AS lower_bound,
  ROUND(prediction_interval_upper_bound, 0) AS upper_bound,
  confidence_level
FROM ML.FORECAST(
  MODEL `bqml_tutorial.ga_arima_model`,
  STRUCT(30 AS horizon, 0.8 AS confidence_level)
)
ORDER BY forecast_date
LIMIT 30;

実演の8月2日は、中心の予測値が2,635、80％予測区間の下限が2,369、上限が2,900でした。この幅はモデルが推定する不確実性を表します。必ずこの中に収まるという保証ではありません。

予測は1日の数字をぴったり当てるためだけでなく、どのくらいの幅で動きそうかを考えるために使えます。目標と比べて流入が足りなそうなら、そこで新しいマーケティング施策を検討できますよね。

結果の可視化を開き、横軸をforecast_date、系列をforecast_visits・lower_bound・upper_boundにすると、中心値と幅を並べて見られます。confidence_levelは設定値なので、訪問数の系列には含めません。

僕の実演では、週単位の波が見えました。土日が下がるように見えるので、次は内訳を確かめます。なお、画像は実行済みクエリの結果欄を表示しています。SQLを試す際は関数名など一部分だけを選択せず、掲載コード全体を実行してください。

土日の波を、予測の内訳で確かめる

SELECT
  DATE(time_series_timestamp) AS date,
  FORMAT_DATE('%A', DATE(time_series_timestamp)) AS day_of_week,
  time_series_type,
  ROUND(time_series_data, 0) AS visits,
  ROUND(trend, 0) AS trend,
  ROUND(seasonal_period_weekly, 0) AS weekly_effect,
  ROUND(seasonal_period_monthly, 0) AS monthly_effect,
  ROUND(seasonal_period_yearly, 0) AS yearly_effect
FROM ML.EXPLAIN_FORECAST(
  MODEL `bqml_tutorial.ga_arima_model`,
  STRUCT(30 AS horizon, 0.8 AS confidence_level)
)
WHERE time_series_type = 'forecast'
ORDER BY date
LIMIT 14;

ML.EXPLAIN_FORECASTでは、トレンドや曜日などの周期的な成分を見られます。実演ではベースとなるトレンドが約2,300で、土日のweekly_effectはおよそマイナス500でした。グラフで見えた下がり方と、週次の成分がつながります。

ただし、このモデルに渡したのは日付と訪問数です。広告費や天気を自動で調べて説明しているわけではありません。そうした要因も扱うなら、追加のデータを用意し、説明変数を使う別のモデル設計を検討します。データを足せば必ず精度が上がるとも限らないので、別期間で評価するところまで必要です。

3．普段と違うアクセスの日を見つける

同じ時系列モデルにML.DETECT_ANOMALIESを使い、普段のパターンから外れた日を抽出します。ここでは新しい表を渡していないため、モデルの学習に使った過去の時系列が対象です。監視や通知を自動設定するSQLではありません。

SELECT
  parsed_date,
  total_visits,
  is_anomaly,
  anomaly_probability,
  ROUND(lower_bound, 0) AS lower_bound,
  ROUND(upper_bound, 0) AS upper_bound
FROM ML.DETECT_ANOMALIES(
  MODEL `bqml_tutorial.ga_arima_model`,
  STRUCT(0.95 AS anomaly_prob_threshold)
)
WHERE is_anomaly = TRUE
ORDER BY anomaly_probability DESC
LIMIT 10;

実演では、通常の上限が約3,279なのに訪問数が4,000を超える日などが出てきました。結果には日付、実測値、上下限、異常の判定が並びます。該当日がなければ0行になります。

異常として出た日は、施策や計測の変化を調べる入口にしましょう。広告を増やした、記事が取り上げられた、計測方法が変わったなど、理由はこの表だけでは決まりません。日付を控え、流入元や実施した施策と突き合わせます。

日々のGA4実績をAIに取得させて、変化したチャネルを調べる流れは、最後のSynapse MCPの章で紹介します。

4．購入見込みを判定し、モデルの得意・不得意を読む

購入したセッションと、しなかったセッションを学習させる

購入した人の動きを知りたいとき、購入した人だけを見るのでは比較できません。僕は、購入しなかった人の動きも一緒に見て、違いを探すことが大事だと考えています。

次のSQLは、購入有無を正解ラベルにした分類モデルを作ります。2016年8月1日〜2017年6月30日を学習用、2017年7月1日〜8月1日を評価用に分けています。is_evalがTRUEの行は、学習から分けて評価に使われます。

CREATE OR REPLACE MODEL `bqml_tutorial.purchase_classifier_fixed`
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['purchased'],
  auto_class_weights = TRUE,
  data_split_method = 'CUSTOM',
  data_split_col = 'is_eval'
) AS
SELECT
  purchased,
  IF(session_date >= DATE '2017-07-01', TRUE, FALSE) AS is_eval,
  device_category,
  channel_grouping,
  source,
  medium,
  country,
  day_of_week,
  new_visit,
  pageviews
FROM `bqml_tutorial.ga_sessions_sample`
WHERE session_date BETWEEN DATE '2016-08-01' AND DATE '2017-08-01';

auto_class_weights = TRUEで、件数が偏ったクラスの重みを自動調整しています。また、ページビュー数も入力しています。来訪前に誰が買うかを当てるモデルではなく、セッション中の行動を含めた購入見込みの判定です。完了したセッションの総ページビューを、購入前の時点で既に分かっていた値として扱わないでください。

実演では作成におよそ1分半かかりました。実行時間はデータ量や環境で変わります。完了後、データセット内にpurchase_classifier_fixedができていることを確認します。

「再現率95％」と「適合率23％」を一緒に読む

ML.EVALUATEを実行すると、モデル作成時に分けた評価データでの指標が返ります。

SELECT
  *
FROM ML.EVALUATE(
  MODEL `bqml_tutorial.purchase_classifier_fixed`
);

• recall（再現率）：実際に購入したセッションを、どれだけ購入として拾えたか。
• precision（適合率）：購入すると予測したセッションのうち、本当に購入していた割合。

実演では、再現率が約95％、適合率が約23％でした。購入者を広く拾う一方で、買わなかったセッションも購入候補に含めています。「95％の確率で買う人を見つけた」という意味ではありません。

候補を広く拾いたいのか、外れの少ない候補に絞りたいのかで、評価する基準を変えましょう。営業や配信の候補として考える場合も、取りこぼしを減らす価値と、外れた候補への対応コストを両方見ます。このサンプルをそのまま配信リストとして利用できるという話ではありません。

混同行列で、拾えた数と外した数を確かめる

SELECT
  *
FROM ML.CONFUSION_MATRIX(
  MODEL `bqml_tutorial.purchase_classifier_fixed`
);

実際の購入は52＋1,022＝1,074件で、そのうち1,022件を拾えています。再現率は1,022÷1,074で約95.2％です。一方、購入予測は3,379＋1,022＝4,401件。その中で実際に購入したのは1,022件なので、適合率は約23.2％になります。

このように件数へ戻すと、モデルの性格が分かりやすいですよね。数値はこのサンプル・この評価期間の結果です。自社でも同じ精度が出ることを示すものではありません。

購入スコアの高いセッションを確認する

ML.PREDICTで、入力したセッションに予測ラベルとスコアを付けます。

SELECT
  predicted_purchased,
  ROUND((
    SELECT prob
    FROM UNNEST(predicted_purchased_probs)
    WHERE label = TRUE
  ), 4) AS purchase_probability,
  device_category,
  channel_grouping,
  source,
  medium,
  country,
  day_of_week,
  new_visit,
  pageviews
FROM ML.PREDICT(
  MODEL `bqml_tutorial.purchase_classifier_fixed`,
  (
    SELECT
      device_category,
      channel_grouping,
      source,
      medium,
      country,
      day_of_week,
      new_visit,
      pageviews
    FROM `bqml_tutorial.ga_sessions_sample`
    WHERE session_date BETWEEN DATE '2017-07-01' AND DATE '2017-08-01'
    LIMIT 1000
  )
)
ORDER BY purchase_probability DESC
LIMIT 15;

このSQLは、対象期間から最大1,000件を取り出した後、その中の上位15件を表示します。入力を絞るLIMIT 1000には並び順の指定がないため、全セッションを比較したランキングでも、毎回同じ対象を選ぶ仕組みでもありません。

また、purchase_probabilityはモデルが出したスコアです。クラスの重みを調整したモデルでもあるので、表示された0.9を「現実に90％の確率で買う」とそのまま受け取らず、実績と照合して使います。

僕の実演では、ページビュー数の多いセッションが上位に並んでいました。モデルはページを多く見ていることを、購入に近い特徴として見ている可能性があります。ただし、ページビューを増やせば購入が増える、という因果関係までは分かりません。

重みから、次に調べる仮説を出す

WITH weights AS (
  SELECT
    processed_input,
    cw.category AS category,
    cw.weight AS weight
  FROM ML.WEIGHTS(
    MODEL `bqml_tutorial.purchase_classifier_fixed`
  ),
  UNNEST(category_weights) AS cw

  UNION ALL

  SELECT
    processed_input,
    CAST(NULL AS STRING) AS category,
    weight
  FROM ML.WEIGHTS(
    MODEL `bqml_tutorial.purchase_classifier_fixed`
  )
  WHERE weight IS NOT NULL
)
SELECT
  processed_input,
  category,
  ROUND(weight, 4) AS weight
FROM weights
WHERE processed_input IN (
  'device_category',
  'channel_grouping',
  'new_visit',
  'pageviews',
  'day_of_week'
)
ORDER BY processed_input, weight DESC;

ML.WEIGHTSでは、このモデルの中で各項目がどちら向きに働いているかを見ます。実演では、ページビューの係数は正、流入チャネルには正負の違いがありました。

ここから「この流入元は購入につながりにくいのでは」「配信先を詳しく見る必要があるのでは」といった仮説を出せます。ただし、モデルはデバイス・流入元・国・ページビューなどを同時に見ています。係数だけで媒体の良し悪しを決めたり、配信停止を決めたりするのは早いです。

重みは、施策の結論を自動で出す答えではなく、次の分析で確かめたい仮説に使えます。まずはチャネル別の購入状況や、流入先のページを確認してみてください。

まとめ：データをためる場所から、予測に使う場所へ

BigQuery MLでは、SQLでモデルを作り、SQLで予測や評価を進められます。今回は、日別訪問数から30日先を予測し、過去の異常な日を抽出し、セッション情報から購入見込みを判定しました。

僕は、この一連の作業がBigQueryの中でつながるところに魅力を感じます。データをためるだけで終わらず、その先の予測や異常検知まで使える分析基盤にできるからです。

一方で、どんなデータを入れるかは自分たちで設計する必要があります。広告費、天気、口コミの量など、事業によって見たい要因も違います。まずは公開サンプルで表の読み方をつかみ、その後に「自社では何を予測したいか」「その時点で使えるデータは何か」を決めてみてください。

手順や仕様をさらに確認する場合は、Google公式のML.FORECAST、ML.DETECT_ANOMALIES、ロジスティック回帰のモデル作成、ML.EVALUATEも参照してください。

Synapse MCPで、気になったGA4の実績をAIに聞く

予測や異常検知で気になる日が見つかったら、次に知りたいのは「どの流入元が変わったのか」「サイトのどこを調べるべきか」ですよね。そのたびにGA4を開いてCSVを作る作業を減らす方法が、僕たちの提供するSynapse MCPです。

Synapse MCPを使うと、普段使っているGA4や広告ツールのデータをAIに聞き、実績の確認から次の分析の相談へ進めます。ここではGA4のデータ取得を使います。BigQuery MLの学習・予測は上で説明したSQLで行い、必要なら取得した結果表をAIに渡して解釈を相談できます。

まず自分のGA4を接続する

1. Synapse MCPへログインし、分析したい店舗・案件の接続管理からGA4を追加します。
2. 対象サイトを閲覧できるGoogleアカウントで認証し、GA4プロパティを選びます。
3. MCPリンクを発行し、利用するAIへ登録・認証します。会話で接続を有効にするまでの画面は、GA4のMCP接続ガイドにまとめています。

日別の実績を取得し、変化した日を絞る

以下は自分のサイトで試すための質問例です。対象と日付を先にそろえ、予測に使うデータとは何を数えているかが違わないかも確認しましょう。

接続済みのGA4プロパティ名とIDを教えてください。複数ある場合は、分析するサイトを私に確認してください。

選んだサイトの2026年9月1日〜9月25日のセッション数を日別に取得し、日付順の表とグラフにしてください。8月1日〜8月25日も同じ条件で取得し、期間合計を比較してください。取得した期間と、データがない日を示してください。

9月が途中なので、前月も25日間にそろえています。AIが返した対象サイト・期間・指標を確認し、GA4の画面でも同じ条件で照合します。グラフは取得した実績をAIが可視化するもので、この依頼だけでBigQuery MLの予測が走るわけではありません。

増減が気になったら、同じ会話で続けて聞けます。

同じGA4プロパティで、2026年9月1日〜25日と8月1日〜25日のセッション数を、セッションのデフォルトチャネルグループ別に比較してください。増減数と増減率を示し、比較元が0なら増減率は算出不可としてください。取得範囲を明示し、原因は事実と仮説に分けて、次に調べる項目を提案してください。

検索流入も、別の数字として確かめる

検索からの流入を調べたいときは、同じサイトのSearch Consoleも接続します。GA4のセッション数と、検索結果でのクリック数は別の指標として並べます。取得の準備はSearch Consoleの接続・分析ガイドをご覧ください。

別の実演でCSVを添付せずにこの表が返ってきたとき、僕は「控えめに言って便利でしかないです」と話しました。普段の会話と同じように、自分たちのデータについて質問を続けられるんですよね。詳しい流れは、画像の出典でもあるGA4の週次レポートをAIで作る方法で紹介しています。

まずは自分のGA4をつなぎ、対象プロパティ名と日別セッション数の表が返るところまで試してみてください。接続先の一覧を見るだけでなく、実際の数値を取得できれば、その会話から次の分析を始められます。

自分のGA4を接続して、日別セッション数をAIに聞く →

新規フリー契約は14日間、3媒体・各媒体1アカウントまでで、自動課金はありません。利用条件と料金を確認してください。利用するAI側の接続機能・プラン・組織設定も別途必要です。