BigQueryでユーザーの購入日から遡って行動を集計する

Googleアナリティクスデータでも、どんなデータでもそうですがデータを集計する期間を例えば2020年1月1日から1月31日までとすると、当たり前ですがその期間のすべての数値を集計することになります。

BQMLなりAutoMLで購入予測をしたいときに、前述のような期間指定の方法で購入者の行動を集計すると場合によってはノイズ的な数値を含む可能性があります。例えばWebトラフィックを使って購入者の教師データを作るケースを考えてみたいのですが、あるユーザーが下記のようにサイトに訪問したとします。

1/ 4 　検討段階

1/10　検討段階

1/12　検討段階

1/13　トランザクション

1/15 　予約・購入の確認

1/20　 メルマガにあったキャンペーンを見たくてたまたま訪問

私たちが普段オンラインでモノを買うときに、よくある行動だと思います。Googleアナリティクスのユーザーエクスプローラでトランザクションしたユーザーを見ると、前述のように行動しているケースはとても多いのではないでしょうか？

先程の話に戻りますが、購入を予測したい教師データを作成するときはトランザクションに至る検討段階の行動をベースにしたテーブルが必要です。購入していないユーザーは上のパターンでは1/15のサイト訪問はあり得ないですし、検討が終わった1/20の訪問もノイズになりかねません。

よって集計期間を1月1日から1月31日とするのではなく、トランザクションから遡って検討期間だけ集計する必要がありますので、そのクエリを書いていきたいと思います。

　WITH
　　yoyaku AS (
　　SELECT
　　　fullvisitorid,paese_DATE('%Y%m%d', date) AS txn_date,
　　FROM
　　　`bigquery-public-data.google_analytics_sample.ga_sessions_*`
　　WHERE
　　　_table_suffix BETWEEN '20170701' AND '20170731'
　　　AND totals.transactions IS NOT NULL),
　　kentou AS (
　　SELECT
　　　fullvisitorid,paese_DATE('%Y%m%d', date) AS date,visitid,
　　　COUNT(totals.pageviews) AS PV_count,
　　FROM
　　　`bigquery-public-data.google_analytics_sample.ga_sessions_*`
　　WHERE
　　　_table_suffix BETWEEN '20170701' AND '20170731'
　　　AND totals.visits IS NOT NULL
　　GROUP BY 1,2,3 )
　SELECT
　　fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
　　visitid, PV_count
　FROM
　　kentou
　LEFT JOIN
　　yoyaku
　USING (fullvisitorid)
　WHERE txn_date IS NOT NULL
　ORDER BY　 1,3　　
　;

 （一般公開のGoogleアナリティクスのサンプルデータを使っています。）

WITH句で2つのテーブルを作成しています。yoyakuにはユーザー単位で予約日を格納しており、WHERE句で予約したレコードのみに絞っています。kentouはユーザーのサイト訪問全ての日とvisitid、PV数を集計しています。

その2つをfullvisitoridをキーにJOINして実行すると以下のように、予約日(txn_date)とすべてのサイト訪問日(date)のカラムができています。また、DATE_DIFF関数で予約日と訪問日の差分を取っています。

上のユーザーでは7月18日にトランザクションが発生していますが、7月24日にも訪問していますのでそれを除外して予約日までの訪問回数も計算したいと思います。尚、GoogleアナリティクスのサンプルデータはPVは1しか入っていません。

　WITH
　　yoyaku AS (
　　SELECT
　　　fullvisitorid,parse_DATE('%Y%m%d', date) AS txn_date,
　　FROM
　　　`bigquery-public-data.google_analytics_sample.ga_sessions_*`
　　WHERE
　　　_table_suffix BETWEEN '20170701' AND '20170731'
　　　AND totals.transactions IS NOT NULL),
　　kentou AS (
　　SELECT
　　　fullvisitorid,parse_DATE('%Y%m%d', date) AS date,visitid,
　　　COUNT(totals.pageviews) AS PV_count,
　　FROM
　　　`bigquery-public-data.google_analytics_sample.ga_sessions_*`
　　WHERE
　　　_table_suffix BETWEEN '20170701' AND '20170731'
　　　AND totals.visits IS NOT NULL
　　GROUP BY 1,2,3 )
　SELECT
　　fullvisitorid,
　　COUNT(visitid) AS total_sessions,
　　COUNT(PV_count) AS total_PV
　FROM (
　　　SELECT
　　　　fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
　　　　visitid, PV_count
　　FROM
　　　　kentou
　　LEFT JOIN
　　　　yoyaku
　　USING (fullvisitorid)
　　WHERE　　txn_date IS NOT NULL
　　ORDER BY　1,3)　　
　WHERE
　　date <= txn_date
　　AND date_diff <= 10
　GROUP BY 1
　;

 最初に示した結果をサブクエリにして、セッション数とPV数をカウントしていますが、最後のWHRER句が肝心のところです。まず、「date <= txn_date」で予約日より前のレコードに絞った上で、何日遡るのかを「date_diff <= 10」で指定しています。トランザクション日から20日遡りたければ20にすればいいわけです。

このようにすれば予約前だけに絞ってユーザーの行動を集計することができるようになります。