BigQueryで配列関数を利用して値をだす

BigQueryは配列関数をサポートしており、それを使ってセッション数やPV数を出すこともできます。例えばユーザー単位でvisitidを配列にして、そのを数えると、ユーザーごとのセッション数を出すことができます。ARRAY_LENGHはドキュメントを見ると「配列のサイズを返します」と書いてあります。配列の中にnullが含まれると、nullもカウントします。

　SELECT
　　fullvisitorid,
　　ARRAY_AGG(visitid) AS visitid,
　　ARRAY_LENGTH(ARRAY_AGG(visitid)) AS session_count
　FROM
　　 `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　WHERE
　　_table_suffix between 'yyyymmdd' and 'yyyymmdd'
　GROUP BY
　　1　　
　;

これを一般公開のGoogleアナリティクスデータで実行すると以下のような結果が返ってきます。

同様に配列関数を使ってセッション中のPV数と訪問別PV数をを出してみます。

　SELECT
　　visitid,
　　ARRAY_AGG(hits.page.pagepath) AS pagepath,
　　ARRAY_LENGTH(ARRAY_AGG(hits.page.pagepath)) AS pageviews,
　　ARRAY_LENGTH(ARRAY_AGG(DISTINCT hits.page.pagepath)) AS unique_pageviews
　FROM
　　`{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`,
　　UNNEST(hits) AS hits
　WHERE
　　_table_suffix BETWEEN 'yyyymmdd' AND 'yyyymmdd'
　GROUP BY
　　1　　
　;

 UNNEST関数でhits.page.pageapathをフラットにした上で、ARRAY_AGG関数で配列に戻していますので、通常はこのようにする必要はまったくないと思いますが、ご参考まで。

一般公開データのGoogleアナリティクスデータを使ってクエリを実行すると下のような結果が返ってきます。

BigQueryでGoogleアナリティクスの集計値を簡単なクエリでだす

BigQueryに送られるGoogleアナリティクスのデータには、関連するフィールドをグループ化してスキーマを広げることができるデータ型があります。そのことを構造体というらしく、概念的には、構造体はメインテーブルに事前に結合された別テーブルのようなものだということです。

BigQueryのGoogleアナリティクスには32個の構造体があり、それを利用すると以下のように簡単に集計値を取得することができます。

　select
　fullvisitorid,
　totals.*
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　where _table_suffix between 'yyyymmdd' and 'yyyymmdd'
　;

 マーケターにはこの辺りが難解なのですが、詳しく知りたい方はQwiklabsのこちらに詳しく書いてあります。

BigQueryでGoogleアナリティクスのトランザクションの間隔をだす

「Googleアナリティクスのセッションの間隔をだす」でセッションの間隔を出したのですが、トランザクションにも応用できるのでポストします。

　select
　round(avg(interval_session),2) as avg_interval_session
　from
　(select
　fullvisitorid,
　paese_date('%Y%m%d', date) as date,
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc) as date2,
　date_diff(parse_date('%Y%m%d', date),
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc),day) as interval_session
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　where _table_suffix between 'yyyymmdd' and 'yyyymmdd')
　and totals.transactions is not null
　;

 トランザクションの間隔をだすには、トランザクションが発生したセッションだけに絞る必要がありますので、where句で「totals.transactions is not null」を書いています。

セッションの間隔で示したクエリと違うのはその部分だけです！

BigQueryでGoogleアナリティクスのセッションの間隔をだす

Googleアナリティクス上ではユーザーあたりのセッション数はでていますが、セッション間の間隔は集計されていませんのでそれを出したいと思います。

　select
　round(avg(interval_session),2) as avg_interval_session
　from
　(select
　fullvisitorid,
　parse_date('%Y%m%d', date) as date,
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc) as date2,
　date_diff(parse_date('%Y%m%d', date),
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc),day) as interval_session
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　where _table_suffix between 'yyyymmdd' and 'yyyymmdd')
　;

 Googleアナリティクスがら送られたdateは文字列になっているので、PARSE_DATE関数を使ってdateを文字列からDATE型に変換しています。

セッション日の間隔を取るためにはLEAD関数を使い、日付を降順にして前のセッションの日付を取得しdate2というカラムに格納しています。

そして、DATE_DIFF関数でdateとdate2の差を取っています。ここまでのクエリを実行するとこのような表になります。（一般公開のGoogleアナリティクスデータを使用して、スプレッドシートに書き出したものです）

最上部のピンク色のユーザーは7/3と7/1に訪問があり、LEAD関数によってdate2に7/1が格納されています。そしてDATE_DIFF関数でその差分の2が計算できています。青のユーザーはセッション日が同じでしたので差は0です。塗りつぶしていないユーザーは1セッションのみでしたのでdate2はnullになっています。

そして、interval_sessionの平均を計算すればセッションの間隔の平均を求めることができます。AVG関数はnullを無視しますので、問題なく平均を求めることができます。

BigQueryで訪問回数ごとのユーザーCVRをだす

「訪問回数ごとのユーザー分布を集計する」でユーザーの訪問回数ごとの分布をだしました。このエントリーでは、その分布にコンバージョンしたユーザー数を追加して、訪問回数単位のユーザーコンバージョン率を出したいと思います。

手順としてはコンバージョンしたユーザーのテーブルと、訪問回数ごとのユーザー分布テーブルをJOINしてそれを集計することになります。

下は前述のエントリーのクエリですが、コンバージョンに関する内容はありません。

　select
　session_count,
　count(distinct fullvisitorid) as users
　from
　(select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　where totals.visits is not null
　order by 1,2)
　group by 1
　order by 1
　;

このクエリにコンバージョンしたユーザーテーブルをJOINして集計したのがこちらです。

　select
　session_count,
　count(distinct fullvisitorid) as users
　count(distinct cv_fullvisitorid) as cv_user,
　round(count(distinct cv_fullvisitorid) / count(distinct fullvisitorid),3)*100 as cvr
　from
　(select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_日付}`
　left join
　(select
　fullvisitorid as cv_fullvisitorid
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_日付}`
　where totals.transactions is not null)
　on fullvisitorid = cv_fullvisitorid)
　group by 1
　order by 1
　;

最下部のselect内でコンバージョンしたユーザーだけのテーブルを作成しています。それを訪問回数を集計するテーブルにJOINしています。ここまでのクエリを実行してスプレッドシートにエクスポートすると下のような表ができます。コンバージョンしたfullvisitoridがcv_fullvisitoridに格納されています。

この表を最上部のselect内で集計しているのですが、一般公開のGoogleアナリティクスデータの2017年8月1日のテーブルを実行した結果が下のようになります。

おそらく実際のデータでも同じように訪問回数が多くなるほど、CVRが高い結果になるのではないでしょうか。モチベーションの高いユーザーほどサイト訪問の頻度が上がりコンバージョンにつながるという当たり前の結果ではありますが、マーケ施策やサイト上のコミュニケーションを検討する上で一つの示唆になるのではないかと思います。

BigQueryによるLTV算出とABC分析

Googleアナリティクスでライフタイムバリューのメニューができて久しいですが、BigQueryで顧客ごとのLifeTimeValueの算出とABC分析を行いたいと思います。

LTVの算出

ユーザー単位での一定期間の売り上げ総額を出すのは比較的簡単です。

　select
　fullvisitorid,
　sum(cast(product.productrevenue as numeric))/1000000 as sales
　FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
　,unnest(hits) as hits
　,unnest(product) as product
　group by 1
　having sum(product.productrevenue)/1000000 <> 0.0
　order by 2 desc
　;

 実行すると売上の多い順にfullvisitoridが並びます。収益（productrevenue）はGAデータの値に10の6乗を掛けた数値がBigQueryに格納されますので、1000000で割っています。上のクエリでは一般公開のGoogleアナリティクスのサンプルデータを使っており、売上はドルです。よって少数を扱えるようにデータをNUMERICに変換しています。

having句は売上のないユーザーを除外するために書いています。ABC分析の際に必要です。

（クエリ消費を少なくするために、2017年の7月だけのデータを使っています）

ABC分析

どのユーザーからいくら売り上げているか分かったので、マーケティング予算の配分等に使えそうですが、上位10%の顧客で全売上の何パーセントを占めているかも確認したいと思います。

　select
　case when row_number < 3788 then '1-10'
　　　when row_number < 7576 then '11-20'
　　　when row_number < 11364 then '21-30'
　　　when row_number < 15152 then '31-40'
　　　when row_number < 18940 then '41-50'
　　　when row_number < 22728 then '51-60'
　　　when row_number < 26516 then '61-70'
　　　when row_number < 30304 then '71-80'
　　　when row_number < 34092 then '81-90'
　　　else '91-100' end as rank,
　sum(sales) as total_sales,
　round(sum(sales) / 28455109.858,3) as sales_ratio
　from
　(
　select
　fullvisitorid,
　sum(cast(product.productrevenue as numeric))/1000000 as sales,
　row_number()
　over(order by sum(product.productrevenue)/1000000 desc ) as row_number
　FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
　,unnest(hits) as hits
　,unnest(product) as product
　group by 1
　having sum(product.productrevenue)/1000000 <> 0.0
　order by 2 desc)
　group by rank
　order by 1
　;

 LTVの算出用クエリにrow_number関数で売上ランキングを1位から順にふって、row_numberというカラムに格納しています。それを使ってユーザーを10グループに分かるのですが、売上のあった顧客の数の37,886を予め計算しておき、それを10分割します。37,886÷10=3,788.6 なので、上位3788が上位10%のグループ、次の7676までが11-20％のグループというようにcase文を書きます。このクエリでは91-100のグループだけ6ユーザー多くなっています。

そして、sum(sales) as total_salesでランクごとの合計売上を算出し、
round(sum(sales) / 28455109.858,3) as sales_ratioでランク毎の売上構成を計算しています。分母の全ユーザーの売上（28455109.858）も予め計算したものです。

これを実行すると下のように結果をかえしてくれます。

パレードの法則（80:20）のようにはなっていませんが、上位20%の顧客で売上の65%を占めていることがわかりますので、顧客構造が可視化されマーケ予算の再配分等に使えそうです。

BigQueryMLでkmeansモデルを使って予測をする

kmeansによるクラスタリングのエントリーでBigQueryMLを使って、サイト訪問者のクラスタリングを行いました。クエリ自体は凝ったものではなく、サイトに訪問するユーザーのデバイス・ブラウザ・アクセス場所等の情報でクラスタリングしていますので、実務ではサイト特性に合わせて取得する値を工夫する必要があると思います。

こちらはそのエントリーでモデル構築したクエリです。

　create model
　`{bigqueryプロジェクトID}.{データセット}.{書き出す先}`
　options(model_type='kmeans',num_clusters = 5)　as　
　select
　fullvisitortid,
　visitnumber,
　device.devicecategory,
　device.browser,
　device.operatingsystem,
　geoNetwork.city,
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_*`
　where _table_suffix between '20200101' and '20200131'
　;

これでモデルが指定したデータセット内に書き出されました。今度はそのモデルを使って各ユーザーがどのクラスタに分類されるのかをML.PREDICT関数を使って予測したいと思います。

　select * from
　ml.predict(model `{bigqueryプロジェクトID}.{データセット}.{書き出されたモデル}`,
　(select
　fullvisitorid,
　visitnumber,
　device.devicecategory,
　device.browser,
　device.operatingsystem,
　geoNetwork.city,
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`))
　;

 2番目のselect以下のサブクエリはモデルに対する入力テーブルです。入力テーブルにはモデル構築で利用したカラムと同じものを含める必要があります。

このクエリを実行すると、
CENTROID_ID、
NEAREST_CENTROIDS_DISTANCE.CENTROID_ID、
NEAREST_CENTROIDS_DISTANCE.DISTANCE
がfullvisitorid毎に出力されます。CENTROID_IDは予測されたクラスタになり、NEAREST_CENTROIDS_DISTANCE.DISTANCEはクラスタまでの距離を示しています。その距離の近い順番にNEAREST_CENTROIDS_DISTANCE.CENTROID_IDが配列で出力されます。

実務で活用するなら、商品の購入者を考えられる特徴量でクラスタリングし、購入してないユーザーがどのクラスタに属するか予測してサイト上でコミュニケーションしたり、マーケ施策への活用が考えられそうです。